The Close: Week 5
The Close
Next drop Monday Oct 19, 16:00 UTC13 in
Your DAG2026-10-17 · night 30 of 30
- payment_idTEXT
- statusTEXT
- amount_usdNUMERIC
- settled_usdNUMERIC
- settled_in_period_usdNUMERIC
- captured_atTEXT
- order_refTEXT
- merchant_countryTEXT
The challenge
You're a data engineer at a card payments company. Every night, a job copies the day's changes from its database into 3 raw tables, which keep only recent copies. Finance is closing its books through and needs 1 clean row per payment. Write the DAG that builds that table from to : it runs on the schedule you set, and each run sees only what the raw tables hold then.
The mess
3 services write to the database, each in its own format and on its own clock, and each records only what it knows. Columns changed meaning, and old rows were never updated. Support fixes stuck payments in the database. A night that fails, or a day with wrong figures, is sent again days later. A copy can still be running when a run starts, or arrive empty. Each export tool writes NULL its own way.
Raw tables
Target table
Click a column for what it holds, or click the bottom of a sheet to see its sample rows.
Notes from finance
- Test merchants are not business: their payments stay out.
- A day with no rate uses the latest earlier rate.
- A rate landed again replaces the one before it.
- The landing column for metadata is 40 characters wide.
How you're scored
- +1the right value. A right null counts.
- 0null where the source held a value. Silence is free.
- −1a wrong value. Silent corruption costs.
- −2a row that should not exist: no matching key, or a second row for the same key. The last row for a key is the one scored.