Data Engineer Top 100 Interview Questions

I would model each business process at its own grain and connect them through stable identifiers. Orders, purchased items, payment attempts, and refunds have different lifecycles.

1. Define separate records for orders, order lines, payment transactions, and refund events, with source identifiers and authoritative business timestamps.
2. Store product and customer context through governed dimensions, and define how discounts, taxes, currencies, and order-level charges are allocated.
3. Link refunds to their original payment or item where the source supports it, preserving partial refunds and failed payment attempts explicitly.
4. Publish summaries at a declared common grain and reconcile booked revenue, collected cash, and refunded amounts as distinct business measures.

flowchart TD
A["Order header"]
B["Order lines"]
C["Payment transactions"]
D["Refund events"]
E["Grain aligned summaries"]
F["Revenue and cash reporting"]
A --> B
A --> C
C --> D
B --> E
C --> E
D --> E
E --> F

Trade-offs:

• Separate process facts require deliberate joins but preserve distinctions that a single flattened table can obscure.
• Allocated charges simplify product reporting only when the allocation rule is documented and reproducible.

I would compare both sides over the same business population and consistency boundary. A matching total row count alone cannot prove that the correct records arrived.

1. Fix the source snapshot or cutoff, business interval, inclusion rules, units, and treatment of updates and deletes before comparing results.
2. Compare control totals such as row counts, distinct identifiers, important amounts, and minimum or maximum event positions by meaningful partition.
3. Investigate mismatches with key-level comparisons and sampled field checks, tracing transformations, filtering, joins, and duplicate handling.
4. Document legitimate differences, repair unexplained ones, and store reconciliation evidence with the published dataset version or pipeline run.

flowchart TD
A["Align cutoff and business rules"]
B["Compare partition controls"]
C{"Controls agree?"}
D["Perform targeted key checks"]
E["Investigate and repair"]
F["Record reconciliation evidence"]
A --> B
B --> C
C -->|yes| D
C -->|no| E
E --> B
D --> F

Trade-offs:

• Detailed row comparisons provide stronger evidence but can be expensive across very large datasets.
• Checksums accelerate detection only when serialization is consistent; matching hashes are not a mathematical guarantee of equality.

A full refresh rebuilds the target from the selected source population. An incremental load applies only new or changed information since a recorded boundary.

When to use which:

• Use a full refresh for small datasets, initial loads, substantial transformation changes, or sources that cannot expose reliable changes within the required operating budget.
• Use incremental processing when volume or freshness makes rebuilding expensive and the source provides dependable identifiers, ordering, and change detection.
• Combine incremental delivery with periodic reconciliation or controlled rebuilds to detect drift and recover from historical defects.

An incremental strategy must account for updates, deletes, late commits, and retries. Filtering only on a creation timestamp is sufficient only for a genuinely immutable append-only source.

Change data capture, or CDC, exposes inserts, updates, and deletes as changes that downstream systems can consume. Log-based CDC reads committed database changes from a supported change log.

• Information: a change normally includes record identity, operation type, ordering metadata, and available before or after values, depending on source capabilities and configuration.
• Comparison: polling current rows can miss intermediate changes and hard deletes unless the source exposes suitable timestamps, tombstones, or another change mechanism.
• Continuity: consumers must persist their position and keep up with log retention; losing the required history can force a new consistent snapshot.
• Operations: monitor source impact, connector permissions, schema changes, and lag, and account for redelivery when applying changes downstream.

CDC transports change evidence; it does not by itself guarantee correct target merges, business transformations, or end-to-end exactly-once effects.

I would use a snapshot procedure coordinated with a known change-log position. Taking an ordinary table export and later starting an unrelated stream can lose or duplicate changes.

1. Select a source-supported snapshot mechanism that establishes a consistent view and a corresponding position in the retained change log.
2. Keep changes from the required boundary available while snapshot rows are copied, buffering or replaying overlap according to the capture protocol.
3. Apply snapshot and change records using stable keys and ordering metadata, so overlap cannot overwrite a newer value with an older one.
4. Validate the target at a known catch-up boundary, then continue consuming changes and persist progress only after durable target application.

