"Think in Pipelines" — The Data Engineer's Mental Model

Skim Card

One thing to remember: a pipeline is a function from raw history to tables; if running it twice, or in another order, over the same raw data changes the answer, it’s broken.

• An appending job turns every retry into double revenue: re-running one day of v1 doubled UK sales for 15 August to £695,131.60.
• Overwrite what you own: rebuild exactly the business dates an upload touched, from immutable raw files, in one transaction.
• Deduplicate on the business key (basket_id), never the event ID: a re-sent basket arrives with a new one.
• An upload partition isn’t a business day: 30,248 of 2,338,812 POS events landed in a neighbouring day’s upload.
• Recent days are provisional: 15 August’s UK sales rose £5,423.19 when the next upload landed. Declare a lateness window.
• Prove it: run forwards, backwards and again, and the gold table’s fingerprint must not change.
• Five promises name how numbers fail: completeness (missing), uniqueness (duplicated), correctness (wrong), timeliness (late), consistency (contradictory).

Use …	When …
Rebuild the whole table	it’s small; the simplest idempotent pipeline there is
Overwrite affected partitions	each partition can be recomputed from raw data you keep
MERGE by key	the source sends changes, not history, or partitions are too big to rebuild (Part 16)
Append, deduplicate on read	immutable events where rewriting partitions costs more than filtering at query time

Verified against: DuckDB 2.0.0, Python 3.13 · docs: Spark 4.2, Iceberg 1.12, Delta Lake 4.3+ · 28 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Rohan’s first job on the Data Platform team was small: a daily table of sales per store, so Farah could stop rebuilding it in a spreadsheet. Every night it read the previous day’s till uploads, kept that date’s sales, and inserted the totals: the shape of a hundred Kafka consumers Rohan had written.

On Tuesday the scheduler hit a timeout on the 15 August run and retried it. Wednesday morning, UK sales for the 15th were £695,131.60, twice the previous figure. Rohan deleted the extra rows by hand. Farah, who’d watched the number change twice in a day, asked the question every finance controller eventually asks a data team: “Why does yesterday’s revenue keep changing?”

Ingrid had a different question. “Before the retry, was the number right?”

It wasn’t. It was £4,746.82 short, net: £5,460.97 of sales had landed in another day’s file and the job never saw them, while £714.15 of re-sent baskets were counted twice.

The Quick Map

Concepts, with use cases

A pipeline is a function

The mental-model shift of this part: a data pipeline is a function from source history to derived tables. Give it the same raw data and it must produce the same tables, however many times it runs, in whatever order, after whatever crash. If it can’t, it’s broken, even if every run reports success.

Maxime Beauchemin, who created Apache Airflow and Apache Superset, made this the core of a 2018 essay, “Functional Data Engineering”: “A pure task should be deterministic and idempotent, meaning that it will produce the same result every time it runs or re-runs.” So tasks don’t UPDATE, APPEND or DELETE (“mutations that ripple in side-effects”); “a pure task should always fully overwrite a partition as its output”. And the inputs stay put, in “a persistent and immutable staging area”.

You already know half of this: an idempotent POST /payments returns the same result when the client retries with the same key. A pipeline needs the same for records and for whole runs.

What breaks without it. Every operational event becomes a data incident: a retry doubles a day, a backfill adds a second copy of a month, a crash leaves half a day loaded.

Larkspur. The daily sales job above. The biggest slice of Farah’s 0.7% gap is the same mistake: re-sent POS events counted as new sales.

Where else you’ll see this. An adtech platform bills advertisers from impression logs: run the hour twice and advertisers pay twice; skip the late logs and impressions are given away.

The data engineering lifecycle

Joe Reis and Matt Housley’s Fundamentals of Data Engineering (O’Reilly, 2022) names five stages: generation (source systems), storage, ingestion, transformation and serving, with six undercurrents beneath them: security, data management, DataOps, data architecture, orchestration and software engineering. Optimise one stage alone and you break the next: Larkspur’s incident touches all of them, from tills that re-send (generation) to two definitions of a session (serving).

