Data engineer technical fundamentals interview questions

As asked

Explain the fundamental design differences between OLTP and OLAP systems, why you cannot run analytics queries directly against your production Postgres database at scale, and what design decisions in a warehouse like Snowflake or BigQuery optimize for analytical workloads.

Sample answer outline

OLTP (operational): optimized for high-concurrency low-latency point reads and writes (INSERT, UPDATE, DELETE by primary key), row-store, normalized to 3NF to minimize write amplification. OLAP (analytical): optimized for large sequential scans aggregating millions of rows, columnar store, denormalized for read performance, often partitioned by date. Running analytics directly against Postgres at scale causes full table scans that degrade OLTP performance and hold locks. Snowflake and BigQuery separate compute from storage, use columnar compression, and do not have row-level locking, making aggregate scans fast and cheap. Strong candidates mention that separation also prevents analytic workloads from affecting operational system availability.

Expect these follow-ups

• What is HTAP (Hybrid Transaction/Analytical Processing) and when would you use it?
• Why does Postgres perform so poorly on a COUNT(*) across 100 million rows compared to Snowflake?

As asked

When would you choose Parquet, Avro, ORC, or JSON for storing data in S3? Walk me through the trade-offs in terms of compression, schema evolution, read performance, write performance, and tooling compatibility.

Sample answer outline

JSON: human-readable, schema-flexible, high storage cost, poor query performance. Good only for raw landing zone or debugging. Avro: row-based, compact, schema-embedded, excellent for streaming and Kafka (Confluent Schema Registry uses Avro natively), and supports rich schema evolution with defaults. Parquet: columnar, excellent compression and predicate pushdown, optimized for OLAP reads, poor for streaming single-record writes. De facto standard for data lake analytical storage. ORC: similar to Parquet but optimized for Hive and MapReduce ecosystems; has built-in ACID support in Hive. Parquet wins for most analytical use cases (Spark, BigQuery, Snowflake all read it natively). Avro wins for streaming event schemas. Strong candidates mention that Iceberg and Delta Lake use Parquet as the underlying file format.

Expect these follow-ups

• How does Parquet's row group size affect read performance for a query that filters on a highly selective predicate?
• What schema evolution capabilities does Parquet support and which ones does it not?

As asked

Walk me through the logical order of operations in a SQL SELECT statement with WHERE, JOIN, GROUP BY, HAVING, and ORDER BY clauses. Then explain a concrete example from a warehouse query, such as why filtering on a window function result requires a CTE rather than a WHERE clause.

Sample answer outline

Logical order: FROM and JOINs (determine the source rowset), WHERE (filter rows before grouping), GROUP BY (group rows), aggregate functions evaluated, HAVING (filter groups), SELECT (evaluate expressions and aliases), DISTINCT (deduplicate), ORDER BY (sort), LIMIT and OFFSET (page). This matters because: you cannot reference a SELECT alias in a WHERE clause (WHERE is evaluated before SELECT), but you can reference it in an ORDER BY or HAVING. You cannot filter on an aggregate (SUM) in a WHERE clause because aggregates are evaluated after WHERE; that filter goes in HAVING. Window functions are evaluated after WHERE and GROUP BY but before ORDER BY. Strong candidates can give a specific example of a query that fails or gives wrong results because of misunderstanding this order.

Expect these follow-ups

• Why does this query fail: SELECT user_id, SUM(amount) AS total WHERE total > 100?
• At what point in the execution order are window functions evaluated?

As asked

Explain the CAP theorem and give a concrete example of a trade-off you make when designing a distributed data pipeline, such as choosing between strong consistency and availability.

Sample answer outline

CAP: a distributed system can guarantee at most two of Consistency, Availability, and Partition Tolerance; in practice network partitions are unavoidable so the real choice is CP vs AP. In data pipelines: Kafka chooses AP by default (producers can still write during a broker failure, at the cost of potential message ordering or loss), while PostgreSQL replication chooses CP (synchronous replication pauses writes if the replica is unavailable). A data engineer chooses based on the use case: for financial audit logs, CP (consistency) is required even if it means the pipeline pauses during network issues; for analytics event collection, AP is acceptable (some events may be duplicated or delayed but the system stays up). Strong candidates mention that the CAP theorem describes distributed systems under partition, and that most practical systems add eventual consistency as a middle ground.

Expect these follow-ups

• What does eventual consistency mean in the context of a Kafka consumer reading from a replicated topic?
• How does the PACELC model extend CAP and why is it more useful for data engineering decisions?

As asked

Walk me through database normalization up to 3NF and explain why data warehouses deliberately denormalize. When is it acceptable to have data redundancy in an analytical schema?

Sample answer outline

1NF: atomic column values, no repeating groups. 2NF: no partial dependency on composite key. 3NF: no transitive dependency (every non-key column depends on the key, the whole key, and nothing but the key). Normalized schemas minimize redundancy and update anomalies, which is critical for transactional systems. In analytical schemas, JOINs across many normalized tables are expensive on billions of rows and columnar stores do not benefit from normalization the way row stores do. Denormalization pre-joins commonly used tables into wide flat tables (fact plus dimensions), trading storage (cheap in a column store) for query speed. Strong candidates note that star schemas are partially denormalized (dimension tables are already flattened) and that wide flat tables in BigQuery or Snowflake are often preferred for performance despite the redundancy.

Expect these follow-ups

• What is the risk of denormalization when source data is updated?
• When would you choose a normalized ODS (Operational Data Store) layer over going straight to a denormalized mart?

