Landing Fivetran Data in Databricks with Unity Catalog: Destination Setup, Cost and Governance

Snowflake gets most of the attention in Fivetran write-ups, ours included. But a large share of the accounts we are asked to fix land in Databricks, and a Databricks destination behaves differently enough that the Snowflake habits do not transfer cleanly. Unity Catalog adds a governance layer that has to be set up before the first sync, deletes land as soft deletes in Delta tables, and the compute you point Fivetran at determines both your latency and a surprising share of your bill.

This is a working setup guide for landing Fivetran data in Databricks on Unity Catalog: permissions, catalog layout, destination configuration, what the tables actually look like, and the operational checks worth having on day one rather than month six.

Decide the catalog layout before you create anything

Fivetran writes one schema per connection inside a single catalog on the destination. You choose that catalog once, and moving it later means re-syncing. So spend twenty minutes on the layout.

The pattern that survives contact with a real organisation:

• raw_prod — every production Fivetran connection lands here, one schema per source (salesforce, postgres_billing, hubspot).
• raw_dev — a separate catalog for sandbox connections, so a developer experimenting with column blocking cannot alter production tables.
• analytics_prod / analytics_dev — dbt or Databricks-native transformation output. Nothing writes into raw_* except Fivetran.

The last rule matters more than it sounds. Fivetran owns the schema of the tables it creates. If an analyst adds a column to a landed table, the next schema-change event may drop it, or the sync may fail outright. Keeping raw catalogs read-only for humans removes a whole class of incidents.

Create them up front:

CREATE CATALOG IF NOT EXISTS raw_prod
  MANAGED LOCATION 'abfss://lakehouse@yourstorage.dfs.core.windows.net/raw_prod';

GRANT USE CATALOG, CREATE SCHEMA ON CATALOG raw_prod TO `fivetran_sp`;
GRANT SELECT ON CATALOG raw_prod TO `analysts`;

A managed location per catalog is worth setting even if you only have one storage account. It gives you a clean blast radius for lifecycle rules and for cost attribution.

Create the service principal, not a personal token

Do not authenticate Fivetran as a human. Personal access tokens die when the person leaves, and the resulting failure looks like a mysterious permissions error at 3am.

1. In the Databricks account console, create a service principal, e.g. fivetran_sp.
2. Add it to the workspace that owns the destination.
3. Generate an OAuth secret (client ID + secret) for it. Fivetran supports OAuth M2M for Databricks destinations; prefer it over a PAT, which expires and has to be rotated manually.
4. Grant it the privileges it actually needs — and no more:

GRANT USE CATALOG ON CATALOG raw_prod TO `fivetran_sp`;
GRANT CREATE SCHEMA ON CATALOG raw_prod TO `fivetran_sp`;
-- Fivetran creates and owns each connection schema, so schema-level
-- grants follow automatically from ownership.

The service principal also needs CAN USE on the SQL warehouse (or cluster) you are going to point at it, and, if you are using an external location rather than managed storage, WRITE FILES on that external location.

Pick the right compute — this is the cost decision

Fivetran needs somewhere to run its merge statements. You have three options, and they are not equivalent.

Serverless SQL warehouse. Starts in seconds, so short syncs do not pay a startup tax. This is the right default for most accounts. A Small serverless warehouse handles a surprising amount of load, because Fivetran's writes are mostly MERGE statements against partitioned Delta tables, not analytic scans.

Pro/Classic SQL warehouse. Cheaper per DBU on paper, but with a start-up delay of a minute or more. If you run fifteen-minute syncs across twenty connections, the warehouse effectively never scales down, and you pay for idle. Only pick this when a policy forbids serverless.

All-purpose cluster. Almost always the wrong answer for a Fivetran destination — highest DBU rate, and you get no benefit from the interactive features.

Two settings on the warehouse matter more than the size:

• Auto-stop: set it shorter than your shortest sync interval gap, otherwise the warehouse idles between syncs at full price. Two to five minutes on serverless.
• Scaling / max clusters: leave it at 1 until you have more than a handful of concurrent connections. Fivetran will queue rather than fan out, and queuing is cheaper than a second cluster.

A common pattern that works well: one dedicated serverless warehouse for the Fivetran destination, sized Small, auto-stop 2 minutes, and a completely separate warehouse for BI. Sharing one warehouse between ingestion and dashboards means your morning executive dashboard competes with a Salesforce merge, and neither team can read the bill.

Configure the destination

In Fivetran, Destinations → Add destination → Databricks. The fields that need thought:

Field	What to put
Server hostname	The workspace hostname, e.g. adb-1234.5.azuredatabricks.net
HTTP path	From the SQL warehouse's Connection details tab
Auth	OAuth 2.0 (client ID + secret of fivetran_sp)
Catalog	raw_prod — the catalog you created above
Data processing location / cloud	Match your workspace's region to avoid egress and latency
Connection method	Direct, SSH tunnel, or Private Link

If the workspace is locked behind a private endpoint, you need Fivetran's Private Link (an added-cost feature on some plans) or a hybrid deployment where the data plane runs in your own network. Decide which before you promise a go-live date; the networking approval usually takes longer than everything else in this guide combined.

Test the connection. A failure here is nearly always one of: the service principal missing CAN USE on the warehouse, the warehouse being stopped and unable to auto-start under that identity, or CREATE SCHEMA not being granted on the catalog.

