The Dwell: Week 6
The Dwell
Next drop Monday Oct 26, 16:00 UTC5 in
Your DAG2026-10-24 · night 30 of 30
- visit_gkeyTEXT
- unit_nbrTEXT
- visit_stateTEXT
- gate_out_timeTEXT
- booking_refTEXT
- storage_usdNUMERIC
- storage_in_period_usdNUMERIC
- bill_toTEXT
The challenge
You're a data engineer at a port's container terminal. The terminal charges each shipping line for every day a container sits in its yard, and billing needs one clean row per visit, a container's stay from ship to gate. Each night, a job copies 3 raw tables out of 2 source systems. The yard system provides unit_visit, a change log with a row for every visit that changed or was charged that night, and line_operator, a nightly snapshot of the shipping lines. Billing's tariff feed provides tariff_rate, a rate table that arrives in full on the first night; after that, only the rows that changed arrive. Write the DAG that builds billing's storage charges for the period ending , from the nightly loads between and .
The mess
3 systems write to the yard database, each on its own clock, each filling in only the columns it owns. A hold or a waiver settled late makes billing send that day again. A night's copy that fails is run again by hand, days later. A copy can still be running at the morning run, or come back empty. A new shipping line starts out as a test carrier. 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 billing
- Visits of a test line are not billed: leave them out.
- The terminal's day runs 06:00 to 06:00 local time (UTC+8).
- A rate applies from its effective day until the next one starts.
- A rate sent again for the same day replaces the earlier one.
- The gate app logs remarks as a line of at most 40 characters.
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.