page50
31
We also understand this component performs entitlement checking to ensure only
accessible objects are referenced and applies both row access policies and data masking
policies. There may be additional functionality performed, but this list provides a flavor
of known (or expected) capabilities delivered by this component.
Referential Integrity
An original design decision made by Snowflake was to allow referential integrity to
be declared but not enforced. The only constraint enforced is
NOT NULL
. You can find
further information at
https://docs.snowflake.com/en/sql-reference/constraints-
overview
.
However, the forthcoming Unistore and hybrid tables change the Snowflake
approach, at least for hybrid tables. It is not clear whether constraints previously not
enforced will become optionally enforceable in the future for standard Snowflake
tables. You can find further details on Unistore at
https://www.snowflake.com/blog/
introducing-unistore/
and
https://www.snowflake.com/en/data-cloud/workloads/
unistore/
.
Regardless of the future state of constraints, I strongly recommend declaring
constraints even if they are not enforced as their presence greatly assists data discovery
via self-service tools, aids cataloging tooling, and is generally accepted as good
practice. Some third-party tooling relies upon the presence of constraints to eliminate
nonrequired tables prior to submitting queries to Snowflake.
Where possible, declaring constraints is good practice.
I also believe the presence of unenforced constraints informs query optimizer
processing, but it is certain their presence is essential for hybrid tables, so you should
adopt best practice wherever possible. You can find further information at
https://
docs.snowflake.com/en/sql-reference/constraints
.
Logical Rewriter
After semantic analysis, the QBIR is passed to the Logical Rewriter where rules and
algorithms are applied to restate the QBIR into an optimal internal representation. It is
reasonable to assume the optimizer statistics (as listed in Chapter 1) inform the rules
Chapter 2
the Query Optimizer