Explain Snowflake's unique three-layer architecture (storage, compute, cloud services) and the core responsibilities of each layer.
Junior
What is Snowflake, and what kind of database is it (e.g., PaaS or SaaS)?
Junior
How important is the storage layer to Snowflake, and what is its use?
Junior
What are the essential features of Snowflake that differentiate it from traditional data warehouses?
Junior
How does the separation of storage and compute in Snowflake differ from traditional data warehouse architectures like shared-nothing and shared-disk, and what are the benefits?
Mid
Can Snowflake be called a data lake, and how does it support data lake functionalities?
Mid
How does Snowflake's object hierarchy affect architectural decisions?
Mid
What are the different Snowflake editions (Standard, Enterprise, Business Critical, VPS), and which key features are gated behind each?
Mid
Explain when you'd reach for Trino instead of Snowflake. Walk me through one concrete workload where Trino wins and one where Snowflake wins, and explain the architectural reason — not just the price tag.
Senior
When would you design a multi-account Snowflake strategy versus a single-account strategy, and what are the trade-offs?
Senior
Discuss Snowflake's architecture in terms of scalability and multi-cloud deployment.
Senior
Virtual Warehouses & Compute
What is a virtual warehouse in Snowflake, and how does it provide compute resources for queries?
Junior
How do the auto-suspend and auto-resume features work for virtual warehouses, and how do they help optimize compute costs?
Junior
Define the different states of the Snowflake Virtual Warehouse.
Junior
How does Snowflake bill for virtual warehouse usage in terms of per-second billing and the 60-second minimum charge?
Junior
Explain the difference between scaling up (changing warehouse size) and scaling out (multi-cluster warehouses) in Snowflake, and when you would choose each approach.
Mid
Describe the different scaling policies (Standard vs. Economy) for multi-cluster warehouses and their impact on concurrency and cost.
Mid
How does query queuing indicate an undersized virtual warehouse, and what actions can be taken?
Mid
How would you isolate different workloads such as ETL vs. BI using virtual warehouses to prevent resource contention?
Mid
How do warehouse sizing and auto-suspend impact cost and performance in Snowflake?
Mid
Explain multi-cluster virtual warehouses and their benefits.
Mid
What is a virtual warehouse in Snowflake, and how would you size one for different workloads?
Mid
Explain how auto-scaling of virtual warehouses contributes to cost management and performance in Snowflake.
Mid
What are the difference modes for multi-cluster warehouses (maximized vs. auto-scale), and how do they affect concurrency handling?
Mid
Micro Partitions & Storage
What is a micro-partition in Snowflake, and what kind of metadata is stored with each micro-partition?
Junior
Explain the benefits of Snowflake's columnar storage format within micro-partitions.
Junior
Does Snowflake use indexes, and if not, why?
Junior
What are micro-partitions in Snowflake, and how do they contribute to query performance?
Mid
How does Snowflake leverage micro-partition metadata for query pruning, and why does Snowflake not use user-defined indexes?
Mid
Where is metadata stored in Snowflake, and how is it managed by the cloud services layer?
Mid
How does Snowflake handle data distribution and partitioning within its architecture?
Mid
Given the immutability of micro-partitions, how do DML operations like UPDATE and DELETE affect data storage in Snowflake?
Senior
Time Travel Cloning & Recovery
Explain Snowflake's Time Travel feature, including its configurable retention period and common use cases for data recovery or historical analysis.
Junior
What are the primary use cases for zero-copy cloning, especially for creating development, testing, or analytical environments from production data?
Junior
What is the UNDROP command in Snowflake and how does it rely on Time Travel?
Junior
What is the difference between Time Travel and Fail-safe in Snowflake?
Mid
How do Time Travel and zero-copy cloning work together in Snowflake, and can you provide a real-world scenario where you would use both?
Mid
How does Time Travel impact storage costs in Snowflake?
Mid
Explain how zero-copy cloning works in Snowflake at a metadata level, why it is near-instant and initially free, and when storage costs begin to accrue.
Senior
Tables Views & Data Types
Differentiate between permanent, transient, and temporary tables in Snowflake, focusing on their implications for Time Travel, Fail-safe, and storage costs.
Mid
Explain the concept of Materialized Views in Snowflake, their benefits for accelerating complex queries, and any associated restrictions or maintenance considerations.
Mid
Explain how external tables work in Snowflake and their use cases for querying data directly from external cloud storage without loading it.
Mid
How does Snowflake handle semi-structured data (JSON, XML, Avro) using VARIANT, OBJECT, and ARRAY data types, and how does this approach differ from traditional relational databases?
Mid
What are Dynamic Tables in Snowflake, and how do they enable declarative, automated data pipelines?
Mid
What are Dynamic Tables in Snowflake, and how do they compare with Streams and Tasks?
Mid
How would you work with semi-structured data in Snowflake, including the significance of the FLATTEN function?
Mid
What is the difference between a standard view, a secure view, and a materialized view in Snowflake?
Mid
How do you architect a Data Lakehouse on Snowflake using Apache Iceberg?
Senior
What are Iceberg tables in Snowflake and how do they enable open-table-format interoperability?
Senior
Data Loading & Ingestion
What is a Snowflake Stage, and how is it used for data loading?
Junior
What is the purpose of the COPY INTO command in Snowflake?
Junior
What is Snowpipe, how does it enable continuous, near-real-time data ingestion, and what are its advantages over traditional batch loading methods?
Mid
What are Snowflake's recommendations for file sizing when loading data, and why is this important for performance and cost?
Mid
Describe the different types of stages in Snowflake (internal user, table, named; external) and how storage integrations are used to securely access external cloud storage.
Mid
Explain the COPY INTO command for bulk data loading in Snowflake, including its idempotency and how it handles file processing metadata.
Mid
Explain the difference between COPY INTO and Snowpipe.
Mid
How are Snowflake's serverless features (like Snowpipe, automatic clustering, and search optimization) billed differently from virtual warehouse compute?
Mid
What is Snowpipe Streaming, and how does it differ from regular Snowpipe in terms of data ingestion granularity and latency?
Senior
Clustering & Caching
How does clustering work in Snowflake, and when would you use clustering keys?
Mid
Describe the three types of caches in Snowflake (result cache, local disk/warehouse cache, and metadata cache) and how each contributes to query performance and cost optimization.
Mid
Explain Snowflake's result caching mechanism and how it affects query behavior.
Mid
Explain the concept of automatic clustering in Snowflake, how it works, and its cost implications.
Mid
What conditions would invalidate the result cache in Snowflake, preventing a query from being served instantly?
Mid
What is the Search Optimization Service in Snowflake and when would you use it?
Senior
How does Snowflake's caching interact with dbt incremental models?
Senior
What are clustering depth and overlap as clustering health metrics, and how do you use SYSTEM$CLUSTERING_INFORMATION to evaluate them?
Senior
When is defining a clustering key the wrong choice in Snowflake, and what are the downsides?
Senior
Cost Management & Billing
Explain Snowflake's credit-based cost model, detailing the three primary cost components: compute, storage, and cloud services.
Junior
How do Resource Monitors help manage and control Snowflake spend, and what actions can they trigger when thresholds are met?
Mid
What does the cloud services layer handle in Snowflake, and how is it billed given the 10% free daily allowance?
Mid
What are the practical levers you would use to reduce or optimize a Snowflake bill?
Senior
Security & Access Control
How do roles and privileges work in Snowflake's Role-Based Access Control (RBAC) model?
Junior
What are masking policies and row access policies in Snowflake, and how do they help handle sensitive data?
Mid
What are the system-defined roles in Snowflake (ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, PUBLIC), and why is it recommended not to run day-to-day work as ACCOUNTADMIN?
Mid
What authentication mechanisms does Snowflake support, such as key-pair authentication, SSO, and MFA, and what are network policies?
Mid
How does role hierarchy and privilege inheritance work in Snowflake's RBAC, and what are future grants and object ownership?
Senior
How does Snowflake handle encryption at rest and in transit, and what is Tri-Secret Secure?
Senior
What is object tagging in Snowflake, and how do tag-based masking policies work?
Senior
Streams Tasks & Pipelines
How do Streams and Tasks support incremental data pipelines and Change Data Capture (CDC) in Snowflake?
Mid
Explain how Streams in Snowflake track changes (INSERTs, UPDATEs, DELETEs) to source tables, including different stream types (standard, append-only) and the consumption contract.
Mid
What are Tasks in Snowflake, how can you orchestrate sequences of tasks (task trees/DAGs), and what is the difference between serverless and warehouse-backed tasks?
Mid
What is the difference between a Stream and a Task in Snowflake?
Mid
When would you choose Streams + Tasks versus Dynamic Tables for a CDC pipeline?
Senior
Query Performance & Monitoring
What is the difference in purpose, latency, and data retention between ACCOUNT_USAGE and INFORMATION_SCHEMA for monitoring and auditing in Snowflake?
Mid
What does spilling to local and remote storage indicate about a virtual warehouse, and how would you resolve it?
Mid
How would you approach troubleshooting and optimizing a slow-running query in Snowflake, and what specific tools and Snowflake-specific concepts would you investigate?
Senior
Explain how to interpret the Query Profile in Snowflake to identify performance bottlenecks such as spilling to local/remote storage, exploding joins, or full-table scans.
Senior
How would you approach tuning joins in Snowflake?
Senior
Data Sharing & Business Continuity
What is the Snowflake Marketplace and how does it relate to secure data sharing?
Junior
How does secure data sharing work in Snowflake, and what are its main advantages?
Mid
What are reader accounts in Snowflake, and how do they enable data sharing with consumers who don't have their own Snowflake account?
Mid
How does Snowflake support business continuity through database replication and failover across regions or clouds?
Senior
Why does sharing data across different regions or cloud providers in Snowflake require replication?
Senior
Programmability & Advanced Features
When would you use stored procedures, UDFs, or external functions in Snowflake?
Mid
What is Snowpark, and where does the actual computation execute when you use its DataFrame API?
Mid
What is Snowflake Cortex, and how does it fit as a native AI capability on the platform?
Mid
How does Snowflake handle multi-statement transactions and ACID guarantees?
Mid
What isolation level does Snowflake provide, and how does the immutable micro-partition design allow readers to never block writers?
Senior
Top 98 Snowflake Data Warehouse Interview Questions And Answers 2025 | TechPrep