How it Works

The sequential_values test from dbt-utils™ validates that a column contains a complete, unbroken sequence of values with no gaps. This is useful for invoice numbers, ticket IDs, or any series where gaps would indicate missing records.

The interval argument (default 1) specifies the expected step size between consecutive values, and datepart can be used for date sequences.

Steps and Conditions

1. Column Selection: Identify the sequential column.

2. Set Interval: Define the step size with interval (default: 1).

3. Optional Date Part: For date sequences, specify datepart.

4. Execution: Consecutive values are compared to check for gaps.

5. Outcome: Pass if no gaps exist; fail if any are found.

Example Usage: Invoice Numbering

A financial system assigns sequential invoice numbers. Any gap in the sequence could indicate a deleted or missing invoice, which requires audit investigation.

models:
  - name: invoices
    columns:
      - name: invoice_number
        tests:
          - dbt_utils.sequential_values:
              interval: 1

models:
  - name: invoices
    columns:
      - name: invoice_number
        tests:
          - dbt_utils.sequential_values:
              interval: 1

models:
  - name: invoices
    columns:
      - name: invoice_number
        tests:
          - dbt_utils.sequential_values:
              interval: 1

A gap in invoice numbers triggers the test, prompting the finance team to investigate potential data loss or unauthorized deletions.