Can DBT tests compare table structure with a dictionary table?

Archive
1

Hello,

Thanks in advance for any assistance.

Background:
DBT is operating against a Snowflake environment that was a forklift from SQL Server. We have imported an enhanced version of the SQL Server data dictionary into Snowflake. Let’s say there are 300 tables brought over from SQL Server.

The requirement is to verify that the column names in each of the 300 snowflake tables match the column names in SQL Server. Somehow we would have to compare the physical tables in snowflake with the dictionary description from the imported SQL Server data dictionary table.

Summarizing, we have the physical tables in Snowflake and only a dictionary table from SQL Server imported to Snowflake with which to compare column names and we would like to use DBT to perform these tests.

Question is, are there any good ways of doing that in DBT using say, macros or dbt tests that iterate over the columns in snowflake and fail a test if they don’t match the column names from the imported SQL Server data dictionary?

Thanks for considering!

2

Hi there!

You can write arbitrary dbt tests using your own SQL queries. You can write a “singular” test that manually queries snowflake’s INFORMATION_SCHEMA, or you could probably write a “generic” test for each model that uses something like dbt-util’s get columns in relation macro.

More on tests here: Tests | dbt Docs (getdbt.com)

There is also a guide for writing your own custom generic tests: Writing custom generic tests | dbt Docs (getdbt.com)

3

Thanks Ted. Will dig into that idea. Appreciate it!