All Data Engineering posts

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

v-csrikanth — Fri, 18 Sep 2026 11:01:16 GMT

Hi llueg
We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Re: Storing and parsing HL7 data

nathancwatkins — Fri, 18 Sep 2026 10:51:10 GMT

Bring in all fields by parsing with |, grab and fill down each Message ID and segment code, make the parsed values a list type, then into JSON, so you have a column containing the values as JSON text for that message ID and segment. The values compress efficiently, and there’s your base ingestion table. Now your ETL query can easily extract values and zip them to a list of field names.

—Nate

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

ezalor — Fri, 18 Sep 2026 06:22:46 GMT

I think my current development direction aligns with this article. The areas I'm still lacking are data quality and AI self-healing. Other than that, I've already covered the AI Agent development part using skills to guide metadata setup. You can take a look at the video to evaluate: Build a Medallion Data Pipeline with DataCoolie + AI | AWS, Fabric & Databricks

Optimising Fabric Capacity Usage

FabricEnjoyer — Fri, 18 Sep 2026 06:08:18 GMT

Hi all,

Due to the recent changes in Fabric capacity metering, I've been spending quite a bit of time trying to optimise our CU consumption. The challenge I've run into is that it's difficult to distinguish between SQL activity generated by user queries versus SQL activity generated during semantic model refreshes, which makes it harder to accurately evaluate different architectural approaches.

Approach 1: Materialising Views as Delta Tables

Historically, our semantic models have queried SQL views hosted in Fabric. To reduce SQL Endpoint consumption, I began converting the T-SQL view logic into notebooks that materialise the results into Delta tables. The semantic models then connect directly to these Delta tables via the ADLS Gen2 connector in Power BI, effectively bypassing the SQL endpoint during refresh.

However, the results have been inconsistent. In some workspaces this appears to reduce overall CU consumption, while in others it actually increases costs because the notebook execution itself incurs significant compute usage.

Current new architecture(old queried straight from omdb sql views):

My questions are:

From a CU consumption perspective, is it generally more efficient to:
- Have semantic models query Fabric SQL views directly, or
- Materialise those views into Delta tables and have semantic models consume the Delta tables through ADLS Gen2?
Has anyone compared the total cost of repeatedly querying views during semantic model refreshes versus the cost of creating and maintaining materialised Delta tables?
While I understand that a star schema is considered best practice for reporting and semantic modelling, if the same business logic can be represented through a set of SQL views, is there still a compelling reason to physically materialise the data into tables from a cost-efficiency perspective?

I've attempted to benchmark both approaches, but isolating the relevant consumption metrics has proven more difficult than expected.

Approach 2: Shifting Transformation Logic to the Semantic Model

Another approach I'm exploring is moving the transformation and view logic out of our Fabric workspace and into semantic models hosted in the client's Power BI Pro tenant.

My assumption is that this would reduce SQL Endpoint consumption within our Fabric capacity because less querying and transformation would occur on our side. However, I would expect semantic model refresh times to increase as more transformation work is pushed downstream.

My questions here are:

Would moving the transformation logic into the semantic model generally reduce or increase overall SQL-related CU consumption?
Have others seen meaningful capacity savings using this approach?
Is there a way to connect one semantic model to another semantic model without using DirectQuery? My concern is that DirectQuery would negatively impact performance and potentially introduce additional query-related costs.

I'd be very interested to hear how others are approaching this, particularly now that SQL Endpoint consumption has become much more visible within Fabric capacity metrics.

I have also reviewed the Query Insights for each workspace in an attempt to correlate SQL activity with overall CU consumption. However, this does not always translate into lower capacity usage. One of the challenges is that many users are querying the SQL Endpoint directly through Excel and other tools, without going through a semantic model at all. Under the new metering model, these ad hoc user queries appear to be a significant contributor to capacity consumption, making it difficult to isolate the impact of semantic model refreshes versus interactive user activity. As a result, determining whether a particular optimisation has genuinely reduced costs becomes far more challenging, as overall CU usage may be heavily influenced by user behaviour outside of the reporting layer.

Thanks in advance!

Re: Storing and parsing HL7 data

Kagiyama_yutaka — Fri, 18 Sep 2026 00:57:19 GMT

