+1 (726) 227-3027
ETL Advisors

Loading Microsoft Fabric from Talend: OneLake, Lakehouse Tables, and Warehouse COPY INTO

Microsoft Fabric keeps showing up on our engagements, usually in the same shape: the reporting team has already moved to Fabric, and the several hundred Talend jobs that feed the old SQL Server warehouse still have years of business logic in them. Nobody wants to rewrite those jobs. They just need somewhere new to land.

The good news is that Fabric does not require a new Talend component set. OneLake speaks the ADLS Gen2 API, and the Fabric Warehouse speaks T-SQL over TDS. Both of those Talend already does well. This tutorial covers the pattern we use most: write Parquet to OneLake, expose it as a Lakehouse table, and use COPY INTO when the destination is a Warehouse instead.

Step 1: Decide whether you are loading a Lakehouse or a Warehouse

This is the decision that determines everything downstream, and teams often get it backwards.

  • Lakehouse — files first. You write Parquet or CSV into OneLake and the table is metadata over those files. Best fit when Talend is already producing bulk extracts, when you want the same files readable by Spark notebooks, and when you can tolerate append/overwrite semantics at the partition level.
  • Warehouse — tables first. You load through T-SQL, either COPY INTO from staged files or straight inserts. Best fit when your existing Talend jobs do row-level upserts, when you have referential logic expressed in SQL, and when downstream Power BI models expect a relational schema.

If your current job ends in a tMSSqlOutput doing update-or-insert, aim at the Warehouse. If it ends in a tFileOutputDelimited that a bulk loader picks up, aim at the Lakehouse. Do not try to make one job do both.

Step 2: Register a service principal and grant it workspace access

Interactive auth is not an option for scheduled jobs. In Entra ID, create an app registration and a client secret, then note three values: tenant ID, client ID, client secret.

Two grants are easy to miss:

  1. In the Fabric admin portal, service principals must be allowed to use Fabric APIs. This is a tenant setting and it is off in plenty of estates.
  2. The principal needs a role on the workspace — Contributor is enough for writes. Workspace membership is what actually authorizes OneLake paths; there is no separate storage ACL to set.

Put all three secrets in Talend context variables and load them from your vault or from implicit context load, not from a hardcoded context group. If you have not standardized that yet, see our notes on managing multiple database environments.

Step 3: Write Parquet to OneLake with tAzureStoragePut / tFileOutputParquet

OneLake exposes every workspace at the DFS endpoint:

https://onelake.dfs.fabric.microsoft.com/<workspace>/<lakehouse>.Lakehouse/Files/<path>

Treat onelake.dfs.fabric.microsoft.com as the account host, the workspace name (or GUID) as the filesystem, and everything after it as the path. That is all the ADLS Gen2 components need.

A minimal job looks like this:

tPreJob -> tSetGlobalVar (load-id, batch timestamp)
tDBInput -> tMap -> tFileOutputParquet (local staging dir)
            |
            OnSubjobOk -> tAzureFSConfiguration -> tAzureStoragePut

On tAzureFSConfiguration, choose Azure Data Lake Storage Gen2, set the account name to onelake, the DFS endpoint to fabric.microsoft.com, and authentication to Active Directory (client credentials) with your tenant/client/secret contexts.

Write Parquet, not CSV, unless you have a reason. Fabric reads both, but Parquet carries types, so you stop losing decimal precision and date formats in translation — the single most common cause of "the numbers do not match Power BI" tickets after a cutover.

Partition the output path by load date rather than writing one growing file:

Files/staging/orders/load_date=2026-02-11/orders-part-0001.parquet

Use the tPreJob load ID in the filename. Reruns then overwrite a known set of files instead of appending duplicates, which is what makes the job safely repeatable.

Step 4: Turn the files into a Lakehouse table