flowchart TD
A["Establish consistent snapshot boundary"]
B["Copy snapshot rows"]
C["Retain overlapping change log"]
D["Apply ordered changes"]
E["Reconcile at catchup boundary"]
F["Continue live consumption"]
A --> B
A --> C
B --> D
C --> D
D --> E
E --> F

Trade-offs:

• Long snapshots require enough log retention and can increase source resource pressure.
• Concurrent snapshot protocols reduce disruption but need precise overlap handling rather than guessed timing.

I would use a persisted extraction boundary and a repeatable target merge, while recognizing that timestamps may not capture every committed change. Source transaction behavior determines the remaining risk.

1. Verify that the chosen timestamp changes on every relevant update, has adequate precision, and has a documented relationship to commit visibility.
2. Record an upper extraction boundary and read a bounded interval, using a stable tie-breaker for equal timestamps when paging through results.
3. Apply an intentional overlap and deduplicate or merge by source key, then advance the saved boundary only after durable target completion.
4. Reconcile periodically and define a separate deletion mechanism; choose log-based capture if arbitrarily late commits or missing updates cannot be bounded.

flowchart TD
A["Read saved boundary"]
B["Set upper extraction boundary"]
C["Read bounded overlapping interval"]
D["Merge records by source identity"]
E{"Target committed?"}
F["Advance boundary"]
G["Retry same interval"]
A --> B
B --> C
C --> D
D --> E
E -->|yes| F
E -->|no| G
G --> C

Trade-offs:

• Overlap improves tolerance to bounded delays but increases repeated reads and is not a guarantee against unlimited late commits.
• A timestamp-based extractor is simple only when the source’s update and deletion semantics support it.

An idempotent load produces the same intended target state when the same input is applied repeatedly. This is essential because failures can leave the outcome of a previous attempt uncertain.

1. Define stable input identity, such as an event identifier, source sequence, or partition and version, independently of the execution attempt.
2. Choose an appropriate write strategy: deterministic replacement of a bounded partition, a keyed upsert, or a transactional event ledger with uniqueness checks.
3. Publish data and progress through a consistent commit mechanism, avoiding blind appends when the previous write may already have succeeded.
4. Test retries after partial work, after target commit, and before acknowledgment, including concurrent attempts and replay of older source versions.

flowchart TD
A["Read identified input"]
B{"Already applied?"}
C["Return existing outcome"]
D["Prepare deterministic target changes"]
E["Commit data and processing record"]
F["Acknowledge completion"]
A --> B
B -->|yes| C
B -->|no| D
D --> E
E --> F

Trade-offs:

• Tracking processed identifiers consumes storage and needs retention aligned with the possible replay horizon.
• Replacing a partition is straightforward only when the input fully defines that partition’s intended contents.

Schema evolution is the controlled change of fields, types, and structure while producers and consumers continue operating. A change is safe only relative to the consumers that must read it.

• Compatibility: assess whether older readers can consume new data and whether newer readers can consume retained older data; these are different directions of compatibility.
• Additions: optional fields with meaningful defaults are often easier to introduce, but nullability and default interpretation must be agreed rather than assumed.
• Breaking changes: renames, removals, type narrowing, and changes in units or meaning may require versioned contracts and a migration period.
• Deployment: validate representative old and new records, coordinate producer and consumer rollout, and preserve a rollback or replay strategy.

Automatically accepting every new column can keep ingestion running while silently changing downstream semantics, so structural flexibility should not replace review of business meaning.

A data contract defines the promises between a dataset producer and its consumers. It should cover meaning and operating expectations as well as the physical schema.

• Structure and identity: specify fields, types, nullability, keys, uniqueness, event identity, and supported versions so records can be interpreted consistently.
• Semantics: define units, business meaning, valid values, timezones, update and deletion behavior, and any ordering guarantees that consumers rely on.
• Service expectations: describe publication frequency, freshness and completeness targets, retention, and how incidents or delays are communicated.
• Ownership and change: name the responsible team, compatibility rules, approval or notification process, and deprecation policy for breaking changes.

Enforce machine-checkable requirements during ingestion and deployment, while keeping human-readable definitions close to the dataset. A contract that nobody monitors will not prevent silent upstream changes.

I would separate valid records from failures according to an explicit quality policy and retain enough evidence for repair. Whether the batch can continue depends on the failed rule’s business impact.