Could you add a bit more detail about your setup? How you store HL7 for analytics can change a lot depending on whether you're working with HL7 v2 or FHIR, and whether your workflow is real‑time or batch.

Storing and parsing HL7 data

ipkus — Thu, 17 Sep 2026 21:38:05 GMT

Any healthcare folks can share what best practices they have found around storing HL7 data for analytics ?

Re: Building Self-Healing Data Pipelines

ipkus — Thu, 17 Sep 2026 21:35:27 GMT

Thanks

Re: Fabric Copy Activity fails in Query mode but succeeds in Table mode through an on-premises gateway

ipkus — Thu, 17 Sep 2026 21:34:48 GMT

Looks like the user / SP has permissions to the LH but does not have permissions to SQL endpoint. Make sure use has READALL ( which gives them permissions to SQL Endpoint ).

I suspect Query mode is forcing execution through the Fabric SQL endpoint and the gateway server either:

cannot resolve/reach that endpoint,
is not authorized to use the connection through the gateway,

Re: Copy Job - Amazon S3 connector fails with region signing error on generic endpoint

Sharan_Srini99 — Thu, 17 Sep 2026 21:26:56 GMT

Hi v-achippa ,

Thank you for your reply, I tried that approach and yes, the connection does work. But the problem there is it is mandatory to specify a bucket name. Is there any way to get around this?

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

ipkus — Thu, 17 Sep 2026 21:25:50 GMT

If you want to take it a step further and reach self correcting / self-healing stage take a look at this article

https://medium.com/@rishabh.gulati_94109/ms-fabric-building-self-healing-data-ecosystems-67a7e83c5380

Diagnosing a slow Fabric Data Warehouse using the new skill

arthurfr23 — Thu, 17 Sep 2026 14:18:02 GMT

When a Fabric Data Warehouse slows down, the investigation usually means jumping between the Capacity Metrics app, Query Insights, and SQL pool diagnostics while manually correlating time ranges across three different tools.

Microsoft just shipped the SQL DW operations skill as Generally Available, you can find the official announcement here:

https://community.fabric.microsoft.com/t5/Fabric-Updates-Blog/Diagnose-Fabric-Data-Warehouse-workloads-with-the-SQL-DW/ba-p/5366102

I dug into what this actually changes in practice: how the skill consolidates capacity spikes, query regressions, and request failures into a single conversational interface, and where it still has blind spots worth knowing before you rely on it in a production incident. There is at least one behavior around time range correlation that surprised me during testing.

To do that, I wrote a full article, and would love to hear if others are already using this in their warehouse troubleshooting flow.

https://medium.com/@arthurfr23/diagnosing-fabric-data-warehouse-with-the-sql-dw-operations-skill-investigating-workloads-without-e4060fe8661e?sharedUserId=arthurfr23

Re: Fabric Copy Activity fails in Query mode but succeeds in Table mode through an on-premises gateway

alisaleh — Thu, 17 Sep 2026 12:29:48 GMT

Hi,

Im not sure how this is going to help. I know the query works as I have tested and simplified multiple times by reducing or removing certain filters or reducing columns, however I still got the same error . Not only that but I used Copy Job instead of copy activity and set it to be incremental with using watermark column option on the source table. this kind of setup and despite the source is set to a direct table not TSQL still fails with the same error because internally the copy job will produce query against the source table to fetch data with where clause filter on the watermark column based on last saved watermark value. So Im 100% sure that it doesnt have anything to do with the query. By the way when I created this setup initially my sql query had some syntax issue but I was getting a different error indicating as such and not connectivity error like the one posted in the initial message.

Re: Building Self-Healing Data Pipelines

v-csrikanth — Thu, 17 Sep 2026 10:45:57 GMT

Hi ipkus
Thanks for reaching out to Fabric community.

Re: Copy Job - Amazon S3 connector fails with region signing error on generic endpoint

v-achippa — Thu, 17 Sep 2026 10:37:13 GMT

Hi Sharan_Srini99,

Thank you for reaching out to Microsoft Fabric Community.

Here the error is that the S3 request is being signed for the wrong AWS region. Fabric is signing the request for us-east-1 but the S3 bucket is in ap-southeast-2.

