I'm writing a query that consists of a column with timestamp data type. For example, event_timestamp column name contains the timestamp of the events (rows) in my output query. I want to enforce a contract on event_timestamp that it should not contain any timestamps before 2025-03-01.

Here's what I have in my example yml:

models:
  - name: my_table
    description: A test table of mine.
    config:
      contract:
        enforced: true
    columns:
      - name: event_timestamp
        data_type: timestamp
        description: timestamp of the event
        data_tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: "2025-03-01"

I cannot simply put the date there as it gets interpreted as INT. How do I implement the test on event_timestamp in this case? I want to be able to do the test on the timestamp, but using date value.

I'm writing a query that consists of a column with timestamp data type. For example, event_timestamp column name contains the timestamp of the events (rows) in my output query. I want to enforce a contract on event_timestamp that it should not contain any timestamps before 2025-03-01.

Here's what I have in my example yml:

models:
  - name: my_table
    description: A test table of mine.
    config:
      contract:
        enforced: true
    columns:
      - name: event_timestamp
        data_type: timestamp
        description: timestamp of the event
        data_tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: "2025-03-01"

I cannot simply put the date there as it gets interpreted as INT. How do I implement the test on event_timestamp in this case? I want to be able to do the test on the timestamp, but using date value.

• testing
• timestamp
• dbt

2 Answers 2

There are a few things to be corrected.

• You mentioned you are comparing a timestamp column, so instead of a date(2025-03-01) you need to pass a timestamp, otherwise since contract is enforced as true, it would throw column datatype mismatch error.
• Instead of double quotes you need to use '''<your timestamp>''' to retain the quotes around timestamp.
• Instead of data_tests, it should be just tests.

I tested in Snowflake but should be similar for other vendors as well.

Here is a sample model where account_created_at is current_timestamp

select 1 as user_id,
CURRENT_TIMESTAMP  as account_created_at

Here is the yml file for the model where max date for account_created_at is set as 1st of March which should fail.

models:
  - name: my_table1
    config:
      materialized: table
      contract:
        enforced: true
    columns:
      - name: account_created_at
        data_type: TIMESTAMP_LTZ
        tests:
          - dbt_utils.accepted_range:
              max_value: '''2025-03-01 11:52:41.081'''

So when I run dbt build , the test fails

Thank you for your comment! I got it working now. I'm using dbt with databricks, so data_tests and using a date to filter on timestamp both work fine. I can actually pass the date to the test, but I should be using expression_is_true instead of accepted_value. And with an extra single quote around the date. All good now!

- dbt_utils.expression_is_true:
                  expression: ">= '2025-03-01'"