BeginnerPipeline Architecture · 25 min · 11 challenges

Schema Drift: Beginner

This lesson follows launch week for tipping in the Uber rider app. The trip service starts sending a new tip_usd field on Monday, and nobody warns the warehouse team. That same night, riders who finished trips in subway tunnels upload them days late. By Wednesday, finance wants to know why tips are missing and why Monday's trip count keeps moving.

When a Producer Adds a Column

Daily Life
Interviews

The field your loader never expected

A producer is the system that writes the data a pipeline reads. Here it is the trip service, which emits an event per completed trip, and the loader is the step that copies those events into a warehouse table called trips. The loader was built when every event carried 4 fields, trip_id and city plus fare_usd and completed_at. Those names and the type of value each holds are the schema the loader expects.

{
"trip_id": "t_88213",
"city": "Lisbon",
"fare_usd": 14.2,
"completed_at": "2026-03-02T23:50:00Z",
"tip_usd": 2
}

Then the producer team ships tipping, and each event gains tip_usd. Their change is correct: the app needs the field and their tests pass. Nobody on that team knows your loader exists, and nothing in the event announces a new shape. This is schema drift: the structure of data changes over time, and each system reading it finds out when the new shape arrives.

3 reactions to an unknown field

When the loader meets a field its table has no column for, it does 1 of 3 things, whether its author chose on purpose or inherited a library default. Learn which one yours does before the next producer release.
def load_event(event, table_columns, mode):
unknown = [name for name in event if name not in table_columns]
if unknown and mode == "reject":
raise ValueError(f"unknown fields: {unknown}")
if unknown and mode == "evolve":
for name in unknown:
add_column(name, infer_type(event[name]))
table_columns.append(name)
row = {name: event.get(name) for name in table_columns}
insert_row(row)
What each reaction costs
  • RejectStop the load and fail the runNo wrong data reaches the table, but every dashboard reading trips goes stale. Retrying changes nothing: this is a permanent failure.
  • IgnoreLoad the known fields, drop the restThe run succeeds and trips stays fresh, while every tip value is thrown away. Without a raw copy of the events, those values are gone.
  • EvolveAdd a column and load everythingTips land from the first event. The risk is unreviewed growth: a typo such as tipUsd in a single app version becomes a second permanent column.

Here is trips after a batch of 3 events: an old event with no tip, then 2 new events tipping 2.00 and 0.00.

ReactionNew rows in tripstip_usd columnWho notices, and when
Reject0 of 3: the batch failsAbsentOn-call, at the failed run
Ignore3 of 3Absent, values lostAn analyst, weeks later, asking for tips
Evolve3 of 3null, 2.00, 0.00Whoever reviews new columns, if anyone does
Multiple Choice

A nightly loader keeps succeeding after a producer release, with row counts that match the source. A week later an analyst finds the new promo_code field nowhere in the warehouse. Which reaction is the loader using?

No reaction is right everywhere. A table feeding driver payouts should reject, because a stale number is safer than a silently incomplete one. A clickstream table with hundreds of loosely owned fields often ignores, with an alert naming each dropped field, because stopping it would page someone every week. A table where analysts want new fields fast can evolve, if someone reviews each new column.
TIP
Keep every raw event in the lake exactly as it arrived. Then reject and ignore both become recoverable: once the team decides what tip_usd means, you reload it from the raw copy instead of asking the producer to resend.
Multiple Choice

Your team owns payouts, which decides what drivers are paid each week, and app_clicks, which collects UI events from 40 product teams that add fields often. Which pairing of reactions fits best?

Forward vs Backward Compatibility

Daily Life
Interviews

Writers and readers upgrade separately

A schema change splits the data into 2 versions: events written before it and events written after. It also splits the code that reads them, because the producer team and the data team ship on different days. For a while, every mix of old and new runs somewhere, and compatibility describes which mixes still work.
DayProducer writesLoader codeMix in play
Mondayv1 events, 4 fieldsv1Old reader, old data
Tuesdayv2 events, adds `tip_usd`v1Old reader, new data
Thursdayv2 eventsv2, knows `tip_usd`New reader, new data
Thursday, reprocessing last weekv1 events from the raw copyv2New reader, old data

Backward and forward

✓Backward compatible
  • New reader, old data
  • The v2 loader reads v1 events
  • Needed when new code reprocesses history
  • Breaks when a new reader requires a field old events lack
