Fivetran Interview Questions for Data Engineers: Soft Deletes, Resyncs, and Destination State

The Fivetran connection dashboard says the last sync succeeded. An analyst still sees a deleted order, another order seems to have two versions, and a revenue model changes halfway through a table re-sync. Which observation is wrong?

The useful answer is not “rerun the connector.” Follow one source key through destination state and a reader's query time. Fivetran's soft delete documentation is the official source for _fivetran_deleted, keyless updates, and re-sync behavior below. The order IDs and incident timeline here are original interview practice, not a candidate report or a prediction of a Fivetran hiring round. The same audit habit applies to Explain ETL schema changes and ensure integrity.

First establish the table contract

Ask which connector, source table, destination, sync mode, stable source primary key, and delete-capture capability are in scope. Fivetran's current sync-mode documentation describes soft delete and history mode, while product and connector settings can differ; do not infer a table's configuration from a generic sentence about the platform. Read the connection's schema and its connector feature table. Fivetran specifically notes that a connector may remain in soft delete mode without its “Captures Deletes” feature. In that situation, source deletions may not become _fivetran_deleted = TRUE in the destination. A filtered destination row is only as reliable as the connector's actual deletion detection.

Define the business view separately from raw replication: “current orders” might be WHERE _fivetran_deleted = FALSE, but only if the mode, connector, key, and sync completeness make that predicate meaningful. History mode has multiple row versions and _fivetran_active/validity columns instead. A consumer that assumes every destination table has both _fivetran_deleted and _fivetran_active is mixing modes. The Fivetran history mode reference explains the separate versioning model and migration effects.

This distinction matters in interviews because a copied SQL filter can hide a data-loss bug. If source key 17 disappeared but the connector does not capture deletes, querying WHERE NOT _fivetran_deleted can still count it. The correct next step is to establish the connector's supported behavior and reconcile source and destination keys; it is not to call the absent flag a fresh business order.

Question 1: trace a soft delete and a reappearance

Use an invented orders table with a stable source order_id. At time A, source keys 11, 12, and 13 exist. Key 12 is deleted at time B; it later reappears at time C. With delete capture working in soft delete mode, the destination's current representation can be traced as follows. This is a practice ledger, not a guarantee about an arbitrary connector's timing.

Moment	Source key 12	Destination key 12	Correct reader interpretation
A: initial sync	Present	_fivetran_deleted = FALSE	Count once
B: deletion captured	Absent	_fivetran_deleted = TRUE	Retained raw row, excluded from current set
C: key reappears and syncs	Present	_fivetran_deleted = FALSE	Current again after observed sync

The official soft delete page shows this flag transition. Ask candidates to explain why a raw COUNT(*) at B is not a current-order count, why a source delete may take time to appear, and why a record reappearance is different from two simultaneous active rows. Report the sync's observed completion time and the source state time when comparing counts. A statement such as “the source has two orders but destination has three rows” is incomplete without the active/deleted breakdown.

What if order_id changes in the source? That is not the same as an update to a non-key attribute. Depending on connector semantics, a changed key may look like one delete and one insert, or a different mutation path. Confirm the source's key contract and connector mapping instead of assuming business identity survived. If the metric groups by a customer, decide whether deleted orders should be retained for historical reporting or excluded from a current operational list; those are different downstream products.

Question 2: why can an update look like two destination rows?

For tables without a usable primary key, Fivetran documents a generated _fivetran_id path in soft delete mode: an updated source row can produce an insertion of a new version followed by marking the old version deleted. The docs explicitly warn that the old version's most recent _fivetran_synced can be later than the new version's timestamp. A query that selects the latest _fivetran_synced row per supposed business key can therefore choose the deleted, old payload.

Suppose a row's quantity changes from 3 to 2 to 1. A destination snapshot may retain versions 3 / deleted, 2 / deleted, and 1 / active. The task is not to apply MAX(_fivetran_synced) blindly. Establish a genuine source identity if one exists, understand the connector's keyless representation, filter to active rows where the documented mode permits it, and test the model against an update sequence. If multiple active rows share the purported business key, investigate whether that key is actually unique and whether a masking or row-access policy prevents Fivetran from matching primary keys. Fivetran's destination duplicate-data troubleshooting names that policy failure mode.

Do not silently “fix” duplicate rows with DISTINCT. Identical attributes can represent two legitimate source records; nonidentical attributes can represent one record's versions. A defensible survivor rule needs an identity contract, mode, and ordering evidence. In an interview, draw three columns—source identity, destination physical row, analytical current row—so the team stops treating them as synonymous.

