I'm using DBT to transform data from source table whith all STRING fields to target table with TYPED fields (eg: DATE, INT, ...)

I would like to ensure (using dbt test command) that datatype conversion is possible before to launch the dbt run command. For instance on expected DATE fields (in STRING in my source table), an assert must be run on the whole column values to pass the test.

On dbt-expectations package, there are some useful tests like "expect_column_values_to_be_of_type", but this test checks the column datatype (in table's structure) instead of checking if all column values match a specific datatype.

Do you have any idea to avoid writing a custom test and use a native or packaged one?

EDIT: candidate should be "expect_column_values_to_match_regex", but perhaps a better one...

Thank you very much for help :)

CodePudding user response：

In the dbt package - dbt-expectations, I think that expect_column_values_to_match_regex and 'expect_column_values_to_not_match_regex` are the best fit to your requirement.

Actually you can use dbt-utils.expression_is_true to write your own SQL. Also, you've asked about native, that's the way doing with native SQL.