Top 50 Snowflake Interview Questions 2026

Q1: What is Snowflake?

Answer: 

Snowflake is a cloud-based data warehousing platform built for the cloud. It offers a fully managed service with a unique multi-cluster shared data architecture that separates storage, compute, and cloud services layers, enabling near-unlimited scalability, concurrent workloads, and zero maintenance.

Q2: What are the three key layers of Snowflake’s architecture?

Answer: 

1. Storage Layer: Centralized, columnar-compressed data stored in cloud object storage (S3, Azure Blob, GCS).

2. Compute Layer (Virtual Warehouses): Independent MPP clusters that process queries without sharing compute resources.

3. Cloud Services Layer: Manages infrastructure, metadata, authentication, query parsing, and optimization.

Q3: What is a Virtual Warehouse in Snowflake?

Answer: 

A Virtual Warehouse is a cluster of compute resources (CPUs, memory, SSD) used to execute SQL queries, DML operations, and data loading. They can be started, stopped, resized, and scaled independently without affecting stored data.

Q4: What are the different sizes of Virtual Warehouses?

Answer: 

XS (Extra-Small), S (Small), M (Medium), L (Large), XL, 2XL, 3XL, 4XL, 5XL, 6XL. Each size doubles the compute power of the previous, with XS being the smallest and 6XL the largest.

Q5: What is the difference between Snowflake’s storage and compute layers?

Answer: 

Storage and compute are fully decoupled. Storage holds compressed columnar data in cloud object storage, billed per TB/month. Compute (Virtual Warehouses) executes queries, billed per second of usage. Multiple compute clusters can access the same storage simultaneously without contention.

Q6: What cloud platforms does Snowflake support?

Answer: 

Snowflake is available on AWS (Amazon Web Services), Microsoft Azure, and Google Cloud Platform (GCP). Users can choose their preferred cloud and region during account setup.

Q7: What is Snowflake’s multi-cluster architecture?

Answer: 

Multi-cluster Warehouses allow Snowflake to automatically spin up additional compute clusters when query concurrency is high. This handles peak demand without manual intervention, ensuring consistent performance for all users.

Q8: How does Snowflake handle concurrency?

Answer: 

Snowflake handles concurrency by allowing multiple Virtual Warehouses to run simultaneously against the same data without conflicts. Multi-cluster warehouses auto-scale horizontally, and the Cloud Services layer manages query routing and resource allocation efficiently.

Q9: What is Snowflake’s Time Travel feature?

Answer: 

Time Travel allows users to access historical data at any point within a defined retention period (up to 90 days on Enterprise edition). It supports querying past states of data using AT/BEFORE clauses and enables undoing accidental DML changes.

Q10: What is Fail-Safe in Snowflake?

Answer: 

Fail-Safe is a 7-day recovery period after Time Travel expires, available for Permanent tables. During this period, Snowflake can recover data only via internal support, not by the user directly. It protects against catastrophic data loss.

Q1: What data types does Snowflake support?

Answer: 

Snowflake supports: Numeric (NUMBER, INT, FLOAT, DECIMAL), String (VARCHAR, CHAR, TEXT), Date/Time (DATE, TIME, TIMESTAMP), Semi-structured (VARIANT, OBJECT, ARRAY), Binary (BINARY, VARBINARY), and Boolean.

Q2: What is a VARIANT data type in Snowflake?

Answer: 

VARIANT is a universal semi-structured data type that can store JSON, Avro, ORC, Parquet, and XML data. It supports up to 16MB per value and allows flexible schema for hierarchical or dynamic data.

Q3: What are the different table types in Snowflake?

Answer: 

1. Permanent Tables: Persist indefinitely with full Time Travel and Fail-Safe.

2. Temporary Tables: Exist only for session duration, no Fail-Safe.

3. Transient Tables: Persist beyond sessions but with limited Time Travel (1 day max) and no Fail-Safe.

4. External Tables: Reference data in external storage (S3/Azure/GCS).

Q4: What is a Snowflake Stage?

Answer: 

A Stage is a storage location (internal or external) used to load/unload data. Internal Stages are managed by Snowflake (User, Table, Named). External Stages point to cloud storage like S3, Azure Blob, or GCS using credentials.

Q5: What is a Snowflake Pipe?

Answer: 

Snowpipe is a continuous data ingestion service that automatically loads files into Snowflake tables as soon as they arrive in a Stage. It uses event notifications (SQS, Azure Event Grid) for near-real-time loading.

