How to validate date format "2022-09-06 06:00:00.000000" on DBT

Question

Hello everyone could anyone help me how to validate this date "2022-09-06 06:00:00.000000" format on DBT?

DBT Command:

  - name: candle_start_time
    tests:
      - dbt_expectations.expect_column_values_to_match_regex:
          regex: ^\\d{4}-\\d{2}-\\d{2}\s\\d{2}:\\d{2}:\\d{2}\\.\\d{6}$

Answer 1

I always try to avoid regex, call me regexphobic. Also there's native timestamp formatting that can make this way more intuitive IMHO.

I'd go with the following, instead:

- name: model_name
  tests:
    - dbt_utils.expression_is_true:
        name: candles_start_time_format
        expression: "str_to_date(candle_start_time, '%Y-%m-%d %H:%i:%s.%f') is null"

This would return a row for every row where the format is not the one that you specified, hence failing.