Design a Batch Ingestion + Warehouse Pipeline

Before a company streams anything, it batches. The first data pipeline almost every business builds is the same shape: an operational database that runs the business by day, copied every night into a warehouse that explains the business by morning. It is unglamorous and it is everywhere, and getting it right is the foundation the fancier pipelines are later measured against. Get it wrong and the CFO opens a dashboard to a revenue number that quietly disagrees with the ledger.

PhoenixWorld runs on Postgres: orders, order items, customers, products. The analysts want a morning dashboard - yesterday's revenue, orders by category, and how last month's new customers are coming back to buy again. Priya's first pipeline is one line: SELECT * from each table every night, drop it into the warehouse, rebuild the reports. It works for a quarter. Then the orders table crosses a few hundred million rows, the nightly full copy runs into business hours, and a refund posted this morning against last week's order makes the two dashboards that pull the same number show two different totals.

The problem is not that batch is wrong. Daily freshness is exactly what these dashboards need. The problem is that a naive batch load ignores everything that makes warehouse data correct: how you extract only what changed without missing rows, how you rerun a failed night without double-counting, how you model a customer whose segment changed halfway through the quarter, and how you keep "yesterday" from silently rewriting itself. This chapter is about all of that.

Step 1 - Understand the Problem and Establish Design Scope

"Copy the database into a warehouse" hides a dozen decisions. The clarifying questions surface them before you draw a single box.

Candidate: What do the analysts actually need to answer?

Interviewer: Daily revenue and order counts, sliced by product category and region, and customer cohort retention - for a group of customers who first bought in a given month, how much do they spend in the months after. Dashboards refresh each morning.

Candidate: How fresh does the data need to be?

Interviewer: Daily is fine. Yesterday's numbers should be complete and queryable by 6am. This is not real-time; nobody is watching it minute to minute.

Candidate: What are the sources and how big are they?

Interviewer: Postgres. Start with orders at about 400 million rows and 5 million new orders a day, order_items at a couple of lines per order, customers at ~50 million, and products at ~2 million. Every table has an updated_at column.

Candidate: Can rows change after they're created? An order that gets refunded, say.

Interviewer: Yes. An order is updated as it ships, and a refund can post days later against an old order. The warehouse has to reflect that, even though it changes a day that already "closed".

Candidate: Do the analysts care about the history of a customer's attributes, or only the current value?

Interviewer: History matters. A customer's segment - say they move from "regular" to "VIP" - changes over time, and cohort analysis has to attribute each order to the segment the customer had at the time of the order, not their segment today.

Candidate: How much load can we put on production Postgres?

Interviewer: Keep it off the primary. There's a read replica you can hit, ideally in an off-peak window.

Candidate: A few things I want to confirm are in scope: reruns after a failed night, backfilling history after a logic bug, and additive schema changes on the source.

Interviewer: All in scope. The rerun and backfill behavior is exactly what separates a toy from a real pipeline.

That pins the design down.

Functional requirements