Q6: What are Streams in Snowflake?

Answer: 

Streams capture CDC (Change Data Capture) — they record INSERT, UPDATE, and DELETE changes made to a table since the stream was last consumed. Used for ELT pipelines, they contain metadata columns like METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID.

Q7: What are Tasks in Snowflake?

Answer: 

Tasks are Snowflake objects that execute a SQL statement or Stored Procedure on a schedule (using CRON syntax) or triggered by a root task. They can form a DAG (Directed Acyclic Graph) of dependent tasks for complex workflows.

Q8: What is a Materialized View in Snowflake?

Answer: 

A Materialized View is a pre-computed result set stored as a table-like object. Snowflake automatically refreshes it when the base table changes. It improves performance for expensive, repeated queries but has limitations on certain SQL constructs.

Q9: What are Dynamic Tables in Snowflake?

Answer: 

Dynamic Tables (introduced as GA in 2023) are declarative, query-defined tables that Snowflake automatically refreshes based on a target lag (freshness threshold). They simplify data pipeline management by replacing complex Streams + Tasks setups.

Q10: What is a Sequence in Snowflake?

Answer: 

A Sequence is a schema-level object that generates unique integer values (not necessarily contiguous) for use as surrogate keys or unique identifiers. Created with CREATE SEQUENCE, consumed via seq_name.NEXTVAL.

Q1: How does Snowflake handle query optimization?

Answer: 

Snowflake uses an automatic query optimizer in the Cloud Services layer. It handles join reordering, partition pruning, predicate pushdown, projection pruning, and statistics-based planning — all without manual indexing or hints required.

Q2: What is Micro-partitioning in Snowflake?

Answer: 

Micro-partitioning is Snowflake’s automatic storage mechanism that divides table data into contiguous, compressed units of 50–500MB (uncompressed). Each micro-partition stores columnar data with metadata about value ranges, enabling efficient partition pruning during queries.

Q3: What is Clustering in Snowflake?

Answer: 

Clustering defines the sort order of data within micro-partitions. Automatic Clustering (cluster keys) keeps data co-located, improving pruning for range and equality filters. It’s most beneficial for large tables with common filter predicates.

Q4: What is the Result Cache in Snowflake?

Answer: 

The Result Cache stores query results for 24 hours. If an identical query is re-submitted and the underlying data hasn’t changed, Snowflake returns results instantly without consuming compute credits. Shared globally across all users.

Q5: What is the Metadata Cache in Snowflake?

Answer: 

Snowflake caches table metadata (row counts, min/max values, NULL counts per micro-partition) in the Cloud Services layer. Simple COUNT(*) and min/max queries can be answered from metadata without spinning up a Virtual Warehouse.

Q6: What is the Local Disk Cache (Data Cache)?

Answer: 

When a Virtual Warehouse processes data, it caches micro-partition data on local SSD storage. Subsequent queries on the same warehouse that touch the same data benefit from cache hits, reducing remote storage I/O.

Q7: Explain the use of FLATTEN function in Snowflake.

Answer: 

FLATTEN is a table function that explodes semi-structured data (arrays or objects) into relational rows. Used with LATERAL JOIN to unnest nested arrays within VARIANT columns. Example: SELECT f.value FROM my_table, LATERAL FLATTEN(INPUT => json_col:items) f;

Q8: What is the QUALIFY clause in Snowflake?

Answer: 

QUALIFY filters the results of window functions — similar to HAVING for aggregates. It eliminates the need for a subquery or CTE to filter window function results. Example: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp QUALIFY rn = 1;

Q9: How do you copy data into Snowflake from an S3 Stage?

Answer: 

COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = ‘CSV’ FIELD_DELIMITER = ‘,’ SKIP_HEADER = 1) ON_ERROR = ‘CONTINUE’; This command loads all files from the stage. Use PATTERN to filter specific files.

Q10: What are Window Functions? Give examples.

Answer: 

Window functions perform calculations across a set of rows related to the current row without collapsing them. Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(). Used with OVER(PARTITION BY … ORDER BY …) clause.

Q1: What methods are available for loading data into Snowflake?

Answer: 

1. COPY INTO: Bulk load from Stages.

2. Snowpipe: Continuous/auto-ingest.

3. PUT + COPY: Upload local files to internal stage then load.

4. Connectors: Kafka Connector, Spark Connector.