What the landed tables actually look like

Once a connection runs, look at one table before you build anything on it.

DESCRIBE EXTENDED raw_prod.salesforce.opportunity;

Things to notice:

• Tables are Delta, managed by Unity Catalog, owned by fivetran_sp.
• Every table has _fivetran_synced (timestamp of the sync that last touched the row) and, on most connectors, _fivetran_deleted.
• Deletes are soft. A row deleted in the source is not removed; _fivetran_deleted flips to true. Every downstream model must filter it:

SELECT * FROM raw_prod.salesforce.opportunity
WHERE NOT coalesce(_fivetran_deleted, false)

Every single model. The number of "our revenue number is too high" tickets that trace back to a missing _fivetran_deleted filter is remarkable. The cheap defence is a set of views in analytics_prod that apply the filter once:

CREATE OR REPLACE VIEW analytics_prod.staging.stg_opportunity AS
SELECT * EXCEPT (_fivetran_deleted)
FROM raw_prod.salesforce.opportunity
WHERE NOT coalesce(_fivetran_deleted, false);

Then make it a review rule that nothing outside staging selects from raw_*.

Housekeeping: OPTIMIZE, VACUUM, and predictive optimization

Fivetran's writes are merges, and merges on Delta produce a lot of small files. Left alone, a busy table becomes slow to read and expensive to merge into — the classic symptom is sync durations creeping up week over week with no change in row volume.

If your workspace supports predictive optimization on Unity Catalog managed tables, enable it at the catalog level and let Databricks handle compaction and vacuuming:

ALTER CATALOG raw_prod ENABLE PREDICTIVE OPTIMIZATION;

If it is unavailable, schedule the maintenance yourself — a nightly job over the busiest tables is enough to start:

OPTIMIZE raw_prod.postgres_billing.invoice_line;
VACUUM  raw_prod.postgres_billing.invoice_line RETAIN 168 HOURS;

Two cautions. Do not set VACUUM retention below the default 7 days without understanding that it breaks time travel and any long-running readers. And do not run OPTIMIZE on a table mid-sync if you can avoid it; schedule maintenance in a window where the noisiest connections are paused.

Liquid clustering is worth considering on large, frequently merged tables — cluster on the primary key Fivetran merges against, so the merge touches fewer files:

ALTER TABLE raw_prod.postgres_billing.invoice_line
  CLUSTER BY (id);

Measure before and after on sync duration. On tables under a few million rows the difference is noise; on hundreds of millions it is often the single biggest win available.

Governance you get for free, and what you still owe

Unity Catalog gives you lineage, audit logs and row/column controls across everything Fivetran lands. Three things are worth doing in the first week:

Tag PII at the source of truth. Fivetran can block or hash columns before they land — do that for the genuinely sensitive ones. For everything else, tag the columns in Unity Catalog so masking policies apply consistently:

ALTER TABLE raw_prod.salesforce.contact
  ALTER COLUMN email SET TAGS ('pii' = 'email');

Check lineage after the first transformation run. Unity Catalog's lineage graph will show Fivetran-landed tables feeding your models, which is the fastest way to prove that nothing is reading raw_* directly.

Set ownership deliberately. fivetran_sp owns the landed tables. Make a group, not a person, the owner of the analytics catalogs, and grant SELECT on raw_prod to a narrow group of engineers rather than to all analysts.

Monitoring worth having on day one

Two layers:

• Fivetran side. Enable the Fivetran Platform Connector into the same catalog and build freshness and failure alerts from sync_end, connection_status and incremental_mar. Sync duration drift is the early warning for the small-file problem above.
• Databricks side. Watch the destination warehouse's DBU consumption in the system tables (system.billing.usage), filtered to the warehouse ID. If it climbs while row volume does not, look at auto-stop, then at file compaction.

A useful weekly check, in one query:

SELECT connection_name,
       date_trunc('day', sync_end) AS day,
       count(*)                    AS syncs,
       avg(datediff(second, sync_start, sync_end)) AS avg_seconds
FROM raw_prod.fivetran_platform.sync_log
WHERE sync_end > current_date() - INTERVAL 30 DAYS
GROUP BY 1, 2
ORDER BY 1, 2;

Rising avg_seconds on a flat row count is your cue to run OPTIMIZE or enable clustering, well before anyone files a "data is late" ticket.

A rollout order that works

1. Create catalogs and the service principal; grant only what is needed.
2. Stand up a dedicated serverless SQL warehouse with a short auto-stop.
3. Create the destination and test the connection.
4. Move one low-volume connection first, and read the landed tables by hand.
5. Build the staging views that filter _fivetran_deleted, and make that the only path downstream.
6. Enable predictive optimization or schedule OPTIMIZE/VACUUM.
7. Add the Platform Connector, freshness alerts, and the warehouse cost query.
8. Migrate the remaining connections in volume order, smallest first, watching sync duration and DBUs after each.

The failure mode we get called about most often is skipping steps 1 and 5: everything lands in a default catalog nobody owns, analysts query raw tables directly, deleted rows inflate the numbers, and by the time anyone notices, forty dashboards depend on it.

If you would like a second pair of eyes on a Databricks destination — catalog design, warehouse sizing, or a Snowflake-to-Databricks move without paying for history twice — our Fivetran data warehousing consulting and performance tuning teams do exactly this. Get in touch with your catalog layout and a sync duration chart, and we will tell you what we would change.