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.
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.
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
- 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.
| Reaction | New rows in trips | tip_usd column | Who notices, and when |
|---|---|---|---|
| Reject | 0 of 3: the batch fails | Absent | On-call, at the failed run |
| Ignore | 3 of 3 | Absent, values lost | An analyst, weeks later, asking for tips |
| Evolve | 3 of 3 | null, 2.00, 0.00 | Whoever reviews new columns, if anyone does |
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?
tip_usd means, you reload it from the raw copy instead of asking the producer to resend.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?
Writers and readers upgrade separately
| Day | Producer writes | Loader code | Mix in play |
|---|---|---|---|
| Monday | v1 events, 4 fields | v1 | Old reader, old data |
| Tuesday | v2 events, adds `tip_usd` | v1 | Old reader, new data |
| Thursday | v2 events | v2, knows `tip_usd` | New reader, new data |
| Thursday, reprocessing last week | v1 events from the raw copy | v2 | New reader, old data |
Backward and forward
- 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
- 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
| Change | Backward? | Forward? | Why |
|---|---|---|---|
| Add `tip_usd` with default 0 | Yes | Yes | New reader fills 0 for old events; old reader skips the field |
| Add `tip_usd` as required, no default | No | Yes | New reader finds no value in old events; old reader skips the field |
| Remove `promo_code`, which had a default | Yes | Yes | New reader skips it in old events; old reader fills the default |
| Remove `city`, required by old readers | Yes | No | New reader skips it in old events; old reader finds it missing |
| Rename `city` to `city_name` | No | No | Each side looks for a name the other side's events lack |
| Change `fare_usd` from a number to text | No | No | Neither reader expects the other's value type |
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 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?
3 classes of schema change
- 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.
| trip_date | revenue_usd |
|---|---|
| 2026-03-08 | 612,400 |
| 2026-03-09 | 598,900 |
| 2026-03-10 | 214,700 |
| 2026-03-11 | null |
Which of these proposals is coordinated?
The Safe Rename: Add, Copy, Switch, Drop
| Step | Producer writes | Readers use | Why nothing breaks |
|---|---|---|---|
| 1. Add | Both `fare_usd` and `fare_amount_usd` | `fare_usd` | A new field passes both compatibility directions |
| 2. Copy | Both | `fare_usd` | Rows from before step 1 get `fare_amount_usd` filled from `fare_usd` |
| 3. Switch | Both | `fare_amount_usd`, a reader at a time | Both 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 |
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.
- 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
- 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
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?
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_id | completed_at (event time) | loaded_at (processing time) | Gap |
|---|---|---|---|
| t_88213 | 2026-03-02 23:50:00 | 2026-03-04 08:10:00 | 32 h 20 m |
| t_88214 | 2026-03-02 21:12:03 | 2026-03-02 21:12:07 | 4 s |
| t_88215 | 2026-03-02 18:40:00 | 2026-03-03 01:55:00 | 7 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:
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.
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
| Reaction | What happens to the 180 | March 2 count | March 4 count | Cost |
|---|---|---|---|---|
| Drop | Discarded as too late | 41,200 | 39,800 | March 2 undercounts by 180, forever |
| Update | Added to March 2, and the day recomputed | 41,380 | 39,800 | A published number moves after people saw it |
| Count on arrival | Added to the day they arrived | 41,200 | 39,980 | Both days are off; the 2-day total is right |
- ▸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
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?
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 on | Days recomputed | March 2 count | What changed |
|---|---|---|---|
| March 3 | Feb 24 to Mar 2 | 41,200 | First count of March 2 |
| March 4 | Feb 25 to Mar 3 | 41,200 | Late trips not in yet |
| March 5 | Feb 26 to Mar 4 | 41,380 | All 180 late trips folded in |
| March 9 | Mar 2 to Mar 8 | 41,380 | Last run that touches March 2 |
| March 10 | Mar 3 to Mar 9 | 41,380 | March 2 is final |
Replace Each Day, Never Append
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.
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?
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
| Arrived within | Share of the day's trips, March average |
|---|---|
| 2 hours (the first run) | 99.52% |
| 1 day | 99.81% |
| 3 days | 99.95% |
| 7 days | 99.99% |
| 30 days | 99.998% |
- 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.
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
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?
> 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.
tip_usd. In trips, evolve leaves pre-launch rows null. sum() skips those nulls, and the rebuild reads them as coalesce(tip_usd, 0).completed_at day because the window rebuilds by event time, which is the update reaction.coalesce(tip_amount_usd, tip_usd) until its window starts after the add step.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
- 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
- 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
- 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
- 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
- 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