skip to content

What does Fivetran manage for you that a hand-written extract-load script does not?

level: juniorimportance: should knowfreq 55%

answer

  1. you buy upkeep, not extraction code
  2. who patches the API change on Friday
  3. state, retries, backfills, destination DDL
  4. transformation is still yours

basics

~20 s

Fivetran owns the connector itself — source API and schema changes, incremental state, retries, backfills and target table DDL. You supply credentials and a destination; the vendor maintains the extraction code you would otherwise write and patch.

solid answer

~50 s

Fivetran is a managed **EL** service: it extracts from a source and loads into a warehouse, and the thing you are actually buying is *connector maintenance*. When a SaaS API deprecates an endpoint, adds a field, or changes pagination, the vendor updates the connector — your team does not. It also handles the plumbing every hand-rolled extractor eventually needs: incremental state so each sync only pulls what changed, retry and resume after a failed sync, historical backfill on first sync, and creating/altering the destination tables to match the source. What it does *not* do is transform: it lands source-shaped tables, plus its own bookkeeping columns such as `_fivetran_synced` and a `_fivetran_deleted` flag for soft-deleted rows, and modelling happens afterwards in the warehouse. It also does not cover sources it has no connector for, and it is not free — you trade engineering time for a consumption bill.

code

text · 6 lines
text
-- destination table: salesforce.account
id              varchar   -- source primary key
name            varchar
annual_revenue  numeric
_fivetran_synced   timestamp  -- when this row was last written
_fivetran_deleted  boolean    -- true = row no longer present at source

go deeper

for a junior

Be ready to say plainly what the product does — extract from a source, load into a warehouse — and that transformation happens afterwards in SQL, not inside the tool.

for a middle

Explain the mechanics the connector hides: incremental state between syncs, backfill on first run, upsert by primary key, soft deletes, and automatic destination DDL when the source schema changes.

for a senior

Show you still operate it: alerting on failed or lagging syncs, expiring credentials, source rate limits, and the cost consequences of re-syncs. Managed shifts the failure modes, it does not remove them.

for a principal

Frame it as sourcing strategy: which connectors are commodity work worth outsourcing, which are differentiating enough to own, and how you avoid a lock-in position you cannot price out of later.

## What the product is Fivetran is a hosted **extract-and-load** service. You point it at a source (a SaaS API such as a CRM or ad platform, an application database, a file store, an event stream), point it at a destination (a cloud warehouse or lakehouse), give it credentials, and it replicates the source into destination tables on a schedule you configure. The **T** in ELT is explicitly out of scope: Fivetran lands data in roughly source shape and expects you to model it in the warehouse afterwards. ## The thing you are actually buying Anyone can write a script that calls an API and inserts rows. What kills hand-written extractors is not the first version — it is the second year. The realistic list of work a managed connector absorbs: - **Source API churn.** Endpoints get deprecated, pagination changes, rate limits tighten, auth moves from API keys to OAuth. Each of those is an unplanned interrupt for whoever owns the script. - **Schema migration.** A new column appears at the source; something must issue the `ALTER TABLE` in the destination and start populating it. A retype at the source must be reconciled with the destination's column type. - **Incremental state.** Something must remember where the last sync stopped and resume from there rather than re-pulling history every run, and must do so correctly across a crash mid-sync. - **Backfill.** The first sync pulls historical data, often far more than a normal run, and must not fall over or blow the source's rate limit. - **Retries and resumability.** Networks fail, sources 500, credentials expire. A sync must fail safe and pick up where it left off rather than leaving a half-loaded table. - **Deletes.** Source rows disappear. Fivetran's default is a *soft* delete: it sets `_fivetran_deleted` to true rather than removing the row, so downstream models must filter on it. - **Operational surface.** Sync history, per-connector status, alerting on failures, and the ability to re-sync a single table. ## What lands in the destination Expect one destination table per source object, with column names and types tracking the source, plus bookkeeping columns Fivetran adds — notably `_fivetran_synced` (when the row was last written) and `_fivetran_deleted` (the soft-delete flag). Loads are upserts keyed on the source primary key, so re-syncing a row updates it in place rather than duplicating it. This is deliberately un-modelled: fact tables, conformed dimensions, and business logic are your job, downstream. ## What it does not do - **It does not transform.** Fivetran can *trigger* dbt models to run after a connector's sync completes, which solves the sequencing problem, but the models are yours and the transformation logic runs in your warehouse. - **It does not cover every source.** If your source has no connector, you are back to writing code — either against Fivetran's connector SDK or entirely outside it. - **It does not remove monitoring.** Credentials still expire, source permissions still get revoked, syncs still fall behind, and a re-sync can produce an unpleasant bill. Somebody on your team still has to watch connector status and act on it. - **It is not a reverse-ETL tool.** Fivetran pulls *into* the warehouse. Pushing modelled warehouse data back out to operational SaaS apps is a different category of product. ## How the tradeoff actually reads The honest comparison is not "managed vs. free" but "vendor bill vs. loaded engineering cost". A managed connector removes a class of unplanned work — the Friday-afternoon API break — and converts it into a predictable consumption charge based on how many rows change. For a small team with a dozen standard SaaS sources, that trade is usually overwhelmingly in the vendor's favour: those connectors are commodity work with no competitive value. It gets less obvious as row volume grows, as the share of *custom* sources grows, or when the data has residency or network constraints that a hosted service makes awkward. ## Version note Fivetran's product surface moves — connector coverage, the transformation offering, and the packaging around custom connectors have all been reshaped more than once. Treat any specific capability claim as something to verify against current vendor documentation rather than recite from memory.

  • If Fivetran only extracts and loads, where does transformation happen?
    In the warehouse, after the load. Fivetran lands source-shaped tables and you model them with dbt or plain SQL. Fivetran can trigger those dbt models once a connector's sync completes, which fixes the sequencing problem of a scheduled job running on half-loaded data — but the models, tests and business logic are yours and the compute is your warehouse's.
  • What still breaks after you adopt a managed connector?
    Credentials and OAuth grants expire, source permissions get revoked, source rate limits throttle syncs, and syncs fall behind or fail. You also still own destination cost and the bill itself — an accidental full re-sync can spike consumption. Managed means someone else patches the connector, not that nobody watches it.
  • What happens in the destination when a row is deleted at the source?
    By default it is soft-deleted: the row stays and `_fivetran_deleted` is set to true. Downstream models must filter on that flag, otherwise deleted records keep appearing in counts and joins. This is a classic first-week surprise — the warehouse row count never goes down.

It is the difference between owning a car and having a leased one with a service plan: you still have to drive it and pay for fuel, but you are not the one sourcing a replacement part when the manufacturer changes the model.

saying these in an interview costs you the question

  • Says Fivetran replaces the transformation layer
  • Assumes any source is supported, including internal APIs
  • Thinks managed connectors mean no monitoring or alerting
  • Ignores the consumption bill when comparing against a script
  • Confuses it with reverse-ETL that pushes data into SaaS apps

context

resolving links…