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