ETL, ELT and EtLT

• ETL transforms data before it reaches the analytical store. It dominated when warehouse storage and compute were expensive and only clean data deserved a place.
• ELT lands raw data first and transforms it inside the platform, usually in SQL. It won in the cloud: object storage is cheap, compute is elastic, and kept raw data can be re-transformed when the logic changes.
• EtLT adds a small “t” before loading for what must happen before data rests anywhere: masking personal data, dropping fields you may not keep.

What breaks without it. Classic ETL kept only what survived the transform, so when the logic turned out wrong there was nothing left to replay it on. Larkspur. The tills’ JSON lands untouched (EL); today’s job is the T. Hashing loyalty IDs before landing would make it EtLT (Part 23). Elsewhere: a health insurer de-identifies claims before they reach the analytics account, then models them there.

Batch, micro-batch, streaming; push and pull

Batch processes a bounded chunk, such as a day’s uploads, on a schedule. Micro-batch runs the same logic on small chunks every few seconds or minutes (Spark Structured Streaming’s default). Streaming processes each event as it arrives and keeps state between events (Flink, Kafka Streams). Lower latency costs more machinery.

Push means the source sends data to you (tills upload files); pull means you fetch it (the nightly updated_at poller). Push sources retry, so expect duplicates. Pull sources depend on what the query can see, so expect gaps: the poller never sees a deleted row. A bank sees both: card networks push authorisations, while an open-banking aggregator pulls account data on a schedule.

What breaks if you choose by habit. Streaming a daily report buys state, watermarks and on-call for nothing; a nightly batch for fraud lets a stolen card run all day.

Larkspur. Daily store sales are batch; Season 3’s stock-out alerts are streaming, because a shelf that’s empty at 10:00 matters at 10:01.

Idempotency, determinism, backfills

Idempotent: running it again changes nothing. Deterministic: the same input always gives the same output (no now(), no random sampling, no unordered “pick one”). Re-playable: you still have the input, so you can run it again. A backfill is a deliberate replay of past periods, after a bug fix or for a new column, and it’s only routine if the first three hold.

Two mechanisms deliver this, and today’s job needs both:

• A dedup key on records: the business identity of a fact, such as basket_id, assigned by the till when the sale happens (not the event_id, assigned per message).
• A partition-scoped overwrite on outputs: each run owns a slice of the output (here, a set of business dates), and deletes and rewrites it atomically.

Larkspur. v2 in Hands-On has both. Elsewhere: a mobile game counting daily players from phones that send queued events days later, sometimes twice, keys on (device_id, event_seq) and overwrites by play date.

Late data and the provisional day

Data is partitioned by the date it arrived in the raw zone, because arrival is the only thing the landing system knows for sure. Reports are by business date, the date the event happened in the store’s own time zone. The two disagree at the edges, through late tills and time zones (Under the Hood shows how).

Beauchemin’s essay recommends the raw half of this split: “Where late arriving facts may exist and need to be handled, time partitioning should always be done on event processing time.” Arrival partitions never change once written, so you can always see what you knew and when. Questions by business date then need more work: each run finds the business dates its upload touched and rebuilds them.

And a recent day isn’t final until its late data has arrived. That’s the honest answer to Farah’s question. The fix isn’t to freeze the number; it’s to declare a lateness window, label days inside it provisional, and close them when it passes.

Elsewhere: smart electricity meters buffer readings during network outages and upload them later, so utilities settle daily consumption after a fixed window.

Layers: raw, silver, gold

The medallion architecture names three layers. Databricks’ documentation describes bronze as “raw, unvalidated data”, silver as “validated, cleaned, and enriched” data, and gold as “highly refined views”, and calls it “a recommended best practice but not a requirement”. The idea is older than the names: keep what arrived, clean it, shape it for questions.

