AWS Learning Journey

OLTP, OLAP, ETL, ELT: Data Engineering Fundamentals for Cloud Engineers

Structured vs. semi-structured data, batch vs. streaming, data lakes, warehouses, and lakehouses, partitioning, file formats, schema evolution, data quality, lineage, and governance — the vocabulary behind every data platform.

8 min read · 1531 words
Data EngineeringAWSBig DataData LakeAnalytics
OLTP, OLAP, ETL, ELT: Data Engineering Fundamentals for Cloud Engineers cover image

AWS from Zero to Production — Phase 9, Part 1 of 11 · See the full series roadmap


The product manager asks a simple question: “How many orders did we get from returning customers last month, by region?”

The orders database knows the answer. But running that query on the production database at 10 a.m. slows down checkout for everyone. The customer data lives in a different system. The region mapping is in a spreadsheet. And half the numbers disagree with the finance report.

That’s the problem data engineering solves: getting data out of the systems that run the business and into a shape that helps people understand it — reliably, securely, and at scale.

This is the start of the longest phase in the series, and the one this whole path has been building toward. Let’s begin with the vocabulary.

What you’ll walk away with


Three kinds of data

Modern platforms handle all three. Structured and semi-structured data feed analytics. Unstructured data increasingly feeds machine learning and generative AI (Phase 11).


OLTP vs. OLAP

OLTP — online transaction processing

The databases that run the business:

OLAP — online analytical processing

The systems that analyze the business:

OLTP: SELECT * FROM orders WHERE order_id = 'A17';         -> 1 row, milliseconds
OLAP: SELECT region, SUM(total) FROM orders GROUP BY region; -> billions of rows scanned

Rule: Don’t run heavy analytics on your production OLTP database. Copy the data to an analytics system — that’s what data pipelines are for.


Batch vs. stream processing

Batch

Streaming

Most organizations need both. Start with batch unless the business genuinely needs fresh data in seconds — fraud detection, real-time dashboards, alerting.


ETL vs. ELT

ETL — extract, transform, load

Source -> extract -> TRANSFORM (on a processing engine) -> load -> Warehouse

Data is cleaned and shaped before it lands in the target. Traditional, and still common when transformations are heavy or sensitive data must be removed early.

ELT — extract, load, transform

Source -> extract -> load raw -> Lake or Warehouse -> TRANSFORM with SQL inside it

Raw data lands first; transformations run inside the warehouse or lake, usually in SQL. Popular because storage is cheap, compute is elastic, and raw data stays available for reprocessing. Tools like dbt follow this model.

In practice, most platforms mix both.


Data lakes, warehouses, and lakehouses

Data lake

Data warehouse

Lakehouse

On AWS: S3 for storage, the Glue Data Catalog for metadata, Lake Formation for permissions, and Athena, Redshift, EMR, and Glue as engines — all sharing one copy of the data.


Data partitioning

Partitioning splits a dataset into folders by a column — usually date:

s3://acme-lake/curated/orders/order_date=2026-10-29/part-0000.parquet
s3://acme-lake/curated/orders/order_date=2026-10-30/part-0000.parquet
s3://acme-lake/curated/orders/order_date=2026-10-31/part-0000.parquet

A query with WHERE order_date = '2026-10-31' reads one folder instead of all of them. That’s partition pruning, and on Athena it directly cuts cost — you pay per byte scanned.

Choose partition keys that:


Data formats

Row-oriented

Columnar

Same 10 GB of CSV:
  as Parquet + Snappy    ~1–2 GB
  query 2 of 30 columns  reads a fraction of that

Rule of thumb: Land raw data in whatever format the source gives you. Convert to Parquet (or Iceberg tables backed by Parquet) for anything people query.


Schema evolution

Sources change. Columns get added, renamed, or change type. Schema evolution is how your pipeline copes:

Protect yourself with:


Data quality

Bad data is worse than no data: it produces confident, wrong answers.

Common checks:

Run checks in the pipeline, and decide what happens on failure: stop, quarantine bad rows, or alert and continue. AWS Glue Data Quality does this natively (Part 3).


Data lineage

Lineage answers: “Where did this number come from?”

orders (RDS) -> DMS -> raw/orders -> Glue job -> curated/orders -> Redshift -> revenue dashboard

Lineage helps you:


Data governance

Governance is the set of rules and controls around data: who owns it, who can access it, how long it’s kept, and how it’s protected.

Governance done well enables access — people can find and trust data quickly. Done badly, it’s either a free-for-all or a wall.


The data platform you’ll build in this phase

Source database (RDS)
    |
    v
AWS DMS
    |
    v
Amazon S3 raw zone
    |
    v
AWS Glue ETL
    |
    v
Amazon S3 curated Parquet zone
    |
    +----> Amazon Athena
    |
    +----> Amazon Redshift
    |
    v
CloudWatch monitoring and SNS alerts

Each part of this phase covers one piece. Part 11 builds it end to end.


Common mistakes


Key takeaways


Quick self-check

Test yourself before moving on:

  1. Which is better for “sum revenue by region over three years”: OLTP or OLAP?
  2. What’s the main difference between ETL and ELT?
  3. Why does partitioning reduce Athena costs?
  4. Why is Parquet preferred over CSV for analytics?

Answers

  1. OLAP.
  2. Where transformation happens: before loading (ETL) or inside the target after loading (ELT).
  3. Queries filtered on the partition column scan less data, and Athena charges per byte scanned.
  4. It’s columnar and compressed, so queries read only the columns they need.

What’s next

In Part 2, we design the foundation: a data lake on Amazon S3 — zones, prefix design, partitioning, file formats, compression, the small-file problem, lifecycle, encryption, cross-account access, and event-driven pipelines.

What’s your team’s biggest data headache today? Tell me in the comments.


Share this article

LinkedIn post draft