5. Partner tools: dbt, Fivetran, Matillion, Talend.

6. Snowflake REST API.

7. Web UI (Snowsight) for small files.

Q2: What file formats does Snowflake support for data loading?

Answer: 

CSV, TSV, JSON, Avro, ORC, Parquet, and XML. Each format has a corresponding FILE_FORMAT specification with format-specific options (e.g., STRIP_OUTER_ARRAY for JSON, SNAPPY_COMPRESSION for Parquet).

Q3: What is a File Format Object in Snowflake?

Answer: 

A named, reusable schema-level object that defines how to interpret staged data files (delimiter, compression, encoding, NULL handling, etc.). Created with CREATE FILE FORMAT and referenced in COPY INTO commands for consistency across loads.

Q4: What is the difference between Snowpipe and COPY INTO?

Answer: 

COPY INTO is a manual/scheduled bulk load — you trigger it explicitly. Snowpipe is event-driven and near-real-time, automatically loading files as they land in the stage. Snowpipe uses a serverless compute model billed per credit consumed per file.

Q5: How does Snowflake handle schema evolution during data loading?

Answer: 

Snowflake supports MATCH_BY_COLUMN_NAME in COPY INTO to map CSV/Parquet columns by name rather than position, tolerating column reordering. For semi-structured data, VARIANT accepts any schema. EVOLVE SCHEMA option (in preview) auto-adds new columns.

Q6: What is the Kafka Connector for Snowflake?

Answer: 

The Snowflake Kafka Connector is a Kafka Connect plugin that consumes messages from Kafka topics and loads them into Snowflake tables via Snowpipe. It supports AVRO, JSON, and protobuf formats and handles schema evolution via Schema Registry.

Q7: How does the PUT command work in Snowflake?

Answer: 

PUT uploads a local file to a Snowflake internal stage. Syntax: PUT file://path/to/file.csv @~; — it automatically compresses the file and uploads it. Subsequently, COPY INTO loads it from the stage into the table.

Q8: What is an External Table in Snowflake?

Answer: 

External Tables let you query data stored in external cloud storage (S3, ADLS, GCS) without loading it into Snowflake. Data stays in place; Snowflake reads it on demand. Performance is lower than native tables but useful for data lake architectures.

Q9: What is Iceberg Table support in Snowflake?

Answer: 

Snowflake supports Apache Iceberg tables, allowing you to manage Iceberg table metadata natively while data resides in external storage. This enables open-format data lake tables with Snowflake’s query engine, supporting ACID transactions and schema evolution.

Q10: How do you unload data from Snowflake to S3?

Answer: 

COPY INTO @my_s3_stage FROM my_table FILE_FORMAT = (TYPE = ‘PARQUET’) OVERWRITE = TRUE; — This exports data to the specified external stage. You can add HEADER = TRUE for CSV, PARTITION BY for partitioned output, and MAX_FILE_SIZE for file splitting.

Q1: What is Snowflake’s Role-Based Access Control (RBAC)?

Answer: 

Snowflake uses RBAC to manage permissions. Roles are granted privileges on objects (databases, schemas, tables, warehouses). Users are assigned roles. Roles can be granted to other roles, forming a hierarchy. Key system-defined roles: ACCOUNTADMIN, SYSADMIN, SECURITYADMIN, USERADMIN, PUBLIC.

Q2: What is the principle of least privilege in Snowflake?

Answer: 

Users and roles should only have the minimum permissions needed for their tasks. In Snowflake, create functional roles (e.g., ANALYST_ROLE) with specific grants, avoid using ACCOUNTADMIN for daily work, and use role hierarchies to compose permissions rather than granting everything to one role.

Q3: What is Dynamic Data Masking in Snowflake?

Answer: 

Dynamic Data Masking applies column-level security policies that replace sensitive data at query time based on the user’s role. For example, a SALES_REP role might see masked SSNs while a COMPLIANCE role sees full values — without changing the underlying data.

Q4: What is Row Access Policy in Snowflake?

Answer: 

Row Access Policies restrict which rows a user can see based on their role or session context. A policy function returns TRUE/FALSE for each row, filtering the result set transparently. Used for multi-tenant data isolation without separate tables.

Q5: How does Snowflake encrypt data?

Answer: 

Snowflake encrypts all data at rest using AES-256 and in transit using TLS 1.2+. Encryption keys are managed in a hierarchy: root keys, account master keys, and file keys. Enterprise accounts support Customer-Managed Keys (Tri-Secret Secure with Snowflake + HSM).

