Performance issues while creating constraints with v1.x.x

[erdinculuturk]

Upgrading from v0.6.3 to any version >= v1.0.0 ended up with performance issues in constraint creation.

We're on dbt Cloud (versionless).
During my tests, I'd built same sample model using same source data.
Table creation time was always same, but there was a huge difference in time required to create constraints.

In the logs below, you can see that it took 3 mins to create a not null constraint on v1.0.0, whereas it used to be 1-2 seconds on v0.6.3.

output with v1.0.0 (same with all v1 releases)

....
....
09:56:23 Installing Snowflake-Labs/dbt_constraints
09:56:23 Installed from version 1.0.0
09:56:23 Updated version available: 1.0.3
....
....
....
09:58:11 Running dbt Constraints
09:58:12 Creating not null constraint for: <colum_name> in <model_name>
10:01:18 Creating unique key: <unique_key_name>
10:01:50 Finished dbt Constraints
10:01:50 5 of 5 START hook: dbt_constraints.on-run-end.0 ................................ [RUN]
10:01:50 5 of 5 OK hook: dbt_constraints.on-run-end.0 ................................... [OK in 0.00s]
....
....

output with v0.6.3

....
....
12:00:52 Installing Snowflake-Labs/dbt_constraints
12:00:52 Installed from version 0.6.3
12:00:52 Updated version available: 1.0.3
....
....
....
12:02:17 Running dbt Constraints
12:02:18 Creating not null constraint for: <colum_name> in <model_name>
12:02:19 Creating unique key: <unique_key_name>
12:02:21 Finished dbt Constraints
12:02:21 5 of 5 START hook: dbt_constraints.on-run-end.0 ................................ [RUN]
12:02:21 5 of 5 OK hook: dbt_constraints.on-run-end.0 ................................... [OK in 0.00s]
....

[sfc-gh-dflippo]

What database are you using? Most of the code changes between those versions was focused on Snowflake. Snowflake does not enforce PK/UK/FK constraints so they are generally fast to execute. Other databases do enforce the constraints so they will take much longer to create the constraints.

Also, please test with version 1.0.4. There were several key bugs resolved in the patch releases after 1.0.0.

[erdinculuturk]

Thanks for the response.

Our database is Snowflake.
Previously I have tested all versions including v1.0.3.
Now I've tested v1.0.4 and the result didn't change for me :(

It is a small table, and unique_combination_of_columns test passes successfully in 2 sec.
However creating the constraint itself takes 2 mins, which used to be 2-3 secs with versions prior to v1.0.0.
Here is the log (not sure if it is useful or not)

15:38:26 Running dbt Constraints
15:38:50 Creating unique key: <unique_key_name> RELY
15:40:39 Finished dbt Constraints

[erikwilkins-doosan]

any update on this? we experienced the same when upgrading from 0.6.3 to 1.0.4 and as such we have not upgraded from 0.6.3.

[erikwilkins-doosan]

any update on this? would like to upgrade to the fusion compatible version but performance is still a major issue