Most warehouses answer "what does this customer's subscription look like now?" perfectly well and fall apart on "what did it look like on the day they churned?" Fivetran, by default, is part of that problem: a standard sync keeps one row per primary key and overwrites it in place. Yesterday's value is gone.
History Mode is Fivetran's answer. Turn it on for a table and Fivetran stops overwriting rows and starts appending versions, each stamped with the window during which it was true. You get a slowly changing dimension (Type 2) without writing a single snapshot model. This tutorial covers what the tables actually look like, how to query them without producing wrong numbers, what it costs, and when a dbt snapshot is still the better tool.
What History Mode changes
With History Mode enabled on a table, Fivetran adds system columns and changes the grain:
| Column | Meaning |
|---|---|
_fivetran_start | When this version of the row became true in the destination |
_fivetran_end | When it stopped being true; active rows carry a far-future sentinel (9999-12-31) |
_fivetran_active | TRUE for exactly one version per primary key |
_fivetran_synced | When Fivetran last wrote the row |
The primary key is no longer your source key alone — it becomes the source key plus _fivetran_start. That single sentence is the source of nearly every bug people hit afterwards, because every existing query that assumed one row per id now silently fans out.
A subscriptions table that used to look like this:
id plan status mrr
417 business active 900
becomes:
id plan status mrr _fivetran_start _fivetran_end _fivetran_active
417 starter trialing 0 2026-01-04 09:12:00 2026-01-18 11:40:00 FALSE
417 starter active 149 2026-01-18 11:40:00 2026-03-02 16:05:00 FALSE
417 business active 900 2026-03-02 16:05:00 9999-12-31 23:59:59 TRUE
Deletes are captured too: a row deleted in the source gets its window closed and _fivetran_active set to FALSE, instead of a hard delete or a lone _fivetran_deleted flag. Nothing disappears.
1. Deciding which tables get it
Do not enable History Mode account-wide. It is a per-table decision, and the right ones share a shape: narrow, low-churn, semantically important.
Good candidates:
subscriptions,plans,pricing— anything that determines revenue.opportunities,deals,leads— CRM records whose stage transitions are the analysis.employees,accounts,territories— dimensions used for point-in-time attribution.- Anything a regulator or auditor may ask you to reconstruct.
Bad candidates:
- Event and log tables. They are already append-only; versioning them doubles storage for nothing.
- Wide tables with a churny non-analytical column (
last_seen_at,updated_at, a cache counter). Every tick of that column creates a new version of all 80 columns. If you need history on such a table, exclude the noisy columns from the sync first — column selection is your friend here. - Tables you sync only to satisfy a join.
A useful sanity check before enabling: how many rows change per day, and how wide is the table? A 40-column table where 5% of rows change daily produces roughly 1.5x its own row count in new versions every month.
2. Enabling it
In the Fivetran UI, open the connection, go to the Schema tab, and set the sync mode on the individual table from Soft delete / Live to History. Support varies by connector, so check the connector's documentation page before you plan around it.
Two things to know before you click:
- Enabling History Mode triggers a re-sync of that table. History starts from the moment you enable it — Fivetran cannot invent versions for changes that happened before it was watching. If a table matters, enable it early, even if nobody is querying the history yet. History you did not start collecting is not recoverable.
- Switching back is destructive to the history. Reverting a table to live mode collapses it. Treat the switch as a one-way door and test on a staging connection first.
Via the REST API, table-level sync mode lives in the schema config endpoint:
curl -X PATCH \
-u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
-H "Content-Type: application/json" \
https://api.fivetran.com/v1/connections/<connection_id>/schemas/<schema>/tables/subscriptions \
-d '{"sync_mode": "HISTORY"}'
If you manage Fivetran with Terraform, put this in the schema config resource so the decision is reviewable rather than remembered — see our Terraform provider tutorial.
3. Querying it without breaking your dashboards
This is where the work actually is. Three patterns cover almost everything.
Current state — replaces your old SELECT * FROM subscriptions:
CREATE OR REPLACE VIEW analytics.subscriptions_current AS
SELECT * EXCLUDE (_fivetran_start, _fivetran_end, _fivetran_active)
FROM raw.stripe.subscriptions
WHERE _fivetran_active;
Build this view the same day you enable History Mode and repoint existing models at it. Every downstream model that still selects from the raw table is now quietly multi-counting revenue, and that is exactly the class of bug nobody notices until a board deck is wrong.
Point in time — what was true on a given date:
SELECT id, plan, status, mrr
FROM raw.stripe.subscriptions
WHERE DATE '2026-03-01' >= _fivetran_start::date
AND DATE '2026-03-01' < _fivetran_end::date;
Note the asymmetry: inclusive start, exclusive end. Fivetran's windows are contiguous, so if you use <= on both sides you will double-count rows that changed on exactly that date. Pick the convention once, write it into a macro, and never hand-roll it again:
-- point-in-time helper, as a table function (Snowflake syntax)
CREATE OR REPLACE FUNCTION analytics.subs_as_of(as_of_ts TIMESTAMP)
RETURNS TABLE (id NUMBER, plan VARCHAR, status VARCHAR, mrr NUMBER)
AS
$$
SELECT id, plan, status, mrr
FROM raw.stripe.subscriptions
WHERE as_of_ts >= _fivetran_start
AND as_of_ts < _fivetran_end
$$;
Transition analysis — the question History Mode exists for:
WITH versions AS (
SELECT
id,
status,
plan,
mrr,
_fivetran_start,
LAG(status) OVER (PARTITION BY id ORDER BY _fivetran_start) AS prev_status,
LAG(mrr) OVER (PARTITION BY id ORDER BY _fivetran_start) AS prev_mrr
FROM raw.stripe.subscriptions
)
SELECT
DATE_TRUNC('month', _fivetran_start) AS month,
COUNT(*) AS downgrades,
SUM(prev_mrr - mrr) AS mrr_lost
FROM versions
WHERE prev_mrr IS NOT NULL
AND mrr < prev_mrr
GROUP BY 1
ORDER BY 1;
Before History Mode, that query needed a nightly snapshot job somebody had been maintaining for two years. Now it is a window function over a table Fivetran maintains.
Point-in-time joins — attributing a fact to the dimension version that was true when the fact happened:
SELECT o.order_id, o.created_at, o.amount, s.plan
FROM analytics.orders o
JOIN raw.stripe.subscriptions s
ON s.id = o.subscription_id
AND o.created_at >= s._fivetran_start
AND o.created_at < s._fivetran_end;
Two cautions. First, _fivetran_start is when Fivetran observed the change, not necessarily when it happened in the source; on an hourly sync your windows are accurate to the hour, no better. If the source carries its own updated_at you may want to reconcile against it for anything legally or financially sensitive. Second, these range joins are expensive on large fact tables — cluster or partition the dimension on _fivetran_start and keep the versioned dimension narrow.
4. What it costs
Two bills move, and it is worth being clear about which is which.
Storage grows with the number of versions, not the number of rows. This is usually the smaller effect and easy to model: versions per month ≈ (rows changed per day × 30).
MAR is the one to watch. Monthly active rows count rows Fivetran writes to your destination, and History Mode makes Fivetran write more rows — each new version is a write. Also, enabling History Mode re-syncs the table, and a re-sync re-activates every row in it for that billing month. Neither of those is a reason not to use the feature; both are reasons to enable it deliberately, on a handful of tables, and then look at your usage page rather than guess. Our connection-level MAR guide covers how to read that page and where the levers are.
Three practical cost controls:
- Deselect churny columns before enabling. One
last_login_atcolumn can multiply your version count. - Enable in one connection at a time and compare MAR week over week, so you can attribute the change.
- Add a retention policy downstream. Fivetran keeps every version; if you only need three years of history for audit, prune the versioned table in your own warehouse rather than paying warehouse storage for a decade of them.
5. History Mode vs. dbt snapshots
Both produce Type 2 dimensions. They fail differently.
| Fivetran History Mode | dbt snapshot | |
|---|---|---|
| Granularity | Every change Fivetran sees, including intra-day | Only what exists at snapshot run time |
| Missed changes | Changes between syncs may collapse into one version | Changes between runs are lost entirely |
| Setup | A toggle per table | A model, a schedule, a strategy choice |
| Applies to | Fivetran-synced tables | Any table, including derived models |
| Cost | MAR + storage | Warehouse compute + storage |
| Backfill | None; starts when enabled | None; starts when first run |
Use History Mode for raw source dimensions — it catches more changes with less code, and it captures deletes for free. Use dbt snapshots for derived models, where the thing whose history you want does not exist in any source system: a computed customer segment, an internal health score, a mapping table someone maintains in a spreadsheet.
Running both on the same table is a genuine mistake and a common one. Pick one owner of history per table and document it.
6. A rollout that will not surprise anyone
- List your dimensions and pick the three where point-in-time truth actually matters.
- Check connector support and drop noisy columns from the sync.
- Enable History Mode on a staging connection, let it run a week, and read the version counts.
- Build
*_currentviews and repoint every existing downstream model before you enable in production. - Enable in production, one connection at a time, watching MAR.
- Add the
as_ofmacro, ship one transition-analysis model to prove the value, and write down the inclusive/exclusive convention where analysts will find it. - Retire the homegrown snapshot job it replaced.
Step 4 is the one people skip and the one that causes the incident. A table whose grain silently changed under a dozen models is a bad afternoon.
Done properly, History Mode retires a category of fragile custom jobs and turns "what did this look like in March?" from a project into a WHERE clause.
If you would like help choosing which tables deserve history, sizing the MAR impact before you flip anything, or rebuilding downstream models around the new grain, our Fivetran data warehousing consulting and performance tuning teams do this regularly — or just get in touch with a table list and a question.