Please configure the Amazon S3 connection using the bucket-specific endpoint instead of the generic regional endpoint:
https://<bucket-name>.s3.ap-southeast-2.amazonaws.com

Then test the connection and rerun the Copy Job. This error is caused by the region mismatch, not by the IAM permissions.

Thanks and regards,
Anjan Kumar Chippa

Re: Fabric Copy Activity fails in Query mode but succeeds in Table mode through an on-premises gateway

v-aatheeque — Thu, 17 Sep 2026 10:20:49 GMT

Hi alisaleh

Thanks for the clarification. That helps narrow down the issue.

Since the same connections work successfully in Table mode, while Query/T-SQL mode fails for both the Lakehouse and SQL endpoint connections, it looks like the issue is specific to the Query mode execution rather than the connection or the query itself.

As a next step, could you please try a simple query such as:

SELECT TOP 10 * FROM <table_name>

If the same error occurs, please share the full error details from the Copy Activity run along with a screenshot of the Source configuration

Re: Can a service principal with federated credentials be used for SQL cloud connection auth?

v-kathullac — Thu, 17 Sep 2026 10:01:03 GMT

Hi CharlieFab ,

Sorry for the above reply that was my mistake. I was about to share the correct debugging steps to help resolve your issue, but I was in a hurry and accidentally posted the wrong steps.

Please find the correct debugging steps below to resolve the issue.

In the current Fabric connection experience, Service Principal authentication supports a service principal key or certificate, as shown in your screenshot, but there is no option to reference an Entra Federated Identity Credential (FIC).

FIC requires the calling service to obtain and present a federated token, and the Fabric-managed refresh process currently doesn't expose a way to configure that federated identity for the connection. So, FIC cannot currently be selected as the credential for this connection.

Since your requirement is to avoid storing a client secret, Service Principal certificate authentication is the available option shown in the connection UI. You would need to manage the certificate lifecycle/rotation and update the connection when the certificate changes.

For your scenario, where the same connection is shared by semantic models across multiple workspaces, using a dedicated Service Principal with certificate authentication can avoid granting every workspace identity access to the SQL database.

Thanks,
Chaithanya.

Re: Business Central Data to Fabric

wardy912 — Thu, 17 Sep 2026 08:48:08 GMT

Hi Saum02, have you considered open mirroring using BC2ADLS?

https://youtu.be/A2HKJ7QDOKU?si=TPnitKnybDcxsuoO

Near real-time BC data in Fabric, 0 capacity usage and free storage (F16 - 16GB, F32 - 32GB etc)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

Copy Job - Amazon S3 connector fails with region signing error on generic endpoint

Sharan_Srini99 — Thu, 17 Sep 2026 03:24:05 GMT

I'm trying to connect Fabric's Amazon S3 connector (Copy activity, pipelines) to a bucket in ap-southeast-2, using Access Key authentication.

When I create a connection with the generic regional endpoint:

https://s3.ap-southeast-2.amazonaws.com

connecting fails with:

Expression.Error: Invalid Url. The authorization header is malformed; the region 'us-east-1' is wrong; expecting 'ap-southeast-2'

I've added s3:ListAllMyBuckets, s3:ListBucket, and s3:GetBucketLocation to the IAM user, and the error persists identically.

Re: Business Central Data to Fabric

CharlieFab — Thu, 17 Sep 2026 01:38:23 GMT

Hi Saum02

It depends on whether you're on BC Online or on-prem.

BC Online: Direct SQL isn't an option, because users can no longer access SQL Server directly once you move to cloud-based BC.

BC on-prem: You can connect to SQL, but I wouldn't build on it long term. Each company has its own tables, extensions add extra tables you have to join, FlowFields aren't stored, and upgrades can change the schema. It also isn't available for BC Online, so it could block a future move to the cloud.

API / Dataflow Gen2 BC connector: This is the supported route and fine for smaller data. For incremental loads, filter on lastModifiedDateTime and run queries on read scale-out.

Re: Can a service principal with federated credentials be used for SQL cloud connection auth?

CharlieFab — Thu, 17 Sep 2026 00:27:34 GMT

Kagiyama_yutaka Thanks for the clarification, the backup option I had was the workspace identities with no key rotation at all