Skip to content

Data Warehouse & ETL Interview Questions: CDC Replay and Backfill Correctness

Fourteen interview questions on change data capture, incremental loads, deletes, replay, and backfills—with runnable examples and production-grade reasoning.

Ekanatha Reddy Urivakili16 min3,259 words

A warehouse pipeline is easy to explain when every event arrives once, in order, and before the dashboard refreshes. Interviews become interesting when those assumptions disappear.

This study guide focuses on change data capture correctness: how incremental ETL and ELT pipelines handle replay, deletes, inconsistent snapshots, schema changes, and historical backfills. These are the questions that distinguish someone who has configured a connector from someone who can operate a trustworthy warehouse.

The examples use PostgreSQL 18 SQL and Python 3.14. The Python example uses SQLite from the standard library to make transaction and recovery behavior locally reproducible; it is not a recommendation to use SQLite as an enterprise warehouse.

Across all fourteen questions, keep four boundaries explicit:

  • Source boundary: which committed changes belong to the extract?
  • Ordering boundary: which changes can be compared or reordered?
  • Transaction boundary: which writes and checkpoints succeed together?
  • Publication boundary: when can downstream readers observe the result?

A strong interview answer explains the normal path, the failure window, and the recovery procedure. Naming a tool is not a substitute for any of those.

Fundamentals

1. How do ETL, ELT, and CDC relate to one another?

What the interviewer is really testing: Whether you distinguish transformation placement from change detection.

Model answer:

ETL transforms data before loading it into the analytical destination. ELT loads data first and performs transformations using the destination’s compute capabilities.

CDC answers a different question: how do we identify source changes? Log-based CDC reads database change records, while polling-based approaches inspect source tables or change-tracking structures.

A CDC pipeline can support either ETL or ELT. For example, it might:

  • Read source changes.
  • Preserve source metadata in a raw landing layer.
  • Apply changes to current-state tables.
  • Transform those tables into dimensional models.

The landing layer should retain enough information to explain and reproduce downstream results, subject to privacy and retention constraints.

Avoid claiming that CDC automatically produces analytical history. A stream of updates becomes usable history only after you define keys, temporal semantics, deletion behavior, and retention.

Follow-up: When would you transform before landing rather than afterward?
Hint: Consider sensitive fields, contractual restrictions, bandwidth, and whether discarded information will ever be needed for replay.

2. Why can a full snapshot followed by CDC still lose data?

What the interviewer is really testing: Whether you understand the handoff between initial extraction and ongoing changes.

Model answer:

A naive implementation copies tables and starts reading changes afterward. Any transaction committed between those operations can disappear from the destination.

Starting CDC first is not sufficient by itself either: replaying an older snapshot row after a newer change can overwrite the newer state.

A correct bootstrap needs a coordinated snapshot and change-stream position. The exact mechanism depends on the database and connector. It must establish which source changes are represented by the snapshot and which must subsequently be applied.

sequenceDiagram
    participant S as Source
    participant C as Capture
    participant W as Warehouse
    C->>S: Establish coordinated snapshot boundary
    S-->>C: Snapshot and stream position
    C->>W: Load snapshot rows
    C->>S: Read changes from agreed boundary
    C->>W: Apply changes using source ordering

The snapshot-to-stream handoff must have a defined boundary, even when delivery intentionally overlaps.

Long-running snapshots also have operational consequences. They can increase retained database history, consume source resources, or outlast available change-log retention.

Follow-up: Is overlap between the snapshot and CDC stream necessarily wrong?
Hint: Overlap is safe when duplicates and ordering are handled correctly; an unexplained gap is not.

3. Which identifiers and timestamps should a change event carry?

What the interviewer is really testing: Whether you separate entity identity, event identity, ordering, and business time.

Model answer:

A useful event envelope usually carries several distinct concepts:

FieldPurposeImportant limitation
Source and table identifierNamespaces the recordTable names can change
Business or source primary keyIdentifies the entityKeys can be updated or reused
Event identifierSupports event-level deduplicationMust survive retries
Source position and tie-breakerOrders changes within a defined domainNot necessarily globally comparable
Transaction identifierGroups related source writesDoes not itself define row order
OperationDistinguishes insert, update, deleteDelete payloads may contain only keys
Source commit timeSupports freshness analysisClock accuracy still matters
Ingestion timeMeasures arrivalDoes not establish source ordering
Business effective timeSupports temporal business meaningMay precede arrival by days