✗Forward compatible
  • Old reader, new data
  • The v1 loader reads v2 events
  • Needed in the gap before readers upgrade
  • Breaks when an old reader needs a field new events dropped

Name the direction from the reader's point of view. Backward means a new reader looks back at data from its past. Forward means an old reader receives data from its future. A change that keeps both working is fully compatible.

2 rules in the reading code

Compatibility comes down to 2 rules in the reading code. If the reader skips fields it doesn't recognize, new fields cannot hurt it. If every field it expects falls back to a default like null or 0 when missing, events that lack the field cannot hurt it. With both rules in place, the verdicts below follow.
ChangeBackward?Forward?Why
Add `tip_usd` with default 0YesYesNew reader fills 0 for old events; old reader skips the field
Add `tip_usd` as required, no defaultNoYesNew reader finds no value in old events; old reader skips the field
Remove `promo_code`, which had a defaultYesYesNew reader skips it in old events; old reader fills the default
Remove `city`, required by old readersYesNoNew reader skips it in old events; old reader finds it missing
Rename `city` to `city_name`NoNoEach side looks for a name the other side's events lack
Change `fare_usd` from a number to textNoNoNeither reader expects the other's value type
Multiple Choice

The trip service wants to add a required field vehicle_type with no default. Readers skip fields they do not recognize. How does the change classify?

On Tuesday the v1 loader meets v2 events, so the change must be forward compatible or Tuesday's load breaks. On Thursday the v2 loader rereads last week's v1 events, so it must be backward compatible or the reprocessing breaks. A batch pipeline that ever reprocesses history needs both, which is why a new field with a default is the change data teams hope to see.
TIP
For any proposed change, check 2 things. If today's reader can read the new events, the change is forward compatible. If next week's reader can read last month's events, it is backward compatible. A no to either means the change needs a plan.
Multiple Choice

On Monday the producer drops the optional field promo_code, whose default is null. The loader is upgraded on Friday. The next week, the team reruns the new loader over 30 days of raw events. Which direction was needed, and when?

Adding Is Safe, Renaming Is Not

Daily Life
Interviews

3 classes of schema change

The compatibility test judges a single change. A proposal also needs a verdict on its plan: can it ship today, or does it need a schedule agreed with other teams? Each proposal falls into 1 of 3 classes.
  • AdditiveAdds something readers can skipA new optional field with a default, or a new table. Readers keep working, so the producer ships without waiting for anyone.
  • DestructiveRemoves or reshapes something readers useAny single release that drops a field or changes its name or type. Some reader breaks the moment it ships, either loudly with an error or quietly with wrong numbers.
  • CoordinatedA destructive goal reached in safe stepsThe same rename or type change, split so that no step removes anything a reader still uses. It takes weeks and needs every reader's owner to agree.

A rename looks like a delete to every reader

A rename feels small to the team making it. To every reader it is 2 changes at once: a field they rely on disappears, and an unknown field appears. Suppose the trip service adopts a naming standard and renames fare_usd to fare_amount_usd in a release at 15:00 on March 10. The loader runs in ignore mode, so it skips the unknown name and stores null in fare_usd. Every load succeeds.

SELECT
DATE(completed_at) AS trip_date,
SUM(fare_usd) AS revenue_usd
FROM trips
WHERE DATE(completed_at) BETWEEN DATE '2026-03-08'
AND DATE '2026-03-11'
GROUP BY DATE(completed_at)
ORDER BY trip_date
trip_daterevenue_usd
2026-03-08612,400
2026-03-09598,900
2026-03-10214,700
2026-03-11null
March 10 holds only the fares from before 15:00, and March 11 sums nothing but nulls, which is null. Revenue appears to fall by almost two thirds and then vanish. No run fails, and a finance analyst is the first to notice. A reject loader would stop on March 10 instead. That is the better failure because it is louder, but readers break either way, so a single-step rename is destructive.
Multiple Choice

Which of these proposals is coordinated?

The Safe Rename: Add, Copy, Switch, Drop

