Landing Fivetran Data in Apache Iceberg: A Managed Data Lake Walkthrough

Most teams we help have the same shape of problem. Fivetran already lands their SaaS and database sources cleanly into a warehouse, the warehouse bill grows every quarter, and then a second team shows up wanting the same data in Spark, or a third wanting it in Trino. The default answer used to be another pipeline, another copy, another set of freshness arguments in Slack.

The alternative is to make the lake the landing zone and let every engine read it. Fivetran's Managed Data Lake Service does exactly that: instead of writing to a warehouse, connections write Apache Iceberg (or Delta) tables into your own object storage, register them in a catalog, and keep them maintained — compaction, schema evolution, and deletes included. This tutorial walks the setup end to end and flags the decisions that are painful to reverse.

When this is the right architecture

Be honest about the fit before you build it. A managed lake destination earns its place when:

• More than one compute engine needs the same raw tables (Snowflake and Databricks, or a warehouse plus ad-hoc Trino/DuckDB analysis).
• You want to retain high-volume, low-value-per-row data (event logs, clickstream, IoT) without paying warehouse storage rates for it.
• Data residency or ownership requirements mean the bytes must sit in your bucket, in your region, under your keys.

It is not the right call if you have exactly one warehouse, a modest data volume, and no plans to change either. In that case the classic warehouse destination is simpler and cheaper to operate. Bear in mind, too, that Monthly Active Rows are counted the same way regardless of destination — moving to a lake changes your storage and compute economics, not your Fivetran consumption.

Step 1: Pick the catalog first

The catalog is the hardest thing to change later, because it is what every query engine points at. Fivetran supports several, and each implies a centre of gravity:

Catalog	Best when	Watch out for
AWS Glue Data Catalog	You are AWS-native and want Athena/EMR/Redshift Spectrum access with no extra service	Glue is regional; cross-region access needs planning
Snowflake Open Catalog (Polaris)	Snowflake is your primary engine but you want external reads	External-table read patterns differ from native tables
Databricks Unity Catalog	Databricks is the primary compute and you want governance in one place	Uniform/managed-table nuances for external writers
Fivetran-managed (glue-less) catalog	You want the fastest path to working tables	Fewer governance hooks than a first-party catalog

Write the decision down along with the reason. Six months from now someone will ask why, and "it was the default" is not an answer that survives an audit.

Step 2: Provision storage and permissions

Create a dedicated bucket or container for Fivetran-managed tables. Do not share it with hand-written Spark output — mixed writers into the same Iceberg prefix is how you end up with orphaned metadata files.

On AWS, the shape is a bucket plus an IAM role Fivetran assumes:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "IcebergTableAccess",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::acme-fivetran-lake",
        "arn:aws:s3:::acme-fivetran-lake/*"
      ]
    },
    {
      "Sid": "GlueCatalogAccess",
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetTable",
        "glue:CreateTable",
        "glue:UpdateTable",
        "glue:GetTables",
        "glue:CreateDatabase"
      ],
      "Resource": "*"
    }
  ]
}

Trust policy: allow the Fivetran AWS account principal shown in the destination setup screen, and require the external ID Fivetran generates for you. The external ID is not optional theatre — without it, the role is confused-deputy bait. Narrow the glue:* resources to your specific catalog and database ARNs once the first sync succeeds; the wildcard above is for getting to green, not for staying there.

On Azure the equivalent is an ADLS Gen2 container plus a service principal with Storage Blob Data Contributor scoped to that container only.

Step 3: Create the destination and move one connection

In the Fivetran dashboard, add a destination of type Managed Data Lake, supply the storage path, catalog details and role ARN, then run the connection test. Common failures, in order of how often we see them:

1. Trust policy missing the external ID — test fails immediately on assume-role.
2. Bucket in a different region from the Glue catalog — table creation succeeds, reads fail later.
3. Bucket policy or SCP denying s3:PutObject for anything without KMS encryption headers. If you use SSE-KMS, grant the Fivetran role kms:GenerateDataKey and kms:Decrypt on the key.

Then migrate exactly one low-risk connection first — a small SaaS source, not your production Postgres CDC. Point it at the new destination, let it complete an initial sync, and compare row counts against the warehouse copy before you touch anything else.

-- from Athena / Trino, against the Iceberg table
SELECT count(*) AS rows, max(_fivetran_synced) AS last_sync
FROM acme_lake.hubspot.deals;

Step 4: Understand how deletes and updates land

This is the part that surprises warehouse-native teams. Iceberg handles updates via merge-on-read delete files, so a freshly synced table can be physically fragmented even though it is logically correct. Two consequences:

• Queries immediately after a sync can be slower until compaction runs. Fivetran's managed service performs table maintenance (compaction and file rewriting) on your behalf; keep an eye on how often, and do not layer your own OPTIMIZE/rewrite_data_files job on top of it.
• Soft-deleted rows still appear unless you filter them. Fivetran marks them with _fivetran_deleted, so every downstream model should read through a view:

CREATE OR REPLACE VIEW analytics.deals_current AS
SELECT *
FROM acme_lake.hubspot.deals
WHERE COALESCE(_fivetran_deleted, false) = false;

Encode that filter once, in a staging layer, rather than trusting thirteen analysts to remember it.

Step 5: Wire up the query engines

The payoff is one copy, many readers.

• Snowflake: create an external volume over the bucket and a catalog integration against Glue or Open Catalog, then define Iceberg tables. Reads are native SQL; no ingestion cost, no second pipeline.
• Databricks: register the location in Unity Catalog as an external location and read the Iceberg tables directly, or use catalog federation if your catalog is Glue.
• Trino / Athena: point the Iceberg connector at the same catalog. This is usually the cheapest path for exploratory work.
• DuckDB: the iceberg extension reads snapshots straight from S3 — excellent for local debugging and for verifying a suspicious row count without spinning up a cluster.

Govern access at the storage and catalog layer, not per engine. If four engines each carry their own grant model, your permission review becomes a four-way diff nobody completes.

Step 6: Operational checks worth automating

Before you call the migration done, put these in place:

• Freshness monitoring. Alert on max(_fivetran_synced) per table exceeding its expected sync interval, and subscribe to Fivetran webhooks for sync_end failures.
• Schema-change policy. Decide per connection whether new columns and tables are allowed automatically; lake consumers with pinned schemas break differently than warehouse views do.
• Storage growth and snapshot expiry. Iceberg keeps history. Know your retention and confirm expired snapshots are actually being cleaned so storage does not creep.
• Cost baseline. Record warehouse storage and ingest compute for the month before the migration, so the saving is a number and not a vibe.

A realistic migration order

1. One small SaaS connection — prove catalog, IAM, compaction and reads.
2. High-volume, low-value sources (events, logs) — this is where the savings actually come from.
3. Shared reference data that more than one engine needs.
4. Database CDC sources last, once you trust delete semantics under load.
5. Retire duplicate warehouse copies only after downstream models have been repointed and reconciled for a full cycle.

Run the old and new destinations in parallel for a couple of weeks on anything important. It costs a little MAR and buys you a rollback.

Where this usually goes wrong

The failure mode is rarely Fivetran. It is a catalog chosen by accident, an IAM role scoped to a whole account, no delete filtering in the staging layer, and a hand-rolled compaction job fighting the managed one. Fix the design decisions first; the configuration is the easy half.

If you want a second pair of eyes on a lake design — or a migration run by people who have already made these mistakes elsewhere — our team does this work daily. Tell us your sources, engines and volumes on the Get In Touch page and we will tell you honestly whether a managed lake destination is worth it for you.