1. Validate required structure, identifiers, types, and important business constraints before records reach trusted serving tables.
2. Send invalid records to a restricted quarantine with source location, rule failure, ingestion metadata, and an appropriate retention policy.
3. Choose whether to stop publication or release the valid subset based on agreed completeness and severity thresholds, reporting the rejected population.
4. Repair the producer or transformation, then replay corrected records through the same idempotent path and reconcile the original input population.

flowchart TD
A["Validate incoming record"]
B{"Required checks pass?"}
C["Write accepted records"]
D["Record restricted quarantine entry"]
E["Assess publication threshold"]
F["Repair and replay failures"]
A --> B
B -->|yes| C
B -->|no| D
C --> E
D --> E
D --> F

Trade-offs:

• Continuing with valid records improves availability but can create biased or incomplete analytics if rejection is not visible.
• Quarantine supports recovery but must not become an uncontrolled copy of sensitive source data.

Offset pagination skips a requested number of rows. Cursor pagination resumes from a supplied token or ordered position, which can be more stable and efficient for large sequential extracts.

When to use which:

• Use offsets for small, stable result sets when the source supports them efficiently and changes during extraction cannot invalidate page boundaries.
• Use cursors for large or changing feeds, preserving the source-provided token exactly or using a unique deterministic key order for keyset pagination.
• Use a consistent snapshot or documented export protocol when the whole extract must represent one point in time rather than a moving collection.

A cursor does not automatically provide snapshot consistency. Confirm ordering, token expiry, update behavior, and whether replaying a page returns the same records.

I would treat each extraction as a resumable sequence with explicit boundaries, durable progress, and duplicate-safe target writes. API availability and pagination behavior are part of the data contract.

1. Confirm authentication scope, rate limits, ordering, update filters, deletion signals, pagination consistency, and the validity period of continuation tokens.
2. Request bounded pages using stable filters, validate each response, and retain source identifiers and extraction metadata before transformation.
3. Retry transient errors with bounded backoff and server guidance, while stopping or escalating permanent authorization and validation failures.
4. Commit page data durably before advancing progress, then reconcile coverage and handle expired cursors or missed history through a defined recovery procedure.

flowchart TD
A["Load extraction boundary and cursor"]
B["Request next page"]
C{"Response valid?"}
D["Commit page data"]
E{"More pages?"}
F["Reconcile and close interval"]
G["Classify failure and retry or stop"]
A --> B
B --> C
C -->|yes| D
C -->|no| G
D --> E
E -->|yes| B
E -->|no| F

Trade-offs:

• Larger pages reduce request overhead but increase memory use and repeated work after a failed page.
• A successful response code does not prove completeness if the API silently truncates history or changes pagination semantics.

A deletion signal must travel through every relevant derived representation. A tombstone is a marker that identifies a deleted record; its exact encoding and retention depend on the source protocol.

• Identity and order: associate the delete with a stable key and source position so an older replayed update cannot incorrectly resurrect the record.
• Target policy: choose between physical removal, a soft-delete flag, or retained history according to the dataset’s purpose and retention requirements.
• Propagation: update analytical tables, aggregates, indexes, and caches whose contents or totals depend on the removed record.
• Recovery: retain deletion evidence for the required replay horizon, and ensure historical reprocessing applies the same deletion policy before publishing results.

Distinguish a business cancellation from a privacy-driven erasure. They can require different treatment of identifiers, history, backups, and derived measures.

I would distinguish an absolute instant from a local business date and preserve enough source context to interpret both. These are different data types conceptually, even when represented as strings.

• Canonical instant: normalize known instants to a consistent reference such as UTC, retaining the original offset or timezone identifier when business interpretation requires it.
• Local meaning: calculate reporting dates using the agreed business timezone rather than the server’s local clock or a fixed offset that changes seasonally.
• Ambiguity: detect local times that occur twice or do not occur during clock changes, and require a source rule instead of guessing silently.
• Precision: preserve sufficient fractional seconds and define interval boundaries consistently, usually including the start and excluding the end.

Do not label an offset-free timestamp as UTC without evidence about how the producer generated it.

Row-oriented storage keeps a record’s fields close together. Column-oriented storage groups values from the same column, favoring scans that read a subset of fields across many records.

