Data Engineering Foundations

What data engineers do

A data engineer builds and runs the systems that move data from where it is produced to where it is useful, in a shape people can trust. Application developers produce data as a side effect (an order row, a click event, a log line). Analysts, data scientists, and ML models consume it. The data engineer owns the path in between:

• Ingestion: pulling from databases, APIs, files, and event streams.
• Storage: choosing formats, layouts, and systems (object storage, warehouses).
• Transformation: cleaning, deduplicating, joining, and aggregating.
• Modeling: designing tables that answer business questions quickly and correctly.
• Orchestration: scheduling jobs, handling dependencies, retries, and backfills.
• Quality and observability: tests, validation, monitoring, lineage, and alerting.
• Serving: exposing data to BI tools, notebooks, APIs, and feature stores.

The job is less about clever algorithms and more about reliability. A pipeline that is correct 99% of the time silently corrupts 1% of decisions. So data engineers obsess over properties you will meet throughout Part 9: idempotency (running twice gives the same result), determinism, incremental processing, and observability.

The data lifecycle

Data moves through a recognizable lifecycle. Naming the stages helps you ask the right question at each one.

flowchart LR
  G[Generate
apps, devices, logs] --> I[Ingest
batch or stream]
  I --> S[Store
raw / bronze]
  S --> T[Transform
clean / silver]
  T --> M[Model
marts / gold]
  M --> U[Serve
BI, ML, APIs]
  U -. feedback .-> G
    

Many teams call the storage layers bronze (raw, exactly as received), silver (cleaned and conformed), and gold (business-level aggregates and models). Keeping the raw layer unchanged is a key professional habit: when you find a bug in a transformation, you can fix the code and replay from raw instead of asking the source to resend months of data.

Remember: data engineering is the reliable movement and shaping of data; keep raw data immutable so you can always replay.

OLTP vs OLAP

Databases serve two very different kinds of work.

The deep reason they differ is storage layout. A row store keeps each row's values together, so fetching one whole order is one contiguous read. A column store keeps each column's values together, so summing amount over a billion rows reads only that column, and similar values sitting next to each other compress extremely well.

