page306
394
primary key (CLIENT_MASK_ID),
foreign key (CLIENT_ID) references TPC.TPC_OWNER.CLIENT(CLIENT_ID)
);
In the
get_ddl
returned string, you see
foreign key (CLIENT_ID) references
TPC.TPC_OWNER.CLIENT(CLIENT_ID)
, which may be programmatically extracted for use.
While not directly relevant to identifying referential integrity,
get_ddl
may also be
used to derive definitions for a wide variety of objects, in this example, for a view called
v_client_sql_statement
.
SELECT get_ddl ( 'VIEW', 'V_CLIENT_SQL_STATEMENT' );
You can find information on the
get_ddl
view at
https://docs.snowflake.com/en/
sql-reference/functions/get_ddl
.
User Defined Objects
In this section I identify some performance limitations and restrictions for several user-
defined objects including tables, views, materialized views, dynamic tables, procedures,
and functions.
Tables
I highlighted the differences between transient and permanent tables earlier in this
chapter, and as I am discussing user-defined objects here, I will repeat the advice for
completeness.
I suggest ingestion raw or staging tables should use transient tables with Time Travel
set to 0 as transient tables do not utilize Fail-Safe. You might also consider transient
tables for frequently refreshed data generated for point-in-time reporting.
Permanent tables should be reserved for persistent storage where Time Travel is
required. Note that the seven-day Fail-Safe period follows. You should optimize the
default database Time Travel setting along with each table Time Travel setting according
to tour use cases. Setting Time Travel to 90 days is often overkill, and a shorter time
period is preferable particularly where high-velocity, low-volume DML operations cause
significant micro-partition churn.
ChapTer 10
OpTimizing perfOrmanCe