• Extract orders, order_items, customers, and products from Postgres into the lake each night, and bootstrap from full history the first time.
• Build a dimensional warehouse: a fact_orders table plus conformed dimensions (dim_customer, dim_product, dim_date) that serve revenue, orders-by-category, and cohort queries.
• Preserve history of customer attributes so an order is attributed to the customer's state at order time (SCD Type 2).
• Correctly reflect late updates to already-loaded rows (a refund against last week's order).
• Rerun a failed night and backfill a date range without double-counting.

Non-functional requirements

• Freshness: yesterday's data complete and queryable by 6am (a daily SLA, not a latency target).
• Correctness: loads are idempotent - rerunning the same night, or backfilling a range, must land the same numbers, never doubled.
• Low source impact: read from a replica, in an off-peak window, and extract only what changed so the scan stays bounded.
• Schema safety: an additive column on the source must not break the pipeline.
• Resilience: a failed run recovers by re-running the task, with no manual data surgery.
• Scalability: handles 30% year-over-year growth, which roughly doubles volume every three years.

Out of scope (say it out loud): minute-level freshness (that's the CDC pipeline, the streaming successor to this design), the BI tool itself, ML feature pipelines, and multi-region. We're doing database to warehouse, once a day, one region.

Back-of-the-envelope estimation

Estimate first, so the architecture rests on numbers instead of vibes.

• Fact volume. ~5M orders/day at ~2 items each is ~10M fact rows/day. At ~200 bytes per row in Parquet, that's ~2 GB/day, roughly 0.7 TB/year. The fact table is the big one; dimensions are small by comparison.
• Nightly extract. New orders plus updates to old ones. 5M new orders, each seeing a few status changes, plus refunds against older orders: call it ~25M changed rows/night across orders and items. At ~1 KB per source row that's ~25 GB pulled per night - trivial for one Spark job in an off-peak window.
• Initial load. A one-time full pull of ~400M orders and their items, tens of GB. It runs once; don't over-engineer it, but don't run it against the primary.
• Dimensions. dim_customer is ~50M rows and grows slowly; with SCD Type 2 it grows a little faster because a changed customer adds a row rather than updating one. Still small.

The number that decides the design: the nightly changed-row volume is tens of gigabytes, which a single batch job handles comfortably in the window. So this is not a throughput problem. The difficulty lives in extracting exactly the changed rows without missing any, applying them idempotently, handling updates to closed days, and modeling dimension history. That's where the interview points are, and where the rest of this chapter goes.

Step 2 - Propose High-Level Design and Get Buy-In

We'll cover the extract contract, the layering, the storage choices, and the high-level diagram.

The extract contract

The equivalent of a query API here is the extract contract: how you decide, each night, which rows to pull. Two options, and naming the tradeoff is worth points.

• Full extract (SELECT *): dead simple, always correct, but it re-reads the whole table every night. Fine for products (2M rows); a non-starter for orders (400M) once you're past the toy stage.
• Incremental extract: pull only rows where updated_at > last_watermark. Bounds the scan to what changed, which is the whole point, but it leans on a reliable change-marker column and a stored high-water mark - and it has a subtle boundary bug we'll dissect in the deep dive.

The design uses full extract for small, slowly-changing dimensions and incremental extract for the big fact-feeding tables, with a stored watermark per table.

The layering: raw to staging to marts

Never transform on the way in. Land the raw extract first, then refine in stages. This is the medallion pattern, and each layer earns its keep.

The rule that makes this safe: raw is immutable and reprocessable, marts are derived. A bug in the star schema is a rebuild from staging, not a re-extract from production.

Choosing the storage and compute

Three jobs, three tools:

• Landing (object storage + Parquet). The raw extract is append-only, columnar-friendly, and cheap to keep. Partition each table's raw data by load date so a rerun overwrites one partition cleanly.
• The warehouse tables (Iceberg). Staging and marts need row-level MERGE (for incremental upserts and SCD Type 2), ACID commits (so a half-finished night is never visible), schema evolution (the additive-column requirement), and partitioning. An open table format gives you all of that on object storage; raw Parquet does not. A cloud warehouse (Snowflake, BigQuery) is an equally valid answer - same model, managed storage.
• The compute (Spark). The nightly transforms are big joins and shuffles - facts against dimensions, SCD Type 2 merges - and backfills reprocess months at once. A batch engine is the fit. dbt on the warehouse is the common alternative when the transforms are pure SQL.
• The orchestrator (Airflow). Something has to run extract before staging before marts, retry a failed task, hold the watermark, and drive backfills over a date range. That's an orchestrator's whole job.

High-level design

Putting it together, the data flows source to serving:

The data's journey:

1. Spark extract reads the replica (never the primary), pulling only rows changed since the stored watermark for each table. Small dimensions get a full pull.
2. The rows land in raw as date-partitioned Parquet - an immutable record of exactly what the source returned.
3. Staging casts types, deduplicates on primary key (a row updated twice in one window appears twice in raw), and produces trustworthy current state in Iceberg.
4. Marts build the star schema: dimensions first (including the SCD Type 2 customer dimension), then fact_orders, which looks up the dimension keys valid at order time.
5. Query engines read the marts like any other tables.

One choice worth defending up front: dimensions are built before facts, and the fact captures dimension keys at load time. That ordering is what lets an order point at the historical version of a customer, and it's the first thing to explain when the interviewer asks how cohorts stay correct.

Step 3 - Design Deep Dive

Now the parts that get interrogated: incremental extract and its boundary bug, idempotent loads and late updates, the star schema and fact grain, SCD Type 2, orchestration and the SLA, backfills, schema evolution, scaling, fault tolerance, and reconciliation.

Full vs incremental extract, and the watermark boundary bug

Incremental extract stores the maximum updated_at it has seen and, next run, pulls everything newer. The obvious version has a data-loss bug, and spotting it is a senior signal.

The trap is commit time versus row time. Suppose last night's job read up to updated_at = 02:00:00 and saved that as the watermark. But a transaction that started at 01:59 with updated_at = 01:59:30 didn't commit until 02:00:05 - after your snapshot read. Tonight you query updated_at > 02:00:00 and that row, stamped 01:59:30, is silently skipped forever.

Two defenses, used together:

• Overlap the window. Re-extract a safety margin: updated_at > watermark - 15 minutes. You re-read a few rows, but the idempotent load (next section) makes re-reading harmless. The cost of overlap is a rounding error; the cost of a gap is a wrong total nobody notices.
• Prefer a monotonic marker where you can. An append-only fact with an auto-incrementing id can watermark on the id, which is assigned in commit order and dodges the clock-skew version. Mutable rows still need updated_at plus overlap.

This is also the moment to say why you're not polling more often: a bigger overlap or a tighter schedule both add source load, and if the business truly needs minutes, batch is the wrong tool and you reach for CDC. Naming that boundary shows you know when to stop.

Idempotent loads and late-arriving updates

The single most-tested idea in a batch warehouse: running the same load twice must not change the answer. The naive full rebuild - TRUNCATE the target and reload everything - is trivially idempotent but re-reads the world every night and doesn't scale. Incremental loads are cheap but you have to engineer the idempotency.

Two idempotent patterns, chosen by table shape:

• Partition overwrite for append-mostly facts: write each day into its own partition, and a rerun replaces that partition wholesale rather than appending to it. Rerun-safe by construction, because the partition ends up with exactly one copy of the day.
• MERGE (upsert) by key for anything that mutates: match on the primary key, update on match, insert on miss. Applying the same change twice lands in the same place - a no-op on replay.

MERGE INTO fact_orders t USING staged_orders s ON t.order_id = s.order_id
WHEN MATCHED     THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Now the assumption that quietly breaks everything: "yesterday is frozen." It is tempting to write yesterday's fact partition once and never touch it. But a refund posts today against an order from last week. If facts are partitioned by order date and you only ever write today's partition, that refund never lands and last week's revenue stays wrong.

The fix follows from the extract: the incremental pull is keyed on updated_at, so the refunded row does come through tonight - it's just an order with an old order-date. So the load has to MERGE by order_id into whichever historical partition that order lives in, not blind-append to today. This is the signature tradeoff of the whole design: truncate-and-reload is simple and always correct but does not scale; incremental is cheap but only correct if late updates can reach back and rewrite closed partitions.

The star schema and choosing the fact grain

Marts are dimensional: one central fact surrounded by conformed dimensions. The decision that shapes everything is the fact grain - what one row means.

For PhoenixWorld, the grain is one row per order line (fact_orders), the finest thing the business measures. A coarser "one row per order" grain would make it impossible to slice revenue by product category, because an order spans categories. Pick the finest grain the questions require; you can always aggregate up, never down.

Two modeling rules to state:

• Surrogate keys, not natural keys. The fact points at customer_key (a warehouse-generated integer), not the source customer_id. This is what makes SCD Type 2 possible: a customer has one customer_id but many customer_key rows over time, one per version.
• Know your measure additivity. net_revenue is fully additive (sum it across any dimension). A ratio like average order value is non-additive and must be computed from summed numerator and denominator, never averaged of averages. Getting this wrong is how a dashboard shows a plausible, wrong number.

SCD Type 2 on the customer dimension

The cohort requirement - attribute each order to the customer's segment at order time - is exactly what Slowly Changing Dimension Type 2 exists for. Instead of overwriting a customer's segment (Type 1, which erases history), Type 2 closes the old version and opens a new one:

The nightly load MERGEs changed customers: for each customer whose tracked attributes changed, expire the current row (set effective_to, is_current = false) and insert a new current row with a fresh surrogate key.

Now the part that ties it together, and the classic mistake it avoids. When fact_orders loads, it looks up the customer version valid on the order date (effective_from <= order_date < effective_to) and stores that customer_key. So an order placed in February permanently points at key 8001 ("regular"); an order in May points at 8002 ("VIP"). History is baked into the fact at load time.

Orchestration, dependencies, and the freshness SLA

The 6am SLA is a scheduling problem. The Airflow DAG encodes the real dependencies: extract, then staging, then dimensions, then facts (facts need the dimension keys to exist first). Get the order wrong and the fact load looks up a customer version that isn't there yet.

Three properties make it operable:

• Idempotent tasks keyed by run date. Every task is parameterized by the logical date (ds), so re-running the 2026-06-30 DAG reprocesses exactly that day - the partition-overwrite and MERGE patterns from earlier make the rerun safe.
• Retries with backoff on the extract and load tasks, so a transient replica hiccup self-heals instead of paging someone.
• A freshness signal, not just "the DAG finished". Publish the marts through a quality gate (row counts in range, revenue within tolerance of yesterday, no null keys) and only then flip the "data is ready" flag the dashboards trust. Green-because-it-ran is how bad data reaches the CEO.

Backfills and reprocessing

Someone finds a bug in the revenue logic that has been wrong for two months. You need to reprocess 60 days without taking the pipeline down or double-counting.

Because every task is idempotent and keyed by date, a backfill is just replaying the DAG over a date range. Two things keep it sane:

• Reprocess from raw, not from the source. The immutable raw layer already holds every night's extract, so a backfill re-runs the transforms over stored data and never re-hits Postgres. This is the payoff for landing raw first.
• Backfill into a side table, then swap. For a big or risky rebuild, write the corrected marts to a shadow table and atomically swap it in once verified, so analysts never see a half-rebuilt table. Iceberg makes the swap a metadata operation.

Schema evolution

Someone runs ALTER TABLE orders ADD COLUMN gift_wrap boolean and doesn't tell you. Handle the change classes deliberately:

The SELECT * extract plus an evolving table format is what makes additive changes a non-event. Pinning an explicit column list would turn every new column into a broken pipeline.

Scale the system

At 30% growth, volume doubles every three years. Each layer scales on a different axis:

• Extract stays bounded because it's incremental - it scales with changed rows, not total rows, so a table getting bigger doesn't lengthen the nightly pull much. The full-dimension pulls do grow; switch them to incremental when they hurt.
• Transforms scale with Spark parallelism. The likely hot spot is the fact-to-dimension join shuffle; partition the fact by date and broadcast small dimensions to avoid a full shuffle.
• Storage scales by partitioning the fact by order date, so queries and compaction touch bounded slices and old partitions can tier to cold storage.
• The window is the real ceiling. If the nightly job stops finishing before 6am, that's the signal to move the hottest tables to incremental micro-batches or CDC - which is the next design.

Fault tolerance

Three failures, three answers:

• A task fails mid-run. Airflow retries it; because tasks are idempotent and keyed by date, the retry reprocesses the same partition cleanly. No half-written state leaks, because Iceberg commits are atomic - readers see the old snapshot until the new one is committed whole.
• The replica is down or lagging. The extract fails and retries; the watermark only advances on success, so a skipped night is caught up by the next run's overlap window. Nothing is lost, the data is just late.
• A bad deploy corrupts the marts. Time-travel the Iceberg table to the last good snapshot, or rebuild from the immutable raw layer. This is why marts are derived and raw is sacred.

Data quality and reconciliation

The warehouse feeds finance, so trust has to be measured, not assumed.

In-pipeline checks gate the publish: row counts within an expected band, no null surrogate keys, net_revenue non-negative, and today's totals within tolerance of a rolling average (a 10x jump means a broken join, not a great sales day).

Reconciliation against the source catches slow drift the checks miss. A periodic job compares an independent aggregate - say total revenue per day - computed in Postgres versus the warehouse, and alerts on mismatch beyond a threshold. The source is ground truth; when a day diverges, you backfill it from raw.

Final design

Alternative designs

You're judged on reasoning about tradeoffs, not on one blessed stack. Reasonable swaps:

• Managed ELT (Fivetran, Airbyte) for the extract instead of hand-rolled Spark: faster to stand up, less control, a per-row bill. Good when the team is small and the sources are standard.
• A cloud warehouse (Snowflake, BigQuery) instead of Iceberg + Spark: same dimensional model, managed storage and compute, and the transforms move to dbt SQL. Often the pragmatic v1.
• dbt for the transform layer on top of either: brings tested, version-controlled SQL, MERGE-based incremental models, and built-in SCD Type 2 snapshots, so you write less orchestration by hand.
• CDC instead of nightly batch when daily is no longer fresh enough: the natural successor, covered in the CDC pipeline design.

Step 4 - Wrap Up

We designed a batch pipeline that turns an operational Postgres database into a dimensional warehouse serving daily revenue, orders, and cohort dashboards - fresh by morning, correct on rerun, and honest about a customer's history. We covered:

• Scope and estimation, and the realization that at tens of gigabytes a night this is a correctness problem, not a throughput one.
• The layering and model: immutable raw as the reprocessable source of truth, staging as the trusted current state, and a star schema mart with fact_orders at order-line grain.
• The deep dive: watermark-based incremental extract and its commit-time boundary bug, idempotent loads via partition overwrite and MERGE, late-arriving updates that break the "yesterday is frozen" assumption, the fact grain and surrogate keys, SCD Type 2 with the dimension key frozen into the fact, orchestration and a real freshness SLA, idempotent backfills from raw, schema evolution, scaling each layer, fault tolerance via atomic snapshots, and reconciliation against the source.

A strong answer lands raw before transforming, keys idempotency on the run date, catches the watermark boundary bug before being asked, and reaches for SCD Type 2 surrogate keys the moment "history" is mentioned. The interviewer's likely follow-ups: how a late refund reaches a closed day (incremental catches it on updated_at, MERGE rewrites the old partition), why the fact stores a surrogate key not a customer id (to freeze the historical dimension version and avoid the is_current join trap), how you backfill two months safely (replay idempotent tasks from raw, swap a shadow table), and when you'd abandon batch for streaming (when the nightly window stops fitting before the SLA, or the business needs minutes).

Go deeper in the curriculum: the batch ingestion and backfills track for incremental extract and watermark patterns, the Spark track under compute engines for the join and shuffle mechanics, the Parquet track under storage and file formats for the columnar layout the fact rests on, the dimensional modeling and SCD tracks under the semantic and metrics layer, and the Airflow track under orchestration for DAG dependencies, idempotent tasks, and backfills. When daily stops being fresh enough, continue to the CDC pipeline.