Top 8 Data Ingestion and Pipeline Reliability Interview Questions

Data engineering interviews often focus on what happens when pipelines encounter real-world problems: late-arriving records, retries, partial failures, schema changes, historical backfills, and mismatched counts. Strong answers show how you make ingestion reliable, observable, recoverable, and reusable.

Interview framework: For every pipeline design question, cover the source, extraction strategy, checkpointing, idempotency, failure recovery, data quality, observability, and security.

1. When would you choose ETL over ELT?

ETL means Extract, Transform, Load: data is transformed before being loaded into the target. ELT means Extract, Load, Transform: raw data is loaded first, then transformed in the destination platform.

Factor	ETL	ELT
Transformation location	Before loading into the target	Inside the destination platform
Common reason to use	Sensitive-data filtering, strict target constraints, specialized transformation engines	Scalable warehouse/lakehouse compute, raw-data retention, SQL-based transformations
Trade-off	Can add processing infrastructure and reduce raw-data availability	May load unnecessary or sensitive data unless ingestion controls are designed carefully

Choose ETL when: data must be masked or filtered before reaching the target, transformations require a specialized engine, or the destination has limited transformation capability.

Choose ELT when: the warehouse or lakehouse can efficiently transform data, raw data is useful for replay and audit, and you want transformations version-controlled separately from ingestion.

Interview tip: ETL and ELT are not mutually exclusive across an entire platform. You might mask sensitive fields before loading, then use ELT for downstream modeling.

2. How would you design an incremental ingestion pipeline?

Incremental ingestion processes only new or changed data since a previous successful checkpoint, rather than reloading the entire source every run.

Design approach:

1. Select a change mechanism: CDC/change logs when available; otherwise use a reliable update timestamp, increasing sequence, or source-specific watermark.
2. Capture a bounded range: Read from the last committed checkpoint to a fixed upper bound for the current run.
3. Land the extracted data: Store raw batches with source, ingestion time, run ID, and relevant offsets or watermarks.
4. Validate and apply changes: Deduplicate, validate schema, and merge inserts/updates; process deletes if the source provides deletion events.
5. Commit the checkpoint: Advance it only after the target changes and required validations have succeeded.
6. Monitor freshness: Track source lag, rows read/written, rejected records, duration, and the latest successfully processed timestamp or offset.

last_checkpoint = load_checkpoint(source) upper_bound = capture_current_upper_bound(source) batch = extract( source, start_exclusive=last_checkpoint, end_inclusive=upper_bound ) write_raw_batch(batch, run_id) validate(batch) apply_idempotent_merge(batch) run_quality_checks(batch) commit_checkpoint(source, upper_bound)

Watch out: A timestamp alone can miss records when timestamps are duplicated, have low precision, arrive late, or can be updated retroactively. Use a tie-breaker, overlap window plus deduplication, or CDC where appropriate.

3. How do you prevent duplicate records when a job is retried?

Design the pipeline to be idempotent: running the same logical batch more than once should produce the same target state as running it once.

• Use stable business keys: Identify the logical record using a primary key or a documented composite key.
• Deduplicate the batch: If multiple events exist for one key, select the correct version using source sequence, event time, or CDC ordering.
• Use MERGE/upsert: Insert unseen keys and update existing keys according to deterministic rules.
• Track batch identity: Record run IDs, source offsets, or batch IDs to detect already-processed work.
• Coordinate checkpointing: Avoid advancing the source checkpoint before target changes are durable and validated.
• Handle concurrent runs: Use locking, orchestration concurrency limits, transactional writes, or conflict handling as supported by the platform.

Illustrative SQL

MERGE INTO target_orders AS t USING staged_orders AS s ON t.order_id = s.order_id WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at) VALUES (s.order_id, s.status, s.amount, s.updated_at);

The exact syntax varies by database. Before merging, ensure the staging source has at most one chosen row per target key; otherwise, some engines reject the merge or produce ambiguous results.

Key distinction: Retries are expected. Correctness should not depend on a scheduler guaranteeing that a task runs exactly once.

4. What happens if a pipeline fails halfway through processing?

The outcome depends on the pipeline's commit boundaries and storage semantics. A failure may leave a partial batch, duplicate output on retry, or an inconsistent checkpoint if the workflow is not designed for recovery.

Design for safe recovery:

1. Split work into observable stages with explicit input and output boundaries.
2. Write to a temporary location or staging table, then publish atomically where supported.
3. Make each stage idempotent and associate output with a run or batch ID.
4. Commit checkpoints only after the relevant data is durable and validated.
5. Retry transient errors with bounded exponential backoff; do not endlessly retry deterministic data or schema errors.
6. Send malformed records to a quarantine or dead-letter location with enough context to diagnose them.
7. Alert on exhausted retries, stale data, and failed quality checks; document a replay or rollback procedure.

