Top Snowflake Interview Questions cheat sheet
The most-asked Snowflake questions on architecture, warehouses, Time Travel, cloning, and performance — with expert answers.
Architecture
How does Snowflake separate storage and compute?- Storage lives in cloud object storage (S3, Azure Blob, or GCS) as immutable compressed micro-partitions; compute is stateless virtual warehouses that read that shared storage and scale independently. A third layer, cloud services, handles parsing, optimization, metadata, and transactions, and compute is billed per second with a 60-second minimum.
What is a micro-partition and why does it matter?- A micro-partition is Snowflake's internal storage unit — 50-500 MB of uncompressed data stored columnar and compressed, typically 16 MB compressed. Snowflake keeps min/max metadata per column per micro-partition, which enables partition pruning: queries skip partitions whose ranges cannot match the filter.
What are the three layers of Snowflake's architecture?- Database storage (micro-partitions in cloud object storage), query processing (virtual warehouses running MPP compute), and cloud services (authentication, metadata, query optimization, transaction management). Cloud services usage is free unless it exceeds 10% of daily compute credits.
How is Snowflake different from a traditional shared-nothing MPP warehouse like Redshift?- Snowflake uses a multi-cluster shared-data architecture: all warehouses see the same storage, so you never redistribute data to scale compute. Traditional shared-nothing systems couple storage to nodes, so resizing requires data reshuffling, and concurrent workloads contend for the same cluster.
What happens in the cloud services layer when you run a query?- The query is parsed, checked against RBAC, and optimized using micro-partition metadata; the optimizer prunes partitions and builds a plan before any warehouse touches data. Results are also cached here for 24 hours (extendable to 31 days) and reused if the query text, role, and underlying data are unchanged.
Does Snowflake use indexes?- No traditional B-tree indexes. It relies on micro-partition metadata pruning, optional clustering keys to co-locate data, and the search optimization service for point lookups. This removes index maintenance but shifts tuning toward clustering and query design.
Virtual warehouses
What is the difference between scaling up and scaling out?- Scaling up means resizing a warehouse (XS to 6XL, each size doubling compute and credits) to make individual queries faster. Scaling out means adding clusters to a multi-cluster warehouse to handle higher concurrency — it helps queuing, not single-query speed.
Explain multi-cluster warehouse modes.- Auto-scale mode adds clusters between min and max as queries queue and shrinks them when load drops; maximized mode runs all clusters at all times. Scaling policy Standard spins up eagerly to prevent queuing, while Economy waits until it estimates 6 minutes of work to justify a new cluster.
What do auto-suspend and auto-resume do, and what defaults would you set?- Auto-suspend stops a warehouse after N seconds idle so you stop paying; auto-resume restarts it on the next query. The UI default of 600 seconds is usually too generous — 60 seconds suits most BI and ELT warehouses, though very low values sacrifice warm warehouse cache.
A query is slow. Does doubling the warehouse size always halve the runtime?- No. Scaling up helps queries that spill to local or remote storage or are compute-bound and parallelizable. It does not help queries bottlenecked on a single-threaded operation, small queries dominated by compilation time, or queuing caused by concurrency.
How does warehouse caching work?- Each warehouse caches micro-partitions it reads on local SSD; repeat queries against the same data hit that cache instead of remote storage. Suspending the warehouse drops the local cache, which is the trade-off of aggressive auto-suspend.
How would you isolate workloads in Snowflake?- Give each workload its own warehouse — for example separate warehouses for ELT, BI, and data science — because warehouses do not contend with each other on compute. Storage is shared, so isolation costs nothing extra in data duplication.
Time Travel, cloning, and data protection
What is Time Travel and what are its limits?- Time Travel lets you query, clone, or undrop objects as of a past point using AT/BEFORE with timestamps, offsets, or statement IDs. Default retention is 1 day; Enterprise edition allows up to 90 days on permanent tables, while transient and temporary tables cap at 1 day.
What is Fail-safe and how does it differ from Time Travel?- Fail-safe is a non-configurable 7-day period after Time Travel expires during which only Snowflake support can recover data — you cannot query it yourself. It exists for disaster recovery, and it applies only to permanent tables, which is why transient tables are cheaper for reloadable staging data.
How does zero-copy cloning work?- CREATE TABLE ... CLONE copies only metadata pointing at the source's existing micro-partitions, so it is instant and initially free. Storage is charged only for partitions that diverge after either side is modified — copy-on-write semantics.
How do you recover an accidentally dropped table?- UNDROP TABLE restores it as long as the Time Travel retention window has not expired and the name has not been reused. If the name was reused, rename the current object first; if retention expired on a permanent table, Snowflake support may recover it from Fail-safe within 7 days.
Compare permanent, transient, and temporary tables.- Permanent tables get configurable Time Travel plus 7 days of Fail-safe; transient tables get at most 1 day of Time Travel and no Fail-safe; temporary tables live only for the session. Use transient for staging layers you can rebuild to avoid Fail-safe storage costs.
Performance and data loading
What is a clustering key and when would you add one?- A clustering key tells Snowflake to co-locate rows with similar key values in the same micro-partitions, improving pruning for selective filters on that key. Add one only on large tables (multi-TB) where queries filter on a column uncorrelated with load order — automatic clustering then costs ongoing credits to maintain.
How do you diagnose a slow query in Snowflake?- Open the Query Profile and check partition pruning (partitions scanned vs total), spilling to local or remote storage, and exploding joins where output rows vastly exceed inputs. QUERY_HISTORY and the ACCOUNT_USAGE views give the fleet-wide view for finding repeat offenders.
COPY INTO vs Snowpipe — when do you use each?- COPY INTO is a batch command you run on your own warehouse, ideal for scheduled bulk loads. Snowpipe is serverless, auto-ingesting files within about a minute of landing in a stage via event notifications, billed per-file plus compute — better for continuous small-file arrival.
Why are many small files bad for loading, and what is the recommended file size?- Each file has fixed per-file overhead in Snowpipe billing and load processing, and tiny files underutilize warehouse parallelism. Snowflake recommends compressed files of roughly 100-250 MB; aggregate small files upstream where possible.
What is the result cache and when does it not apply?- Query results are stored for 24 hours and returned without a running warehouse if the same query text runs again with an unchanged underlying dataset and compatible role context. It is bypassed when data changed, when functions like CURRENT_TIMESTAMP appear, or when the session disables it.
How does Snowflake handle semi-structured data?- The VARIANT type stores JSON, Avro, ORC, Parquet, or XML, and Snowflake automatically columnarizes repeated JSON paths into the same micro-partition metadata structure as native columns. You query it with dot and bracket notation plus FLATTEN, often getting near-native performance without a fixed schema.
Security and access control
Explain Snowflake's RBAC model.- Privileges are granted to roles, roles are granted to users or other roles, and every session acts under one primary role (plus optional secondary roles). Best practice is a hierarchy where functional roles roll up to SYSADMIN, and object ownership sits with dedicated access roles rather than individual users.
What is the difference between ACCOUNTADMIN, SYSADMIN, and SECURITYADMIN?- ACCOUNTADMIN is the top-level role for billing and account parameters and should be tightly restricted with MFA. SECURITYADMIN manages users, roles, and grants globally; SYSADMIN owns and manages databases, warehouses, and other objects. Day-to-day object creation belongs under SYSADMIN, not ACCOUNTADMIN.
How does Snowflake encrypt data?- Everything is encrypted at rest with AES-256 in a hierarchical key model (root, account, table, file keys) with automatic 30-day key rotation, and TLS protects data in transit. Tri-Secret Secure on Business Critical edition adds a customer-managed key so revoking it renders data unreadable.
What options exist for column-level security?- Dynamic data masking applies a masking policy at query time based on the executing role — for example showing full email to PII_ADMIN and a hash to everyone else. External tokenization integrates with a tokenization provider so raw values never enter Snowflake unprotected.
How would you restrict which networks can reach your account?- Network policies whitelist or block CIDR ranges at the account, user, or integration level. For stricter setups, private connectivity (AWS PrivateLink, Azure Private Link) keeps traffic off the public internet entirely — a Business Critical feature.
From DataLane — tutorials at/blog, practice SQL live in theplayground.