Incremental Order Events Pipeline

Instacart: Load raw order events one day at a time into clean tables that stay correct with duplicates, late events and a changing schema.

Instacart's apps send an event every time an order moves forward: placed, shopper assigned, picked and delivered. The events land in storage as one file per day. Analysts keep getting different order counts from the same data, and the operations team wants daily numbers it can trust. You're a data engineer on the fulfillment data team.

Build a pipeline that loads the daily event files one at a time into clean tables, producing an order table and daily metrics that are correct and do not change when the pipeline runs again.

1. 1Load the three event files in date order, one run per file, as if each one arrived on its own day. Profile each file before transforming it.
2. 2Design a staging layer that keeps every event exactly once, even when the same event is sent twice or arrives in a later file.
3. 3Handle the new column that appears in the third file without breaking the earlier loads.
4. 4Build a fact_orders table with one row per order: order_id, store_id, order_date (the UTC date of the placed event), placed_at, final_status (delivered, canceled or in_progress) and minutes_to_deliver (from placed to delivered, rounded to 1 decimal place).
5. 5Build a daily_order_metrics table with order_date, orders_placed, orders_delivered, orders_canceled and avg_minutes_to_deliver, rounded to 1 decimal place. A day's numbers must update when its late events arrive in a later file.
6. 6Make every load safe to run twice: loading the same file again must not change the output tables. Add data tests for unique orders, valid statuses and events in a sensible order.
7. 7Write a README that explains how to run the pipeline, the table design and every data problem you found.

All files come in one download.

A public GitHub repo with your pipeline (Python, SQL or dbt), the fact_orders and daily_order_metrics outputs as CSV files, your data tests, and a README explaining how to run it and how it handles duplicates, late events and schema changes.

When you're done, post your repo in the Solutions tab to share it with other learners.