As asked

Explain each of the four ACID properties with a specific example from a data pipeline context. Then describe which of these properties Spark RDD operations and Kafka topic writes provide natively.

Sample answer outline

Atomicity: all operations in a transaction succeed or all fail (a Kafka producer transaction writes to multiple topics atomically). Consistency: data must satisfy all defined constraints before and after (a NOT NULL constraint on a warehouse table). Isolation: concurrent transactions do not see each other's intermediate state (Snowflake's snapshot isolation). Durability: committed data survives crashes (Kafka's replication factor ensures committed offsets survive broker failure). Spark RDD operations are not ACID: they can fail mid-job leaving partial output, have no isolation guarantees between concurrent jobs, and require manual cleanup on failure. Delta Lake and Iceberg add ACID to Spark by using write-ahead logging and atomic metadata swaps. Kafka provides atomicity and durability for producers with transactions enabled, but not isolation or consistency in the database sense.

Expect these follow-ups

• How does Delta Lake implement atomicity for a Spark write operation?
• What is the difference between isolation levels: read committed versus snapshot isolation?

As asked

Compare B-tree and bitmap indexes in terms of how they work, what cardinality they are suited for, and name a specific scenario in a data warehouse where you would choose a bitmap index over a B-tree.

Sample answer outline

B-tree: a balanced sorted tree structure that supports equality and range queries efficiently (O(log n)) and works well for high-cardinality columns like user_id or order_id. Bitmap: stores one bit per row per distinct value, uses bitwise AND or OR to combine multiple filter predicates extremely efficiently. Best for low-cardinality columns (status, gender, country) with many rows. Bitmap indexes use much less space than B-tree for low-cardinality columns and are faster for multi-predicate queries (AND two bitmaps is a single bitwise operation). However, bitmap indexes are expensive to update (each INSERT changes multiple bitmaps), making them unsuitable for OLTP. In a data warehouse, a bitmap index on an order_status column (5 distinct values, 1 billion rows) is far more efficient than a B-tree. Most modern columnar warehouses (Snowflake, BigQuery) handle this internally without explicit bitmap indexes.

Expect these follow-ups

• Why are bitmap indexes rarely used in OLTP systems like Postgres?
• How does Snowflake achieve similar benefits to bitmap indexes without requiring the user to create them explicitly?

As asked

Explain the architectural difference between a traditional message queue (like RabbitMQ or SQS) and a log-based streaming platform like Kafka. What does Kafka's log retention give you that a queue does not?

Sample answer outline

Traditional message queues: messages are consumed and deleted; each message is delivered to one consumer; consumers compete for messages; no replay capability. Kafka: messages are stored in an ordered, immutable log per partition for a configurable retention period; multiple consumer groups independently read the same log without affecting each other; any consumer can replay from any offset. This gives Kafka capabilities a queue cannot provide: adding a new consumer group that replays the full history, reprocessing historical data when pipeline logic changes, and maintaining audit trails. The trade-off is that Kafka requires managing consumer offsets and is more operationally complex. Strong candidates note that Kafka is unsuitable for work queue patterns where only one worker should process each message (though it can emulate this with single-consumer groups).

Expect these follow-ups

• When would you still choose RabbitMQ or SQS over Kafka for a data pipeline?
• What does topic compaction do in Kafka and how is it different from time-based retention?

As asked

Your dashboard reads from a Snowflake table that is refreshed every 30 minutes by a pipeline that reads from multiple source tables. Explain eventual consistency in this context and how you communicate data freshness to users accurately.

Sample answer outline

In this context, the dashboard data is eventually consistent with the source systems: it will reflect the true state, but only after the next pipeline run. Users need to understand this lag to avoid drawing incorrect conclusions (e.g., thinking a marketing campaign performed poorly when data from the last 30 minutes is simply not loaded yet). Practical measures: display a 'data as of [timestamp]' label on the dashboard using the MAX(load_timestamp) from the warehouse table, set a data freshness SLA and alert when the pipeline misses it, and design the dashboard to clearly distinguish between 'no data' and 'metric is zero'. Strong candidates discuss how to reduce perceived staleness: more frequent incremental runs, streaming aggregation for the most critical metrics, and using a semantic layer that always shows the freshness timestamp.

Expect these follow-ups

• How do you implement a dashboard freshness indicator that automatically turns red when data is stale?
• What is the risk of serving stale data for a financial metric that feeds a regulatory report?

As asked

Explain consistent hashing and describe at least two specific places where it appears in the data engineering stack you work with regularly.

Sample answer outline

Consistent hashing maps keys to nodes on a virtual ring so that adding or removing a node only remaps 1/n of keys rather than all keys. In data engineering: Kafka uses consistent hashing by default for partition assignment (hash of key modulo partition count), which is why changing partition count changes which partition a given key goes to. Cassandra uses consistent hashing with virtual nodes for data distribution. Redis Cluster uses hash slots (16384 buckets) distributed across nodes via consistent hashing. Strong candidates explain the rehashing problem in naive modulo hashing (all keys must be remapped when node count changes) and how the virtual ring solves it. They also note the hotspot problem: a popular key range can land on one node even with consistent hashing, requiring virtual nodes or explicit key mapping.

Expect these follow-ups

• Why does Kafka's hash-based partition assignment cause ordering problems when you change partition count?
• How do virtual nodes in Cassandra improve load distribution compared to a simple ring?