Articles

Airbyte Interview Questions: Incremental Sync, Cursors, and Deduplication

Prepare for Airbyte interview questions with cursor and key examples, replay and delete scenarios, checkpoint reasoning, and connector-specific checks.

An Airbyte sync succeeds, the destination has one row per order, and yesterday's corrected revenue is still wrong. Deduplication can be working perfectly while the source connector never extracts the correction.

For Airbyte interview questions, separate two responsibilities: the cursor determines which changes can be discovered, while the primary key identifies which entity a delivered record should update. Explain replay and deletes next, then identify the acknowledged checkpoint from which recovery can resume.

Evidence boundary: Official Airbyte documentation was checked September 21, 2026. Connector behavior depends on the source, destination, replication method, and installed versions. The examples below are original reasoning exercises, not candidate reports or verified employer questions. A small Python model was run locally; no deployed Airbyte connection was tested.

What is the difference between a cursor and a primary key?

Official fact: Airbyte describes a cursor as the value used to identify new or updated records. A primary key identifies an entity, potentially through multiple fields. In incremental append-plus-deduped mode, the final table keeps one row per primary key, choosing the latest data using the cursor. Incremental append and deduplication

Original example: An order has order_id = 42, updated_at = 10:00, and amount = 100. Its identity remains 42 when the amount changes. Its modification timestamp should advance if a timestamp-based connector must discover that change.

Choosing created_at because it is populated is insufficient for mutable orders: creation time may never change after insertion. Choosing an amount as the primary key is equally problematic because unrelated orders can share an amount and one order's amount can change.

For a multitenant application, ask whether order_id is globally unique. If tenant A and tenant B both issue order 42, the intended identity may be (tenant_id, order_id). A syntactically valid key can still collapse unrelated business entities.

A precise interview answer is: “I need a stable identity and a discovery mechanism that covers every relevant mutation. I would test them independently.”

Does incremental append mean exactly-once delivery?

Official fact: Incremental append adds newly extracted versions rather than replacing existing rows. Airbyte documents at-least-once replication and permits records to be resent, including at ambiguous cursor boundaries. Append destinations can therefore contain repeated versions. Incremental append

Interview inference: A duplicate in an append table is not automatically evidence of a failed connector. First distinguish an expected replay from a new source version, then check the destination contract.

Suppose the same order version is delivered twice after a retry. An append table may hold both deliveries; a correctly configured final deduplicated table should converge to one entity row. That does not make every downstream action exactly once. A consumer that emails customers for each arrival can still send two messages unless it has its own stable event identity and effect-control mechanism.

Also distinguish a current-state table from a complete audit history. Polling a row at noon cannot reconstruct three intermediate changes that occurred and disappeared before noon. Keeping every delivered version is not the same as observing every source mutation.

Ask which guarantee the business needs: current order state, every mutation, or one execution of a downstream effect. Those are different requirements, even when the same pipeline participates in all three.

Work through a source-change ledger before choosing a mode

The following is an original bounded exercise for timestamp polling. Assume an inclusive extraction predicate, updated_at >= saved_cursor, and a stable order key. These assumptions define the exercise; they do not assert that every Airbyte connector implements identical filtering.

Initial destination rows are order 42 at cursor 10 with amount 100, and order 43 at cursor 11 with amount 200. The saved cursor is 11.

Source event	Expected reasoning under these assumptions
Insert order 44 at cursor 12	It is eligible for extraction and adds a new entity.
Resend order 43 at cursor 11	The inclusive boundary can replay it; final identity must remain unique.
Replay the entire delivered batch	An append table grows; a final current-state table should converge.
Change order 42 to amount 150 but leave cursor 10	It falls behind the boundary and is missed. Deduplication cannot repair it.
Hard-delete order 42 from the source	Polling surviving rows emits no deletion signal for that missing row.
Deliver two different values for one key at the same cursor	The ordering is ambiguous unless the connector/destination defines a further rule.

Local model result: A Python fixture extracted orders 43 and 44, replayed that batch, and still produced three unique keys. Order 42 incorrectly remained at amount 100 instead of 150. This verifies the counterexample's logic, not Airbyte's production implementation.

The last row deserves attention. Do not invent a universal “last record wins” rule based on arrival order. Obtain the exact destination's tie-breaking behavior and decide whether it satisfies the business definition of latest. If two states truly share an indistinguishable ordering value, downstream SQL cannot infer missing business order from nothing.

How do equal timestamps, late data, and lookback windows interact?

Official fact: Airbyte's connector-builder incremental configuration supports a lookback window that moves the next starting cutoff backward. Its documentation presents this as a trade-off between rereading data and catching updates to previously observed records. Connector-builder incremental sync

Original exercise: An API exposes only creation time. Orders are usually corrected within 48 hours, so a three-day lookback is proposed. It may catch typical corrections, but a correction on day ten remains outside that policy. The business must either accept that bounded risk or fund a separate reconciliation and repair path.

Write the boundary precisely. Is the timestamp UTC? What precision survives serialization? Can a source return a record at exactly the saved boundary? Does pagination remain stable while new records arrive? A cursor comparison can be correct while unstable page traversal still skips data.