Why keep raw forever. Every bug you find later is a replay. The revenue incident can be explained to the penny only because the raw till uploads still exist, re-sends and all; a cleaned table can’t tell you what it threw away. Elsewhere: a bank keeps every raw card authorisation message beside its cleaned and reporting tables, because when a regulator asks how a 2023 figure was built, raw is the only honest answer.

Every record counts: five promises

A number is trustworthy only if every record behind it is accounted for. That breaks down into five promises, and every later part strengthens at least one.

The promises pull against each other. Waiting for every late upload improves completeness and hurts timeliness; the lateness window is where you choose, out loud.

Under the Hood

One upload, three business dates

Each raw partition is the UTC date a file arrived, and Hands-On step 1 shows events landing a day early as well as a day late. Early is time zones: a Singapore sale at 07:30 local is 23:30 UTC the previous day, and lands in that day’s upload. Late is offline tills uploading the next day. So any one upload holds sales for up to three business dates, and any one business date is spread over up to three uploads:

v1 assumed the arrows were horizontal. v2 runs for an upload, rebuilds every business date it points to, and reads every upload that can point to each of those dates.

What makes v2 deterministic

• The input set is a rule, not a run. Business date d is built from uploads d − 1 to d + 2, filtered row by row, whichever run triggered the rebuild.
• Duplicates resolve the same way every time. DISTINCT ON (basket_id) keeps the first row in ORDER BY uploaded_at, event_id, so even two copies uploaded in the same microsecond pick the same winner. Without the tie-breaker, “first” is whatever the engine read first.
• No clocks in the data. No now(), no “today’s” FX rate (revenue defect 4), no per-run loaded_at column. Run metadata belongs in a run log, not in the rows you compare.
• Silver and gold change together. All four statements run in one transaction: readers see the old dates or the new ones, never a half-rebuilt day, and a crash rolls everything back.

The lateness window

The window is a policy, declared in code as LATE_DAYS = 2, twice the worst lateness measured. An upload that holds a sale older than the window doesn’t trigger a rebuild of that date; the run reports it instead of dropping it silently. Real pipelines route those rows to a quarantine table and alert, and finance decides whether to reopen a closed day. Season 2 builds that (Parts 18 and 21).

Overwrite means different things in different engines

The pattern is universal; the syntax and the traps aren’t.

Overwrite or MERGE? Overwrite recomputes a slice from raw, so it also applies code fixes. MERGE applies changes by key: right when the source only sends changes (CDC, Part 16) or a partition is too big to rebuild. A small table can simply be rebuilt whole, the simplest idempotent pipeline there is; immutable events too big to rewrite can be appended and deduplicated at query time, if every reader does it. Each is idempotent only if the key is right.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-2). This part needs no containers: Python and DuckDB on your machine read the generated files in data/raw. Run make data first if you skipped Part 0.

scripts/part02_pipeline.py keeps its own raw zone in data/lab/part02/raw; its land command copies upload partitions into it, as if the tills had just sent them. The SQL lives in sql/part02/. Outputs are trimmed in three ways only: DuckDB’s column-type row is removed after step 1, later check results show just their rows (same columns as step 2), and multi-day runs show at most their last lines.

Step 1: how late is late?

uv run python scripts/duck.py --local sql/part02/lateness.sql

┌────────────────────────────┬─────────┬─────────┐
│ upload_minus_business_date │ events  │ baskets │
│           int64            │  int64  │  int64  │
├────────────────────────────┼─────────┼─────────┤
│                         -1 │    8193 │    8190 │
│                          0 │ 2308564 │ 2301632 │
│                          1 │   22055 │   19671 │
└────────────────────────────┴─────────┴─────────┘

30,248 of 2,338,812 events sit in a neighbouring day’s upload. About 1.3%, which is plenty to make a revenue figure wrong.

Step 2: v1, the job Rohan wrote

sql/part02/naive_load.sql, run once per day by the script:

-- v1 of the daily sales job (DuckDB): "load yesterday's upload, keep yesterday's sales, append".
-- Three bugs, one per line marked (!).
INSERT INTO naive.daily_store_sales                                                   -- (!) append
SELECT (e.event_ts AT TIME ZONE s.timezone)::DATE AS business_date,
       e.store_id,
       e.currency,
       count(*)                       AS baskets,                                     -- (!) per event
       sum(e.tenders[1].amount_minor) AS revenue_minor
FROM read_json(getvariable('landed_root') || '/pos/events/upload_date=' || getvariable('run_date') || '/*.jsonl.gz') e
JOIN landed_stores s USING (store_id)
WHERE (e.event_ts AT TIME ZONE s.timezone)::DATE = getvariable('run_date')            -- (!) one file = one day
GROUP BY ALL;

It does compute the local business date correctly (Part 1 learned that). Land every upload and run it for Q3:

uv run python scripts/part02_pipeline.py land 2026-05-31 2026-10-05
uv run python scripts/part02_pipeline.py naive 2026-07-01 2026-09-30
uv run python scripts/part02_pipeline.py check 2026-08-15

landed 128 upload partitions, 2026-05-31 to 2026-10-05
v1 naive: 92 runs, 55,200 rows appended
business date 2026-08-15, revenue in minor units
┌─────────┬────────────┬─────────┬────────────┬────────────┬───────────────────┐
│ country │  v1_naive  │ v2_gold │ recompute  │ v2_baskets │ recompute_baskets │
├─────────┼────────────┼─────────┼────────────┼────────────┼───────────────────┤
│ GB      │   34756580 │    NULL │   35231262 │       NULL │              7638 │
│ IN      │ 3087632055 │    NULL │ 3104455043 │       NULL │             10770 │
│ SG      │   32681995 │    NULL │   33103093 │       NULL │              3852 │
└─────────┴────────────┴─────────┴────────────┴────────────┴───────────────────┘

check (sql/part02/check.sql) shows one business date three ways: v1’s table, v2’s (empty so far), and a recompute of every landed event, deduplicated by basket_id, in one query. Amounts are in minor units (pence, paise, cents). v1 says £347,565.80 for the UK; the recompute says £352,312.62. explain (sql/part02/v1_explained.sql) shows why:

uv run python scripts/part02_pipeline.py explain 2026-08-15

business date 2026-08-15: what v1 missed and what it counted twice
┌─────────┬──────────────────────────┬──────────────┬──────────────┬──────────────────────┐
│ country │ baskets_in_other_uploads │ missed_minor │ extra_copies │ double_counted_minor │
├─────────┼──────────────────────────┼──────────────┼──────────────┼──────────────────────┤
│ GB      │                      127 │       546097 │           17 │                71415 │
│ IN      │                       81 │     24178660 │           30 │              7355672 │
│ SG      │                       71 │       574780 │           13 │               153682 │
└─────────┴──────────────────────────┴──────────────┴──────────────┴──────────────────────┘

For the UK: 35,231,262 − 546,097 + 71,415 = 34,756,580, to the penny. v1 lost 127 baskets whose only copies landed in the 14th’s or the 16th’s upload (whose runs filter them out too), and counted 17 re-sent copies twice. Two bugs in opposite directions, partly cancelling: the kind that survives review because the total “looks about right”.

Step 3: the retry

uv run python scripts/part02_pipeline.py naive 2026-08-15
uv run python scripts/part02_pipeline.py check 2026-08-15

v1 naive: 1 runs, 600 rows appended
│ GB      │   69513160 │    NULL │   35231262 │       NULL │              7638 │
│ IN      │ 6175264110 │    NULL │ 3104455043 │       NULL │             10770 │
│ SG      │   65363990 │    NULL │   33103093 │       NULL │              3852 │

Exactly double: £695,131.60. Nothing failed; the job did what it says.

Step 4: v2, rebuild what the upload touched

