SnowPro Advanced Data Engineer: Data Modeling and Schema Design

What candidates must know

Design decisions hinge on Snowflake's storage model: tables are stored in immutable, columnar micro-partitions whose natural clustering follows ingestion order and drives pruning. Understand when a clustering key is worth it (large tables with selective, repeated filters) versus harmful (small tables, high-cardinality keys with heavy DML). Know the type system: NUMBER(p,s), FLOAT, VARCHAR, BINARY, semi-structured VARIANT/OBJECT/ARRAY, and the three timestamp flavors where TIMESTAMP_NTZ stores wall-clock time and TIMESTAMP_LTZ renders in each session's timezone. Distinguish permanent, transient, temporary and hybrid tables, Time Travel retention, and zero-copy cloning for dev/test environments.

Traps

• NOT NULL is enforced; PRIMARY KEY, UNIQUE and FOREIGN KEY are declarative only and are not enforced, so duplicates are still possible.
• A clustering key does not sort or deduplicate data; it only co-locates similar values to improve pruning.
• Transient tables skip Fail-safe, so they are cheaper but unsuitable for critical data.
• VARIANT holding a JSON null is not the same as SQL NULL; use IS_NULL_VALUE versus IS NULL.
• Very wide VARIANT columns discourage column pruning and inflate scan size compared with flattened, typed columns.

Rule of thumb

Model for the query, not for the ER diagram: denormalize into star schemas when analysts filter and aggregate the same dimensions repeatedly, and only cluster on columns used in highly selective predicates.

Worked example

A 5 TB event table is always filtered by event_date and tenant_id. Create the table clustered by (event_date, tenant_id) - date first because it is the most common range filter - then verify improvement in the query profile by comparing partitions scanned against total partitions.