Example: If a batch is loaded to a staging table but fails validation, leave the production target unchanged. After fixing the issue, rerun validation and publish the batch. If the target merge succeeded but the checkpoint update failed, an idempotent retry should safely recognize and reapply the same batch.

Interview tip: Describe the exact recovery boundary: what is committed, what is retried, how partial writes are detected, and how the pipeline knows which data is safe to publish.

5. How would you backfill six months of historical data?

A backfill reprocesses historical data to repair gaps, apply corrected logic, or populate a new target. Treat it as a controlled workload, not simply a normal job with a much larger date range.

1. Define the scope: Confirm the six-month date range, affected tables, expected volume, and business reason.
2. Check source availability: Verify retention, historical snapshots, deletion history, and whether source records can be reconstructed.
3. Use bounded slices: Process by day, week, partition, or source shard so failures can be retried without restarting everything.
4. Isolate backfill resources: Rate-limit extraction and compute to avoid starving production pipelines or source systems.
5. Make writes idempotent: Use partition replacement, deterministic merges, or versioned outputs rather than blind appends.
6. Validate each slice: Check counts, key uniqueness, aggregate totals, and quality rules before marking it complete.
7. Track progress: Maintain a manifest of date ranges, run IDs, status, counts, and error details.
8. Reconcile and publish: Compare the completed history to the source, then switch consumers to the corrected data if a shadow target was used.

for date_range in six_month_window.chunk_by_week(): batch = extract_historical(date_range) write_to_staging(batch, date_range) validate_schema_and_quality(date_range) merge_or_replace_target_slice(date_range) reconcile_counts_and_totals(date_range) mark_slice_complete(date_range)

Important: Coordinate backfills with normal ingestion. If both write the same keys or partitions, define precedence rules and prevent overlapping writes. Consider a shadow table and controlled cutover for high-risk corrections.

6. How do you handle schema changes in source systems?

Schema evolution should be detected early, classified by compatibility, and handled through explicit policies rather than allowing unexpected changes to silently break downstream consumers.

Change	Typical response
New optional field	Often compatible if downstream readers tolerate it; validate and document it.
Field removed or renamed	Usually breaking; coordinate versioning, migration, and consumer updates.
Type change	Assess whether safe conversion is possible; quarantine invalid values and avoid silent truncation.
Meaning or unit changes	Treat as a semantic change even if the physical type stays the same.

Recommended controls:

• Capture and compare schemas at ingestion; store schema versions with raw batches.
• Define compatibility rules for adding, removing, renaming, and changing fields.
• Use data contracts and CI tests to identify breaking changes before production rollout when possible.
• Keep raw data or source events replayable so transformations can be rerun after model changes.
• Alert owners, document the impact, and use a migration window or versioned interface for breaking changes.

Good answer: “I distinguish physical schema changes from semantic changes. Adding a nullable field may be compatible, while changing a field's meaning can break reports even when its data type is unchanged.”

7. How would you reconcile source and target record counts?

Record-count reconciliation compares the expected source population with what was loaded, while recognizing that filters, deduplication, late data, and transformations can legitimately change the counts.

Approach:

1. Define the comparison scope: same time window, source snapshot, partition, and deletion semantics.
2. Record source counts and extraction metadata for each batch or partition.
3. Compare extracted, accepted, rejected, deduplicated, inserted, updated, and deleted record counts.
4. Compare stronger checks where appropriate: distinct business keys, null counts, min/max timestamps, sums, and grouped totals.
5. Account for expected transformations and document the reconciliation equation.
6. Alert when differences exceed agreed tolerances and retain evidence for debugging or audit.

source_count = extracted_count + source_read_errors extracted_count = accepted_count + rejected_count accepted_count = inserted_count + updated_count + unchanged_count - duplicate_rows_removed

These equations are illustrative; define the exact terms for your load semantics, especially for deletes, updates, and deduplication. In a CDC pipeline, source event counts will not generally equal the number of rows in the target table.

Do not rely on counts alone: A missing record and an unexpected duplicate can cancel each other out. Combine counts with key-level checks, aggregate reconciliation, and sampled or partition-level comparisons.

8. How would you design a reusable ingestion framework for multiple sources?

Build a common framework for shared ingestion concerns, while keeping source-specific behavior configurable or implemented through well-defined connectors. Avoid a one-size-fits-all abstraction that hides important source semantics.