The ordering domain matters as much as the position. A numeric position from one database cannot generally be compared with a position from another.

Some systems also need a connector epoch or generation identifier to distinguish positions across resets or source migrations.

Follow-up: Can you use ingestion time to choose the latest row?
Hint: A delayed retry can arrive last while representing an older source change.

4. When is a timestamp-based incremental extract sufficient?

What the interviewer is really testing: Whether you can choose a simpler mechanism without overlooking its failure modes.

Model answer:

Timestamp polling can be appropriate when the source guarantees that every relevant mutation updates a reliable marker, deletes are represented explicitly, and extraction cannot miss transactions because of commit timing.

Those are stronger conditions than “the table has an updated_at column.”

ApproachStrengthMain correctness riskSuitable context
Timestamp pollingSimple and widely availableMissed deletes, commit races, unreliable clocksControlled sources with documented guarantees
Snapshot comparisonDoes not require a change logCost, inconsistent reads, coarse change timingSmall datasets or periodic reconciliation
Log-based CDCCaptures committed changes with source metadataRetention, bootstrap, connector semanticsSustained incremental replication

An overlap window reduces some polling risks but does not prove completeness. A transaction can assign its timestamp, remain open longer than the overlap, and commit after the extractor has advanced.

A timestamp-plus-key cursor handles equal timestamps during pagination. It does not repair unreliable timestamp semantics.

Follow-up: How would you validate a polling pipeline’s completeness?
Hint: Use independent reconciliation against a consistent source boundary, not just successful job status.

Intermediate

5. What makes an incremental load idempotent?

What the interviewer is really testing: Whether retries preserve state instead of duplicating or reversing changes.

Model answer:

An operation is idempotent when applying it repeatedly produces the same intended result as applying it once.

For a current-state table, a common pattern is:

  • Enforce one destination row per source key.
  • Store the last applied source version.
  • Apply only a strictly newer version.
  • Retain deletion state when older events could return.

This minimal example is runnable in a PostgreSQL session:

CREATE TEMP TABLE customer_current (
    customer_id bigint PRIMARY KEY,
    email text,
    source_version bigint NOT NULL,
    is_deleted boolean NOT NULL
);

-- Apply an insert.
INSERT INTO customer_current AS current_row
    (customer_id, email, source_version, is_deleted)
VALUES
    (42, 'first@example.test', 10, false)
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
    source_version = EXCLUDED.source_version,
    is_deleted = EXCLUDED.is_deleted
WHERE EXCLUDED.source_version > current_row.source_version;

-- Apply a newer delete, retaining its ordering information.
INSERT INTO customer_current AS current_row
    (customer_id, email, source_version, is_deleted)
VALUES
    (42, NULL, 12, true)
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
    source_version = EXCLUDED.source_version,
    is_deleted = EXCLUDED.is_deleted
WHERE EXCLUDED.source_version > current_row.source_version;

-- Replay an older update: it must not resurrect the customer.
INSERT INTO customer_current AS current_row
    (customer_id, email, source_version, is_deleted)
VALUES
    (42, 'stale@example.test', 11, false)
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
    source_version = EXCLUDED.source_version,
    is_deleted = EXCLUDED.is_deleted
WHERE EXCLUDED.source_version > current_row.source_version;

SELECT * FROM customer_current;

Notice that version 12 remains a tombstone after version 11 is replayed.

The scalar version is an example assumption. Real connectors may require a composite ordering key. Equal versions with different payloads should trigger investigation rather than silently choosing a winner.

Follow-up: Is an idempotent row upsert enough for an aggregate table?
Hint: Retrying total = total + amount is different from replacing a row with a known versioned value.

6. Why are deletes harder than inserts and updates?

What the interviewer is really testing: Whether you understand resurrection, privacy, and downstream propagation.

Model answer:

A delete removes the very row that might otherwise carry its last applied version.

If the destination physically removes the row immediately, an old replay can look like a new insert. A tombstone preserves the key and deletion version so the pipeline can reject stale changes.

