+1
Under review

Snowflake rely contraint property

roman gyger 2 weeks ago updated by hannah gryska 2 weeks ago 1

Hi,

It looks like contraints properties for Snowflake are not supported yet, which makes sense as Snowflake does not enforce them apart from the property RELY, which helps the snowflake optimizer to enahnce query performance:

----------------------------

RELY | NORELY

Specifies whether a constraint in NOVALIDATE mode is taken into account during query rewrite.

By default, this constraint property is set to NORELY.

If you have ensured that the data in the table does comply with the constraints, you can change this to RELY to indicate that the query optimizer should expect the data in the table to adhere to the constraints. Setting this can improve query performance (e.g. by eliminating unnecessary joins).

For primary and foreign key constraints, set this property on both the primary key constraint and the foreign key constraint. For example:

ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY;
ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;

https://docs.snowflake.com/en/sql-reference/constraints-properties

-------------------------------

We use this option, but doesn't seems to be supported within SqlDBM yet. Is there a way to have it generated or does it need actually added manually the the scripts? If it is not supported yet, I would be more than happy if it would be supported in the near future as it is an important Snowflake query performance feature.


Thanks for your support.

Best regards

Roman

Under review

Hi Roman, 

You are correct; we do not currently support RELY constraints. It is on our roadmap, though I do not have an answer of the timeline of that. We are working on releasing tags next for Snowflake :) 

As for a workaround, I will gather an answer from the team and get back to you once I have that. Thanks!