All Data Engineering posts

Approach for automating Dataflow Gen2 connection binding across Dev/Prod?

lavginqo2 — Mon, 14 Sep 2026 19:53:22 GMT

We're deploying Dataflow Gen2 from Dev to Prod via fabric-cicd with all destination Lakehouse IDs and source parameters fully parameterized. Definitions deploy cleanly.

The blocker: every query's data source connection has to be manually reconfigured in the target workspace — "Configure connection" doesn't auto-bind to an existing connection object even when one already exists for that connector, and this has to be repeated per query/table, not once per Dataflow. Docs confirm this is expected ("connections are statically bound... can't be altered using workspace variables or parameterization"), but that leaves genuine automation impossible for Dataflows with many tables (~47 in our case).

And it is even not possible to update connection. It seems that each table has to be created fresh in Prod.

Has anyone found a real workaround — Service Principal-based automation, REST API call to bind connections post-deploy etc.

What's actually working for people running CI/CD from Dev to Prod at scale here?

Thanks

Re: Pure Python Notebook: authenticate to Azure DevOps Artifacts using Fabric Workspace Identity

ShivekMaharaj — Mon, 14 Sep 2026 19:42:36 GMT

Hi llueg,

I think you have found a real gap between the current pure Python notebook experience and the newer Workspace Identity support for Azure Artifacts.

As far as the current documentation goes, I don't see a supported way for a pure Python notebook to directly request an Azure DevOps/Azure Artifacts access token from its Workspace Identity and pass that token to pip.

The reason is that NotebookUtils credential token acquisition currently documents only storage, pbi, keyvault and kusto as supported token audiences. Azure DevOps isn't exposed as a documented audience, so I wouldn't rely on passing an Azure DevOps resource ID to getToken() as an undocumented workaround.

There is, however, a newer PAT-less path that gets very close to what you want. Fabric now supports authenticating an Azure Artifact Feed connection with the Workspace Identity. The Workspace Identity can be added to the Azure DevOps feed with at least Feed Reader, and Fabric then uses that authenticated connection when resolving packages in a Fabric Environment.

Microsoft documents that flow under library management for Fabric Environments.

The limitation for your scenario is that the current pure Python notebook documentation explicitly lists Environment integration as unavailable for Python notebooks. That Workspace Identity → Azure Artifacts flow therefore currently helps Spark notebooks using Fabric Environments, but it doesn't give a pure Python notebook a token that can be handed directly to pip install --index-url.

There is also the newer Fabric Connection integration for notebooks, which supports Workspace Identity authentication and is currently in Preview, but I haven't found Microsoft documentation showing an Azure Artifact Feed connection credential being exposed for direct pip authentication in a pure Python notebook.

So at the moment I would describe it as:

Spark notebook + Fabric Environment: Workspace Identity → Azure Artifacts is supported.
Pure Python notebook + direct pip install: I don't see a documented Workspace Identity-based Azure DevOps token flow yet.

For pure Python, your Key Vault-backed PAT remains the documented practical option unless Microsoft extends the Environment/Azure Artifact Feed integration to the Python kernel or exposes Azure DevOps as a supported notebook credential audience.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Re: New Browser Tab Opening to Previous or Existing Session

KentKMishra — Mon, 14 Sep 2026 18:16:31 GMT

This seems to be a "feature" rolled out over the summer and it drives me crazy. We actually need different tabs within the same workspace (dealing with items from different source systems) and the number of tabs takes up too much real estate and can cause notebook connections to go weird. Then I'm jumping through 10 tabs to see where the problem is. Has slowed down me and my teams' productivity.

Re: GraphQL update... mutation for Fabric SQL DB failing

msturzen — Mon, 14 Sep 2026 13:47:20 GMT

Hi Kagiyama_yutaka,
the update mutation requires the pk column as the query's first argument so it can identify the row to update. The PK column itself is not updated in the update snippet I posted initially.
I only send the writable column or am I misreading something?

Best
Martin

Re: Is a table ordinary, or special (= from a shortcut, a system table or materialized lake view table)?

Ben-Dev — Mon, 14 Sep 2026 13:43:14 GMT

Thank you, v-aatheeque. Your response is helpful. Hopefully, an easier--and unified--way to get these details will be added in the future. Thanks, again, for your help!

Re: GraphQL update... mutation for Fabric SQL DB failing

msturzen — Mon, 14 Sep 2026 13:38:30 GMT

Sorry for being unresponsive for the last 2 weeks. Had to take a few days off.
I raised a support ticket that is currently being worked on. I'll post updates asap.
Best
Martin