However, a source-row tombstone is not the same as a privacy deletion policy. Raw event storage, historical dimensions, aggregates, backups, and derived extracts may all need separate treatment.

A defensible answer distinguishes:

  • Replication delete: reflect source absence.
  • Business cancellation: retain an entity with a changed status.
  • Privacy erasure: remove or irreversibly anonymize protected information.
  • Retention expiry: dispose of data according to policy.

Tombstones should generally avoid retaining unnecessary personal data. Their lifetime must cover the replay and restore scenarios the system actually supports.

Follow-up: When can you safely remove tombstones?
Hint: Only after proving older events cannot return through retained logs, archives, backfills, or restored checkpoints.

7. Where should the checkpoint be committed?

What the interviewer is really testing: Whether you can identify the crash window between data writes and progress tracking.

Model answer:

Committing the checkpoint before the destination write risks data loss. Committing it afterward can cause replay, which is acceptable only if the destination handles replay safely.

When the destination supports it, commit the applied changes and checkpoint in the same transaction. Then acknowledge upstream delivery. If the acknowledgement fails, the batch can return without changing the result.

This runnable example demonstrates atomic recovery using a local database. Its synthetic delivery offsets are contiguous and single-stream; actual connector positions may be opaque and require per-partition tracking.

Save this as replay_demo.py and run python replay_demo.py:

from dataclasses import dataclass
from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory


@dataclass(frozen=True)
class Event:
    offset: int
    key: str
    value: str | None
    deleted: bool = False


def connect(path: Path) -> sqlite3.Connection:
    db = sqlite3.connect(path, isolation_level=None)
    db.executescript("""
        CREATE TABLE IF NOT EXISTS checkpoint (
            stream TEXT PRIMARY KEY,
            position INTEGER NOT NULL
        );
        INSERT OR IGNORE INTO checkpoint VALUES ('customers', 0);

        CREATE TABLE IF NOT EXISTS current_state (
            entity_key TEXT PRIMARY KEY,
            value TEXT,
            source_version INTEGER NOT NULL,
            deleted INTEGER NOT NULL
        );
    """)
    return db


def apply_batch(
    db: sqlite3.Connection,
    events: list[Event],
    fail_before_commit: bool = False,
) -> int:
    db.execute("BEGIN IMMEDIATE")
    try:
        position = db.execute(
            "SELECT position FROM checkpoint WHERE stream = 'customers'"
        ).fetchone()[0]

        # Already-committed delivery offsets are safe to replay.
        for event in events:
            if event.offset <= position:
                continue

            # This check belongs to this demo's contiguous-offset contract.
            if event.offset != position + 1:
                raise ValueError("Missing or out-of-order delivery offset")

            db.execute("""
                INSERT INTO current_state
                    (entity_key, value, source_version, deleted)
                VALUES (?, ?, ?, ?)
                ON CONFLICT(entity_key) DO UPDATE SET
                    value = excluded.value,
                    source_version = excluded.source_version,
                    deleted = excluded.deleted
                WHERE excluded.source_version >
                      current_state.source_version
            """, (
                event.key,
                None if event.deleted else event.value,
                event.offset,
                int(event.deleted),
            ))
            position = event.offset

        db.execute(
            "UPDATE checkpoint SET position = ? WHERE stream = 'customers'",
            (position,),
        )

        if fail_before_commit:
            raise RuntimeError("Simulated process failure")

        db.commit()
        return position
    except BaseException:
        db.rollback()
        raise


def main() -> None:
    events = [
        Event(1, "customer-a", "first"),
        Event(2, "customer-b", "second"),
        Event(3, "customer-a", None, deleted=True),
    ]

    with TemporaryDirectory() as directory:
        path = Path(directory) / "warehouse.db"
        db = connect(path)

        try:
            apply_batch(db, events, fail_before_commit=True)
        except RuntimeError:
            pass

        assert db.execute(
            "SELECT COUNT(*) FROM current_state"
        ).fetchone()[0] == 0

        assert apply_batch(db, events) == 3
        db.close()

        # Restart and redeliver the entire batch.
        db = connect(path)
        assert apply_batch(db, events) == 3
        rows = db.execute("""
            SELECT entity_key, value, source_version, deleted
            FROM current_state
            ORDER BY entity_key
        """).fetchall()
        assert rows == [
            ("customer-a", None, 3, 1),
            ("customer-b", "second", 2, 0),
        ]
        print(rows)
        db.close()


if __name__ == "__main__":
    main()

Notice that both the simulated failure and restart preserve the relationship between destination state and checkpoint.

Follow-up: What changes when data and checkpoints live in different systems?
Hint: Expect replay; use durable manifests or idempotent application rather than assuming a distributed atomic commit.

8. How should late-arriving changes affect a Type 2 dimension?

What the interviewer is really testing: Whether you distinguish source ordering from historical business validity.

Model answer:

A Type 2 dimension preserves successive versions of an entity, usually with a surrogate key and validity interval.

An event arriving today might describe a business change effective last week. Appending it with today’s timestamp produces ingestion history, not necessarily correct business history.

First clarify the required semantics:

  • System time: when the warehouse learned the information.
  • Business valid time: when the information was effective.
  • Bitemporal history: both perspectives.

For business-valid history, a late correction may split an existing interval and require fact-to-dimension relationships to be reconsidered. Use half-open intervals such as [valid_from, valid_to) to make boundaries unambiguous.

If the source provides no business-effective timestamp, do not invent one. Explain that source commit time supports a different historical interpretation.

Follow-up: Should every late dimension change trigger a full fact-table rebuild?
Hint: Identify affected keys and time intervals, then evaluate targeted repair and publication cost.

9. How do you handle schema changes without silently corrupting the warehouse?

What the interviewer is really testing: Whether schema evolution includes semantic contracts, not just parser compatibility.

Model answer:

Classify changes by their effect on both ingestion and consumers.

Adding a nullable source column may be technically compatible, while changing a monetary field from major units to minor units is semantically breaking even if its database type stays unchanged.

A practical policy includes:

  • Versioned source-to-target mappings.
  • Explicit handling of unknown fields.
  • A quarantine path with retained original payloads where permitted.
  • Compatibility checks before publishing transformed models.
  • A backfill or migration plan for changed historical meaning.

Dropping malformed records and advancing the checkpoint can create permanent analytical gaps. If the business permits partial processing, record exactly what was excluded and expose incompleteness to consumers.

“Fail open” and “fail closed” should be deliberate choices, not connector defaults nobody reviewed.

Follow-up: What should happen when a previously nullable field becomes required?
Hint: Separate future-write validation from repairing historical rows and migrating downstream contracts.

Senior/System-Design Level

10. How do you run a historical backfill while CDC remains live?

What the interviewer is really testing: Whether you can prevent older bulk data from overwriting newer incremental state.

Model answer:

Treat a backfill as a controlled data migration with its own boundary and publication plan.

For a full rebuild, a robust approach is:

  1. Establish a consistent source boundary.
  2. Load the snapshot into shadow tables.
  3. Retain or capture changes from that boundary.
  4. Apply the catch-up stream to the shadow tables.
  5. Validate state at a known applied boundary.
  6. Publish through a destination-supported atomic switch.

If switching multiple related objects is not atomic, use a publication manifest or versioned dataset reference so readers select a coherent generation.

Do not assign an invented “very large version” to backfill rows. That can block legitimate future CDC.

A chunked in-place backfill is possible, but it needs a documented collision protocol: snapshot rows and concurrent updates must have comparable provenance or a connector-supported reconciliation mechanism.

Follow-up: How do you stop two overlapping backfills from interfering?
Hint: Use scoped ownership, generation identifiers, and a publication fence—not just different job names.

11. What does “exactly once” mean for a warehouse pipeline?

What the interviewer is really testing: Whether you can define a guarantee at a specific boundary.

Model answer:

Exactly-once delivery, exactly-once processing, and exactly-once observable results are different claims.

A connector may checkpoint its own progress correctly while the downstream transformation writes duplicates. A warehouse transaction may commit atomically while an external notification is sent twice.

For many warehouse pipelines, the practical objective is:

At-least-once delivery with deterministic, replay-safe application and controlled publication.

That requires more than a unique constraint:

  • Stable identities and ordering metadata.
  • Transactional or recoverable checkpoint handling.
  • Deterministic transformation inputs.
  • Explicit behavior for deletes and corrections.
  • Replay-safe aggregate maintenance.
  • A clear boundary for external side effects.

A transformation that consults today’s exchange rate during every replay is not reproducible unless that rate input is versioned.

Follow-up: Can a pipeline be exactly-once and still produce incorrect business results?
Hint: Processing guarantees do not validate grain, joins, units, or temporal business rules.

12. How would you size the pipeline and recover from a prolonged outage?

What the interviewer is really testing: Whether your architecture survives backlog growth and source-log expiry.

Model answer:

Start with explicit assumptions rather than vendor throughput claims.

Illustrative assumptions: peak arrival is 5,000 changes per second, sustained tested application capacity is 12,000 changes per second, and the outage lasts one hour.

The backlog is approximately 18 million changes. With continuing arrivals of 5,000 per second, net catch-up capacity is 7,000 per second, so recovery takes roughly 43 minutes—before allowing for contention, retries, or workload variation.

The important equation is:

catch-up time = backlog / (apply capacity - ongoing arrival rate)

If application capacity does not exceed arrival rate, the pipeline never catches up.

Source retention must cover detection, outage duration, recovery, and safety margin. Also check whether source transaction boundaries, hot keys, destination locks, or merge costs prevent the assumed throughput.

When source history expires, the recovery plan is usually rebootstrap or reconciliation—not retrying an invalid cursor forever.

Follow-up: Why might adding workers fail to improve catch-up time?
Hint: Look for ordering constraints, source throttling, shared destination bottlenecks, and partition skew.

13. How would you test correctness beyond checking row counts?

What the interviewer is really testing: Whether you can turn architectural promises into executable invariants.

Model answer:

Test sequences and failure boundaries, not just individual transformations.

Useful invariants include:

  • Replaying a batch leaves the same final state.
  • An older update cannot overwrite a newer delete.
  • A checkpoint never claims uncommitted destination work.
  • Current-state keys remain unique.
  • Historical validity intervals do not overlap unexpectedly.
  • Publication never exposes half of a dataset generation.

Inject failures before writes, after partial writes, before commit, after commit, and before acknowledgement. Exercise snapshot overlap, schema changes, key changes, and source-position resets.

Reconciliation should compare equivalent states at a known source boundary. Counts alone can match despite incorrect values. Partition-level checksums and business totals provide additional evidence, but canonicalization matters: nulls, decimals, timestamps, and text normalization must agree.

Independent checks are especially valuable. Reimplementing the same faulty mapping in the validator only confirms that both implementations share the bug.

Follow-up: What makes an effective property-based test here?
Hint: Generate duplicate and delayed deliveries, then compare the result with a simple reference model.

14. What observability proves the pipeline is healthy and correct?

What the interviewer is really testing: Whether you monitor consumer-visible outcomes rather than green scheduler runs.

Model answer:

Instrument extraction, landing, application, transformation, and publication separately. A successful extract says nothing about whether analysts can query fresh data.

Observability: logs, metrics, traces, and SLOs

Log structured batch outcomes with pipeline identity, source boundary, destination commit reference, schema version, counts, duration, and retry classification. Do not log raw personal data or use entity keys as metric labels.

An illustrative successful batch log:

{
  "event": "warehouse_batch_committed",
  "pipeline": "customer_cdc",
  "run_id": "run-847",
  "stream_partition": "customers-0",
  "checkpoint_before": "1200",
  "checkpoint_after": "1500",
  "destination_commit_id": "commit-291",
  "schema_version": "v3",
  "records_read": 300,
  "records_applied": 294,
  "records_ignored_as_stale": 6,
  "records_quarantined": 0,
  "duration_ms": 840,
  "outcome": "success"
}

Use RED for pipeline services:

  • Rate: incoming changes, batch attempts, committed changes.
  • Errors: extraction, application, validation, and publication failures.
  • Duration: processing latency and end-to-end publication delay.

Use USE for resources:

  • Utilization: CPU, memory, destination slots, connection pools.
  • Saturation: queued batches, blocked writers, retained-log headroom.
  • Errors: storage failures, throttling, connection exhaustion.

