Snowflake and Databricks Data Engineering Interview Questions (2026 Master Guide)

Q1. Explain the multi-cluster shared data architecture of Snowflake.

Foundation • 0-2 Yrs

Snowflake decouples storage and compute into 3 independent layers: 1. Database Storage Layer (compressed, columnar micro-partitions on cloud blob storage: S3/Azure Blob/GCS); 2. Compute Layer (independent Virtual Warehouses executing queries without resource contention); 3. Cloud Services Layer (central coordination: authentication, metadata, query optimization, security).

Q2. What is a Virtual Warehouse in Snowflake and how does auto-scaling work?

Foundation • 0-2 Yrs

A Virtual Warehouse is an independent cluster of compute resources (T-shirt sized: X-Small to 6X-Large). Warehouses can be resized dynamically with zero downtime. Multi-cluster warehouses auto-scale by automatically spinning up and shutting down clusters in response to concurrent query demand.

Q3. What are Micro-Partitions and Data Clustering in Snowflake?

Foundation • 0-2 Yrs

Snowflake automatically stores data in immutable, compressed columnar micro-partitions (50 to 500 MB uncompressed). Metadata (min/max values, distinct counts) for every column is recorded automatically, enabling partition pruning where queries bypass non-relevant partitions without reading them.

Q4. Explain Snowflake Time Travel and what are its retention periods.

Foundation • 0-2 Yrs

Time Travel allows querying, cloning, or restoring historical data that has been modified or deleted. Data can be accessed using AT or BEFORE timestamp/statement ID. Standard edition retains data for up to 1 day; Enterprise edition retains data for up to 90 days.

Q5. What is Snowflake Fail-Safe and how does it differ from Time Travel?

Foundation • 0-2 Yrs

Time Travel is directly accessible by users for historical querying and restoring (0-90 days). Fail-Safe is an 7-day non-configurable disaster recovery storage window immediately following Time Travel, accessible strictly by Snowflake Support to recover data lost to catastrophic operational failures.

Q6. What is Zero-Copy Cloning in Snowflake?

Foundation • 0-2 Yrs

Zero-Copy Cloning creates a new database, schema, or table without duplicating the underlying physical micro-partitions on storage. It copies only the metadata pointers, taking seconds to execute and incurring zero additional storage cost until data modifications occur in the cloned object.

Q7. What is Snowpipe and how does automated continuous data loading work?

Foundation • 0-2 Yrs

Snowpipe is a serverless continuous data ingestion tool. When files land in cloud storage (S3/Azure), storage notifications (SNS/SQS) trigger Snowpipe. Snowpipe automatically loads data into target tables in micro-batches in seconds, billing strictly for compute resources consumed.

Q8. What is the Databricks Lakehouse Architecture?

Foundation • 0-2 Yrs

A Lakehouse combines the best elements of Data Lakes (low cost, open formats, storage of structured and unstructured data) and Data Warehouses (ACID transactions, data governance, indexing, high BI performance), unified on Apache Spark and Delta Lake.

Q9. What is Delta Lake and what problems does it resolve in traditional data lakes?

Foundation • 0-2 Yrs

Delta Lake is an open-source storage layer on top of cloud object storage using Parquet files and an ACID transaction log (_delta_log). It resolves traditional data lake issues: lack of ACID transactions, corrupted writes, lack of schema enforcement, and slow metadata lookups.

Q10. Explain the Medallion Architecture (Bronze -> Silver -> Gold) in Databricks.

Foundation • 0-2 Yrs

Bronze (Raw Layer): Ingests raw data from source systems as-is with historical append. Silver (Curated/Enriched Layer): Cleansed, filtered, deduplicated, and conformed data with verified schemas. Gold (Aggregated/Business Layer): Business-ready aggregations, KPIs, and star schemas optimized for reporting and analytics.

Q11. What is Apache Spark and how does it achieve high performance over MapReduce?

Foundation • 0-2 Yrs

Apache Spark is a distributed general-purpose computing engine. It outperforms MapReduce by up to 100x because it processes data in-memory (RAM) across resilient distributed datasets rather than writing intermediate state to disk after every step, and builds lazy execution DAGs.

Q12. What is a Spark DataFrame vs RDD (Resilient Distributed Dataset)?

Foundation • 0-2 Yrs

RDD: Low-level distributed collection of JVM objects with functional transformations, lacking schema optimization. DataFrame: High-level distributed dataset organized into named columns with schema awareness, optimized by Spark's Catalyst Optimizer and Tungsten execution engine.

Q13. What is Unity Catalog in Databricks?

Foundation • 0-2 Yrs

Unity Catalog provides unified data governance across all Databricks workspaces. It enables centralized access control (GRANT/REVOKE on tables and columns), data lineage tracking, audit logging, and data discovery across multi-cloud lakehouse deployments.

Q14. What is the difference between Snowflake Internal Stages and External Stages?

Foundation • 0-2 Yrs

Internal Stages store files directly inside Snowflake's managed cloud storage (User, Table, or Named internal stage). External Stages point directly to external customer-managed cloud buckets (Amazon S3, Azure Blob, Google Cloud Storage) using secure storage integrations.

