Microsoft Fabric Loader
The Microsoft Fabric loader pulls tables and views from a Fabric Warehouse or a Lakehouse’s SQL analytics endpoint into your data warehouse — warehouse-to-warehouse loading.
It reads over Fabric’s SQL endpoint (the SQL Server TDS protocol) as a Microsoft Entra service principal. Fabric exposes no change feed to a SQL client — no rowversion, no change tracking, and Delta change data feed is readable only from Spark — so incremental reads compare a date or timestamp cursor column against the position of the last run.
Prerequisites
- A Microsoft Entra ID app registration (service principal) with a client secret
- Service principals can use Fabric APIs enabled for that service principal by a Fabric administrator
- The service principal added to the Fabric workspace as Viewer or higher
- A connected Warehouse as the load target, with write permissions on the target schema
Authentication
Fabric accepts Microsoft Entra authentication only — there are no SQL logins.
- In the Azure portal , open Microsoft Entra ID → App registrations and create (or select) an app.
- Copy the Application (client) ID and the Directory (tenant) ID from its overview page.
- Under Certificates & secrets, create a client secret and copy its value — it is shown only once.
- In the Fabric Admin portal → Tenant settings → Developer settings, enable Service principals can use Fabric APIs, either for the whole organisation or for a security group containing the service principal.
- In the Fabric workspace that holds the warehouse or lakehouse, open Manage access and add the service principal as Viewer.
Viewer is enough. It grants read access to every warehouse and SQL analytics endpoint in the workspace, which is all the loader does. The Microsoft Fabric destination asks for Contributor because it writes; the loader does not. Sharing a single warehouse directly with a service principal is not supported by Fabric, so workspace membership is the way to grant access.
Role changes in Fabric can take up to two hours to apply. A connection test that fails with database was not found, or you have insufficient permissions straight after step 5 usually just needs time.
Configuration
| Field | Type | Required | Description |
|---|---|---|---|
| Client ID | String | Yes | The app registration’s Application (client) ID |
| Client Secret | Secret | Yes | The app registration’s client secret |
| SQL Endpoint | String | Yes | The host from the item’s SQL connection string (Settings → SQL endpoint), for example abc123.datawarehouse.fabric.microsoft.com. No https://, port or path |
| Database | String | Yes | The name of the Warehouse or Lakehouse |
| Tenant ID | String | Yes | The Directory (tenant) ID the service principal belongs to |
| Schema | String | — | The schema to read from. Defaults to dbo |
Streams
Every table and view in the configured schema is a stream. Each stream lands as one table in your warehouse.
| Table shape | Cursor | Default sync mode | Why |
|---|---|---|---|
| Has a date or timestamp column and a primary key | Auto-detected | Incremental (merge on key) | Rows can change after they are written, and the key lets a re-read row replace its earlier version |
| Has a timestamp column, no primary key | Auto-detected | Loader-wide mode | Nothing to merge on, so the loader’s own setting applies |
| Has only a date column, no primary key | Auto-detected | Full Refresh | A date cursor re-reads its last day every run (see Timestamp cursor); without a key, append would land those rows again and merge has nothing to fold them into |
| No date or timestamp column | None | Full Refresh | It is read whole every run, and only a replace drops rows deleted in Fabric |
The default is applied when the loader is created; you can change any stream’s mode afterwards. See Sync mode per stream.
Primary keys are read from the table’s PRIMARY KEY ... NOT ENFORCED constraint. Fabric Warehouse lets you declare one; a Lakehouse SQL analytics endpoint has none. Fabric does not enforce the constraint, so a declared key with duplicate values is possible — merge mode then keeps one of the duplicates.
Sync modes
| Mode | Behaviour |
|---|---|
| Full Refresh | Re-reads every row each run and replaces the target table |
| Incremental (append) | Reads rows whose cursor is newer than the last run and appends them. A row read twice lands twice |
| Incremental (merge on key) | Reads rows whose cursor is newer than the last run and upserts them on the primary key, leaving one row per record |
Merge mode is what you want for anything a model, audience or trait reads.
Change detection
Timestamp cursor
Compares a date or datetime2 column against the last run’s position. By default Zeotap picks a timestamp column before any date column — a non-nullable one first, then a nullable one — and a date column only when the table has no timestamp. Cursor column overrides that per stream. The override works as on the Snowflake loader — only date and timestamp columns are offered, and a column that no longer exists fails the run — and the same caveat applies: pick the column that moves when the record changes (updated_at, not created_at). The default pick differs from Snowflake’s, which takes the first date or timestamp column in catalog order: Fabric prefers a timestamp to a date, as described above.
Rows whose cursor is NULL are read by the first run and never again: NULL is never newer than anything.
A date cursor re-reads the last day. A date cannot tell a row written this morning from one written this afternoon, so after a run that ended on 2 March, a row added later on 2 March carries the same value as the checkpoint. To avoid skipping it, a date cursor resumes with on or after the checkpoint rather than after it, so every run reads the last day it saw again. In Incremental (merge on key) those rows simply replace themselves; in Incremental (append) they land again each run. Prefer a timestamp column, and use merge mode with a date cursor.
Rows that arrive behind the cursor
The cursor only moves forward, so a row whose cursor value is older than the last checkpoint when it becomes readable is never read by an incremental run. Two ways this happens:
- Lakehouse metadata sync. A Lakehouse’s SQL analytics endpoint reflects Delta tables through a background sync, which can lag a Spark or pipeline write by minutes. If a later commit is synced before an earlier one, or a write stamps its rows with a time from before the last run, those rows appear behind the checkpoint.
- Late commits. A transaction that sets
updated_atwhen it starts but commits after a run has already read past that time.
Use Incremental (merge on key) and schedule a periodic Full Refresh of the stream (for example weekly) to pick up anything missed. If the table is written by your own pipeline, stamp the cursor column at commit time rather than at the start of the job.
Deletions
A cursor cannot see a deleted row. Rows deleted in Fabric stay in the target until a Full Refresh run replaces the table.
How it works
- One statement per table. Each run issues a single
SELECT … WHERE cursor > last ORDER BY cursor(on or after the last value, for a date cursor) and streams the result, so the whole read sees one consistent snapshot (Fabric runs every statement under snapshot isolation) and only one batch of rows is held in memory at a time. - Batches of at most 10,000 rows, each checkpointed. A failed run resumes after the last checkpoint rather than starting over.
- A checkpoint never falls inside a group of rows sharing a timestamp. The next run resumes after the checkpoint, so a checkpoint in the middle of identical timestamps would skip the rest of them. When a batch fills partway through such a group, the batch is checkpointed at the value before the group and the group moves to the next batch. A group larger than a whole batch is written without moving the checkpoint until it ends, so a failure inside it re-reads the group instead of losing part of it.
Data types
| Fabric type | Lands as |
|---|---|
bit | Boolean |
smallint, int, bigint | Integer |
decimal, numeric with decimal places, float, real | Number |
decimal(p,0), numeric(p,0) with more than 15 digits | Text, the exact value |
date | Date |
datetime2 | Timestamp (UTC) |
time | Text, HH:MM:SS.ffffff |
char, varchar | Text |
uniqueidentifier | Text, the canonical GUID form |
varbinary | Text, hex with a 0x prefix |
Numbers are loaded as floating point, which holds about 15 significant digits exactly. A whole-number decimal wider than that — usually an identifier, such as a decimal(38,0) key — is loaded as text so it keeps every digit and still matches. A decimal with decimal places stays a number; if one carries more than 15 significant digits and you need them exact, expose it through a view as CAST(amount AS varchar(40)).
Rate limits
Fabric has no per-request rate limit; reads consume the workspace capacity’s compute units. A capacity that has been over its limits for a sustained period rejects new queries with error 24801 (your organization’s Fabric compute capacity has exceeded its limits). The run is retried automatically. If it keeps happening, scale the capacity or schedule the loader outside peak hours. A paused capacity rejects every connection until it is resumed.
Troubleshooting
| Symptom | Fix |
|---|---|
| database was not found, or you have insufficient permissions | Check the Database name. Then check that the service principal is a workspace member (Viewer or higher) — role changes take up to two hours — and that Service principals can use Fabric APIs is enabled for it |
| token request returned 400/401 | The tenant ID, client ID or client secret is wrong, or the secret has expired. Create a new secret in the app registration |
| sql_endpoint must be a bare host name | Paste only the host from the SQL connection string — no https://, :1433 or path |
| no tables or views found in schema | The Schema is wrong (it is case-sensitive in a lakehouse), or the schema is empty |
| can see the table but may not have SELECT on it | The connection test reads zero rows from the first table to prove access. A DENY SELECT or item-level permission is blocking the service principal — grant it read access to the table, or Read all data using SQL on the item |
| A recently created lakehouse table is missing from the stream list | The SQL analytics endpoint has not synced it yet. Refresh the endpoint’s metadata in Fabric, then reload the streams |
| Edits to existing rows never arrive | The cursor column does not change when a row changes — usually created_at was picked. Set Cursor column to the column that moves on update |
| Some rows are missing although the run succeeded | They arrived behind the cursor — see Rows that arrive behind the cursor. Run a Full Refresh of the stream |
| The same day’s rows land again every run | The stream uses a date cursor in append mode. Switch it to a timestamp cursor, or to Incremental (merge on key) |
| cursor column ”…” is not a column of this table | The override names a column the table no longer has. Pick another, or return it to Auto-detected |
| Error 24801 | The Fabric capacity is throttled — see Rate limits |
| A table appears with fewer columns than in Spark | The SQL analytics endpoint drops Delta columns it has no SQL type for (arrays, maps, structs). Flatten them in a view or a Warehouse table |