Defining Source Schemas and Freshness Metrics

In this lesson, you will learn how to formally define your raw data sources in dbt and configure freshness thresholds to ensure your data pipeline remains reliable and up-to-date.

Defining Your Sources

In dbt, raw data that exists outside your project is called a Source. While you can simply reference raw tables using their full database names in your models, defining them in a YAML file offers significant advantages. It creates a centralized location to document your raw data and allows you to use the source() function, which tracks lineage even before your transformations begin.

You define these sources in a file ending in .yml within your models/ directory:

version: 2

sources:
  - name: raw_ecommerce
    database: analytics
    schema: raw_data
    tables:
      - name: orders

Implementing Freshness Metrics

Data engineers often struggle with "stale" data—situations where the pipeline runs successfully, but the underlying source data hasn't been updated by the upstream system. To prevent this, dbt allows you to define freshness metrics. By specifying freshness blocks, dbt calculates how long it has been since the most recent record was loaded into your source table.

You can add these metrics directly to your source definition:

    tables:
      - name: orders
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
        loaded_at_field: updated_at

• loaded_at_field: This tells dbt which column contains the timestamp of when the record was last modified.
• warn_after: If the most recent timestamp is older than 12 hours, dbt will issue a warning.
• error_after: If the data is older than 24 hours, the freshness check will fail.

You can trigger these checks by running dbt source freshness. If a check fails, your team is alerted that the upstream system is not providing current data, allowing you to debug the latency before your downstream models process incomplete information.

Key Takeaway: Defining sources in YAML allows you to automate lineage tracking and set proactive latency thresholds, ensuring you catch stale data before it impacts your downstream reporting.