A coordinated rename reaches the same end state in 4 steps, and nothing a reader uses disappears until that reader has stopped using it.
StepProducer writesReaders useWhy nothing breaks
1. AddBoth `fare_usd` and `fare_amount_usd``fare_usd`A new field passes both compatibility directions
2. CopyBoth`fare_usd`Rows from before step 1 get `fare_amount_usd` filled from `fare_usd`
3. SwitchBoth`fare_amount_usd`, a reader at a timeBoth names hold the same values, so each reader moves on its own schedule
4. Drop`fare_amount_usd` only`fare_amount_usd`No reader is left on the old name
SELECT
COUNT(*) AS rows_missing_new_name
FROM trips
WHERE fare_amount_usd IS NULL
AND fare_usd IS NOT NULL

Run the steps out of order and readers break. Drop before switch, and any reader still on fare_usd reads nulls: the March 11 dashboard again. Switch before the copy finishes, and a switched reader sees nulls for every row older than step 1. A type change uses the same steps under a new name: to store fares as whole cents, add fare_cents, copy, switch, then drop the old field.

✓Do
  • Ship new fields with a default so they pass both directions
  • Treat every rename as add, copy, switch, drop
  • List every reader of a field before removing it
✗Don't
  • Rename or retype a field in a single release
  • Drop the old field because the producer stopped writing it
  • Trust ignore mode during a rename: it hides the break
Multiple Choice

A team renaming city to city_name finishes the add and copy steps, then removes city from the producer and the table the next morning. What happens?

What Late Data Means

Daily Life
Interviews

2 clocks on every event

Every event has 2 timestamps, even if the pipeline stores only one. Event time is when the thing happened in the world: the moment the trip ended, recorded by the phone in completed_at. Processing time is when the pipeline received it: the loader stamps loaded_at on each row it writes into trips. Usually the 2 sit seconds apart, but sometimes they sit days apart.

trip_idcompleted_at (event time)loaded_at (processing time)Gap
t_882132026-03-02 23:50:002026-03-04 08:10:0032 h 20 m
t_882142026-03-02 21:12:032026-03-02 21:12:074 s
t_882152026-03-02 18:40:002026-03-03 01:55:007 h 15 m

Trip t_88213 ended in a tunnel, and the phone held the event until it found a signal on the morning of March 4. Trip t_88215 was sent on time, then sat in a buffer during a trip service outage. Gaps like these have a handful of causes:

Phones offline in tunnels, on flights or in rural areas send events when they reconnect
Phones offline in tunnels, on flights or in rural areas send events when they reconnect
A producer outage holds events in a buffer, then releases hours of them at once
A producer outage holds events in a buffer, then releases hours of them at once
A partner delivers Monday's file on Wednesday
A partner delivers Monday's file on Wednesday
An upstream retry resends a failed batch long after its events happened
An upstream retry resends a failed batch long after its events happened

An event is late once its period is computed

A gap matters once the pipeline has already answered a question about the event's period. The nightly job runs at 02:00 and counts the previous day's trips. At 02:00 on March 3 it published 41,200 trips for March 2. Trip t_88215 arrived at 01:55 and made it in. Trip t_88213 arrived more than 30 hours after that count went out. A late event is one whose event-time period was already computed when it arrived.

SELECT
DATE_DIFF('hour', completed_at, loaded_at) AS hours_late,
COUNT(*) AS trips
FROM trips
WHERE DATE(completed_at) = DATE '2026-03-02'
GROUP BY DATE_DIFF('hour', completed_at, loaded_at)
ORDER BY hours_late
Multiple Choice

A row in trips shows completed_at 2026-03-05 22:10 and loaded_at 2026-03-06 09:30. Each day's count runs at 02:00 the next morning. Is this trip late, and for which day?

3 reactions to a late event

By the end of March 4, 180 more March 2 trips have arrived, all of them after that morning's 02:00 run. March 4 has 39,800 trips of its own. The pipeline can do 1 of 3 things with the 180.
ReactionWhat happens to the 180March 2 countMarch 4 countCost
DropDiscarded as too late41,20039,800March 2 undercounts by 180, forever
UpdateAdded to March 2, and the day recomputed41,38039,800A published number moves after people saw it
Count on arrivalAdded to the day they arrived41,20039,980Both days are off; the 2-day total is right
Count on arrival groups by processing time: a published day never moves and a month's total comes out right, while a Monday trip shows up as Wednesday's. Drop and update both group by event time and differ in whether a published day may change. Update is the one reaction that ends with every day correct, and the next section builds it for a batch pipeline.
Which reaction fits which table
  • ▸Finance close, where a filed number must never move: count on arrival, into the next open period
  • ▸Daily trip counts feeding a demand forecast: update, since the model needs each day right
  • ▸A live operations screen showing the last hour: drop, since a 2-day-old trip is noise there