When to use which:

• Use row-oriented layouts when workloads frequently fetch or update individual records and need many fields from each selected row.
• Use column-oriented layouts when queries aggregate large datasets but read relatively few columns, allowing less input and effective value encoding.
• Evaluate mixed workloads using actual read and write patterns, including update frequency, batch size, indexing, and maintenance behavior.

The storage orientation is not the only determinant of performance. Partitioning, compression, statistics, caching, and query planning can outweigh a simplistic row-versus-column comparison.

I would choose a file representation based on structure, interoperability, scan patterns, and schema requirements. Human readability is useful but can impose parsing and storage costs at scale.

When to use which:

• Use CSV for simple exchange when delimiter, quoting, encoding, header, and type-conversion rules are explicitly agreed between producer and consumer.
• Use JSON when retaining nested structure or flexible message attributes matters, while validating the actual schema instead of accepting arbitrary drift.
• Use typed binary formats for sustained processing when schema support, compact storage, efficient reads, and compatible readers justify the additional tooling.

A file format is not a transactional table system. Concurrent updates, snapshots, deletes, and metadata commits need an additional management layer.

These techniques organize data so a query can avoid work or distribute it more effectively. Their exact behavior and terminology vary by storage and execution engine.

When to use which:

• Use partitions around common selective filters and manageable cardinality, such as business date, while avoiding millions of tiny partitions.
• Use bucketing when supported hash layouts fit join or distribution needs; compatible bucket definitions and engine behavior determine whether shuffles can be avoided.
• Use clustering or sorting when queries repeatedly filter ranges or related keys and storage statistics can exploit the resulting locality.

Evaluate maintenance costs and changing access patterns. A layout optimized for one query can create skew, expensive writes, or poor pruning for another.

Small files can make metadata, file-open, scheduling, and request overhead dominate useful data processing. Compaction combines them into larger files suited to the workload.

• Reading cost: each file may require listing, metadata inspection, planning, and a separate read request even when it contains very few records.
• Write causes: overly frequent micro-batches, excessive partitions, and uncoordinated writers can create a large file count despite modest total data volume.
• Compaction design: combine files within appropriate partitions while preserving records, schema, ordering requirements, and a consistent published table snapshot.
• Operational balance: choose target sizes using query parallelism and storage characteristics, and coordinate compaction with retention and concurrent writes.

One enormous file is not automatically better because it can reduce parallelism or increase rewrite cost. Measure file count, scan efficiency, and maintenance overhead together.

Predicate pushdown moves supported filtering closer to the data source. Projection pushdown reads only required columns, reducing the data that enters later processing stages.

• Filtering: a source may evaluate a predicate directly or use file and row-group statistics to skip regions that cannot contain matching records.
• Column selection: selecting only needed fields can reduce input substantially in column-oriented storage, while row-oriented sources may offer a different benefit.
• Limitations: unsupported expressions, conversions, complex functions, or missing statistics can prevent a filter from being pushed down or used for skipping.
• Verification: inspect the physical plan and actual bytes or records read rather than assuming an early-looking filter reduced source work.

Pushdown must preserve the query’s meaning, including NULL behavior, type conversions, and timezone interpretation. A logically early filter is not necessarily a physically efficient one.

Lazy transformations describe the intended computation without immediately processing the entire dataset. An action or write triggers planning and execution of the required work.

1. Build a logical plan describing reads, filters, projections, joins, and aggregations, preserving expressions the optimizer can reason about.
2. Optimize that plan using supported rules and statistics, then choose physical operators and data-exchange requirements.
3. Divide execution around exchange boundaries into stages and tasks that process partitions across available workers.
4. Schedule tasks, move required data, recover failed work according to the engine’s guarantees, and commit or return the completed result.

Laziness allows optimizations such as removing unused columns, but it also means an error may appear only when execution starts. Repeated actions can recompute shared work unless an appropriate result is persisted.

flowchart TD
A["Build logical transformations"]
B["Action requests result"]
C["Optimize physical plan"]
D{"Data exchange required?"}
E["Create exchange boundary"]
F["Schedule partition tasks"]
G["Return or commit result"]
A --> B
B --> C
C --> D
D -->|yes| E
D -->|no| F
E --> F
F --> G