Core components:

• Source connectors: Database CDC, REST APIs, files, object storage, event streams, and SaaS systems.
• Configuration and metadata: Source identifier, credentials reference, extraction mode, schema, keys, watermark, schedule, target, and SLA.
• Execution engine: Shared orchestration, bounded retries, rate limiting, concurrency controls, and parameterized processing.
• Raw landing: Immutable or versioned batches with run metadata, source offsets, and schema versions where appropriate.
• Validation and loading: Schema checks, data quality rules, deduplication, idempotent merge, and checkpoint management.
• Observability: Standard logs, metrics, lineage, freshness, row counts, error categories, and alerts.
• Security: Secret-manager integration, least-privilege identities, encryption, and sensitive-data handling.
• Developer experience: Templates, local tests, CI/CD, documentation, onboarding examples, and versioned configuration.

Illustrative source configuration

source_name: crm_orders connector: postgres_cdc load_mode: incremental primary_key: - order_id checkpoint: type: source_lsn target: dataset: raw_crm table: orders quality: required_columns: [order_id, updated_at] unique_key: [order_id] reliability: max_retries: 4 quarantine_invalid_rows: true observability: freshness_sla_minutes: 60

How to keep it reusable: Standardize the lifecycle—discover, extract, land, validate, apply, reconcile, commit, and report—but let connectors define source-specific pagination, CDC offsets, API rate limits, and deletion semantics.

Senior-level insight: Measure framework success by onboarding time, failure recovery time, operational consistency, and the percentage of sources that use standard paths—not by the number of abstractions or configuration fields.

Quick Revision Table

Question	Core takeaway
ETL vs. ELT	Choose based on transformation location, security, compute, and raw-data needs.
Incremental ingestion	Use reliable change tracking, bounded batches, and post-success checkpoints.
Duplicate prevention	Design idempotent writes using stable keys, deduplication, and merge/upsert.
Partial failures	Use explicit commit boundaries, safe retries, quarantine, and replay procedures.
Historical backfills	Chunk work, isolate resources, track progress, and reconcile each slice.
Schema evolution	Detect, classify, validate, version, and communicate changes.
Reconciliation	Combine counts with key-level and aggregate checks.
Reusable framework	Standardize the lifecycle while preserving connector-specific behavior.

Scenario-Based Practice Questions

Try answering each scenario out loud before checking the suggested approach.

Scenario 1: The job loaded data successfully, but failed before updating its checkpoint. What do you do?

Retry the same bounded batch. The target write should be idempotent, using a deterministic merge or batch identity. Validate whether the target data is already present, then commit the checkpoint only after confirming the batch is durable and correct. Add monitoring for checkpoint lag and recovery attempts.

Scenario 2: Your API source starts rate-limiting the ingestion job during a backfill.

Use bounded concurrency, exponential backoff with jitter, and respect server retry hints where available. Persist progress by page or time slice, separate backfill capacity from production ingestion, and resume from the last safely recorded checkpoint rather than restarting the entire load.

Scenario 3: Source and target counts match, but business users report missing orders.

Compare distinct business keys, not only total counts. Check date boundaries, timezone conversions, filters, duplicate keys, late-arriving data, deleted records, and rejected rows. Reconcile at the relevant partition and inspect missing keys end-to-end through extraction, raw landing, transformations, and the serving model.

Scenario 4: Different sources have different keys, delete semantics, and rate limits. Can one framework handle them?

Yes, if the framework standardizes shared lifecycle and observability while connectors declare their semantics. Make keys, extraction mode, checkpoint type, pagination, delete handling, and rate limits configurable or connector-specific. Avoid forcing every source into the same incremental strategy.

Chapter Summary

• Choose ETL or ELT based on workload, security, and target-platform capabilities.
• Use reliable checkpoints and bounded extraction windows for incremental ingestion.
• Assume retries will happen; make writes idempotent and checkpoint commits safe.
• Design explicit recovery boundaries for partial failures.
• Run backfills in controlled slices with resource limits and reconciliation.
• Treat schema evolution as a governed process, not just a parsing concern.
• Reconcile keys and aggregates as well as row counts.
• Build reusable frameworks around shared lifecycle stages while allowing source-specific behavior.

Practice Challenge

Design an incremental ingestion pipeline that reads orders from a transactional database, lands raw data in cloud storage, and loads curated tables into a warehouse. Explain how you would handle retries, duplicate events, late updates, schema changes, six-month backfills, and source-to-target reconciliation.

Suggested Blogger post title: Top 8 Data Ingestion and Pipeline Reliability Interview Questions | Practical Examples