Q15. Explain the difference between managed tables and external tables in Databricks.

Foundation • 0-2 Yrs

Managed Tables: Databricks manages both metadata and physical Parquet files in the default storage location; dropping the table deletes both metadata and data. External Tables: Points to a custom storage path; dropping the table deletes only metadata while raw data files remain intact.

Q16. How does the Delta Lake Transaction Log (_delta_log) guarantee ACID transactions?

Experienced • 3-5+ Yrs

Delta Lake writes atomic JSON commit files (000000.json, 000001.json) for every transaction using optimistic concurrency control. Every 10 commits, it compiles a checkpoint Parquet file. Queries read the latest checkpoint and replay remaining JSON files to obtain a perfectly consistent snapshot.

Q17. Explain Delta Lake Time Travel and how it is implemented.

Experienced • 3-5+ Yrs

Because Delta Lake maintains historical commit logs and data files, users can query past snapshots using VERSION AS OF or TIMESTAMP AS OF . Running VACUUM removes underlying unreferenced Parquet files older than a retention threshold, terminating Time Travel beyond that point.

Q18. How do you resolve Spark Shuffle and Skew issues in PySpark jobs?

Experienced • 3-5+ Yrs

Data Skew occurs when one partition is vastly larger than others, causing single tasks to take hours. Solutions: 1. Salting (appending random prefixes to skewed join keys to distribute data); 2. Broadcast Joins (broadcasting small tables to eliminate shuffles entirely); 3. Enabling Adaptive Query Execution (AQE: spark.sql.adaptive.skewJoin.enabled).

Q19. What is Adaptive Query Execution (AQE) in Apache Spark 3.x?

Experienced • 3-5+ Yrs

AQE re-optimizes query plans at runtime based on real stage execution statistics: 1. Dynamically coalescing post-shuffle partitions (combining tiny partitions); 2. Dynamically switching join strategies (e.g., converting Sort-Merge Join to Broadcast Hash Join if table shrinks); 3. Dynamically handling skewed joins.

Q20. Explain the difference between Broadcast Hash Join and Sort-Merge Join in Spark.

Experienced • 3-5+ Yrs

Broadcast Hash Join: Copies the smaller table (<10MB default, adjustable) to all executor nodes, executing joins in memory without network shuffles (fastest). Sort-Merge Join: Standard join for large tables; shuffles both tables by join key, sorts partitions, and merges matches (scales to petabytes but requires network I/O).

Q21. How does Snowflake Streams and Tasks enable Change Data Capture (CDC)?

Experienced • 3-5+ Yrs

A Stream records change data capture (CDC) deltas (inserts, updates, deletes) made to a table using metadata pointers without copying data. A Task schedules recurring SQL execution (cron-like). Together, they automate continuous pipeline ELT (consuming from stream and updating target tables).

Q22. Explain Dynamic Data Masking and Row Access Policies in Snowflake.

Experienced • 3-5+ Yrs

Dynamic Data Masking obfuscates sensitive data (e.g., PII: SSN, Email) at query runtime based on the caller's role (using CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val ELSE '***' END). Row Access Policies restrict which rows are returned based on user roles and attributes.

Q23. What is Z-Ordering (Liquid Clustering) in Databricks Delta Lake?

Experienced • 3-5+ Yrs

Z-Ordering is a multidimensional clustering technique that colocates related information in the same files based on specified columns, maximizing data skipping on queries filtering by those columns. Liquid Clustering (modern replacement) provides dynamic, incremental clustering without rewriting full datasets.

Q24. How do you optimize slow queries in Snowflake using Query Profile?

Experienced • 3-5+ Yrs

In Snowflake UI, inspect Query Profile: 1. Partitions Scanned vs Total Partitions (high ratio indicates poor pruning -> cluster table); 2. Spilling to Local Storage (virtual warehouse RAM exceeded -> size up warehouse); 3. Spilling to Remote Storage (extreme RAM shortage -> critical bottleneck); 4. Most expensive nodes.

Q25. Explain the difference between Auto-Drop, Auto-Suspend, and Auto-Resume in Snowflake.

Experienced • 3-5+ Yrs

Auto-Suspend: Automatically shuts down a warehouse after N seconds of inactivity to stop compute charges. Auto-Resume: Automatically wakes up the warehouse in milliseconds when a new query arrives. Auto-Drop applies to temporary or transient objects when sessions end.

Q26. What is dbt (data build tool) and how does it integrate with Snowflake/Databricks?

Experienced • 3-5+ Yrs

dbt manages the 'T' (Transform) in ELT. Data engineers write SQL SELECT models with Jinja templating. dbt handles dependency graphs (DAGs), materializations (views, tables, incremental models), automated testing (uniqueness, not null), and documentation generation.

Q27. Explain Structured Streaming in Databricks and how checkpointing works.

Experienced • 3-5+ Yrs

Structured Streaming is a scalable stream processing engine built on Spark SQL. It processes streaming sources incrementally as micro-batches. Checkpointing records current read offsets in cloud storage, guaranteeing fault tolerance and exactly-once processing across pipeline restarts.

