skip to content

In Fivetran, what does enabling History Mode change about the rows a table lands?

level: seniorimportance: nice to knowfreq 30%

answer

  1. overwrite becomes append
  2. one row per key becomes one row per version
  3. validity columns bound each version
  4. every downstream query needs a filter now

basics

~20 s

History Mode stops overwriting rows in place. Each change writes a new versioned row with validity columns — _fivetran_active, _fivetran_start, _fivetran_end — so the table keeps every past state instead of only the current one.

solid answer

~50 s

By default Fivetran upserts: a changed source row overwrites the destination row and the previous values are gone. **History Mode** changes that to an append-versioned table — each change closes the prior version and inserts a new one, with `_fivetran_active` marking the current version and `_fivetran_start`/`_fivetran_end` bounding when each version was valid. The result is effectively a slowly-changing-dimension Type 2 table produced by the loader rather than by your models. The cost is real: the table grows with change volume rather than with entity count, and every downstream query must now filter — `WHERE _fivetran_active` for a current-state view, or a validity-range predicate for as-of queries. That filter is the classic bug: a model written against the default behaviour and pointed at a history table silently starts double-counting. Enable it per table where auditability genuinely matters, not everywhere.

code

sql · 12 lines
sql
-- orders, History Mode enabled
-- order_id | status   | _fivetran_active | _fivetran_start | _fivetran_end
--     1001 | pending  | false            | 2026-03-01      | 2026-03-04
--     1001 | shipped  | true             | 2026-03-04      | (open)

-- current state only
SELECT * FROM orders WHERE _fivetran_active;

-- state as of a point in time
SELECT * FROM orders
WHERE '2026-03-02' >= _fivetran_start
  AND ('2026-03-02' < _fivetran_end OR _fivetran_active);

go deeper

for a junior

Know the basic contrast: normally a changed row overwrites the old one, and with History Mode the old version is kept and marked no longer active alongside the new one.

for a middle

Explain the versioning columns and derive the query consequence — counts and joins now operate on versions, so a current-state query must filter to the active version.

for a senior

Show the operating judgment: enable it per table against a real point-in-time requirement, budget the storage and consumption growth, and protect consumers with a current-state view so nobody double-counts.

for a principal

Decide where historisation belongs at all — loader-generated versions versus modelled Type 2 dimensions versus an event-sourced source — and own the cost and governance consequences of keeping every prior state.

## Default behaviour: current state only A normal Fivetran connector maintains a destination table that mirrors the source's *current* state. Each sync upserts changed rows on the primary key: the row is overwritten and its previous values are not retained anywhere. That is usually what you want — the warehouse table looks like the source table — but it means the warehouse cannot answer historical questions. What was this customer's plan tier in March? What was this deal's stage before it was re-classified? The information was overwritten. ## What History Mode changes Enabling History Mode on a table switches it from overwrite-in-place to **append-versioned**. Instead of replacing a row, each change closes the existing version and inserts a new one. Fivetran adds validity columns to express this: - `_fivetran_active` — a boolean marking the currently valid version of each key. - `_fivetran_start` — when this version became valid. - `_fivetran_end` — when it stopped being valid (open-ended for the active version). The practical shape is one row per (primary key, version). The table's natural key is no longer the source primary key alone — it is the key plus the version's start — and that is the detail that breaks naive joins. ## The relationship to Type 2 dimensions What this produces is structurally a slowly-changing-dimension Type 2 table, generated by the ingestion layer rather than by transformation logic. That framing is the fastest way to explain it, but keep the distinction clear in an interview: the *modelling* discipline around Type 2 dimensions — surrogate keys, change-detection strategy, which attributes are historised, how late-arriving facts join to the right version — is a data-modelling topic in its own right. What is specific to the tool here is that the loader is emitting versions for you, on whichever tables you enable, using its own validity columns. ## The costs **Growth.** A default table grows with the number of entities. A history table grows with the number of *changes*. A status column that transitions through six states per order turns one row into six. On a high-churn table this dominates: the versioned table can be an order of magnitude larger than the current-state one, with the corresponding storage and scan cost in the warehouse. **Consumption.** Producing more rows in the destination generally means more billable activity under a per-changed-row model. Do not assume history is free relative to your ingestion bill; check how your plan counts versioned writes before enabling it on a large table. **Query correctness.** This is the one that actually bites. Every existing query against the table is now wrong unless it filters. A `COUNT(*)` counts versions, not entities. A join fans out to every historical version of the joined key. Downstream models must adopt one of two shapes: - *Current state*: filter on the active flag, which reproduces the default table's semantics. - *As-of*: predicate the event time against the version's validity window, which is the whole point of enabling the feature. A good practice is to expose a current-state view over the history table so consumers who do not care about history cannot accidentally read versions. ## When it is worth it - **Audit and compliance.** You must be able to prove what a record said at a point in time — regulated reporting, financial restatement, consent state. - **Point-in-time analytics.** Cohort or funnel analysis where the attribute value *at the time of the event* matters, not today's value. Attributing revenue to the sales region a customer was in when the deal closed is the canonical example; using today's region rewrites history every reorg. - **Debugging state machines.** Understanding how records transitioned, not just where they ended. ## When it is not - The source already keeps history — an append-only event table, or a source with its own versioning. Historising it again is duplication. - The attribute never meaningfully changes, or changes so noisily (a heartbeat timestamp) that versions carry no information and enormous volume. - You want history on *everything*. Enable it per table, ideally per table where a consumer has articulated a point-in-time question. Blanket enabling is how teams discover the storage bill. ## The interview signal Candidates who have used it volunteer the query-side consequence unprompted — "and every downstream model needs the active-flag filter or you double-count". Candidates who have only read about it describe the columns and stop. It is a legitimately nice-to-know feature: not knowing it is not disqualifying, but reasoning correctly about versioned tables is. ## Version note History Mode's availability varies by connector and destination, and the feature has evolved; confirm which of your tables actually support it and how versioned rows are counted for billing before designing around it.

  • A dashboard's order count tripled the day History Mode was enabled. Why?
    The query counts rows, and rows are now versions rather than entities. Every status transition added a row for the same order. The fix is to filter to the currently valid version — the active flag — or to count distinct primary keys. Exposing a current-state view over the history table stops other consumers hitting the same trap.
  • When is History Mode the wrong tool even though point-in-time questions exist?
    When the source is already append-only or already versioned — historising an event log duplicates it — or when the churning attribute is a heartbeat or counter that generates enormous volume with no analytical meaning. It is also wrong as a blanket default: enable it per table where a consumer has an actual as-of question.
  • How does History Mode interact with the soft-delete flag?
    They answer different questions. The soft-delete flag says the row no longer exists at the source; the validity columns say which version of it was true when. In a history table a deletion closes the current version, so a point-in-time query can still see the record as it stood before deletion — which is usually exactly why the feature was enabled.

The default table is a whiteboard you erase and rewrite; History Mode is a logbook where each correction is a new dated entry and the old one stays readable.

saying these in an interview costs you the question

  • Says it just adds a timestamp column to each row
  • Assumes existing queries keep working unchanged
  • Thinks table growth tracks entity count rather than change count
  • Enables it on every table as a default safety measure
  • Confuses it with the soft-delete flag for removed source rows

context

resolving links…