Q6: What is Network Policy in Snowflake?

Answer: 

A Network Policy restricts Snowflake account or user access to specific IP address ranges (whitelisting/blacklisting). Created with CREATE NETWORK POLICY and applied at account or user level using ALTER ACCOUNT or ALTER USER.

Q7: What are Object Tags in Snowflake?

Answer: 

Tags are key-value metadata labels attached to Snowflake objects (tables, columns, warehouses). Used for data governance, sensitivity classification (PII, PCI, HIPAA), cost attribution, and integrating with masking policies. Supported via TAG_REFERENCES views.

Q8: What is the difference between ACCOUNTADMIN and SYSADMIN?

Answer: 

ACCOUNTADMIN is the top-level role with full control over the account, billing, users, and all objects. SYSADMIN manages databases, warehouses, and all database objects but cannot manage users/roles or view billing. Best practice: use SYSADMIN for object creation.

Q9: What is Multi-Factor Authentication (MFA) in Snowflake?

Answer: 

Snowflake supports MFA via Duo Security integration. Users enroll their mobile device and receive a push notification or TOTP code when authenticating. MFA can be enforced at the user level and is strongly recommended for privileged accounts.

Q10: What is Snowflake’s Private Link support?

Answer: 

Snowflake supports AWS PrivateLink, Azure Private Link, and Google Cloud Private Service Connect. These enable private connectivity between a VPC/VNet and Snowflake without traffic traversing the public internet, enhancing security compliance.

Q1: How do you improve Snowflake query performance?

Answer: 

Key strategies: 1) Use appropriate warehouse size for workload. 2) Leverage clustering keys on large filtered tables. 3) Use result cache by running repeated queries. 4) Avoid SELECT *; only project needed columns. 5) Use search optimization for point lookups. 6) Partition large VARIANT columns. 7) Use Materialized Views for expensive aggregations.

Q2: When should you use clustering keys?

Answer: 

Use clustering keys on large tables (multi-TB) where queries consistently filter on specific columns (e.g., date ranges, region). Avoid over-clustering small tables or columns with very high/low cardinality. Monitor DML_HISTORY and clustering depth to assess effectiveness.

Q3: What is Search Optimization in Snowflake?

Answer: 

Search Optimization (Enterprise+) builds a persistent search access path structure to improve point lookup and substring search queries on large tables. Enabled per table/column with ALTER TABLE … ADD SEARCH OPTIMIZATION. Trades storage cost for faster selective queries.

Q4: How do you monitor query performance in Snowflake?

Answer: 

Use Snowsight Query Profile to visualize query execution steps, bottlenecks, and data flow. Also use QUERY_HISTORY and QUERY_HISTORY_BY_SESSION views in ACCOUNT_USAGE schema, EXPLAIN PLAN for query plan analysis, and WAREHOUSE_METERING_HISTORY for credit usage.

Q5: What is Query Acceleration Service in Snowflake?

Answer: 

Query Acceleration Service (QAS) offloads portions of eligible queries to a serverless compute pool, reducing the impact of large scans on a shared warehouse. Enabled per warehouse with ALTER WAREHOUSE … SET ENABLE_QUERY_ACCELERATION = TRUE.

Q6: How do you handle slow queries in Snowflake?

Answer: 

Steps: 1) Check Query Profile for bottlenecks (TableScan, Join, Aggregation). 2) Look for spilling to local/remote storage — resize warehouse. 3) Check partition pruning efficiency. 4) Verify clustering. 5) Add Search Optimization for point lookups. 6) Refactor SQL to reduce data volume early.

Q7: What causes ‘spilling to disk’ in Snowflake and how to fix it?

Answer: 

Spilling occurs when a query’s intermediate data exceeds available memory, overflowing to local SSD (spill to local) or remote storage (spill to remote). Fix by upsizing the Virtual Warehouse to provide more memory per node or by optimizing query to reduce intermediate data volume.

Q8: What is the impact of warehouse auto-suspend and auto-resume?

Answer: 

Auto-suspend stops the warehouse after a period of inactivity (saving credits). Auto-resume starts it automatically on query submission. The first query after resume has a ~1-2 second cold start. Set auto-suspend to 60s for interactive workloads, longer for batch jobs.

Q9: How does Snowflake handle large JOIN operations?

Answer: 