Pure Python exaggerates nothing here: real column stores add vectorized execution and compression on top, which is why DuckDB can scan hundreds of millions of rows per second on a laptop (you'll try it in M61).

Remember: OLTP = many tiny row operations; OLAP = few huge column scans. The layout follows the workload.

ETL vs ELT, batch vs streaming

ETL (extract, transform, load) cleans and reshapes data before loading it into the warehouse, usually in a separate processing engine (a Python job, Spark). ELT (extract, load, transform) loads raw data first and transforms it inside the warehouse with SQL (often managed with dbt). ELT became popular because cloud warehouses are powerful and cheap to scale, and keeping raw data in the warehouse makes replays easy.

Batch vs streaming

Batch processing handles a bounded chunk of data at a time: "every night, process yesterday's files". It is simple, cheap, easy to re-run, and its latency is the batch interval. Streaming processes an unbounded sequence of events continuously, one by one or in tiny micro-batches, so results are seconds old instead of hours. It costs more complexity: ordering, late events, state, and exactly-once guarantees (M63 covers these).

Step through the two visualizers below with the same twelve records. In batch mode each stage finishes the whole batch before the next starts, so the first row reaches the target late. In streaming mode every record moves on as soon as a stage is done with it, so all stages work at once. Compare the tick at which the first row lands. Invalid records are parked in a dead-letter queue instead of crashing the run.

Remember: ETL transforms before loading, ELT after; batch trades latency for simplicity.

Data modeling for analytics: star schemas

An OLTP schema is normalized into many small tables to avoid update anomalies. Analysts would need ten joins to answer "revenue by product category by month". Dimensional modeling (popularized by Ralph Kimball) reorganizes data around business processes into two kinds of tables:

• Fact tables record measurable events: a sale, a ride, a click. They hold measures (numbers you aggregate: amount, duration, quantity) and foreign keys to dimensions. They are long and narrow and grow forever.
• Dimension tables describe the context of an event: who, what, where, when. They hold descriptive attributes you filter and group by (customer segment, city, day of week). They are short and wide.

Arrange one fact table in the middle with dimensions around it and you get a star schema. If dimensions are themselves normalized into sub-dimensions (city → region → country), it becomes a snowflake schema; the star is usually preferred for its simpler joins.

erDiagram
  DIM_DATE ||--o{ FACT_RIDE : "date_key"
  DIM_RIDER ||--o{ FACT_RIDE : "rider_key"
  DIM_DRIVER ||--o{ FACT_RIDE : "driver_key"
  DIM_ZONE ||--o{ FACT_RIDE : "pickup_zone_key"
  FACT_RIDE {
    text ride_id PK
    int date_key FK
    int rider_key FK
    int driver_key FK
    int pickup_zone_key FK
    real distance_km
    real fare
    int duration_s
  }
  DIM_DATE {
    int date_key PK
    text date
    int month
    text weekday
    int is_weekend
  }
  DIM_RIDER {
    int rider_key PK
    text rider_id
    text segment
  }
  DIM_DRIVER {
    int driver_key PK
    text driver_id
    text vehicle_type
  }
  DIM_ZONE {
    int zone_key PK
    text zone_name
    text city
  }
    

Grain: the most important decision

The grain of a fact table is exactly what one row represents: "one row per ride", "one row per order line", "one row per store per day". Declare it first, in words, before choosing columns. Mixing grains in one table (some rows per order, some per order line) is the classic cause of double-counted revenue.

Surrogate keys (rider_key) are integers the warehouse assigns; natural keys (rider_id) come from the source system. Facts reference surrogate keys so dimensions can keep history, which brings us to slowly changing dimensions.

Slowly changing dimensions

Dimension attributes change: a customer moves city, a product is recategorized, a station is renamed. How you handle the change determines what historical reports say. The two types you must know:

There are other types (type 3 keeps a "previous value" column; type 6 combines 1, 2, and 3), but types 1 and 2 cover almost all real work. Remember: declare the grain first; facts hold measures at that grain; type 2 dimensions preserve history with versioned rows.

File formats compared

Data at rest lives in files far more often than you might expect: in object storage, in data lakes, in exports between teams. The format decides how fast you can read it, how small it is, whether types survive the round trip, and whether the schema can evolve.

Parquet wins for analytics because it combines a columnar layout (read only needed columns), per-column encodings (dictionary encoding turns repeated strings into small integers; run-length encoding collapses repeats), compression, and min/max statistics per row group that let readers skip whole chunks ("predicate pushdown"). Arrow is the in-memory twin: Parquet is how columnar data sleeps on disk; Arrow is how it is awake in RAM.

Remember: CSV and JSON Lines for interchange and raw landing; Avro for row-oriented streaming records; Parquet (or ORC) for analytics at rest; Arrow in memory.

Compression

Compression trades CPU time for fewer bytes on disk and over the network. In data systems, I/O is usually the bottleneck, so a good codec often makes jobs faster, not just smaller. The codecs you will meet:

Repetitive structured data (field names, a few distinct values) compresses many times over; random bytes do not compress at all. That is why columnar formats, which put similar values next to each other, compress so well.

Remember: zstd is the default choice today; snappy/LZ4 when CPU is the bottleneck; avoid gzipped CSV for large files you need to read in parallel.

Partitioning

A table of a billion rows stored as one file forces every query to read everything. Partitioning splits data into directories by the value of one or more columns, so a query filtering on those columns reads only the matching directories ("partition pruning"). The near-universal convention is Hive-style paths, where each directory level is column=value:

s3://spokes-lake/trips/year=2026/month=03/day=05/part-0000.parquet
s3://spokes-lake/trips/year=2026/month=03/day=06/part-0000.parquet
s3://spokes-lake/trips/year=2026/month=03/day=06/part-0001.parquet

Spark, DuckDB, Polars, pyarrow, Athena, and Trino all read these paths and turn the directory names back into columns. A query with WHERE year = 2026 AND month = 3 AND day = 6 touches one directory.

Remember: partition by what you filter on, keep partitions coarse enough for big files, and use column=value directory names.

Schemas and schema evolution

A schema is the declared structure of data: field names, types, nullability, and sometimes constraints. Schema-on-write (warehouses, Parquet, Avro) checks data when it is written; schema-on-read (raw JSON in a lake) stores anything and interprets it when read, which is flexible but pushes errors downstream.

Sources change over time. Schema evolution is the discipline of changing a schema without breaking the people on either side:

• Backward compatible: consumers using the new schema can read data written with the old one. Adding a field with a default is backward compatible; adding a required field without a default is not (old records lack it).
• Forward compatible: consumers using the old schema can read data written with the new one. Adding an optional field is forward compatible (old readers ignore it); removing a required field is not.
• Breaking: renaming a field, or changing its type incompatibly (string → int), breaks both directions unless the reader maps it explicitly.

Remember: add fields with defaults, never repurpose a name, and treat a type change as a new field.

Lakes, warehouses, and lakehouses

Open table formats give you: ACID commits (readers never see half-written data), time travel (query the table as of version 42 or last Tuesday), schema evolution, efficient MERGE/upsert and deletes (important for GDPR), and hidden partitioning (Iceberg derives partitions from columns so queries don't need to know the layout). Many engines (Spark, Trino, DuckDB, Snowflake, Polars) read them, so storage is decoupled from compute.

flowchart TB
  subgraph Lakehouse
    direction TB
    E1[Spark] --> TF
    E2[DuckDB / Polars] --> TF
    E3[Trino / Snowflake] --> TF
    TF[Table format metadata
Delta log / Iceberg snapshots] --> F[(Parquet files in object storage)]
  end
    

Remember: warehouse = managed and governed; lake = cheap files; lakehouse = files plus a table format that adds transactions, time travel, and evolution.

Data contracts

Most pipeline breakages are not bugs in the pipeline: an upstream team renamed a column, started sending amounts in cents, or emptied a field. A data contract is an explicit, versioned agreement between a data producer and its consumers, stating:

• the schema (names, types, nullability) and semantics ("amount is in USD dollars, tax included");
• quality expectations (uniqueness of order_id, allowed values, freshness: "lands by 02:00 UTC");
• ownership and support (who to page) and the change process (deprecation windows, versioning).

Contracts work when they are enforced in code, usually in the producer's CI and at the pipeline's front door.

In M62 you will meet the libraries that do this at scale (pandera, Great Expectations, pydantic). Remember: a contract turns "someone changed something" from a 3 a.m. surprise into a failed check in the producer's pull request.

Practice

from collections import Counter

def grain_violations(rows, grain):
    counts = Counter(tuple(row[c] for c in grain) for row in rows)
    return sorted(key for key, n in counts.items() if n > 1)

def partition_path(record, keys):
    return "/".join(f"{k}={record[k]}" for k in keys)

def parse_partitions(path):
    out = {}
    for segment in path.split("/"):
        if "=" in segment:
            name, _, value = segment.partition("=")
            out[name] = value
    return out

def apply_scd2(dim, snapshot, as_of):
    added = 0
    for sid in sorted(snapshot):
        name = snapshot[sid]
        current = next((r for r in dim if r["id"] == sid and r["current"]), None)
        if current is not None and current["name"] == name:
            continue
        if current is not None:
            current["valid_to"] = as_of
            current["current"] = False
        key = max((r["key"] for r in dim), default=0) + 1
        dim.append({"key": key, "id": sid, "name": name, "valid_from": as_of, "valid_to": None, "current": True})
        added += 1
    return added

def backward_problems(old, new):
    problems = []
    for name, spec in new.items():
        if name not in old:
            if "default" not in spec:
                problems.append(f"added_without_default:{name}")
        elif old[name]["type"] != spec["type"]:
            problems.append(f"type_changed:{name}")
    return sorted(problems)

import sqlite3

def load_star(conn, sales):
    conn.executescript("""
    CREATE TABLE dim_product (product_key INTEGER PRIMARY KEY, name TEXT UNIQUE, category TEXT);
    CREATE TABLE fact_sale (sale_id TEXT PRIMARY KEY, product_key INTEGER, amount REAL);
    """)
    conn.executemany("INSERT OR IGNORE INTO dim_product (name, category) VALUES (?, ?)",
                     [(s["product"], s["category"]) for s in sales])
    conn.executemany("INSERT INTO fact_sale SELECT ?, product_key, ? FROM dim_product WHERE name = ?",
                     [(s["sale_id"], s["amount"], s["product"]) for s in sales])
    conn.commit()

def revenue_by_category(conn):
    return conn.execute("""
        SELECT p.category, SUM(f.amount) AS total
        FROM fact_sale f JOIN dim_product p ON p.product_key = f.product_key
        GROUP BY p.category ORDER BY total DESC
    """).fetchall()

Mini-project: a ride-sharing star schema

pixi init rideco && cd rideco
pixi add python pandas pytest
pixi run python make_data.py     # write the three CSVs with a fixed random seed
pixi run python load.py          # build rideco.db
pixi run pytest

Quiz

Recap

• Data engineers build reliable paths from producers to consumers: ingest, store, transform, model, serve, observe.
• OLTP handles many small row operations; OLAP scans columns. Column stores compress well and read only what they need.
• ETL transforms before loading; ELT loads raw data and transforms in the warehouse. Start with batch unless decisions need fresh data.
• Star schemas put measures in facts at a declared grain and context in dimensions; SCD type 2 preserves history.
• Parquet is the analytics default; Avro for row-based streams; Arrow in memory; zstd is the modern codec (in the 3.14 stdlib).
• Partition by date-like columns you filter on, using column=value paths, without creating tiny files.
• Evolve schemas compatibly, use table formats for lakehouse guarantees, and enforce data contracts in code.

Going further

• compression.zstd and csv in the Python docs
• Apache Parquet documentation and Apache Arrow for Python
• Apache Iceberg and Delta Lake documentation
• Kimball and Ross, The Data Warehouse Toolkit (dimensional modeling)
• Reis and Housley, Fundamentals of Data Engineering
• Next: M61 · Analytical SQL & DuckDB