Load these before editing — the warehouse file is the part most often wrong if guessed:
-
Detect the warehouse. Read profiles.yml (the type: — snowflake, bigquery,
databricks, duckdb/MotherDuck, redshift, fabric, postgres) or ask. The warehouse
decides how freshness can be computed, and this is narrower than dbt's own metadata support
— judge it by Orchestra SAO's matrix, not dbt's. Read the matching warehouses/*.md
(other.md for anything unlisted).
-
Infer the freshness signal — actively, per source. Default to figuring it out, not
asking. For each source table, work down this order and use the first that applies; only leave
freshness unset if the user told you to, or if you genuinely can't locate any usable signal:
- Find a real load-timestamp column →
loaded_at_field. Look first at the columns the
source already declares in YAML, then — if your client can run read-only warehouse queries —
list the table's columns (information_schema.columns, describe table, etc.) and pick the
best load/sync timestamp. Strong candidates: loaded_at, _loaded_at, _synced_at,
_fivetran_synced, _airbyte_emitted_at, ingested_at, etl_loaded_at, dbt_loaded_at.
Weaker fallbacks (event time, not load time): updated_at, created_at — usable, but
confirm with the user since they can lag the actual load. (Large/partitioned table → add a
filter or a bounded loaded_at_query for cost.)
- No load column, but the warehouse exposes simple last-modified metadata → author a
metadata
loaded_at_query so dbt reads it cheaply instead of scanning data. Available
where simple: Snowflake (INFORMATION_SCHEMA … LAST_ALTERED) and BigQuery
(__TABLES__.last_modified_time) — see those warehouse files for the exact query. Confirm the
view/column is reachable; warn the user it reflects any change, not just loads.
- Databricks → you may omit
loaded_at_field entirely; Orchestra infers freshness via
DESCRIBE HISTORY.
- Nothing locatable (MotherDuck/DuckDB, Redshift, Fabric, Postgres, others with no obvious
column and no simple metadata) → ask the user which column marks load time. Only leave that
source's freshness unset if they can't say or explicitly want it skipped — and call it out.
Net: try to populate freshness for every source you can from warehouse metadata/columns; empty
is the exception (user opt-out or no signal found), not the default.
-
Check the dbt version. From require-dbt-version in dbt_project.yml or dbt --version.
1.9+ → put freshness under config:; 1.10+ → loaded_at_field under config: too. If the
project already uses an older root-level form, stay consistent with it. (See the reference.)
-
Find the sources. Locate existing sources: YAML (often models/**/_sources.yml or
models/**/src_*.yml). If sources aren't defined yet, that's a prerequisite — define them or
tell the user. Read a neighbouring source file to match the project's naming and layout.
-
Author freshness. Add warn_after/error_after based on each source's real load cadence
plus headroom (see the reference for picking thresholds). Add the freshness signal chosen in
step 2 (loaded_at_field, a metadata loaded_at_query, or — Databricks only — neither). Add a
filter for soft-deletes or partition pruning where it helps cost. Don't invent tight
thresholds you can't justify — leave a marked placeholder and explain it if cadence is unknown.
-
Ensure SAO is enabled on the Orchestra task. use_state_orchestration: true is the SAO
master switch — it makes Orchestra consume all SAO config (freshness and build_after),
not a freshness-specific setting. Find the dbt Core task (integration: DBT_CORE,
integration_job: DBT_CORE_EXECUTE) and make sure it's on. Follow orchestra-task.md for the
Git-backed (edit YAML + commit) vs Orchestra-backed (validate + update_pipeline, falling back
to migrate_pipeline on a 422) distinction. If it's already enabled (e.g. build_after was set
up first), just confirm it — don't re-toggle. Don't touch the task's commands — flipping
the toggle is all that's needed; the existing dbt build is fine. Orchestra runs the freshness
check for you, so do not prepend dbt source freshness or change the dbt commands.
-
Hand off. Report: files changed, the freshness signal used per source (real column vs
metadata query) and why, thresholds (and any placeholders to tune), whether SAO was newly
enabled, and how to verify (next step).
Tell the user how to confirm, rather than executing. This is a local sanity check, not a
pipeline step — don't add it to the Orchestra dbt task:
