Most of the destination questions we get at SyncSpur used to be some flavour of "Snowflake or BigQuery?". Over the last couple of years a third answer has started showing up in enterprise shortlists: Microsoft Fabric. Usually not because the data team asked for it, but because the organisation already pays for Power BI Premium, someone consolidated the licensing, and now there is a Fabric capacity sitting there with a mandate attached.
If that is where you are, the practical question is narrow: how do you get Fivetran to land data cleanly in Fabric, what does it cost in capacity units, and what breaks that would not break on Snowflake? This tutorial walks the whole path — destination setup, table format reality, capacity sizing, incremental behaviour, governance and the failure modes we see most often.
First: decide what "Fabric" means for your pipeline
Fabric is an umbrella. For a Fivetran destination you are really choosing between two landing targets, and they behave differently.
Lakehouse (OneLake, Delta tables). Data lands as Delta Parquet files in OneLake. Query it with Spark, with the SQL analytics endpoint (read-only), or via Direct Lake in Power BI. This is the option that behaves most like a data lake and the one that plays nicely with open table formats.
Warehouse (T-SQL, read-write). A full relational engine with T-SQL DML, still storing Delta underneath. Familiar to teams coming from Azure Synapse or SQL Server, and the right target if your transformation layer is stored procedures rather than dbt or Spark.
The rule of thumb we use:
- Transformation layer is dbt or Spark, and Power BI is one consumer among several → Lakehouse.
- Transformation layer is T-SQL, and Power BI is the point → Warehouse.
- You want the same tables readable from Snowflake, Databricks or Trino as well → Lakehouse, and read our managed data lake and Iceberg walkthrough before you commit, because a Fivetran-managed Iceberg lake with Fabric shortcut access is sometimes the better shape.
Do not mix. Splitting raw landing across both targets doubles your governance surface for no benefit.
Prerequisites
- A Fabric capacity (F-SKU, or a Premium P-SKU with Fabric enabled). F2 is enough to prove a pipeline; it is not enough to run a production warehouse with concurrent Power BI users.
- A workspace assigned to that capacity, with a Lakehouse or Warehouse item created.
- An Entra ID service principal with access to the workspace. Do not wire this up with a named user account. When that person leaves, every pipeline stops.
- Service principal access enabled at tenant level in the Fabric admin portal — this is off by default in many tenants and is the single most common reason setup tests fail.
- Fivetran's outbound IPs allowed if your capacity sits behind a private endpoint or firewall rules.
1. Create the service principal and grant it the right things
az ad sp create-for-rbac --name "fivetran-fabric-prod"
# note appId, password, tenant
Then, in Fabric:
- Workspace → Manage access → add the service principal as Contributor (Viewer is not enough; Fivetran creates schemas and tables).
- Admin portal → Tenant settings → Service principals can use Fabric APIs → enable, ideally scoped to a security group containing only your integration principals.
- If you are landing in a Warehouse, confirm the principal can connect over the SQL endpoint — grab the connection string from the Warehouse item's settings.
Grant at the workspace level, not per item. Fivetran will create new tables as new sources arrive, and item-level grants leave you debugging permission errors every time a connector picks up a table.
2. Configure the destination in Fivetran
In Fivetran, Destinations → Add destination, then pick the Fabric target that matches your choice above. You will be asked for:
| Field | Where it comes from |
|---|---|
| Tenant ID | Entra directory ID |
| Client ID / secret | The service principal above |
| Workspace | Fabric workspace ID (from the URL) |
| Lakehouse / Warehouse name | The item you created |
| Schema prefix strategy | Your naming convention — decide now |
Run the setup tests before saving. They check authentication, workspace visibility and write permission separately, so the failure message tells you which of the three prerequisites you missed.
On naming: whatever prefix you choose is effectively permanent. Changing a destination schema prefix later forces Fivetran to treat the tables as new, which means a fresh initial sync and a fresh pile of monthly active rows. We use raw_<source> — raw_salesforce, raw_postgres_app — and keep every transformed model out of those schemas entirely.
3. Understand how writes actually happen
This is where Fabric differs from Snowflake in ways that matter for your bill and your freshness SLA.
Fivetran writes into Fabric in micro-batches: it stages changed rows and applies them as Delta merges. Each merge rewrites the affected Parquet files. So:
- Small, frequent syncs are disproportionately expensive on Fabric. On Snowflake, a 5-minute sync frequency mostly costs you warehouse uptime. On Fabric, each merge consumes capacity units and creates small files that later need compaction. A 15-minute sync on a wide, high-churn table can quietly eat a meaningful share of an F8.
- Sync frequency is a capacity decision, not just a freshness decision. Start every connection at hourly. Move to 15 minutes only for the specific tables a business process actually needs at that latency, and be able to name the process.
- Deletes are soft by default. Fivetran marks
_fivetran_deleted = truerather than removing the row. Every downstream model must filter on it. This is not Fabric-specific, but it bites harder here because Direct Lake reports read tables directly — an unfiltered Power BI model will happily show deleted records to executives.
4. Maintenance: the step everyone skips
Delta tables in a Fabric Lakehouse need housekeeping, and unlike a managed warehouse nobody does it for you.
# In a Fabric notebook, scheduled nightly
from delta.tables import DeltaTable
tables = ["raw_salesforce.account", "raw_salesforce.opportunity", "raw_postgres_app.orders"]
for t in tables:
spark.sql(f"OPTIMIZE {t}")
spark.sql(f"VACUUM {t} RETAIN 168 HOURS")
OPTIMIZE compacts the small files that frequent merges create; without it, query times on a busy table degrade over weeks and Direct Lake can fall back to DirectQuery, which is much slower and much more visible to your users. VACUUM removes obsolete files and reclaims OneLake storage. Seven days of retention is a reasonable default — shorter if storage is tight, longer if you rely on time travel.
Schedule this. Put it in the same repository as the rest of your pipeline config, and alert if it fails.
5. Direct Lake, and why your report went slow
Direct Lake is the reason many teams pick Fabric: Power BI reads Delta files directly, with no import refresh and no DirectQuery latency. It is genuinely good, and it has guardrails. A semantic model falls back to DirectQuery when a table exceeds the row or file-size limits for your SKU, when the underlying table has too many small files, or when a query pattern is not supported.
Practical implications for a Fivetran-fed workspace:
- Never point a semantic model at a
raw_*table. Raw Fivetran output is wide, contains system columns, and includes soft-deleted rows. Build a curated layer and point Power BI at that. - Run
OPTIMIZEbefore you blame Fivetran. Most "Direct Lake got slow after we added the pipeline" tickets are file fragmentation. - Watch the fallback indicator in the semantic model settings. Silent fallback is the difference between a 2-second and a 40-second report, and nothing errors.
6. Governance, which is the reason Fabric got chosen in the first place
If Fabric is in play, someone in the organisation cares about Purview, sensitivity labels and Entra-based access. Set this up while the pipeline is small.
- Keep raw in its own workspace.
dataplatform-rawfor Fivetran landing,dataplatform-curatedfor models,dataplatform-reportingfor semantic models and reports. Analysts get access to the second and third, never the first. - Block PII before it lands. Fivetran's column blocking and hashing means regulated fields never reach OneLake at all, which is a much easier conversation with a compliance team than "we delete it afterwards". Our column-level governance tutorial covers the mechanics.
- Apply sensitivity labels at the Lakehouse item, and let them flow downstream to semantic models and reports.
- Manage the whole thing as code. The Fivetran side goes in Terraform — see managing Fivetran as code — and Fabric items can be deployed with deployment pipelines or the Fabric REST API. A hand-clicked Fabric workspace is just as unauditable as a hand-clicked Fivetran account.
7. Cost: two meters, not one
Budget for both sides, because teams routinely model one and get surprised by the other.
Fivetran charges on monthly active rows, counted per connection. That is driven by how many distinct rows change, not by how much data you store. Our connection-level MAR guide explains how to read and reduce it.
Fabric charges on capacity units — a flat reserved or pay-as-you-go rate for the SKU — plus OneLake storage. Ingestion merges, OPTIMIZE runs, SQL endpoint queries and Spark notebooks all draw from the same capacity pool. The failure mode is capacity throttling: when the pool is exhausted, Fabric smooths and then throttles, and the symptom is that reports go slow and syncs queue at the same time, usually on a Monday morning.
Two habits prevent most of this:
- Install the Fabric Capacity Metrics app on day one and look at it weekly. It shows which operation classes consume your CUs.
- Run heavy Fivetran syncs and
OPTIMIZEjobs outside peak reporting hours. Fivetran's schedule settings let you pin a connection's sync start time; use it.
A pre-production checklist
Before you point production sources at Fabric:
- Service principal auth, no named accounts, secret in a key vault with a rotation plan.
- Raw landing in its own workspace with analyst access denied.
- Schema prefix convention agreed and documented — renaming later costs a re-sync.
- Schema change handling set to block new tables by default, so MAR does not grow silently.
- PII columns blocked or hashed at the connector, before landing.
- Nightly
OPTIMIZEandVACUUMscheduled and alerting on failure. - Sync frequencies defaulted to hourly, with exceptions justified in writing.
- Capacity Metrics app installed, with an owner who looks at it.
- Every downstream model filters
_fivetran_deleted. - Failure webhooks wired to the channel a human actually reads — see our pipeline observability walkthrough.
When Fabric is the wrong answer
Consulting honesty: Fabric is a good destination when the organisation is already committed to the Microsoft stack and Power BI is the primary consumption layer. It is a poor one when your workloads are heavy concurrent SQL from many teams, when you need to scale compute independently per workload, or when your data science stack lives on Databricks. In those cases a Fivetran-managed Iceberg lake with a Fabric shortcut over it gives Power BI what it needs without forcing every workload onto one capacity.
If you are weighing that decision, or you have a Fabric capacity that is already throttling, our Fivetran data warehousing consulting and cost optimisation teams do this assessment regularly — get in touch with your SKU, source list and current sync frequencies and we can tell you fairly quickly whether the shape is right.