A useful verification set includes two records sharing a timestamp, an update just before the cutoff, and a delayed record whose business date is old but whose modification time is new. These cases test different mechanisms; calling all of them “late data” hides the distinction.

For a custom connector, test the actual API's sorting and filtering contract. Increasing the lookback window cannot fix an API that never exposes the updated object, nor can it guarantee recovery once source history has expired.

Why does a checkpoint need destination acknowledgment?

Official fact: The Airbyte protocol separates source-emitted state from state acknowledged by the destination. A state becomes a safe resume point only after the destination confirms the corresponding records were committed. Source state contents are connector-owned; the platform treats them as opaque. Airbyte protocol: state and checkpointing

Original failure timeline: The source emits records A and B, then checkpoint S. The destination commits A but crashes before durably handling B and acknowledging S. Restarting from S solely because the source emitted it would risk losing B.

Conversely, the destination can commit records before the acknowledgment reaches the coordinator. Recovery from an older acknowledged checkpoint may replay those records. This is where replay-safe destination handling matters.

A strong answer names the durable boundary instead of saying “the job remembers where it stopped.” Ask which checkpoint was acknowledged, which stream it covers, and what data remains available for replay. Do not manually advance a cursor to silence a retry without accounting for the skipped range.

For an incident, preserve the failing job's connector versions and relevant state before changing configuration. Compare the last confirmed checkpoint with destination evidence and a bounded source sample. A green status on the next job alone does not establish that the previous gap disappeared.

Can Airbyte incremental sync capture hard deletes?

Official fact: Airbyte's CDC documentation distinguishes log-based change capture from ordinary incremental polling. Supported CDC sources can emit deletion information. A source that does not observe a deletion cannot pass that information downstream merely because deduplication is enabled. Change data capture

Connector-specific fact: The current Postgres source documentation lists CDC, xmin, and a user-defined-cursor method. CDC is recommended when deletion records are required; xmin tracks inserts and updates without choosing an application cursor. These methods are not interchangeable contracts. Postgres connector

Interview application: Start with the deletion requirement. If the warehouse must remove deleted customers, identify the actual signal: a CDC delete, a soft-delete field that advances the cursor, or a complete authoritative snapshot used for reconciliation.

Then test the destination's selected mode. Keeping a tombstone as an appended event and removing a row from a final current-state table serve different consumers. State which representation downstream models expect.

For a soft-delete design, a flag alone is insufficient if changing it leaves the extraction cursor behind the saved boundary. For snapshot comparison, the source listing must be complete before absence is treated as deletion. A partial API response or permission change should not silently erase valid warehouse entities.

Why must you check the destination version and loading path?

Official fact: Airbyte's typing-and-deduping documentation says that implementation is being phased out in favor of Direct Load. Direct Load removes the intermediate raw-JSON table and maintains a destination table per stream. Therefore, older diagrams showing a mandatory raw/history/final table layout are not universally current. Typing and deduping, Direct loading tables

Interview recommendation: Record the source connector version, replication method, destination connector version, stream key, and supported destination mode. Inspect the tables actually produced before designing downstream dependencies around an internal table name.

If a decimal or timestamp field changes type, inspect rejected records and destination metadata as well as row counts. A table can contain the expected number of keys while an important field has become null or changed meaning. Treat schema compatibility and historical value repair as separate questions.

For a version upgrade, compare the same bounded fixture before and after the change. Include equal-cursor values, missing fields, deletes, and a replay. The acceptance criterion should be the intended final data contract, not the presence of a familiar intermediate table.

How would you prove a sync is correct enough to release?

An original acceptance plan should test completeness, convergence, and meaning separately.

First, compare source and destination over a bounded cohort whose state is stable or captured at a known cutoff. Check missing and extra keys, not only total counts. Equal counts can hide one missing order and one unexpected order.

Next, replay the same delivered batch and compare the final current-state result. Verify that business fields do not regress when older versions arrive after newer ones. Test equal-cursor conflicts explicitly rather than relying on a lucky arrival order.

Finally, reconcile values: order totals, status transitions, null rates, and deletion outcomes. A duplicated amount and a missing amount can cancel in a total, so retain key-level mismatches for investigation. Choose tolerances from the data contract; do not invent a universal acceptable error percentage.

During recovery, distinguish records not extracted, records not committed, and records committed with an unexpected interpretation. Each failure points to a different repair. Rebuilding downstream models cannot restore absent source records; resyncing everything does not automatically fix a wrong entity key.

Practice these Airbyte interview questions through adjacent problems

Question	What to explain
Deduplicate and Order Batch and Streaming Logs	Distinguish event identity, replay, and ordering.
Merge Hundreds of Paginated Sorted Sources into One Deduplicated Record Stream	Preserve pagination boundaries and stable identity.
Deduplicate events and rank products with SQL	Define the winner before writing a window function.
Reason About Duplicate Data and Scaling in Spark	Separate expected repeated input from incorrect output.
Defend a Data Pipeline Architecture and Its Trade-offs	State the correctness contract and recovery evidence.

Choose one failure from the ledger and explain its detection and repair aloud. Continue with Data Engineer practice, keeping extraction completeness and destination convergence separate in every answer.