Data Engineer Top 100 Interview Questions

A data engineer builds and operates systems that turn source data into dependable datasets. The role connects business requirements with ingestion, storage, transformation, and delivery.

• Requirements: define who uses the data, what each field means, how fresh it must be, and which failures would affect important decisions.
• Construction: implement pipelines and data models that preserve source meaning, support expected queries, and handle changing volume and schemas.
• Reliability: test completeness and correctness, monitor processing, investigate incidents, and make recovery and reruns predictable.
• Ownership: document datasets, protect sensitive information, manage access and cost, and coordinate changes with producers and downstream consumers.

Success is measured by useful, trustworthy data delivered consistently, rather than by the number of pipelines created.

I would define the business outcome and measurable delivery requirements before choosing the architecture. Volume alone does not determine whether a pipeline should be batch or streaming.

1. Identify consumers, decisions, source owners, required fields, business definitions, and the consequences of incorrect or unavailable data.
2. Estimate typical and peak volume, growth, update patterns, acceptable freshness, retention, and the need to recover historical changes.
3. Check source access, delivery guarantees, privacy requirements, budget, and operational ownership; agree on how completeness will be verified.
4. Choose the simplest design that meets these constraints, then document acceptance criteria and test representative failure scenarios.

flowchart TD
A["Identify consumer need"]
B["Define delivery targets"]
C{"Continuous updates needed?"}
D["Evaluate streaming"]
E["Evaluate scheduled batches"]
F["Validate cost and recovery"]
A --> B
B --> C
C -->|yes| D
C -->|no| E
D --> F
E --> F

Trade-offs:

• Lower latency usually requires more continuous infrastructure and operational attention; confirm that consumers benefit from it.
• A stricter recovery target can justify extra retained history and redundancy even when the ordinary workload is small.

ETL transforms extracted data before loading it into the target analytical store. ELT loads it first and performs transformations using the target environment.

When to use which:

• Use ETL when sensitive fields must be removed before entering the destination or when the destination cannot perform the required transformations efficiently.
• Use ELT when scalable analytical compute is available and retaining source-shaped data makes new transformations and historical reprocessing easier.
• Choose a hybrid when ingestion requires basic validation or redaction but most business transformations belong close to the analytical data.

Both approaches still need versioned logic, quality checks, access controls, and recovery procedures. Loading raw data does not automatically make ELT simpler or cheaper.

The choice depends on acceptable delay, workload behavior, and operational complexity. Streaming is useful when decisions cannot wait for the next scheduled batch.

When to use which:

• Use batch for workloads with relaxed freshness requirements, predictable processing windows, and substantial historical transformations that benefit from bounded inputs.
• Use micro-batches when short delays are acceptable and batching improves throughput or simplifies checkpointed processing of incoming records.
• Use continuous processing when event-level responsiveness has measurable value and the team can operate state, ordering, backpressure, and recovery correctly.

Measure complete source-to-consumer delay rather than only processing time. A fast engine cannot compensate for a source that publishes data once per day.

OLTP supports operational transactions, while OLAP supports analytical questions across many records. They favor different access patterns and physical designs.

When to use which:

• Use an OLTP design when individual business operations must update a small amount of data with appropriate transactional guarantees and low response time.
• Use an OLAP design when users compare trends, combine historical datasets, or aggregate large portions of a table across many dimensions.
• Separate heavy analytics from operational traffic when scans and transformations would compete with customer-facing transactions for resources.

These are workload categories rather than absolute product boundaries. A system may support both, but isolation, concurrency, and cost still need evaluation.

These architectures organize storage and analytical access differently. Their usefulness depends on governance, query performance, supported data types, and operational requirements.

When to use which:

• Use a warehouse when governed SQL analytics, stable table semantics, and predictable reporting are the central requirements for most consumers.
• Use a lake when retaining varied source data and enabling multiple processing patterns are important, while explicitly providing ownership and metadata management.
• Use a lakehouse when shared file storage should support transactional table operations and analytical queries across compatible engines.

The label does not guarantee reliability. Verify transaction boundaries, schema enforcement, access controls, maintenance costs, and behavior when multiple writers update the same data.

The categories describe how explicitly records expose their organization. Every category still requires decisions about interpretation, validation, and downstream use.

