Snowflake Table Types, Table Design, and Cloning

1.Permanent, transient, and temporary tables

When you run CREATE TABLE without a type keyword, you get a permanent table. Snowflake has two other types for data that doesn't need to be kept for long: temporary and transient. The difference between them is how long the data lives, who can see it, and how much recovery protection it gets.

A temporary table belongs to the session that created it. It lasts for the rest of that session, and other users and sessions can't see it. When the session ends, the data is purged and cannot be recovered, by you or by Snowflake. Inside a Snowflake Scripting stored procedure you can also create a procedure-scoped temporary table, which lasts for only one execution of the procedure. You don't need the CREATE TABLE privilege on the schema to create a temporary table.

Temporary tables are not free. While a temporary table exists, its data counts toward your storage bill. For that reason Snowflake recommends dropping large temporary tables explicitly, especially in sessions that stay open for more than 24 hours. Creating large numbers of temporary tables can also slow down queries against the Information Schema COLUMNS and TABLES views.

This shadowing causes real damage. Once a temporary table hides a permanent one, every query and DDL statement on that name in your session hits only the temporary table. Be careful with DROP followed by a Time Travel restore, and with CREATE OR REPLACE, because either one can act on a different table than you meant.

A transient table persists until someone drops it, and any user with the right privileges can see it. The one thing it lacks is a Fail-safe period, so it also has no Fail-safe storage costs. If you create a schema as transient, every table in it is transient. If you create a database as transient, every schema in it is transient. Two more rules: hybrid tables can't be temporary or transient, and neither temporary nor transient tables can be converted to another type after creation.

CREATE  ?  TABLE mytranstable (id NUMBER, creation_date DATE);

Sources1

2.Table design: data types, constraints, and clustering keys

Choosing a table type is the first design decision. The next ones are about the columns. Store dates and timestamps in DATE or TIMESTAMP columns, not VARCHAR. Snowflake stores the native types more efficiently, so queries on them run faster. Pick the type whose granularity matches what you need to record.

Constraints are the most commonly misunderstood part of the design. On standard tables, Snowflake enforces only NOT NULL and CHECK. Primary key, foreign key, and unique constraints are recorded as information but not enforced. Hybrid tables are the exception: their constraints are enforced. Declaring keys is still worthwhile. They show your team how the tables relate, many BI tools import them to build correct joins automatically, and some tools use them to rewrite queries, for example through join elimination.

CREATE OR REPLACE TABLE salespeople (
  sp_id INT NOT NULL UNIQUE,
  name VARCHAR DEFAULT NULL,
  region VARCHAR,
  constraint pk_sp_id PRIMARY KEY (sp_id)
);
CREATE OR REPLACE TABLE salesorders (
  order_id INT NOT NULL UNIQUE,
  quantity INT DEFAULT NULL,
  description VARCHAR,
  sp_id INT NOT NULL UNIQUE,
  constraint pk_order_id PRIMARY KEY (order_id),
  constraint fk_sp_id FOREIGN KEY (sp_id) REFERENCES salespeople(sp_id)
);

To check which constraints a table has, run GET_DDL('TABLE', ...). It returns a DDL statement that would recreate the table, constraints included. To list constraints for a whole schema or database, query the Information Schema TABLE_CONSTRAINTS view.

Most tables don't need a clustering key. Snowflake's micro-partitioning and optimization engine handle most cases, and clustering a small table rarely helps much. For a large table, consider a clustering key in two situations. The first is when the order the data is loaded in doesn't match the column it is usually queried by, for example data loaded by date but filtered by ID. The second is when Query Profile shows that typical filtered queries spend a large share of their time scanning. Reclustering has a cost: it rewrites data, costs compute in proportion to the amount reordered, and keeps the previous ordering for 7 days for Fail-safe.

Sources2

3.When to clone a database, schema, or table

CREATE ... CLONE is mainly used to make zero-copy clones of databases, schemas, and tables. It can also clone other objects, such as external stages, file formats, sequences, and database roles. A clone starts out sharing the storage of the original, so it is created almost instantly and costs nothing extra until either copy changes. That makes clones a quick way to take a backup or to give a team a full copy of production data for development or testing.

For databases, schemas, and non-temporary tables, you can add an AT | BEFORE clause to clone the object as it was at an earlier point in Time Travel. That's useful when you need a copy from just before a bad load or deployment. When you clone a database or schema this way, IGNORE TABLES WITH INSUFFICIENT DATA RETENTION skips any table whose data has already been purged from Time Travel. Transient tables with a one-day retention period are a typical case.

CREATE [ OR REPLACE ] { DATABASE | SCHEMA } [ IF NOT EXISTS ] <object_name>
  CLONE <source_object_name>
    [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
    [ IGNORE TABLES WITH INSUFFICIENT DATA RETENTION ]
    [ IGNORE HYBRID TABLES ]
    [ INCLUDE INTERNAL STAGES ]
  ...

After cloning, the two objects go their separate ways. A row added, deleted, or updated in the clone creates new micro-partitions that belong only to the clone. Because each clone has its own lifecycle, total storage becomes harder to calculate.

Cloning also interacts with table types. If you clone a permanent table as a transient table, the clone uses no storage at first because it shares the original's micro-partitions. Now suppose the permanent table is dropped. Normally its bytes would enter the 7-day Fail-safe period. Any micro-partitions the transient clone still shares, however, enter Fail-safe only when the clone is dropped too.

Sources341

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A PRIMARY KEY or UNIQUE constraint on a standard Snowflake table stops duplicate rows from being inserted.Why is that wrong?

   On standard tables, only NOT NULL and CHECK are enforced. Key constraints are informational, and only hybrid tables enforce them.

   Covered in Table design: data types, constraints, and clustering keys

2. 2.You can start with a transient table and ALTER it to permanent once the data becomes important.Why is that wrong?

   A table's type is fixed when it is created. To get Fail-safe protection you must create a new permanent table and copy the data into it.

   Covered in Permanent, transient, and temporary tables