Q28. What is the difference between Transient Tables and Temporary Tables in Snowflake?

Experienced • 3-5+ Yrs

Temporary Tables: Visible only within the creator session and automatically dropped upon session termination (0-1 day Time Travel, no Fail-Safe). Transient Tables: Persist indefinitely until explicitly dropped, have 0-1 day Time Travel, but zero Fail-Safe, saving storage costs for staging pipelines.

Q29. How do you implement CI/CD for Databricks Lakehouse pipelines using Databricks Asset Bundles (DAB)?

Experienced • 3-5+ Yrs

DAB allows defining Databricks jobs, pipelines, and notebooks as infrastructure as code in YAML files. Versioned in Git, CI/CD pipelines (GitHub Actions) test, validate, and deploy bundles across Dev, Staging, and Prod workspaces with unified permissions.

Q30. What is Search Optimization Service in Snowflake?

Experienced • 3-5+ Yrs

Search Optimization Service creates a background persistent search access path on high-cardinality tables. It accelerates point lookup queries (e.g., finding a single transaction ID or IP address among billions of rows) by orders of magnitude without requiring manual clustered keys.

Q31. Write a Snowflake SQL query to create a transient staging table with automatic cluster key.

Live Coding Challenge

Define transient table with cluster by expression.

CREATE OR REPLACE TRANSIENT TABLE stg_customer_orders (
    order_id STRING,
    customer_id STRING,
    order_date DATE,
    amount NUMBER(12, 2)
)
CLUSTER BY (order_date, customer_id);

Q32. Write a Snowflake Time Travel query to restore a dropped table.

Live Coding Challenge

Use UNDROP TABLE statement.

UNDROP TABLE customer_dim;

Q33. Write a PySpark snippet to read a Delta Lake table and perform aggregation.

Live Coding Challenge

Read delta format, group by category, and compute total revenue.

from pyspark.sql import SparkSession
from pyspark.sql.functions import sum, col

spark = SparkSession.builder.appName("DeltaAnalysis").getOrCreate()
df = spark.read.format("delta").load("/mnt/lakehouse/silver/sales")

summary_df = df.groupBy("product_category") \
    .agg(sum("revenue").alias("total_revenue")) \
    .filter(col("total_revenue") > 100000)

summary_df.write.format("delta").mode("overwrite").save("/mnt/lakehouse/gold/category_summary")

Q34. Write a Snowflake SQL query to clone a production database for QA testing.

Live Coding Challenge

Use CLONE syntax for zero-copy cloning.

CREATE DATABASE qa_analytics_clone CLONE prod_analytics;

Q35. Write a PySpark Broadcast Join between a large facts DataFrame and small dimension DataFrame.

Live Coding Challenge

Use broadcast function from pyspark.sql.functions.

from pyspark.sql.functions import broadcast

# Large transaction table joined with small store lookup table
joined_df = large_fact_df.join(
    broadcast(small_dim_stores),
    large_fact_df.store_id == small_dim_stores.store_id,
    "inner"
)

Q36. Write a Snowflake Stream and Task definition for incremental CDC updates.

Live Coding Challenge

Create stream on source table and task executing MERGE on schedule.

CREATE OR REPLACE STREAM orders_stream ON TABLE raw_orders;

CREATE OR REPLACE TASK process_orders_task
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = '5 MINUTE'
WHEN
  SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  MERGE INTO final_orders target
  USING orders_stream src
  ON target.order_id = src.order_id
  WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.amount = src.amount
  WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT' THEN INSERT (order_id, amount) VALUES (src.order_id, src.amount);

Q37. Write a Databricks SQL command to optimize and Z-Order a Delta table.

Live Coding Challenge

Run OPTIMIZE with ZORDER BY.

OPTIMIZE silver_customer_events
ZORDER BY (customer_id, event_date);

Q38. Write a Snowflake query querying historical table state 2 hours ago.

Live Coding Challenge

Use AT (OFFSET => ...) clause.

SELECT COUNT(*)
FROM orders AT(OFFSET => -60*60*2); -- 2 hours ago

Q39. Write a PySpark script to handle data skew using salting on join keys.

Live Coding Challenge

Append random integer 0-9 to skewed key on both sides.

from pyspark.sql.functions import concat, col, lit, floor, rand, explode, array

# Add salt 0-9 to skewed DataFrame
skewed_df = skewed_df.withColumn("salt", floor(rand() * 10))
skewed_df = skewed_df.withColumn("salted_key", concat(col("user_id"), lit("_"), col("salt")))

# Replicate dimension table 10 times with salts
dim_replicated = dim_df.withColumn("salt", explode(array([lit(i) for i in range(10)])))
dim_replicated = dim_replicated.withColumn("salted_key", concat(col("user_id"), lit("_"), col("salt")))

# Join on salted_key
result = skewed_df.join(dim_replicated, "salted_key")

Q40. Write a Snowflake Dynamic Data Masking policy masking emails for non-admin roles.

Live Coding Challenge

Create masking policy using regexp_replace.

CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('ADMIN_ROLE', 'DATA_OFFICER') THEN val
    ELSE REGEXP_REPLACE(val, '^.*@', '****@')
  END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;