Snowflake uses hash joins internally. To optimize: broadcast small tables, ensure join keys are selective, avoid Cartesian joins, use CTEs to pre-filter data before joins, and consider denormalization. Query Profile shows join type and data volumes per side.

Q10: What is Adaptive Scans in Snowflake?

Answer: 

Adaptive Scans allow multiple compute nodes in a warehouse cluster to share work on scanning micro-partitions, even if they were locally cached by individual nodes. This improves parallelism and avoids redundant re-reads when the warehouse has multiple workers.

Q1: What is Snowpark?

Answer: 

Snowpark is a developer framework that allows writing data transformations in Python, Java, or Scala using a DataFrame API that executes natively within Snowflake’s compute engine. It brings the code to the data, avoiding data movement, and supports UDFs, stored procedures, and ML model training.

Q2: What are Snowflake UDFs (User-Defined Functions)?

Answer: 

UDFs extend SQL with custom logic written in JavaScript, Python, Java, Scala, or SQL. Scalar UDFs return one row per input row; Table UDFs (UDTFs) return multiple rows. They run within Snowflake’s sandbox for security. Vectorized Python UDFs process batches of rows via Pandas for performance.

Q3: What are Stored Procedures in Snowflake?

Answer: 

Stored Procedures allow procedural SQL logic (loops, conditionals, exception handling) using JavaScript, Python, Java, Scala, or Snowflake Scripting (SQL). They can execute DML, DDL, and call other procedures. Unlike UDFs, they can return a single value or a table.

Q4: What is Snowflake Cortex?

Answer: 

Snowflake Cortex is a suite of AI/ML capabilities built into Snowflake, including: Cortex LLM Functions (COMPLETE, SUMMARIZE, TRANSLATE, SENTIMENT using LLMs like Mistral, Llama), Cortex ML Functions for forecasting/anomaly detection, and Cortex Analyst for natural language to SQL.

Q5: What is Data Sharing in Snowflake?

Answer: 

Secure Data Sharing allows sharing live data across Snowflake accounts without copying it. The provider grants access to specific objects; the consumer creates a database from the share and queries it in real time. No data movement, no ETL — ideal for data marketplaces.

Q6: What is the Snowflake Marketplace?

Answer: 

Snowflake Data Marketplace is a platform where data providers list datasets (weather, financial, demographic, etc.) that consumers can discover and access directly in their Snowflake account. Some datasets are free; others are paid subscriptions.

Q7: What is Snowflake’s support for Machine Learning?

Answer: 

Snowflake supports ML via: Snowpark ML (Python-based ML model training/inference in Snowflake), ML Functions (Forecasting, Anomaly Detection, Contribution Explorer), Model Registry for versioning/deploying models, and Feature Store for centralized feature management.

Q8: What are Hybrid Tables in Snowflake?

Answer: 

Hybrid Tables (in GA 2024) combine OLAP and OLTP capabilities in one table type, supporting row-level locking, unique indexes, and fast single-row lookups alongside analytical queries. They enable operational workloads within Snowflake without a separate OLTP database.

Q9: What is Snowflake Unistore?

Answer: 

Unistore is Snowflake’s workload type that unifies transactional and analytical processing using Hybrid Tables. It eliminates the need to replicate data between OLTP and OLAP systems, enabling applications that need both fast transactional operations and complex analytics.

Q10: What is Zero-Copy Cloning in Snowflake?

Answer: 

Zero-Copy Cloning creates an instant snapshot copy of a table, schema, or database without physically duplicating data. The clone shares underlying micro-partitions with the source until modified (copy-on-write). Storage cost is only incurred for changed data — ideal for dev/test environments.

Q1: How is Snowflake pricing structured?

Answer: 

Snowflake pricing has two dimensions: 1) Compute: Billed per second of Virtual Warehouse usage in Snowflake Credits (price varies by edition and cloud). 2) Storage: Billed per TB/month for compressed data stored. Serverless features (Snowpipe, Tasks, Search Optimization) have separate per-credit billing.

Q2: How do you control costs in Snowflake?

Answer: 

Cost control strategies: Set RESOURCE MONITOR on warehouses/accounts with credit quotas and alerts. Enable AUTO_SUSPEND (60 seconds for interactive). Right-size warehouses — don’t default to XL. Use Result Cache. Schedule batch workloads off-peak. Review WAREHOUSE_METERING_HISTORY regularly. Use Budgets feature (2024+).