Question 3: what does a table re-sync do to visible state?

Fivetran's official soft delete page describes a specific re-sync sequence: merge fetched snapshot rows, mark those arriving rows _fivetran_deleted = FALSE, then after each batch mark rows whose _fivetran_synced predates the re-sync start as deleted. Across multiple batches, completed batches and not-yet-processed rows can be mixed temporarily. The documentation recommends pausing downstream readers until the re-sync finishes. This means the connector can be doing its documented job while an analyst's intermediate query is a poor representation of a coherent source snapshot.

Try a concrete replay. At re-sync start, destination keys 11, 12, 13 look current, but the source now contains only 11 and 14. Batch one lands 11 and reactivates it; batch two later lands 14. During processing, a reader might see a mixture of refreshed, stale, and newly marked rows. At completion, the expected current-key set for this invented source is {11, 14} and the old missing keys should not remain active under the documented behavior. The exact intermediate row set depends on batch timing, so do not promise one deterministic halfway COUNT(*). This is a useful place to distinguish official product behavior from our illustrative inference.

A safe downstream contract might hold scheduled models during the re-sync, publish a staging snapshot only after completion and checks, or use a versioned table swap. The choice depends on freshness requirements and warehouse capabilities. Merely filtering _fivetran_deleted = FALSE during the run may still expose a mixed state; the flag addresses row status, not atomicity of a multi-batch refresh. The official re-sync instructions distinguish connection and table operations. State which scope you propose, the expected cost, and how you will know it finished.

Question 4: when is the destination trustworthy again?

Use a staged reconciliation rather than one success badge. First record the connector/table, re-sync trigger, start time, source extraction boundary, and destination schema. Next verify the operation reached a terminal successful state. Then compare source and destination current-key sets at comparable times, separating rows marked deleted and active. Finally run downstream metric tests and release readers. If the source continues changing, a single key-set comparison while capture is still catching up may show a legitimate lag; set a watermark or stop-the-world boundary that the source and destination can both explain.

Evidence	What it tests	What it cannot prove alone
Connector/table completed status	The managed operation ended	Every downstream model read a coherent snapshot
Source-versus-active-key comparison	Missing or extra current identities	Business measures and field values are correct
Field-level sample/hash reconciliation	Payload agreement on selected keys	Unchecked rows or connector delete support
Downstream count and money checks	Reader-visible impact	Root cause of a mismatch

For the invented {11, 14} case, verify both keys are active, missing keys are inactive under the documented soft-delete semantics, and values match the source boundary. If destination 12 remains active, ask whether the connector captures deletes, whether the re-sync completed, and whether source filtering or permissions changed. If key 14 is missing, inspect source extraction errors and schema selection before retrying. If 11 exists twice, examine physical keys, masking policies, and the table's mode. Each observation suggests a different repair.

Question 5: how would you respond to a wrong dashboard?

Freeze the downstream claim first: note which dashboard refresh consumed which destination version. Then classify the failure: wrong source extraction, missed delete, keyless version handling, transient re-sync read, destination key mismatch, or transformation bug. Preserve the raw evidence and define the expected current set. You might pause readers or mark the metric stale while a repair runs; you should not overwrite an audit trail simply to make today's graph look normal.

If the failure is a keyless update model, change the analytical model and test version transitions. If the source primary key is masked from the Fivetran service identity, resolve the access policy and validate the destination match path before re-syncing. If a multi-batch re-sync was exposed to users, establish a publish barrier and rerun affected metrics. If a connector lacks delete capture, choose a supported reconciliation mechanism or explicitly label the destination as not authoritative for current-source deletions. These actions follow from the failure, not from a generic recommendation to “reset Fivetran.”

Expect a follow-up about rollback. A rollback of downstream transformed tables does not necessarily undo source changes or destination replication. Specify which layer can be restored, how freshness is represented during the hold, and what reconciliation proves the new cutover safe. Monitor active-row count, unexpected duplicate keys, deleted-row transitions, field drift, sync lag, and dashboard deltas. The best interview answer ends with a falsifiable recovery criterion: “After completion, the active source and destination key sets match at boundary T, sampled values match, and the downstream measure reproduces from that published snapshot.”

Sources and further reading

• Fivetran: Soft Delete Mode — delete-capture limits, keyless updates, and documented multi-batch re-sync behavior.
• Fivetran: History Mode — versioning columns and mode migration contrasted with soft deletes.
• Fivetran: How to Trigger Historical Re-Syncs — operational scope of a table or connection re-sync.
• Fivetran: Duplicate Data in the Destination — troubleshooting primary-key visibility and duplicate rows.