testingtimestampdbt

How to do dbt_tests on timestamp data type?


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.


Solution

  • 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'"