• Structured data: records follow defined columns and types, making schema checks straightforward; business constraints and relationships still require separate validation.
• Semi-structured data: nested objects or varying fields preserve useful hierarchy, but pipelines must handle missing attributes, changing types, and repeated elements deliberately.
• Unstructured data: documents, images, and recordings may require extraction before they can support relational analysis; retain provenance and extraction confidence where relevant.
• Design consequence: preserve a governed source representation, define the intended analytical schema, and record transformations so consumers can trace derived values.

Schema-on-read delays some interpretation decisions; it does not eliminate the need for a schema or dependable data contracts.

Normalization organizes relational data to reduce redundant facts and the update anomalies they cause. It is based on keys and functional dependencies, not simply on splitting large tables.

• First normal form: represent values within the chosen relational domains without repeating column groups; each row must be identifiable and have a consistent structure.
• Second normal form: satisfy first normal form and ensure every non-key attribute depends on the whole of each candidate key, not only part of a composite key.
• Third normal form: remove inappropriate dependencies between non-key attributes; formally, each nontrivial dependency has a superkey determinant or a prime attribute on its right side.
• Practical trade-off: normalization helps maintain operational consistency, while deliberate denormalization can simplify analytical reads at the cost of additional update responsibility.

ACID describes properties that make a transaction a dependable unit of database work. The guarantees apply within the database's documented transaction boundary and configuration.

• Atomicity: all operations in a transaction commit together or their changes are rolled back, preventing a partial business update from becoming the final state.
• Consistency: committed work preserves enforced constraints and application-defined invariants when the transaction logic is correct; the database cannot infer every business rule.
• Isolation: concurrent transactions interact according to the selected isolation level, which determines which intermediate effects or anomalies may be observed.
• Durability: acknowledged commits survive the failures covered by the configured persistence and replication guarantees.

A transaction does not automatically include a separate message broker, external API, or file write. Coordinating those effects requires an additional design.

NULL represents an unknown or missing value rather than zero or an empty string. Comparisons involving NULL usually produce UNKNOWN, so ordinary two-valued reasoning can give incorrect results.

• Filtering: a WHERE clause keeps rows whose condition is TRUE; both FALSE and UNKNOWN are excluded, so use IS NULL or IS NOT NULL for missing values.
• Aggregation: COUNT(*) counts rows, while COUNT(column) ignores NULL values; most numerical aggregates also ignore NULL inputs and can return NULL for an empty group.
• Defaults: COALESCE supplies a replacement when that replacement has an agreed business meaning; turning an unknown amount into zero can distort reporting.
• Anti-matching: a NULL in a NOT IN subquery can make otherwise unmatched comparisons UNKNOWN; an appropriately correlated NOT EXISTS often expresses the intended rule more safely.

A join combines rows according to a matching rule. Select the join based on which unmatched records must remain visible in the result.

When to use which:

• Use an inner join when unmatched records should be excluded intentionally, after confirming that missing keys do not hide a data-quality problem.
• Use a left or full outer join when missing matches carry business meaning, such as identifying customers without activity or comparing two extracts.
• Use a cross join only when the full combination is needed and its expected row count is acceptable.

Any matching key with multiple rows can multiply the result. Filtering a right-side column in WHERE after a left join can also remove its unmatched rows.

WHERE filters input rows before grouping. HAVING filters the resulting groups, usually using aggregate conditions such as a minimum total or count.

When to use which:

• Use WHERE to remove records that should not contribute to the calculation, such as canceled transactions when measuring completed revenue.
• Use HAVING to select groups based on their aggregate results, such as customers whose completed revenue exceeds a defined threshold.
• Use both when the input population and the group-level acceptance rule differ; state those rules separately so the query remains auditable.

Filtering rows early can reduce work, but moving an aggregate condition into WHERE changes the meaning or makes the statement invalid. The logical evaluation stages do not prescribe an engine’s exact physical execution order.

UNION combines compatible query results and removes duplicate result rows. UNION ALL combines them while preserving every occurrence, including duplicates.

When to use which:

• Use UNION ALL when duplicates represent legitimate repeated events, when source partitions are disjoint, or when business-key deduplication occurs explicitly in another step.
• Use UNION when duplicate complete output rows are genuinely interchangeable and set semantics match the intended result.
• Check column positions, compatible types, and consistent units before combining results; matching column names alone does not establish compatibility.

