Three Cadences, One Output
A revenue team wants hourly revenue in USD, and each hour should appear on the dashboard only once all of its inputs are in. Those inputs arrive on 3 cadences, from 3 DAGs owned by 3 teams.
| Upstream DAG | Writes | Schedule | Loaded through, at finish |
|---|---|---|---|
| `orders_15min` | `warehouse.orders` | interval, every 15 minutes from :01 | the quarter hour that ended |
| `payments_hourly` | `warehouse.payments` | cron `5 * * * *` | the top of the current hour |
| `fx_rates_daily` | `warehouse.fx_rates` | event, when the bank's file lands | midnight ending the day that is starting, whose rates the overnight file carries |
The offset design and where it breaks
A first draft schedules revenue_by_currency at :30 past every hour, reasoning that orders finish by :06, payments by :15 and rates by 06:20. That stacks 3 time offsets, each failing silently: a payments file 40 minutes late means the 14:30 run builds 13:00 to 14:00 without payments and publishes it as final. Sensors fit badly too: the event-triggered rates DAG has no schedule from which to compute the run to wait for, and mapping each hourly run to 4 orders runs works but hard-codes both teams' cadences.
Join on freshness instead
In the design that holds up, every upstream DAG ends with a task that records how far its table is loaded, as a row in pipeline_freshness, and that task declares the table as an asset. Then revenue_by_currency runs whenever any of the 3 assets updates, and its first task asks the freshness table a single question: up to which hour are all 3 inputs complete?
- app database (source, operational database)
- payment processor (source, hourly settlement file)
- bank rates file (source, overnight file drop)
- orders_15min (transform, orchestrator, interval); freshness: 15 min
- payments_hourly (transform, orchestrator, cron :05); retries: 2
- fx_rates_daily (transform, orchestrator, external trigger); alerting: no run by 06:20 UTC
- pipeline_freshness (storage, loaded_through per table)
- revenue_by_currency (transform, orchestrator, any asset update); freshness: final through min loaded_through
- revenue dashboard (consumer, dashboard)
- app database -> orders_15min
- payment processor -> payments_hourly
- bank rates file -> fx_rates_daily
- orders_15min -> pipeline_freshness
- payments_hourly -> pipeline_freshness
- fx_rates_daily -> pipeline_freshness
- orders_15min -> revenue_by_currency
- payments_hourly -> revenue_by_currency
- fx_rates_daily -> revenue_by_currency
- pipeline_freshness -> revenue_by_currency
- revenue_by_currency -> revenue dashboard
- Sources: app database, payment processor, bank rates file
- Ingest DAGs: orders_15min, payments_hourly, fx_rates_daily
- Freshness: pipeline_freshness
- Serving: revenue_by_currency, revenue dashboard
pipeline_freshness at 14:40 UTC on 2026-10-24
| table_name | loaded_through | updated_at |
|---|---|---|
| warehouse.orders | 2026-10-24 14:30 | 2026-10-24 14:36 |
| warehouse.payments | 2026-10-24 14:00 | 2026-10-24 14:12 |
| warehouse.fx_rates | 2026-10-25 00:00 | 2026-10-24 03:25 |
SELECT
date_trunc('hour', min(loaded_through)) AS build_through,
min_by(table_name, loaded_through) AS slowest_input,
count(*) AS inputs_reporting
FROM pipeline_freshness
WHERE table_name IN ('warehouse.orders', 'warehouse.payments', 'warehouse.fx_rates')The run then builds every hour that ends by build_through and has not been built yet, and it writes build_through beside the output so the dashboard can label its numbers final through 14:00. If inputs_reporting is below 3, an upstream has never reported, and the run fails loudly. The slowest input sets the line, whichever input that happens to be.
At 09:50 UTC on 2026-10-24, pipeline_freshness shows orders loaded through 2026-10-24 09:45, payments through 2026-10-24 09:00 and fx_rates through 2026-10-24 00:00. What does the freshness query return?
- build_through 09:00, slowest input warehouse.payments
- build_through 09:00, slowest input warehouse.orders
- build_through 09:45, slowest input warehouse.orders
- build_through 2026-10-24 00:00, slowest input warehouse.fx_rates
| Time (UTC) | Update that starts the run | build_through | Hours built |
|---|---|---|---|
| 14:06 | orders loaded through 14:00 | 13:00 | none new |
| 14:12 | payments loaded through 14:00 | 14:00 | 13:00 to 14:00 |
| 14:21 | orders loaded through 14:15 | 14:00 | none new |
| 14:36 | orders loaded through 14:30 | 14:00 | none new |
| 15:06 | orders loaded through 15:00 | 14:00 | none new |
| 15:12 | payments loaded through 15:00 | 15:00 | 14:00 to 15:00 |
When a payments file is 2 hours late, orders updates keep starting runs that find build_through stuck at 14:00, so the dashboard keeps saying final through 14:00 rather than publishing the hour ending 15:00 with no payments. The alert pages the owner of slowest_input, with a threshold per input: orders or payments at 90 minutes behind the clock, rates only if still slowest after 06:20, since until the bank's file lands they hold build_through at midnight every night.
A teammate proposes running revenue_by_currency only when all 3 assets have updated since its last run. What happens to the dashboard?
- Hourly revenue arrives about once a day, after the rates file lands
- Hourly revenue arrives every 15 minutes, after each orders load
- Hourly revenue arrives every hour, after each payments load
- Hourly revenue stops entirely, since the 3 assets never align
About this lesson
This section is part of the Orchestration: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.