The Weekly: a data engineering challenge every week, scored blind

The Dwell

Next drop Monday Oct 26, 16:00 UTC

Week 6

hosted bydatadriven

Results

next drop

5 scored

16 submissions

first in german_gerken, 22h 7m

coral_mesa_99430.9558

german_gerkenKobe_Bryant

Your DAG2026-10-24 · night 30 of 30

unit_visit290,765 rows
tariff_rate24 rows
line_operator9,213 rows
unit_visit_history
tariff_rate_history
line_operator_history
storage_charges
storage_charges
  • 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

unit_visit
tariff_rate
01tariff_cdTEXT
04_batch_idTEXT
05_loaded_atTEXT
line_operator
02line_nameTEXT
05_batch_idTEXT
06_loaded_atTEXT

Target table

storage_charges

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.
Questions go in this week's thread.