From On-Premises SQL Server to Microsoft Fabric with a Metadata-Driven Medallion Pipeline

ezalor — Mon, 14 Sep 2026 10:48:56 GMT

In this demo, I use DataCoolie to move WideWorldImporters data from an on-premises SQL Server into Microsoft Fabric. The pipeline is driven by one metadata.json file and follows the Bronze, Silver, and Gold pattern.

The focus here is the Fabric execution flow, not the earlier development process.

The complete sample is available in the datacoolie/dc-demo-fabric repository.

Architecture

The SQL Server remains on-premises, so ingestion runs from an external Polars runner. Downstream processing runs inside Fabric:

Stage	Runtime	Output
source2bronze	On-premises Polars	Parquet in Files/bronze
bronze2silver	Fabric Python 3.12 notebook with Polars and delta-rs	Delta tables in silver
silver2gold	Fabric Spark notebook	Delta tables in gold

The workspace uses two Lakehouses:

lh_wwi_medallion_demo
├── Files/bronze
├── Tables/silver
└── Tables/gold

lh_etl
└── Files/control/test-fabric
├── metadata/current/metadata.json
├── logs
└── watermarks

This keeps operational state separate from business data while allowing every runtime to use the same metadata and watermarks.

One metadata file, three stages

The final metadata contains four connections, 68 dataflows, and shared schema hints. Each dataflow declares its stage, source, destination, load type, watermark, merge keys, or SQL query. The runner remains generic: it reads this file and receives only the stage name it should execute.

The Fabric environment maps logical connections to:

bronze_parquet → Files/bronze
silver_delta → Tables/silver
gold_delta → Tables/gold

Note: An AI agent will assist in generating the metadata.json file.

Uploading the artifacts with Fabric CLI

After creating the two Lakehouses, install the Fabric CLI, authenticate, upload the metadata, and import the two notebooks:

pip install --upgrade ms-fabric-cli fab auth login $workspace = 'wwi_medallion_demo_test_fabric.Workspace' $etl = "$workspace/lh_etl.Lakehouse" fab mkdir "$etl/Files/control/test-fabric/metadata/current" fab cp '.\metadata.json' ` "$etl/Files/control/test-fabric/metadata/current/metadata.json" -f fab import "$workspace/run_fabric_polars.Notebook" ` -i '.\runners\run_fabric_polars.ipynb' --format ipynb -f fab import "$workspace/run_fabric_spark.Notebook" ` -i '.\runners\run_fabric_spark.ipynb' --format ipynb -f

Stage 1: SQL Server to Bronze

The on-premises runner reads 31 business tables and appends raw Parquet batches to OneLake. Every table has a watermark: ValidFrom, LastEditedWhen, or VehicleTemperatureID. Timestamp-based flows reread a one-day overlap to reduce the chance of missing late records.

Bronze files are organized by ingestion date:

Files/bronze/<schema>__<table>/YYYY/MM/DD/

This stage still runs on-premises because it requires SQL Server connectivity:

$control = 'abfss://wwi_medallion_demo_test_fabric@onelake.dfs.fabric.microsoft.com/lh_etl.Lakehouse/Files/control/test-fabric' python '.\runners\run_fabric_polars_azure_sdk.py' ` --metadata-path "$control/metadata/current/metadata.json" ` --watermark-base-path "$control/watermarks" ` --base-log-path "$control/logs" ` --stage source2bronze --max-workers 4

Stage 2: Bronze to Silver

A Fabric Python notebook runs this stage with Polars and delta-rs, without Spark. It reads new Bronze files through __file_modification_time, removes duplicate source keys, and performs a Delta merge_upsert for all 31 tables.

The result is a current-state Silver replica while Bronze keeps the raw append history.

After Bronze is ready, start the notebook with Fabric CLI. The Lakehouse configuration binds the notebook to lh_wwi_medallion_demo:

$lakehouseConfig = '{"defaultLakehouse":{"name":"lh_wwi_medallion_demo","id":"<lakehouse-id>","workspaceId":"<workspace-id>"}}' fab job run "$workspace/run_fabric_polars.Notebook" ` -P "STAGE:string=bronze2silver,_inlineInstallationEnabled:bool=true" ` -C $lakehouseConfig --timeout 3600

Stage 3: Silver to Gold

The final stage runs in a Fabric Spark notebook. SQL queries stored in metadata read two-level names such as silver.Sales__InvoiceLines and create six Gold products:

DimDate
DimGeography
DimProduct
DimPerson
DimDeliveryMethod
FactInvoiceLineProfitability

The source instance had no customer data, so customer-dependent Gold models were intentionally excluded.

Run Gold only after the Silver notebook succeeds:

fab job run "$workspace/run_fabric_spark.Notebook" ` -P "STAGE:string=silver2gold,_inlineInstallationEnabled:bool=true" ` -C $lakehouseConfig --timeout 3600

Monitoring the complete flow

Fabric Monitoring shows notebook status, duration, and execution details. DataCoolie writes dataflow-level logs and watermarks to lh_etl, allowing DataCoolie Studio to display metadata, assets, lineage, jobs, and individual dataflow runs across all three stages.

Result

The completed pipeline has 31 incremental Bronze flows, 31 key-merged Silver tables, and six Gold products. The runner stays generic; metadata defines the tables, watermarks, merge keys, paths, and SQL transformations.

This separation makes the pipeline easier to extend and keeps the hybrid on-premises/Fabric workflow observable as one system.

Re: DataFlow Gen2 Issue

EduardoCastro — Sat, 12 Sep 2026 22:43:37 GMT

You are likely correct that this is a firewall issue. The error The underlying connection was closed: An unexpected error occurred on a send typically indicates that the request from the Dataflow service is being blocked before it can complete the handshake with the Fabric Lakehouse API.

Because you can access the Lakehouse from Notebooks (which run directly within the Fabric environment) but cannot from Dataflow Gen2 (which may route metadata or data traffic differently via the on-premises data gateway or specific outbound IP ranges), it strongly suggests a one-way firewall rule is currently in place.

To resolve this, the firewall configuration must be changed to allow two-way traffic. It is not enough to simply allow outbound traffic from your network; the connection requires a request-and-response cycle. The firewall must explicitly permit the return traffic from the Power BI/Fabric API endpoints back to the Dataflow service or the data gateway.

Re: DataFlow Gen2 Issue

ShivekMaharaj — Sat, 12 Sep 2026 18:48:45 GMT

Hi LUISS252,

One thing I would separate here is the Pipeline network path from the Dataflow Gen2 network path.

The fact that your Pipeline Copy activity started working after the firewall maintenance is useful, but it does not necessarily prove that every endpoint required by Dataflow Gen2 is now reachable.

For an on-premises gateway, Dataflow Gen2 can also need access to OneLake and the staging Lakehouse endpoints, depending on whether staging, referenced queries or Fast Copy are involved.

I would also not focus too much on the missing Azure connections setting. That setting is mainly for workspace-level Azure storage / Log Analytics configuration and is not normally required just to write to a standard Fabric Lakehouse.

I would try one very small isolation test:

one SQL Server query
one Lakehouse destination
no referenced queries
staging disabled
Fast Copy disabled
only a few rows

Microsoft documents that in that specific pattern, Dataflow Gen2 should not require the staging-Lakehouse connection over port 1433.

If that minimal dataflow succeeds, I would then re-enable staging / Fast Copy or add the query references one at a time. That should tell you exactly when the failure appears.

If the minimal version still fails at the Lakehouse destination, I would treat this as a destination-validation / network-path issue rather than a SQL Server source problem, especially since your source preview succeeds and notebooks can already write to the same Lakehouse.

The current Dataflow Gen2 gateway guidance is particularly useful here because it lists the different endpoint requirements for staging and Lakehouse destination scenarios.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Re: Bronze data contracts for REST/API sources — workaround for Get Metadata's connector gap

ShivekMaharaj — Sat, 12 Sep 2026 18:45:35 GMT

Hi damo_mac85,

I think your workaround is reasonable, and I don't think you're missing a native Get Metadata option here.

The current Fabric REST connector lists Pipeline Copy activity as its supported pipeline capability, but it does not currently list Get Metadata.

One thing I would consider changing is where the contract validation happens.

If Bronze is intended to preserve the source payload, I would lean toward:

REST -> raw JSON -> schema/contract validation -> Parquet/Delta

rather than:

REST -> Parquet -> inspect Parquet schema

The reason is that if the API introduces a structural change that prevents the response from being serialized into your expected Parquet shape, the Copy could fail before your notebook ever gets the opportunity to compare schemas. Keeping the raw JSON first gives you an immutable payload to inspect and replay.

For a pre-flight check, Fabric's Web activity can call a REST endpoint before the Copy activity.

If the API exposes an OpenAPI/Swagger or dedicated schema endpoint, I would use that as the contract source where possible. Otherwise, a small sample call could be used to inspect the returned JSON before starting the full extraction.

I would just be careful about treating a sample response as the complete schema, since optional fields may not appear in every payload.

So personally I would use:

Web/schema or sample check -> contract validation -> full raw landing -> typed promotion

and still validate the landed payload as a second guard before promoting it to Delta.

I also couldn't find a currently published Fabric roadmap item for adding REST support to Get Metadata, so I wouldn't design around that arriving at a particular time.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Re: Business Central Data to Fabric

ShivekMaharaj — Sat, 12 Sep 2026 17:35:45 GMT

Hi Saum02,

If this is Business Central Online, I would not build the Fabric solution around direct access to the underlying SQL database.

Microsoft's current Business Central integration guidance recommends using the REST API stack for integrations rather than treating the SaaS database as a directly accessible SQL source.

On the Fabric side, there are a few options worth looking at:

Fabric currently has a Dynamics 365 Business Central connector available as a Dataflow Gen2 source.
For more controlled/custom extraction, the standard Business Central REST APIs are still the preferred interface.
There is also a newer option called BC2Fab, which Microsoft highlighted in the Fabric What's New updates. It is a partner Fabric workload that replicates Business Central data into OneLake using Open Mirroring and incremental change detection, so it may be worth evaluating if your goal is ongoing analytical replication rather than repeatedly extracting through APIs.

If this is Business Central on-premises, then direct SQL access is technically another option because you control the underlying SQL Server. I would still compare that against the API approach before coupling your Fabric model directly to the physical BC database schema.

So the first question I would ask is whether your environment is Business Central Online or on-premises, and roughly how much data / how many tables you need to bring into Fabric.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Re: SharePoint Shortcut showing no Content

ShivekMaharaj — Sat, 12 Sep 2026 16:54:29 GMT

Hi mkjit256,

That extra URL helps quite a lot.

I would not move the files into Shared Documents yet. Microsoft's current OneDrive/SharePoint shortcut documentation does not state that shortcuts are limited to the default Documents library. In fact, Microsoft recommends creating the shortcut at the most specific folder level rather than at the document-library root.

From the URL you posted, it looks like:

BIA is the SharePoint site
Operations is the display name of the document library
Ops is probably the library's internal URL name
PowerBI Data/Output is the folder you actually want

So the target path appears to be something like:

/sites/BIA/Ops/PowerBI Data/Output

The fact that the Fabric picker only exposes Shared Documents therefore looks more like a library-enumeration issue than proof that your custom library is unsupported.

One useful isolation test would be to create a fresh connection against the exact site URL:

https://companyname.sharepoint.com/sites/BIA

using the same organizational account that can browse the Ops library in SharePoint, then check whether Ops appears in the shortcut browser.

If it still only shows Shared Documents, I would capture that as the repro.

There is also a OneLake Shortcuts REST API where a SharePoint shortcut is represented by a location and subpath. That may be worth testing as a workaround because the API lets you specify the SharePoint subpath directly rather than relying on the folder picker.

I would use the library's URL/internal name (Ops) rather than its display name (Operations) when testing the path.

Also confirm that BIA is a normal SharePoint site rather than a subsite or hub site, because Microsoft currently lists SharePoint subsites and hub sites as unsupported for OneLake shortcuts.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Re: Business Central Data to Fabric

NandanHegde — Sat, 12 Sep 2026 14:52:03 GMT

You cannot directly access the underlying database
similar thread : Accessing BC Data

Re: Business Central Data to Fabric

Olufemi7 — Sat, 12 Sep 2026 14:17:35 GMT

Hello Saum02,
If you're on BC Online, I will look at the Business Central APIs first rather than building the Fabric connection around the underlying SQL database.

Microsoft recommends the REST API stack for Business Central integrations.For BC Online, I will start with the Business Central APIs rather than building the Fabric solution around the underlying SQL database.

If this is an on-premises deployment, SQL access is another route to consider, depending on what you need to extract.

Is your BC environment Online or on-premises?

Microsoft docs:
Integration overview for Business Central (for architects and developers)
Introduction to Microsoft Fabric and Business Central

Pure Python Notebook: authenticate to Azure DevOps Artifacts using Fabric Workspace Identity

llueg — Sat, 12 Sep 2026 12:47:07 GMT

Can a Pure Python Fabric notebook running under a Workspace Identity obtain an Azure DevOps access token so that `pip install --index-url ...` can authenticate without a PAT?

It would be very nice if we could use the Workspace Identity to authenticate directly against a DevOps Feed in order to pull dependencies, as that would allow DevOps to directly target Feed-permissions for that Workspace (in particular, `Feed Reader`). For Spark Notebooks, we can use a Connection to do that, but for Pure Python Notebooks I can't find a documented way.

At the end of the day, one needs a token to pass to `pip` in order to bootstrap the notebook's runtime environment from the Feed, but as far as I can see there is no documented way to generate that token from the Workspace Identity. Currently, I'm using a keyvault'ed PAT. However, while this is fine-ish on the Fabric-side, it requires a dedicated Identity on the DevOps side, which I'd like to avoid.

Re: SharePoint Shortcut showing no Content

DreamITLearn — Sat, 12 Sep 2026 10:13:10 GMT

Hey mkjit256 ,

this is usually one of these:

Delegated vs. app permissions – If your SharePoint connection is using an app-only/service principal auth method rather than your own delegated login, it can browse the site and see the folder structure but fail to enumerate files if permissions weren't explicitly granted at the library level, not just the site level.
Broken/synced files showing as empty – If the "Shared Documents" folder has files synced via OneDrive client or has some in a "pending" state, the shortcut browser sometimes shows nothing even though the web UI displays content, this is a caching mismatch between SharePoint's Graph API response and what you see in browser.
URL trailing slash or casing – Since you're using the sites/sitename pattern, double check there's no trailing slash and the site name casing matches exactly. Fabric's shortcut connector can silently fail to traverse if there's a mismatch.
Folder-level unique permissions – If someone broke inheritance on subfolders inside Shared Documents (common in larger orgs), the connector account may see the parent folder exists but can't read into it, this shows as "No Content" rather than an access-denied error, which is misleading.

Fastest way to isolate it: try creating the shortcut against a brand new, simple test folder with no nested subfolders or broken permissions. If that works, it confirms it's a permissions/inheritance issue on your actual folder, not a connector bug.

Re: Fabric SQL Database vs Warehouse

EduardoCastro — Fri, 11 Sep 2026 20:32:27 GMT

Based on the requirements for building a metadata-driven ETL framework in Fabric, the Fabric SQL Database is the more appropriate choice for the metadata storage layer. It is better suited for OLTP (transactional) needs, while the Warehouse is specifically designed for OLAP analytics. Metadata and logs are OLTP — constant small inserts, updates (status flags, watermarks), and deletes.

Re: SharePoint Shortcut showing no Content

mkjit256 — Fri, 11 Sep 2026 13:19:03 GMT

Hi v-aatheeque you might have missed my comment yesterday. Sorry seems that i didn't reply to you but placed my comment as a general reply.

Business Central Data to Fabric

Saum02 — Fri, 11 Sep 2026 11:18:28 GMT

Hii All,

Has anyone explored the BC connection to it's underlying SQL database? Is there limitation to this approach? I need to get data from BC to fabric, is SQL database of BC worth exploring?

Re: SharePoint Shortcut showing no Content

v-aatheeque — Fri, 11 Sep 2026 10:38:24 GMT

Hi mkjit256

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

All Data Engineering posts

Approach for automating Dataflow Gen2 connection binding across Dev/Prod?

Re: Pure Python Notebook: authenticate to Azure DevOps Artifacts using Fabric Workspace Identity

Re: New Browser Tab Opening to Previous or Existing Session

Re: GraphQL update... mutation for Fabric SQL DB failing

Re: Is a table ordinary, or special (= from a shortcut, a system table or materialized lake view table)?

Re: GraphQL update... mutation for Fabric SQL DB failing

From On-Premises SQL Server to Microsoft Fabric with a Metadata-Driven Medallion Pipeline

Architecture

One metadata file, three stages

Uploading the artifacts with Fabric CLI

Stage 1: SQL Server to Bronze

Stage 2: Bronze to Silver

Stage 3: Silver to Gold

Monitoring the complete flow

Result

Further reading

Re: DataFlow Gen2 Issue

Re: DataFlow Gen2 Issue

Re: Bronze data contracts for REST/API sources — workaround for Get Metadata's connector gap

Re: Business Central Data to Fabric

Re: SharePoint Shortcut showing no Content

Re: Business Central Data to Fabric

Re: Business Central Data to Fabric

Pure Python Notebook: authenticate to Azure DevOps Artifacts using Fabric Workspace Identity

Re: SharePoint Shortcut showing no Content

Re: Fabric SQL Database vs Warehouse

Re: SharePoint Shortcut showing no Content

Business Central Data to Fabric

Re: SharePoint Shortcut showing no Content