For each upload, the script first asks which business dates it contains (sql/part02/affected_dates.sql), keeps those inside the lateness window, and then runs sql/part02/rebuild.sql in one transaction:

-- silver: every basket of those dates, from every landed upload that can hold them, once each.
DELETE FROM silver.pos_baskets WHERE business_date IN (SELECT business_date FROM affected);

INSERT INTO silver.pos_baskets
SELECT DISTINCT ON (basket_id)                       -- the dedup key: a re-sent basket keeps its basket_id
       basket_id, store_id, country, currency, business_date, event_ts, upload_date, event_id, amount_minor
FROM (
    SELECT e.*, s.country,
           (e.event_ts AT TIME ZONE s.timezone)::DATE AS business_date,
           e.tenders[1].amount_minor                  AS amount_minor
    FROM landed_pos_events e
    JOIN landed_stores s USING (store_id)
    WHERE e.upload_date BETWEEN getvariable('first_upload') AND getvariable('last_upload')  -- prune partitions
)
WHERE business_date IN (SELECT business_date FROM affected)
  AND upload_date BETWEEN business_date - 1 AND business_date + getvariable('late_days')  -- the lateness policy
ORDER BY basket_id, uploaded_at, event_id;           -- first upload wins; ties broken, so reruns agree

-- gold: overwrite the same dates from silver.
DELETE FROM gold.daily_store_sales WHERE business_date IN (SELECT business_date FROM affected);
INSERT INTO gold.daily_store_sales
SELECT business_date, store_id, country, currency, count(*) AS baskets, sum(amount_minor) AS revenue_minor
FROM silver.pos_baskets WHERE business_date IN (SELECT business_date FROM affected) GROUP BY ALL;

The Python runs the statements between BEGIN TRANSACTION and COMMIT, with ROLLBACK on any error. basket_id is silver’s primary key, so a dedup bug fails the run instead of inflating revenue. Run v2 for every upload, then check the same day:

uv run python scripts/part02_pipeline.py run 2026-05-31 2026-10-05
uv run python scripts/part02_pipeline.py check 2026-08-15

v2 run for upload 2026-10-03: rebuilt 2026-10-02, 2026-10-03, 2026-10-04
v2 run for upload 2026-10-04: rebuilt 2026-10-03, 2026-10-04
v2 run for upload 2026-10-05: rebuilt 2026-10-04
│ GB      │   69513160 │   35231262 │   35231262 │       7638 │              7638 │
│ IN      │ 6175264110 │ 3104455043 │ 3104455043 │      10770 │             10770 │
│ SG      │   65363990 │   33103093 │   33103093 │       3852 │              3852 │

v2 equals the recompute in every country, baskets and money. Now re-run a day and compare a fingerprint of the whole gold table, a row count and an MD5 over every row in key order:

uv run python scripts/part02_pipeline.py fingerprint
uv run python scripts/part02_pipeline.py run 2026-08-15
uv run python scripts/part02_pipeline.py fingerprint

gold.daily_store_sales: 75,600 rows, md5 d9fb386e6152a3d6e8e3b0b13a20a533
v2 run for upload 2026-08-15: rebuilt 2026-08-14, 2026-08-15, 2026-08-16
gold.daily_store_sales: 75,600 rows, md5 d9fb386e6152a3d6e8e3b0b13a20a533

The upload of the 15th touched three business dates (the 16th through early-morning sales in stores ahead of UTC), all three were rebuilt, and not a byte changed.

Step 5: a backfill, backwards, from nothing

Delete everything, land every upload again, and run v2 newest first, the way a panicked backfill might:

uv run python scripts/part02_pipeline.py reset
uv run python scripts/part02_pipeline.py land 2026-05-31 2026-10-05
uv run python scripts/part02_pipeline.py run 2026-10-05 2026-05-31
uv run python scripts/part02_pipeline.py fingerprint

gold.daily_store_sales: 75,600 rows, md5 d9fb386e6152a3d6e8e3b0b13a20a533

