Design: free practice questions

Option A is correct. For maximum micro-partition pruning, the leading column of the clustering key should be the highest-cardinality column that appears most frequently in WHERE clause filters — TRANSACTION_DATE with ~1,000 distinct values fits perfectly as the sole key for the dominant query pattern. Option B is wrong: placing MERCHANT_CATEGORY (only 25 distinct values, low cardinality) first means Snowflake sorts data by that column first, resulting in poor pruning for date-range queries. Option C adds MERCHANT_CATEGORY as a secondary key, which is not harmful but adds unnecessary reclustering cost because the column has very low cardinality and queries rarely filter on it exclusively. Option D is incorrect: clustering on a high-cardinality synthetic primary key like TRANSACTION_ID creates extremely fine-grained micro-partitions with almost no rows per unique value, providing no pruning benefit for range or categorical filters and wasting significant credits.

Defining a clustering key on (TRADE_DATE, INSTRUMENT_TYPE) and enabling automatic reclustering is correct because the leading column should be the one most frequently used as the primary filter (TRADE_DATE is typically a high-cardinality date column that drives range pruning), and automatic reclustering keeps the table well-clustered as new data arrives. Option B is wrong because placing low-cardinality INSTRUMENT_TYPE first reduces pruning effectiveness for date-range queries, which dominate the workload. Option C is wrong because a materialized view cannot fully replace clustering on a 3-billion-row fact table; it adds refresh cost and only helps pre-defined aggregation patterns, not ad-hoc filters. Option D is wrong because Search Optimization Service excels at equality predicates and point lookups, not range predicates on high-cardinality columns in large fact tables — its cost would be disproportionate compared to clustering.

Snowflake materialized views have a well-documented limitation: the defining query cannot reference more than one table — joins between multiple base tables are not supported. The architect must design summaries that can be computed from a single base table, or use alternative patterns (e.g., dynamic tables or pre-aggregated staging tables) for multi-table joins. Option A is false: materialized views are first-class queryable objects; Snowflake's query optimizer can transparently rewrite queries against the base table to use the materialized view, and users can also query the materialized view directly. Option C is false: materialized views in Snowflake are refreshed automatically and incrementally by Snowflake's background service whenever the base table changes — no manual trigger is required. Option D is false: materialized views can coexist with clustering keys on the base table; in fact, clustering can improve materialized view refresh efficiency.

Correct answers (A) and (C): (A) Private Listings (or direct Data Shares) are the correct mechanism for Snowflake-to-Snowflake sharing. The consumer account mounts the share and queries live, provider-managed data with zero data movement — this is the core value proposition of Snowflake's data sharing. (C) Reader Accounts (formerly 'managed accounts') allow the data provider to create a Snowflake sub-account on behalf of a non-Snowflake consumer. The provider loads or shares data into the Reader Account, and the external customer queries it using Snowflake's interface without needing their own Snowflake account. (B) is incorrect — full nightly replication creates data copies, increases storage cost, and defeats the purpose of live sharing; it is not the recommended pattern. (D) is incorrect — external tables on a public S3 bucket expose raw files without access control, billing tracking, or governance — not appropriate for a monetization use case. (E) is incorrect — Streams + Snowpipe is an ETL/CDC pattern, not a data sharing architecture; it requires the consumer to have significant integration infrastructure and involves data movement. (F) is incorrect — Data Shares are assigned to Snowflake account identifiers, not AWS account IDs; a non-Snowflake customer has no Snowflake account to receive a share.

Transient tables eliminate both Time Travel (beyond 0 days) and Fail-safe storage costs, making them the lowest-cost option for data that is intentionally ephemeral and not worth recovering. Because multiple pipeline steps in the same session or across sessions need to read these tables, transient tables are the correct choice over temporary tables, which are session-scoped and invisible to other sessions. Option A (permanent tables with Time Travel=0) still incurs 7-day Fail-safe storage charges, making them costlier than transient tables. Option C (temporary tables) are session-scoped; if a downstream step runs in a different session or a different task, the table would not be visible, making them impractical for multi-step pipelines unless all steps share a single session. Option D (external tables) require data to live in cloud object storage, add I/O overhead, and do not eliminate compute or storage management concerns — they are designed for querying data that already resides outside Snowflake, not for intermediate pipeline staging.