Neither operator guarantees presentation order without a final ORDER BY. UNION also does not resolve conflicting versions of one entity when their non-key values differ.

GROUP BY reduces input rows to one result per group. A window function computes across related rows while retaining each row in the windowed result.

When to use which:

• Use GROUP BY when the requested output is a summary at a different grain, such as one total per customer instead of one row per order.
• Use a window function when each detail row must remain available alongside a comparison, rank, previous value, or cumulative measurement.
• Combine the techniques when you first need a daily summary and then want to compare each daily result with previous days.

A window’s PARTITION BY defines independent groups, ORDER BY establishes sequence, and its frame controls which rows participate in applicable calculations. These clauses serve different purposes.

These functions assign positions within an ordered window, but they treat tied ordering values differently. The tie policy must match the question being answered.

When to use which:

• Use ROW_NUMBER to select a fixed number of rows or one winning version, adding a stable unique tie-breaker when reproducible selection matters.
• Use RANK when competition positions should reflect how many rows precede a tied group, leaving the corresponding gaps afterward.
• Use DENSE_RANK when the requirement refers to distinct score or salary levels, so ties share a position and the next level follows immediately.

Adding a unique identifier to the ranking order removes ties. Do that only when distinct row positions, rather than shared business ranks, are intended.

I would rank each entity’s versions by the authoritative update sequence and retain the first row. A timestamp alone may not establish a unique winner.

• Partition: group versions by the complete business identifier, including a source identifier if different systems can reuse the same key.
• Order: sort by the business update time or source sequence descending, then use a deterministic tie-breaker with a documented meaning.
• Select: calculate ROW_NUMBER in a subquery or common table expression and filter to position one in the outer query.
• Validate: check equal timestamps, missing ordering values, deletions, and late arrivals; ingest time should not replace business ordering without justification.

The result is a current-state projection, so retain the original version history separately when historical reconstruction is required.

I would rank distinct salary levels in descending order and select the requested rank. Clarifying whether ties count separately prevents a common interpretation error.

• Input rule: exclude missing salaries unless the business question defines a meaningful ordering for them, and require n to be a positive integer.
• Ranking: use DENSE_RANK over salary descending so equal salaries share a level and later distinct salaries receive consecutive ranks.
• Output: select the requested rank and return a distinct salary; include employee fields instead if the request is for all employees at that level.
• Edge cases: return no matching row when fewer than n salary levels exist, and define whether the calling application should display a missing value.

An offset over all employee rows answers a different question when salaries are tied.

I would aggregate revenue to one row per category and product before ranking products within each category. Ranking transaction rows would answer the wrong question.

• Aggregation: calculate revenue using agreed rules for refunds, discounts, and currency, ensuring every transaction contributes once to the intended product total.
• Partitioning: create an independent ranking for each category rather than applying one global limit to the whole result.
• Tie policy: use ROW_NUMBER with a deterministic product tie-breaker for at most three products; use shared ranks if all ties must be returned.
• Validation: check categories with fewer than three products, equal revenues, and products that changed categories during the measurement period.

State the reporting interval explicitly in a production query so its results remain reproducible.

I would use a correlated NOT EXISTS condition to keep customers for whom no matching order exists. It directly expresses an anti-join without multiplying customer rows.

• Population: begin with the authoritative customer table so customers with no activity are present in the starting dataset.
• Matching: correlate orders using the complete customer key and apply any relevant business rule, such as whether canceled orders count as orders.
• Missing keys: decide how unassigned orders should be handled separately; a NULL customer key in an order does not establish a match to a valid customer.
• Performance: inspect the execution plan and available access paths for the matching key rather than assuming one SQL spelling is always fastest.

Filtering the order table first cannot reveal customers who never appear in it.

I would use a windowed SUM with an explicit ordering and row frame. The frame makes it clear that the result includes the current row and all preceding rows.

• Grain: first decide whether the cumulative measure should advance per transaction, day, or another business unit, and aggregate to that grain when necessary.
• Partition: restart the total at the correct boundary, such as each account, instead of mixing unrelated balances or revenue streams.
• Ordering: add a deterministic tie-breaker for equal timestamps when individual transaction order affects intermediate totals.
• Frame: specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for row-by-row accumulation; default peer-based behavior may combine equal ordering values.

Window ordering determines the calculation, while a final ORDER BY controls how returned rows are displayed.