Same fingerprint. The 128 runs took 67.6 seconds (0.53 s per run) with a peak of 619 MiB resident, measured with /usr/bin/time -l on a MacBook Pro M3 Pro. scripts/part02_profile.py re-runs ten mid-August uploads and times each statement (0.82 s per run there; one machine, not a benchmark):

uv run python scripts/part02_profile.py

1. count(*) over six uploads, median of 5
   default sampling             0.46s
   maximum_sample_files = 1     0.03s
2. v2 runs for uploads 2026-08-10 to 2026-08-19: 0.82s per run
   find affected dates (read 1 upload)    0.121s   15%
   silver DELETE                          0.012s    1%
   silver INSERT (read + dedupe JSON)     0.159s   19%
   gold DELETE                            0.001s    0%
   gold INSERT                            0.005s    1%
   COMMIT                                 0.527s   64%

Two thirds of a run is the COMMIT writing changes to the database file on disk; a third is reading and deduplicating the gzipped JSON. The first section is a tuning note: by default read_json samples many files to infer a schema every time the view is used, so sql/part02/views.sql samples one.

Step 6: why yesterday’s revenue changes

Now simulate the real schedule: each morning one upload lands, then v2 runs for it.

uv run python scripts/part02_pipeline.py reset
uv run python scripts/part02_pipeline.py daily 2026-08-13 2026-08-15
uv run python scripts/part02_pipeline.py check 2026-08-15

v2 run for upload 2026-08-13: rebuilt 2026-08-12, 2026-08-13, 2026-08-14
v2 run for upload 2026-08-14: rebuilt 2026-08-13, 2026-08-14, 2026-08-15
v2 run for upload 2026-08-15: rebuilt 2026-08-14, 2026-08-15, 2026-08-16
│ GB      │     NULL │   34688943 │   34688943 │       7512 │              7512 │
│ IN      │     NULL │ 3088436737 │ 3088436737 │      10709 │             10709 │
│ SG      │     NULL │   33052860 │   33052860 │       3845 │              3845 │

On the morning of the 16th, UK sales for the 15th are £346,889.43 from 7,512 baskets, and that’s correct for what has arrived. Then the 16th’s upload lands:

uv run python scripts/part02_pipeline.py daily 2026-08-16
uv run python scripts/part02_pipeline.py check 2026-08-15

v2 run for upload 2026-08-16: rebuilt 2026-08-15, 2026-08-16, 2026-08-17
│ GB      │     NULL │   35231262 │   35231262 │       7638 │              7638 │
│ IN      │     NULL │ 3104455043 │ 3104455043 │      10770 │             10770 │
│ SG      │     NULL │   33103093 │   33103093 │       3852 │              3852 │

126 late baskets, £5,423.19, and the 15th now matches step 4’s final figure: correct both times for what had arrived, and provisional until the window closes.

Step 7: the tests

tests/test_part02.py runs eight tests, one per claim above, on the small generated world (three weeks of uploads, no containers).

uv run pytest -q tests/test_part02.py

........                                                                 [100%]
8 passed in 8.39s

The crash is simulated by appending SELECT error('simulated crash') to the transaction, after the gold insert.

The Landscape

Product names as of 28 October 2026. Cells that couldn’t be verified say so.

Tips & Gotchas

Gotcha — every scheduler is at-least-once. A run that times out after writing and is retried has run twice; so has a backfill started while the original still runs. Design every task to run twice.

Gotcha — the event ID isn’t the dedup key. Larkspur’s tills give a re-sent basket a new event_id, so deduplicating on it removes nothing. Use the key assigned when the thing happens, and check it survives retries.

Gotcha — “load yesterday’s partition” isn’t “yesterday’s sales”. Step 2’s job silently lost about 1.5% of a day’s UK revenue this way.