Multiple Choice

A payroll report is filed with a government agency each month, and a filed month can never be changed. Which reaction to late events fits it?

Late Data: Rerun Last 7 Days

Daily Life
Interviews

Recompute the window every night

The update reaction needs a mechanism, and for a batch pipeline the simplest one is a wider rerun. Instead of computing only yesterday, the 02:00 job recomputes the last 7 days by event time. When t_88213 lands in trips on March 4, the run at 02:00 on March 5 recomputes March 2 anyway, so the late trip reaches its correct day without anyone touching it.

Run at 02:00 onDays recomputedMarch 2 countWhat changed
March 3Feb 24 to Mar 241,200First count of March 2
March 4Feb 25 to Mar 341,200Late trips not in yet
March 5Feb 26 to Mar 441,380All 180 late trips folded in
March 9Mar 2 to Mar 841,380Last run that touches March 2
March 10Mar 3 to Mar 941,380March 2 is final

Replace Each Day, Never Append

The window works only if rerunning a day is idempotent. If the job appends, the March 4 run adds a second copy of March 2 on top of the first, and by March 9 the day has been inserted 7 times. The job must replace: delete the window's rows, then insert the recomputed ones. Run it once or 5 times and the table ends in the same state.
DELETE FROM daily_trip_counts
WHERE trip_date BETWEEN current_date - INTERVAL '7' DAY AND current_date - INTERVAL '1' DAY
INSERT INTO daily_trip_counts
SELECT
date(completed_at) AS trip_date,
city,
count(*) AS trips
FROM trips
WHERE date(completed_at) BETWEEN current_date - INTERVAL '7' DAY AND current_date - INTERVAL '1' DAY
GROUP BY
date(completed_at),
city

Both the filter and the grouping use completed_at, the event time, because each day is rebuilt from every trip that belongs to it. If the insert fails after the delete, the retry repeats both statements and still ends correct.

Multiple Choice

A team switches its nightly job to cover the last 7 days with INSERT INTO daily_trip_counts SELECT ... and no delete. What does the table hold for March 2 after the March 9 run?

trip service
Trip events
warehouse table
trips
a row per trip
orchestrator
Rebuild last 7 days
daily at 02:00RETRY3IDEMPOTENCYdelete + insert per day
each rebuilt day vs its last count
Row count gate
ERRORblock if a day drops over 20%
warehouse table
daily trip counts
SLAby 03:00 daily
last 7 days marked provisional
Demand dashboard

Each night the rebuild reads 7 days of trips, and the gate compares each rebuilt day with its previous count before the dashboard sees it.

Choose the window from measured lateness

The 7-day window came from the lateness query in the previous section. Run it over a month and record what share of each day's trips had arrived by each point after the day ended.
Arrived withinShare of the day's trips, March average
2 hours (the first run)99.52%
1 day99.81%
3 days99.95%
7 days99.99%
30 days99.998%
Widening from 1 day to 7 catches nearly all of the remaining tail. Widening to 30 recovers a sliver more for more than 4 times the nightly work, and keeps 30 days provisional for readers instead of 7.
7x
nightly work vs a 1-day job
0.01%
trips arriving after day 7
Where the window stops helping
  • Trips arriving after day 7 reach trips but never daily_trip_counts. A daily check on rows loaded today with a completed_at over 7 days old counts them.
  • The rerun reads 7 days of history with today's code, so every schema change must stay backward compatible for at least that long.
Multiple Choice

A partner's daily sales file arrives late in this pattern: 92% of a day's rows within 1 day, 99.9% within 3 days, 99.95% within 14 days. Rebuilding a day takes 5 minutes. Which window is the best starting choice?

Capstone: drift and lateness together

Multiple Choice

The trip service starts renaming fare_usd to fare_amount_usd on March 10 with add, copy, switch, drop. The nightly job rebuilds 7 days of revenue from raw events stored exactly as they arrived. On March 14 the team points the job at fare_amount_usd alone. What happens to the rebuilt days?

❯❯❯PUTTING IT ALL TOGETHER

> Back at Uber's tipping launch, the trip service adds tip_usd with a default of 0. The loader runs in evolve mode with an alert naming each new column, and riders in tunnels upload trips days late. A nightly job rebuilds the last 7 days of daily_tips by completed_at, deleting and inserting each day. A month later, the team renames tip_usd to tip_amount_usd to match a naming standard.