Q3: What is a Resource Monitor in Snowflake?

Answer: 

A Resource Monitor tracks credit usage for Virtual Warehouses or the entire account. You define credit quotas and thresholds that trigger notifications or automatically suspend/disable warehouses when limits are reached. Essential for cost governance in production environments.

Q4: What is the difference between Snowflake editions?

Answer: 

Standard: Core features, 1-day Time Travel, no MFA enforcement. Enterprise: 90-day Time Travel, multi-cluster warehouses, masking policies. Business Critical: HIPAA/PCI compliance, Private Link, Tri-Secret Secure. Virtual Private Snowflake (VPS): Dedicated infrastructure, highest isolation.

Q5: How do you monitor account usage in Snowflake?

Answer: 

Use the SNOWFLAKE.ACCOUNT_USAGE schema (latency up to 45 min) for historical analysis: QUERY_HISTORY, WAREHOUSE_METERING_HISTORY, STORAGE_USAGE, LOGIN_HISTORY, ACCESS_HISTORY, PIPE_USAGE_HISTORY. For real-time: INFORMATION_SCHEMA views (no latency, 7-day retention).

Q6: What is Snowflake Replication?

Answer: 

Database Replication copies databases across Snowflake accounts in different regions or clouds for disaster recovery or global data distribution. Replication Groups can include multiple databases, shares, and tasks. Failover Groups enable business continuity with automated failover.

Q7: What is the difference between INFORMATION_SCHEMA and ACCOUNT_USAGE?

Answer: 

INFORMATION_SCHEMA: Real-time, low latency, per-database scope, 7-day retention for usage views, ANSI standard. ACCOUNT_USAGE: Account-wide scope, up to 45-minute latency, 1-year retention, richer historical data. Use INFORMATION_SCHEMA for current state, ACCOUNT_USAGE for historical analysis.

Q8: How does Snowflake handle maintenance and upgrades?

Answer: 

Snowflake is a fully managed SaaS platform — upgrades, patches, and infrastructure maintenance are handled automatically with zero downtime. There are no maintenance windows. Users transparently get new features and performance improvements without intervention.

Q9: What are Snowflake Budgets?

Answer: 

Budgets (GA 2024) allow setting spending limits on groups of Snowflake objects (warehouses, tasks, pipes) with email/webhook alerts when thresholds are reached. More granular than Resource Monitors, they support cost governance per team, project, or business unit.

Q10: What is Snowflake’s support for compliance regulations?

Answer: 

Business Critical edition supports HIPAA, PCI DSS, SOC 1/2, ISO 27001, FedRAMP (Government edition), and GDPR. Features include end-to-end encryption, Private Link, audit logging, Tri-Secret Secure (customer-managed keys), and data residency controls.

Q1: How does dbt work with Snowflake?

Answer: 

dbt (data build tool) connects to Snowflake via a profile configuration (account, user, role, warehouse, database, schema). dbt models are SQL SELECT statements that dbt materializes as tables or views in Snowflake. dbt handles DAG orchestration, testing, and documentation automatically.

Q2: What materializations does dbt support in Snowflake?

Answer: 

Table: DROP + recreate each run. View: Always up-to-date SQL definition. Incremental: Merge/INSERT only new/changed rows using unique keys. Ephemeral: CTEs, not persisted. Snowflake-specific: Dynamic Tables (via dbt-labs adapter), and custom materializations are also supported.

Q3: What is dbt’s incremental strategy for Snowflake?

Answer: 

dbt offers merge (default), append, delete+insert, and insert_overwrite strategies for incremental models on Snowflake. The merge strategy uses Snowflake’s MERGE statement to upsert rows based on unique_key. insert_overwrite replaces entire partitions, suitable for date-partitioned tables.

Q4: What is Fivetran, and how does it work with Snowflake?

Answer: 

Fivetran is a managed ELT connector platform that replicates data from 500+ sources (Salesforce, databases, APIs) into Snowflake automatically. It handles schema drift, incremental loads, and normalization. Data lands in a raw schema; dbt then transforms it downstream.

Q5: What is the Modern Data Stack?

Answer: 

The Modern Data Stack is a cloud-native data architecture typically consisting of: Source connectors (Fivetran/Airbyte) → Snowflake (warehouse) → dbt (transformation) → BI tools (Tableau, Looker, Power BI) → Reverse ETL (Census/Hightouch). All components are managed SaaS with minimal infrastructure overhead.

