Question 1

What is the difference between repartition and coalesce in Apache Spark?

Accepted Answer

**Repartition(n)**: Performs a full shuffle to redistribute data across exactly `n` partitions. Can increase or decrease partition count. Uses hash partitioning by default—all rows are exchanged across the network.

**Coalesce(n)**: Merges existing partitions into fewer partitions without a full shuffle. Only decreases partition count....

Question 2

CDC During Migration - explain approaches for real-time Change Data Capture

Accepted Answer

CDC captures inserts, updates, and deletes from a source and applies them to a target in near real-time, enabling minimal-downtime migrations. **Approaches**: Log-based CDC (Debezium, AWS DMS)—reads WAL/redo logs; lowest latency, no schema change. Trigger-based—triggers on source; adds load and schema coupling. Timestamp/version columns—incremental only; misses deletes and out-of-order updates. Dual-write with reconciliation—applications write to both; eventual consistency and complexity....

Question 3

Explain the differences between a Data Lake and a Data Warehouse.

Accepted Answer

**Data Lake**: Low-cost object storage (S3, ADLS) for raw, semi-structured, unstructured data. Schema-on-read; used for exploratory analytics, ML, archival. **Data Warehouse**: Structured, curated storage optimized for SQL; schema-on-write; used for BI and reporting. **Why both exist**: Lakes offer flexibility and cost at scale; warehouses offer query performance and concurrency....

Question 4

Explain the differences between Data Warehouse, Data Lake, and Delta Lake

Accepted Answer

**Data Warehouse**: Structured, schema-on-write; optimized for SQL analytics (Snowflake, BigQuery). High compute cost, fast queries. **Data Lake**: Raw/semi-structured object storage (S3, ADLS); schema-on-read; low cost, flexible. **Delta Lake**: Open-source storage layer on a data lake adding ACID transactions, schema enforcement, time travel, upserts. **Why the distinction**: Warehouses scale compute and storage together; lakes decouple them....

Question 5

Can you explain the difference between OLTP and OLAP?

Accepted Answer

**OLTP**: Optimized for many small transactions (inserts, updates, deletes). Row-oriented, normalized, high concurrency. Examples: MySQL, PostgreSQL. **OLAP**: Optimized for complex analytical queries and aggregations on large datasets. Column-oriented, denormalized (star/snowflake). Examples: Snowflake, BigQuery, Redshift. **Why the split**: Different access patterns; mixing them degrades both. OLTP needs low latency and ACID; OLAP needs scan throughput....

Question 6

What is a Common Table Expression (CTE), and when would you use it?

Accepted Answer

**CTE**: A named temporary result set in a WITH clause, referenced in the main query. **Use cases**: Readability—break complex queries into steps. Reusability—reference same CTE multiple times. Recursion—hierarchies (org chart, bills of materials). **Why it matters**: CTEs improve maintainability; deep subqueries are hard to debug. **Scalability**: In some engines (e.g., PostgreSQL), CTEs are optimization fences—materialized once. In others (Snowflake, BigQuery), they're inlined....

Question 7

What is the difference between a primary key and a unique key?

Accepted Answer

**Primary Key**: Unique identifier; NOT NULL; one per table; often clustered. **Unique Key**: Enforces uniqueness; can have NULL (SQL allows one NULL per column in uniqueness); multiple per table. **Why it matters**: PK defines identity and referential integrity; unique constrains alternate keys (e.g., email). **Scalability**: PK is often the clustering key; choice affects physical layout. Unique indexes enable lookups. **Cost**: Each constraint adds index overhead....

Question 8

When would you choose a Snowflake schema over a Star schema?

Accepted Answer

Star: One fact, denormalized dimensions—simple, fewer joins, fast. Snowflake: Normalized dimensions (e.g., dim_product → dim_category → dim_category_group)—more joins, less redundancy. Why Snowflake: When dimension tables are large and shared—a single dim_category serves many products; denormalizing would duplicate millions of rows. Avoids inconsistent attributes across copies (e.g., category name updated in one place). Storage: Snowflake saves space when hierarchy is deep and wide....

Question 9

Describe the data pipeline architecture you've worked with.

Accepted Answer

**Section 1 — The Context (The 'Why')**
Data pipeline design must reconcile batch latency (hours) with streaming complexity (exactly-once, backpressure). A naive approach either over-engineers (Kafka for daily batch) or under-engineers (no idempotency, no lineage)....

Question 10

Difference Between Internal and External Tables in BigQuery

Accepted Answer

**Architectural Logic**: Internal (native) tables: BigQuery owns metadata and storage (Colossus). Columnar layout, partitioning, clustering, automatic optimization. Dropping = data deleted. External tables: BigQuery owns metadata only; data lives in GCS, Drive, Bigtable. Dropping = metadata deleted, data persists. **Why**: Internal enables DML, streaming inserts, partitioning strategies, cost-effective storage classes....

Snowflake Interview Questions for Data Engineers

Companies asking these questions

All 50 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Snowflake Interview Questions for Data Engineers

Companies asking these questions

All 50 Questions

More Interview Prep Guides

Get All Answers in PDF Format