The default of 0 makes the launch additive for any reader that does not know tip_usd. In trips, evolve leaves pre-launch rows null. sum() skips those nulls, and the rebuild reads them as coalesce(tip_usd, 0).
Evolve mode lets tips land from the first event, and the alert gives a person the review that evolve skips.
Late tunnel trips count toward their completed_at day because the window rebuilds by event time, which is the update reaction.
Delete-then-insert makes each rebuilt day idempotent. A day rebuilt on 7 nightly passes, plus any retries, still never double counts.
The later rename adds the new field, copies values into it, switches readers over and then drops the old one. The rebuild reads coalesce(tip_amount_usd, tip_usd) until its window starts after the add step.
KEY TAKEAWAYS
A loader that meets an unknown field either rejects it or lets it through, and letting it through means ignoring the field or evolving the table to hold it. Know which one yours does before the next producer release.
Backward compatible means a new reader reads old data. Forward compatible means an old reader reads new data.
Adding a field with a default is additive and safe in both directions. Removing a field in one release is destructive, and so is changing its type or its name.
Rename in 4 steps. Add the new field and copy values into it, then switch readers over, and drop the old field only after every reader has switched.
Event time is when something happened; processing time is when the pipeline saw it. A late event arrives after its period was computed.
A late event can be dropped, used to update the old result, or counted on arrival.
Rebuilding the last 7 days nightly with delete-then-insert folds late events into their correct day, and measured lateness sets the window.

Producers change fields and events show up days late. Learn the reactions, rules and rerun window that hold.

Category
Pipeline Architecture
Difficulty
beginner
Duration
25 minutes
Challenges
11 hands-on challenges

Topics covered: When a Producer Adds a Column, Forward vs Backward Compatibility, Adding Is Safe, Renaming Is Not, What Late Data Means, Late Data: Rerun Last 7 Days

Lesson Sections

  1. When a Producer Adds a Column (concepts: Schema Evolution)

    The field your loader never expected 3 reactions to an unknown field When the loader meets a field its table has no column for, it does 1 of 3 things, whether its author chose on purpose or inherited a library default. Learn which one yours does before the next producer release. No reaction is right everywhere. A table feeding driver payouts should reject, because a stale number is safer than a silently incomplete one. A clickstream table with hundreds of loosely owned fields often ignores, with

  2. Forward vs Backward Compatibility (concepts: Schema Evolution)

    Writers and readers upgrade separately A schema change splits the data into 2 versions: events written before it and events written after. It also splits the code that reads them, because the producer team and the data team ship on different days. For a while, every mix of old and new runs somewhere, and compatibility describes which mixes still work. Backward and forward 2 rules in the reading code Compatibility comes down to 2 rules in the reading code. If the reader skips fields it doesn't re

  3. Adding Is Safe, Renaming Is Not (concepts: Schema Evolution)

    3 classes of schema change The compatibility test judges a single change. A proposal also needs a verdict on its plan: can it ship today, or does it need a schedule agreed with other teams? Each proposal falls into 1 of 3 classes. A rename looks like a delete to every reader March 10 holds only the fares from before 15:00, and March 11 sums nothing but nulls, which is null. Revenue appears to fall by almost two thirds and then vanish. No run fails, and a finance analyst is the first to notice. A

  4. What Late Data Means (concepts: Late-Arriving Data)

    2 clocks on every event An event is late once its period is computed 3 reactions to a late event By the end of March 4, 180 more March 2 trips have arrived, all of them after that morning's 02:00 run. March 4 has 39,800 trips of its own. The pipeline can do 1 of 3 things with the 180. Count on arrival groups by processing time: a published day never moves and a month's total comes out right, while a Monday trip shows up as Wednesday's. Drop and update both group by event time and differ in wheth

  5. Late Data: Rerun Last 7 Days (concepts: Late-Arriving Data)

    Recompute the window every night Replace Each Day, Never Append The window works only if rerunning a day is idempotent. If the job appends, the March 4 run adds a second copy of March 2 on top of the first, and by March 9 the day has been inserted 7 times. The job must replace: delete the window's rows, then insert the recomputed ones. Run it once or 5 times and the table ends in the same state. Choose the window from measured lateness The 7-day window came from the lateness query in the previou