Track both backlog volume and age. Position differences are not automatically elapsed time. Heartbeats or source-position-to-commit-time mappings help detect a stalled pipeline when business traffic is quiet.

For tracing, use spans per batch and major stage rather than per row. Link asynchronous producer and consumer spans where propagation exists.

Install the stable OpenTelemetry Python packages:

python -m pip install opentelemetry-api opentelemetry-sdk

This standalone example demonstrates the tracing boundary; production workers should wrap their real apply transaction at the same point.

from opentelemetry import trace
from opentelemetry.sdk.resources import Resource
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import (
    BatchSpanProcessor,
    ConsoleSpanExporter,
)

provider = TracerProvider(
    resource=Resource.create({"service.name": "warehouse-cdc-worker"})
)
provider.add_span_processor(BatchSpanProcessor(ConsoleSpanExporter()))
trace.set_tracer_provider(provider)
tracer = trace.get_tracer("warehouse.cdc")


def process_batch(apply_transaction):
    with tracer.start_as_current_span("cdc.batch") as span:
        span.set_attribute("pipeline.name", "customer_cdc")
        span.set_attribute("source.partition", "customers-0")
        with tracer.start_as_current_span("warehouse.apply"):
            committed_rows = apply_transaction()
        span.set_attribute("pipeline.rows_committed", committed_rows)


try:
    process_batch(lambda: 300)
finally:
    provider.shutdown()

Notice that the application span ends after the transaction callback returns; exceptions are recorded by the span context manager.

For production export, follow the official OpenTelemetry Python exporter documentation.

The following are proposed targets, not measured results:

SLIExample SLOMeasurement
Published freshness99.5% of minute probes below five minutes over 30 daysHeartbeat commit to queryable publication
Delivery completeness99.9% of eligible source changes published within 15 minutes over 30 daysSource-boundary reconciliation
Publication validityEvery publication passes blocking invariantsManifest and validation records

Page on sustained freshness breaches, imminent source-log expiry, verified loss or corruption, and repeated publication failure that threatens the SLO. Route isolated retries and recoverable quarantines to nonpaging workflows unless they threaten correctness or contractual deadlines.

Follow-up: Why is “the job finished successfully” a weak SLI?
Hint: It measures an execution outcome, not fresh, complete, queryable data.

Common Mistakes Candidates Make

  • Claiming that MERGE guarantees correctness. It is a write mechanism; ordering, duplicate source matches, concurrency, and checkpoint semantics still need design.
  • Treating arrival order as source order. Retries and parallel delivery routinely invalidate that assumption.
  • Ignoring warehouse reader consistency. Correct individual tables can still expose an inconsistent cross-table publication.
  • Using timestamps without specifying their meaning. Business time, commit time, and ingestion time answer different questions.
  • Advancing past quarantined data without a repair contract. This converts an ingestion problem into a hidden completeness problem.
  • Assuming every delete permits permanent removal. Replay protection, privacy policy, and retention obligations must be reconciled explicitly.
  • Promising replay without retaining dependencies. Raw events alone cannot reproduce a transformation whose reference data and code versions are missing.
  • Giving capacity numbers without assumptions. State workload, event size, skew, transaction distribution, and tested destination behavior.

In an interview, a qualified answer is stronger than a universal claim. Say which guarantees depend on the source, connector, destination, and publication mechanism.

Quick-Revision Checklist

Before the interview, make sure you can explain:

  • Why CDC is independent of ETL versus ELT.
  • How snapshot extraction hands off to incremental capture without gaps.
  • The difference between entity identity, event identity, source order, and business time.
  • Why timestamp overlap does not guarantee completeness.
  • How a version-aware upsert handles stale replay.
  • Why deletes need durable ordering information.
  • Where the checkpoint commits and what happens after each possible crash.
  • How late corrections change Type 2 validity intervals.
  • Which schema changes are semantically breaking.
  • How shadow backfills catch up and publish coherently.
  • What “exactly once” means at the boundary you control.
  • How backlog, catch-up capacity, and source retention interact.
  • Which invariants and independent checks establish correctness.
  • Which freshness and completeness failures deserve a page.

The strongest recurring answer is simple: define the boundary, preserve enough provenance to recover, and demonstrate correctness under replay.


This article was generated with AI. #AIGenerated