Forty staggered scheduled queries with no real dependency graph

Choose Dataform in BigQuery. Models are SQLX files; each declares SELECT ... FROM ${ref('stg_orders')}, and ref() is the dependency edge. The compiler builds the DAG from the SQL, so ordering is derived rather than guessed, and independent branches run concurrently inside BigQuery. Forty models across roughly six levels finishes in minutes, not six hours - the 6 hours was never compute, it was 15-minute sleeps. You also get compiled SQL in Git, release configs, built-in assertions, and a serverless runner nobody has to operate. Team skill is SQL and Git; zero Python.

Against the status quo. Time offsets are a latency guess, not a dependency. They encode an assumption about how long the previous query takes and fail silently when it is wrong - which is precisely the 'publishes yesterday's data' symptom. There is also no lineage, so a source column change has no impact analysis.

Against Composer. Airflow is correct when the DAG crosses systems: trigger a Dataflow job, wait on a GCS sensor, call an external API, retry with custom logic. Here every node is BigQuery SQL. Composer adds an environment to size and patch (roughly $300-400/month at minimum), Python DAG authoring your team does not have, and a second place where dependency logic can drift away from the SQL. Keep it as an escalation path if cross-system orchestration appears later.

Also: tag the 12 core models and schedule them hourly on their own trigger, so the freshness SLA is not coupled to the full 40-model chain.

Hourly full rebuild is the wrong default. 900 M rows at 150-200 bytes is 150-180 GB scanned per run. The on-demand dollars are small ($1 per pass), but 24 rebuilds a day of a 900 M-row table consume the slots that your ad-hoc users need and blow the wall-clock budget the SLA is measured in.

Incremental model with a bounded lookback, compiled to MERGE:

• type: 'incremental', uniqueKey: ['event_id'], partitionedBy: 'event_date', clusterBy: ['customer_id'] - Dataform compiles this to a MERGE on event_id, so retries and re-runs converge instead of appending duplicates.
• Lookback of two days, not the current hour: WHERE event_date BETWEEN DATE_SUB(run_date, INTERVAL 2 DAY) AND run_date. Device lateness is up to 6 hours, so an hour-only filter permanently loses late rows and never self-heals.
• Because the target is partitioned on event_date and the merge prunes to those partitions, cost tracks the lookback window rather than the table size.

Guard rails. First execution builds as a table and later runs merge; a column type change forces a full refresh, so schedule a weekly off-peak full rebuild with maximum_bytes_billed set so it fails loudly rather than running all night. Add an assertion that the row count delta versus the prior run is within expected bounds - a silent zero-row merge is the failure mode incremental models actually have.

Evaluate a materialized view for the 24-hour rolling dashboard before inventing a second pipeline: if the logic is a plain aggregate it refreshes automatically and costs nothing to maintain. Expect to keep the incremental model as source of truth because MVs will not accept arbitrary joins or window functions.

Per-table atomicity already exists. CREATE OR REPLACE TABLE and a single MERGE are atomic - readers see the old table or the new one, never a partial load. So the 'empty or half-loaded' fear is mostly already handled; the real defect is cross-table inconsistency.

Cross-table consistency: publish a run as a versioned set. Write all mart outputs for a run into mart_v{run_id} tables (or a _staging dataset), then point a mart_current view per entity at the winning version. Flipping all views at the end of the run is a short scripted step, and each flip is individually atomic - the window where a dashboard joins fact from run N to dimension from run N-1 shrinks from minutes to seconds. Better still, model the dimension as SCD-2 with valid_from/valid_to, so fact-to-dimension joins are consistent by event time rather than by load time, and the window disappears entirely.

Kill silent staleness with assertions that block the publish:

• row_count > 0 on every mart model - never CREATE OR REPLACE an empty result into production.
• Freshness: MAX(event_timestamp) > run_start - INTERVAL 2 HOUR.
• Referential integrity: every fact customer_key exists in the dimension version being published.

Dataform assertions fail the workflow, so the bad table never becomes current. That is the difference between a failed run and a wrong dashboard.

Detect it anyway, because assertions only cover what you thought of. A log-based metric on the workflow's success log, alerting when a tagged core model has no successful publish within 60 minutes - the SLA is on the publish event, not on individual queries. Combined with immutable versioned outputs, a missed run leaves last hour's correct data in place instead of corrupting this hour's.