Warehouse production gotchas — cost, full refreshes, late data

At a glance

Learning objectives

• Gate full-refreshes on row thresholds and criticality before they run
• Size lookback windows against real late-arriving data
• Keep warehouse cost visible with weekly top-cost query reviews

What you need

• A dbt project with at least one incremental model in production
• The manifest stats the guard hook reads row thresholds from
• Install: npx de-agent-platform add dbt-model-review

Steps

1. See the blast radius concretely — `dbt run --full-refresh --select fct_orders` on a 4B-row model is the command the guard pauses for confirmation. (from hook-dbt-full-refresh-guard )
2. Understand the gate — the hook inspects dbt commands for `--full-refresh`; if any affected model exceeds a row threshold from the manifest's stats or is tagged `critical`, it requires an explicit confirmation token and logs the operator identity. (from hook-dbt-full-refresh-guard )
3. Late data is the silent drop — a late-arriving order missed means the lookback window was too short. (from project-dbt-warehouse-tests )
4. Environments lie too — CI green but prod differs means CI ran on a different target/schema. (from project-dbt-warehouse-tests )
5. Deletes never arrive on their own — decide explicitly between soft-delete and a periodic full-refresh. (from skill-dbt-model-review )
6. Watch the bill — review weekly query history for the top cost queries and alert on the partitions_scanned/partitions_total ratio. (from runbook-warehouse-query-slow )
7. Harden the loop — CI builds to a per-PR staging schema and runs full tests, source freshness checks watch raw, and exposures are declared for dashboards. (from project-dbt-warehouse-tests )

Check yourself

• A full-refresh of your largest model now requires a confirmation token and is logged with the operator identity.
• Your lookback window is sized from observed late-arriving data, not guessed.
• Top-cost queries are reviewed weekly and the partitions ratio has an alert.

Worked example

dbt run --full-refresh --select fct_orders
# on a 4B-row model this is paused for confirmation

CI builds to a per-PR staging schema and runs full tests
Source freshness checks on raw
Exposures declared for dashboards

Sourced from

• dbt Full-Refresh Guard
• dbt → warehouse → tests → docs
• dbt Model Review
• Warehouse query is slow

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Late-arriving order missed → lookback window too short.
• CI green but prod differs → CI ran on a different target/schema.
• Deletes never arrive. Decide explicitly: soft-delete, or periodic full-refresh.

Production considerations

• Leave the full-refresh guard hook enabled — it pauses destructive rebuilds on models over the row threshold or tagged critical.
• Run CI to a per-PR staging schema with full tests, source freshness on raw, and declared exposures.

Diagram

production gotchas
   |
   |- --full-refresh --> row threshold / critical tag --> confirmation + logged identity
   |
   |- incremental --> lookback window sized for late data
   |
   |- deletes --> soft-delete or periodic full-refresh, decided explicitly
   |
   |- cost --> weekly top-cost review + partitions-ratio alert

Registry stats