How it Works

The expect_row_values_to_have_data_for_every_n_datepart test from the dbt-expectations package validates that a model contains at least one row for every interval within a specified date range. This is critical for time-series models where gaps in the date spine would silently break cumulative calculations, trend lines, or period-over-period comparisons.

Key arguments include date_col, date_part, interval, start_date, and end_date.

Steps and Conditions

1. Date Column: Specify the timestamp or date column using date_col.

2. Date Part & Interval: Define the expected granularity (e.g., every 1 day, every 1 hour).

3. Date Range: Set the expected start_date and end_date.

4. Execution: A date spine is generated and compared against the model's actual date values.

5. Outcome: Pass if all expected intervals are present; fail if any gaps exist.

Example Usage: Daily Reporting

A daily sales reporting model must have exactly one row per day for the full reporting period. Any missing day would break cumulative and rolling average calculations.

models:
  - name: daily_sales_summary
    columns:
      - name: report_date
        tests:
          - dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
              date_col: report_date
              date_part: day
              interval: 1
              start_date: '2024-01-01'
              end_date: '2024-12-31'

models:
  - name: daily_sales_summary
    columns:
      - name: report_date
        tests:
          - dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
              date_col: report_date
              date_part: day
              interval: 1
              start_date: '2024-01-01'
              end_date: '2024-12-31'

models:
  - name: daily_sales_summary
    columns:
      - name: report_date
        tests:
          - dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
              date_col: report_date
              date_part: day
              interval: 1
              start_date: '2024-01-01'
              end_date: '2024-12-31'

Any missing day in the reporting period surfaces as a test failure, protecting the integrity of cumulative sales metrics.