Gotcha — INSERT OVERWRITE doesn’t mean one thing. In Spark’s default static mode, without a PARTITION clause it replaces the whole table; in Snowflake it always does. Check the engine’s definition (Under the Hood) before trusting the word.

Tip — fingerprint your tables. A row count plus a hash over every row in key order turns “I think the re-run was safe” into a one-line assertion. It’s also the cheapest regression test when you refactor a pipeline.

Troubleshooting

Design Drill

The question: “Design the daily pipeline that computes driver payouts for a ride-hailing company in 40 cities. Drivers earn fares, tips, incentives and cancellation fees; tips can be added up to three days after a trip, and fares get adjusted after disputes. Payouts go to banks every Monday. It must be safe to re-run.”

Clarify first

• What’s the payout period and cut-off, and in whose time zone: the city’s, the driver’s, UTC?
• Which earnings arrive late, and how late: tips, dispute adjustments, offline trips? Is an adjustment to a paid period reopened or carried forward?
• Which identifiers exist on each earning, and are they stable across retries from the trip service?
• What must reconcile with what: completed trips, the bank’s payout file, the finance ledger?

The shape of a strong answer

• Land trips, tips and adjustments raw and immutable, partitioned by arrival date, kept for the audit period.
• Silver: one row per earning line, deduplicated on (trip_id, earning_type, adjustment_id), with the city’s local business date. Gold: daily earnings per driver by business date, rebuilt for every date an arriving batch touches, inside a declared lateness window (tips: three days), in one transaction.
• Mark days provisional until the window closes; the Monday payout only includes closed days, so a paid amount never changes under you.
• Money that has left is append-only: adjustments to a paid period become new lines in the next payout. Recompute what is derived; never what was sent to a bank.
• Make the payout run idempotent too: a payout ID from (driver_id, period), sent to the bank as the idempotency key.
• Prove completeness every run: earning lines versus completed trips, payouts versus the bank file; alert on any difference.
• Test the property, not just the logic: replaying raw for any range must reproduce silver and gold exactly.

Trade-offs to say out loud

• A longer lateness window carries fewer adjustments forward but closes later.
• Recomputing affected partitions applies code fixes for free; MERGE is cheaper when adjustment volume is tiny compared with the partition.

Common weak answers

• An append-only daily job with “we’ll make sure it only runs once”.
• Deduplicating on message or event IDs that the trip service regenerates on retry.
• Overwriting a period that was already paid, so the warehouse no longer matches what the bank sent.

Further Reading

1. Joe Reis and Matt Housley, Fundamentals of Data Engineering (O’Reilly, 2022), chapter 2, “The Data Engineering Lifecycle”.
2. Maxime Beauchemin, “Functional Data Engineering — a modern paradigm for batch data processing” (8 January 2018). https://maximebeauchemin.medium.com/functional-data-engineering-a-modern-paradigm-for-batch-data-processing-2327ec32c42a
3. Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd edition (O’Reilly, 2026), chapter 11, “Batch Processing”.
4. Apache Iceberg documentation, “Spark Writes”: INSERT OVERWRITE, dynamic overwrite and MERGE INTO. https://iceberg.apache.org/docs/latest/spark-writes/
5. Databricks documentation, the medallion lakehouse architecture. https://docs.databricks.com/aws/en/lakehouse/medallion

Story Closing

Rohan deleted v1, and kept the two tests that mattered most: backwards from nothing gives the same fingerprint, a crash halfway leaves both layers untouched. Farah got a promise for the next version: every day marked provisional for two days, closed after that. “So the numbers still move,” Farah said, “but now I’ll know which ones, and when they stop.” That was more than any spreadsheet had promised.

Ingrid was reading the profile. “A third of every run is decompressing and parsing every field of gzipped JSON to use a handful of them, and a schema the files never state. Your pipeline is fine now. Your files aren’t.”

In Part 3, Rohan writes the same POS data as CSV, JSON, Avro and Parquet, opens a Parquet footer, and finds out how an analytical engine skips data it never reads.

This is Part 2 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.