Files in Files/ are not queryable as tables. You have three ways to promote them:

  • Load to Tables in the Fabric UI — fine for a one-off, useless for automation.
  • A Fabric notebook or pipeline activity that reads the staging path and writes a Delta table. This is the usual production choice, triggered after Talend finishes.
  • A shortcut from the Lakehouse Tables/ area to an existing Delta location, if something else already maintains Delta.

Talend can trigger the promotion itself. Call the Fabric REST API with tRESTClient — POST /v1/workspaces/{workspaceId}/items/{itemId}/jobs/instances?jobType=RunNotebook — using the same service principal token. Then poll the job instance URL until the status leaves InProgress, and fail the Talend job if it comes back Failed. Without the poll you have a green Talend run sitting on top of a broken load, which is worse than a red one. The token fetch is the standard client-credentials flow described in calling OAuth 2.0 APIs from Talend.

Step 5: Warehouse loads with COPY INTO

For a Warehouse destination, stage to OneLake exactly as above, then run one statement from tDBRow against the Warehouse SQL endpoint:

COPY INTO dbo.orders_stg
FROM 'https://onelake.dfs.fabric.microsoft.com/Analytics/Bronze.Lakehouse/Files/staging/orders/load_date=2026-02-11/*.parquet'
WITH (
  FILE_TYPE = 'PARQUET',
  CREDENTIAL = (IDENTITY = 'Storage Account Key')
);

In practice you will use a workspace identity or shared access signature rather than a key — but the shape holds: stage files, then one bulk statement. Do not point tDBOutput at a Fabric Warehouse and let it insert row by row. The Warehouse is a distributed engine with no clustered indexes and no singleton-insert optimization; a 200,000-row tDBOutput load that took four minutes on SQL Server can take well over an hour. Batch size tuning will not save it.

Then do the merge in SQL, from staging into target:

DELETE FROM dbo.orders
WHERE order_id IN (SELECT order_id FROM dbo.orders_stg);

INSERT INTO dbo.orders
SELECT * FROM dbo.orders_stg;

Fabric Warehouse MERGE support has been arriving gradually; delete-then-insert inside an explicit transaction is the version that works everywhere and is trivially rerunnable. Wrap both statements in a single tDBRow with BEGIN TRAN / COMMIT so a failure mid-load does not leave the target short.

Step 6: Connection details that will bite you

  • Driver. Use the Microsoft JDBC driver, version 12 or later, in a tDBConnection set to Other databases / generic JDBC if your Talend build does not list Fabric explicitly. Older drivers do not negotiate the required TLS and Entra token handshake.
  • JDBC URL. Take the SQL connection string from the Warehouse settings pane; it ends in datawarehouse.fabric.microsoft.com. Append ;authentication=ActiveDirectoryServicePrincipal;encrypt=true;.
  • Capacity pauses. If the Fabric capacity is paused or throttled, connections fail with generic timeouts. Give the job a retry with backoff and a distinct error message, or you will spend an afternoon debugging Talend for a billing problem.
  • No USE across databases. Cross-warehouse queries need three-part names, and Talend jobs that relied on USE <db> statements will need editing.
  • Case sensitivity. Fabric Warehouse collation is case-sensitive by default. Column names that worked against a case-insensitive SQL Server schema will fail in your tMap output mappings.

What this pattern buys you

The jobs keep their extraction logic, their context management, their error handling, and their scheduling. What changes is the last subjob and the connection metadata. On a recent migration we moved 140 jobs to Fabric this way in about six weeks, and roughly ninety of them needed nothing beyond a new output component and a context group.

The jobs that needed real work were the ones doing row-by-row upserts against SQL Server. Those had to become stage-then-merge, which is a better design regardless of destination. Fabric just forced the issue.

If you are weighing a Fabric landing zone against Snowflake or an Iceberg lakehouse, our post on ETL vs ELT in 2026 covers where the pushdown boundary should sit. And if you would rather not work out the capacity sizing and merge semantics on your own schedule, get in touch — we do this migration regularly.