The Complete Guide to dbt Freshness Tests

Your CFO is presenting revenue numbers to the board. The numbers are from Tuesday. It's Friday.

Nobody knows.

This happens more often than anyone admits. And dbt freshness tests are the first line of defense.

Why Freshness Tests Matter

Data freshness tests answer one question: "Is this data recent enough to trust?"

Without them:

• Dashboards show stale data without warning
• Stakeholders make decisions on outdated information
• Pipeline failures go unnoticed for days
• Trust in data erodes slowly, then suddenly

Understanding dbt Source Freshness

dbt has built-in source freshness testing. Here's how it works:

Basic Configuration

# models/staging/_sources.yml

version: 2


sources:
  - name: production_db
    database: prod
    schema: public


    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}


    loaded_at_field: updated_at


    tables:
      - name: orders
      - name: customers
      - name: products

This configuration:

1. Checks the updated_at column for each table
2. Warns if data is older than 12 hours
3. Errors if data is older than 24 hours

Running Freshness Tests

dbt source freshness

Output:

Running source freshness checks...

Source production_db.orders: PASS (last updated 2 hours ago)
Source production_db.customers: WARN (last updated 14 hours ago)
Source production_db.products: ERROR (last updated 36 hours ago)

Beyond Basic: Advanced Patterns

Pattern 1: Different SLOs Per Table

Not all tables need the same freshness. A real-time transactions table is different from a daily aggregation.

sources:

  - name: production_db
    tables:
      - name: transactions
        freshness:
          warn_after: {count: 1, period: hour}
          error_after: {count: 2, period: hour}
        loaded_at_field: created_at


      - name: daily_summary
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}
        loaded_at_field: report_date


      - name: monthly_snapshot
        freshness:
          warn_after: {count: 7, period: day}
          error_after: {count: 14, period: day}
        loaded_at_field: snapshot_date

Pattern 2: Using dbt_utils for Model Freshness

Source freshness only works on sources. For freshness on transformed models, use dbt_utils.recency:

# models/marts/orders/_orders.yml

version: 2


models:
  - name: fct_orders
    tests:
      - dbt_utils.recency:
          datepart: hour
          field: order_timestamp
          interval: 6
          config:
            severity: warn

Pattern 3: Business Hour Awareness

Some tables only update during business hours. Standard freshness tests will false-alert on weekends.

# Custom test in tests/freshness_business_hours.sql

{% test freshness_business_hours(model, column_name, max_hours) %}


WITH latest AS (
    SELECT MAX({{ column_name }}) AS last_update
    FROM {{ model }}
),


business_hours AS (
    SELECT 
        last_update,
        EXTRACT(DOW FROM CURRENT_TIMESTAMP) AS day_of_week,
        EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour_of_day
    FROM latest
)


SELECT 
FROM business_hours
WHERE 
    -- Only check during business hours (Mon-Fri, 9 AM - 6 PM)
    day_of_week BETWEEN 1 AND 5
    AND hour_of_day BETWEEN 9 AND 18
    AND last_update < CURRENT_TIMESTAMP - INTERVAL '{{ max_hours }} hours'


{% endtest %}

Pattern 4: Freshness with Row Counts

Sometimes data arrives but is incomplete. Combine freshness with volume checks:

models:

  - name: stg_orders
    tests:
      - dbt_utils.recency:
          datepart: hour
          field: updated_at
          interval: 4


      - dbt_utils.expression_is_true:
          expression: "COUNT() > 100"
          config:
            where: "DATE(created_at) = CURRENT_DATE"

Integrating with CI/CD

GitHub Actions Example

# .github/workflows/dbt-freshness.yml

name: dbt Freshness Check


on:
  schedule:
    - cron: '0    '  # Every hour
  workflow_dispatch:


jobs:
  freshness:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3


      - name: Install dbt
        run: pip install dbt-snowflake


      - name: Run freshness checks
        run: dbt source freshness --profiles-dir .
        env:
          DBT_PROFILES_DIR: ${{ github.workspace }}
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}


      - name: Slack notification on failure
        if: failure()
        uses: 8398a7/action-slack@v3
        with:
          status: failure
          fields: repo,message,author
        env:
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK }}

dbt Cloud Job

In dbt Cloud:

1. Create a new job
2. Add command: dbt source freshness
3. Set schedule: Every 30 minutes
4. Enable Slack notifications on failure

Common Mistakes

Mistake 1: One SLO for Everything

# ❌ Bad: Same SLO for all tables

sources:
  - name: prod
    freshness:
      error_after: {count: 24, period: hour}
    tables:
      - name: realtime_events  # Needs hourly!
      - name: annual_report    # Daily is fine

# ✅ Good: Table-specific SLOs

sources:
  - name: prod
    tables:
      - name: realtime_events
        freshness:
          error_after: {count: 1, period: hour}
      - name: annual_report
        freshness:
          error_after: {count: 7, period: day}

Mistake 2: Wrong Timestamp Column

# ❌ Bad: Using created_at for mutable data

loaded_at_field: created_at  # Only reflects initial creation

✅ Good: Using updated_at

loaded_at_field: updated_at  # Reflects latest modification

Mistake 3: No Alerting

Running freshness tests without alerting is like having a smoke detector with no alarm.

Must have:

• Slack/Teams notifications
• PagerDuty for critical tables
• Dashboard visibility

Pallisade Auto-Fix for Freshness

When Pallisade detects a table without freshness tests, we generate the fix automatically:

Issue Detected: > Table fct_orders has no freshness test. Last update was 3 days ago.

Auto-Generated Fix:

# Add to models/marts/orders/_orders.yml

version: 2


models:
  - name: fct_orders
    description: "Fact table for customer orders"
    tests:
      - dbt_utils.recency:
          datepart: hour
          field: order_timestamp
          interval: 24
          config:
            severity: error


    columns:
      - name: order_timestamp
        description: "Timestamp of order creation"
        tests:
          - not_null

[Create PR] [Copy to Clipboard]

Getting Started Checklist

• [ ] Identify your 5 most critical tables
• [ ] Define freshness SLOs for each (hours/days)
• [ ] Add source freshness configuration
• [ ] Add dbt_utils.recency tests for models
• [ ] Set up CI/CD job to run hourly
• [ ] Configure Slack alerting
• [ ] Document SLOs for stakeholders

Freshness SLO Template

---

Need help implementing freshness tests?

Pallisade scans your dbt project and auto-generates freshness tests for every table—with intelligent SLO recommendations based on your update patterns.

Get Your Free Data Freshness Audit →