dbt: Flexible Freshness Thresholds in dbt Using Custom Time-Aware Macros

Sure! Here’s a polished and professional English version of your explanation, suitable for a technical blog or your personal website:

💡 Flexible Freshness Thresholds in dbt Using Custom Time-Aware Macros

When implementing freshness checks in dbt, using custom macros with time-based thresholds is the most flexible and maintainable solution—especially in environments where expectations differ between business and non-business hours.

🧠 Problem

Standard freshness configurations in schema.yml are static, meaning they apply the same thresholds at all times. However, in many real-world scenarios:

• During business hours, teams need stricter thresholds to catch data delays quickly.
• During off-hours (nights, weekends), more relaxed thresholds are acceptable.
• Managing multiple sources or environments with separate configs can quickly become unmanageable.

✅ Solution: Custom Macro with Dynamic Logic

By using a custom macro, we can define thresholds like warn_after and error_after based on current time, day of week, or even holidays—without duplicating logic or configurations.

📦 Example: schema.yml

version: 2

sources:
  - name: raw
    tables:
      - name: sales
        freshness:
          warn_after: "{{ freshness_threshold('warn') }}"
          error_after: "{{ freshness_threshold('error') }}"
        loaded_at_field: ingestion_timestamp

version: 2

sources:
  - name: raw
    tables:
      - name: sales
        freshness:
          warn_after: "{{ freshness_threshold('warn') }}"
          error_after: "{{ freshness_threshold('error') }}"
        loaded_at_field: ingestion_timestamp

⚙️ Macro: freshness_threshold.sql

{% macro freshness_threshold(level) %}
  {% set current_hour = modules.datetime.datetime.now().hour %}
  {% set current_day = modules.datetime.datetime.now().weekday() %}

  {% if 0 <= current_day <= 4 and 9 <= current_hour <= 18 %}
    {# Business hours: Mon–Fri, 9am–6pm #}
    {% if level == "warn" %}
      {{ "{minutes: 15}" }}
    {% elif level == "error" %}
      {{ "{minutes: 30}" }}
    {% endif %}
  {% else %}
    {# Off-hours: evenings & weekends #}
    {% if level == "warn" %}
      {{ "{hours: 2}" }}
    {% elif level == "error" %}
      {{ "{hours: 4}" }}
    {% endif %}
  {% endif %}
{% endmacro %}

{% macro freshness_threshold(level) %}
  {% set current_hour = modules.datetime.datetime.now().hour %}
  {% set current_day = modules.datetime.datetime.now().weekday() %}

  {% if 0 <= current_day <= 4 and 9 <= current_hour <= 18 %}
    {# Business hours: Mon–Fri, 9am–6pm #}
    {% if level == "warn" %}
      {{ "{minutes: 15}" }}
    {% elif level == "error" %}
      {{ "{minutes: 30}" }}
    {% endif %}
  {% else %}
    {# Off-hours: evenings & weekends #}
    {% if level == "warn" %}
      {{ "{hours: 2}" }}
    {% elif level == "error" %}
      {{ "{hours: 4}" }}
    {% endif %}
  {% endif %}
{% endmacro %}

🚀 Benefits

• Single source of truth for freshness logic.
• Automatic adjustment of thresholds based on time.
• Zero duplication across models or environments.
• Easy to update as business rules evolve.

🔄 Bonus

You can extend this approach further by:

• Reading time windows from a config table or YAML file.
• Adding holiday logic.
• Logging freshness behavior to monitoring tools.
• Triggering alerts when thresholds are exceeded.

This method keeps your dbt project clean, adaptable, and production-ready, ensuring that your data freshness checks align with real-world operational expectations—no matter the time of day.