page65
47
Before you do anything, you must change to
tpc_owner_role
.
USE ROLE IDENTIFIER ( $tpc_owner_role );
USE DATABASE IDENTIFIER ( $tpc_database );
USE SCHEMA IDENTIFIER ( $tpc_owner_schema );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
I (almost) always explicitly set my execution context by setting the database, schema,
warehouse, and role. I strongly recommend this approach as a best practice. I have lost
count of the number of times I ended up using the wrong role, wasting time and effort, so
I make a practice of explicitly setting up each environment at the outset.
When asked to investigate queries, i insist upon having the context along with
the query.
The current execution context can be derived by this query:
SELECT current_role(),
current_warehouse(),
current_database(),
current_schema();
As you know from Chapter 2, even though constraints are not enforced, the query
optimizer can use declared constraints.
To ensure you have equivalence between your data source and copied data, you
must check whether constraints have been declared on the source tables. You perform
this check by investigating
information_schema
for declared constraints. In this
example, you are looking for referential integrity constraints declared within any schema
in the SNOWFLAKE_SAMPLE_DATA database:
SELECT DISTINCT unique_constraint_schema
FROM snowflake_sample_data.information_schema.referential_constraints;
You should expect to see a single row TPCDS_SF100TCL indicating the chosen
schema TPCH_SF1000 does not have any constraints declared.
ChapTer 3
The Query profiler