Q1: How would you design a real-time data pipeline using Snowflake?

Answer: 

Architecture: Source systems → Kafka → Snowflake Kafka Connector → Snowpipe → Landing table → Streams + Tasks → Transformed tables → BI layer. Use Kafka for high-throughput ingestion, Snowpipe for continuous loading, Streams to capture CDC, and Tasks to orchestrate transformation logic with near-real-time latency.

Q2: How do you handle SCD Type 2 in Snowflake?

Answer: 

SCD Type 2 in Snowflake: Use MERGE statement with a Stream on the source table. Detect changed records, INSERT a new row with the new values and CURRENT_TIMESTAMP as START_DATE, UPDATE the old row setting END_DATE and IS_CURRENT = FALSE. dbt’s snapshot feature automates this pattern.

Q3: You notice a query that ran in 10 seconds now takes 5 minutes. How do you investigate?

Answer: 

Investigation steps: 1) Check Query Profile — compare current vs. historical plan. 2) Look for data volume changes (table growth, bad filters). 3) Check if clustering is stale — run SYSTEM$CLUSTERING_INFORMATION. 4) Verify warehouse size hasn’t changed. 5) Check for lock contention (LOCK_TIMEOUT). 6) Look for spilling. 7) Review recent schema/DML changes.

Q4: How would you implement multi-tenancy in Snowflake?

Answer: 

Three approaches: 1) Separate schemas per tenant (easy isolation, shared warehouse). 2) Separate databases per tenant (stronger isolation, higher admin overhead). 3) Row-level isolation using Row Access Policies filtering by tenant_id column (single table, policy-based segregation). Choice depends on isolation requirements, tenant count, and compliance needs.

Q5: How do you optimize storage costs in Snowflake?

Answer: 

Strategies: 1) Use Transient tables for staging/temp data (no Fail-Safe cost). 2) Set Time Travel to 0 days on transient/dev tables. 3) Drop unused clones and old table versions. 4) Compress data well (Parquet/ORC for loads). 5) Archive cold data to external storage as Iceberg tables. 6) Monitor STORAGE_USAGE in ACCOUNT_USAGE regularly.

Q6: Explain a real-world use case where you used Snowflake Streams and Tasks.

Answer: 

Use case: Incremental ETL for a sales analytics pipeline. A Stream on the raw_orders table captures new/changed orders. A Task runs every 5 minutes: it reads the stream, applies business logic (currency conversion, territory mapping), and MERGES into the sales_fact table. Another child Task then refreshes the Materialized View for the dashboard.

Q7: How would you migrate data from an on-premise Oracle database to Snowflake?

Answer: 

Migration approach: 1) Extract Oracle data to CSV/Parquet using Oracle Data Pump or JDBC export. 2) Upload files to S3/Azure Blob. 3) Create external stage in Snowflake pointing to cloud storage. 4) Use COPY INTO to load into Snowflake tables. 5) Validate row counts and data quality. 6) Use dbt for transformation layer. Tools: AWS DMS, Fivetran, or Matillion for automation.

Q8: How do you implement data quality checks in Snowflake?

Answer: 

Methods: 1) dbt tests (not_null, unique, accepted_values, relationships) — automated on each dbt run. 2) Snowflake Data Metric Functions (DMFs, GA 2024) — built-in metrics like NULL_COUNT, DUPLICATE_COUNT applied as constraints. 3) Custom SQL assertions in Tasks. 4) Monte Carlo / Great Expectations for advanced observability.

Q9: What is your approach to Snowflake disaster recovery?

Answer: 

DR strategy: 1) Use Replication Groups to sync production databases to a secondary region. 2) Set up Failover Groups with a defined failover account. 3) Test failover regularly using ALTER FAILOVER GROUP … PRIMARY. 4) Combine with Time Travel + Fail-Safe for data recovery. 5) Use Business Critical edition for maximum RPO/RTO guarantees.

Q10: How would you approach Snowflake cost optimization for a large enterprise?

Answer: 

Enterprise cost optimization: 1) Audit warehouse utilization — suspend idle warehouses. 2) Set Resource Monitors with credit quotas per team/warehouse. 3) Use Budgets for departmental chargebacks. 4) Migrate ETL workloads from expensive XL warehouses to right-sized ones. 5) Implement Result Cache-friendly query patterns. 6) Use dbt incremental models to avoid full refreshes. 7) Archive cold data to Iceberg external tables.