Primary and foreign key data type do not match

[Dikootje]

When adding constraints to our project we've discovered some datatype inconsistencies within our models.

For example in a fact table we specify the customer_key as varchar and in the dimension as varchar(50). By default Snowflake will create the fact table with varchar(16777216) which obviously is not the same as varchar(50).

Upon running the dbt build every test passes. But in the post hook when actually creating the constraints Snowflake will return the database error that keys are not matching.

When running with multiple threads its very hard to figure out which constraint has failed to build.

There's a couple of solutions to help finding the cause of the error:

1. When the database error occurs also log which constraint is causing it
2. When a foreign key is specified not only test the existence of the key, but also check the data type and fail / warn if there's a mismatch.

[sfc-gh-dflippo]

I usually try to avoid errors and report the condition to users so I like option 2. I will implement the check and let you know when it is available.