Clustering keys for large tables in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use clustering keys on large tables?

Imagine you have a huge table with millions of rows. Why would adding clustering keys help when querying this table?

A Clustering keys duplicate data to make queries run faster.

B Clustering keys physically sort the data to speed up queries that filter on those keys.

C Clustering keys automatically compress the data to reduce storage size.

D Clustering keys create indexes that speed up all types of queries regardless of filters.

💡 Hint

Think about how data is stored and accessed on disk.

Clustering keys organize data physically by the key columns, so queries filtering on those keys scan less data, improving speed.

❓ Model Choice

intermediate

Choosing clustering keys for time-series data

You have a large table storing sensor readings with columns: sensor_id, reading_time, temperature. Which clustering key choice is best to speed up queries filtering by recent time ranges?

A CLUSTER BY (reading_time)

B No clustering key needed

C CLUSTER BY (temperature)

D CLUSTER BY (sensor_id)

💡 Hint

Queries filter mostly by recent timestamps.

Clustering by reading_time groups data by time, so queries filtering recent times scan less data.

❓ Hyperparameter

advanced

Impact of too many clustering keys

What is a likely effect of defining too many clustering keys on a large table?

A Increased maintenance cost and slower data loading due to complex clustering.

B Automatic removal of unused keys by Snowflake.

C Improved query speed for all queries without any downsides.

D No effect; clustering keys do not impact performance.

💡 Hint

Think about the cost of maintaining physical data order.

Too many clustering keys make the clustering process complex and costly, slowing data loading and maintenance.

❓ Metrics

advanced

Evaluating clustering effectiveness

Which metric helps you understand if your clustering keys are effective in Snowflake?

A Total storage size of the table.

B Number of rows in the table.

C Clustering depth metric showing how well data is sorted by keys.

D Number of columns in the table.

💡 Hint

Look for a metric that measures data organization quality.

Clustering depth measures how well data is physically sorted by clustering keys, indicating clustering effectiveness.

🔧 Debug

expert

Why is clustering not improving query speed?

You added clustering keys on columns user_id and event_date for a large events table. But queries filtering on event_date are still slow. What is the most likely reason?

A Snowflake does not support clustering on date columns.

B Clustering keys only speed up queries filtering on all key columns together.

C The table is too small for clustering to have any effect.

D The clustering keys are defined in the wrong order; event_date should be first.

💡 Hint

Think about how clustering keys order data physically.

Clustering keys order data by the first key, then second, so if event_date is second, filtering only by event_date may not be efficient.