Define dbt sources with freshness tests and configure source freshness alerting in a CI pipeline

Steps

1. Define sources in a sources.yml file under a models directory: specify the database, schema, tables, and optionally a loaded_at_field (a timestamp column indicating when each row was loaded) for each source table you want to monitor
2. Add freshness constraints under each source table: freshness: warn_after: {count: 12, period: hour} and error_after: {count: 24, period: hour}; these thresholds determine when dbt source freshness reports warn or error status
3. Run dbt source freshness from the CLI: dbt source freshness --select source:<source_name>; dbt queries the max(loaded_at_field) for each table and compares it to the current timestamp, then outputs warn/error/pass status
4. Integrate into CI by adding dbt source freshness as a step before dbt build in your pipeline YAML; exit on error status so the pipeline fails if sources are stale before model runs begin
5. For alerting, parse the freshness output file (target/sources.json) in CI and send notifications (Slack, PagerDuty) based on source status; or use dbt Cloud's built-in source freshness alerting if running on dbt Cloud