Data Engineering topics

How Can Teams Automate Data Workflows and Reduce Manual Operational Tasks?

codeautomation — Tue, 03 Nov 2026 17:41:12 GMT

As businesses handle increasing volumes of data, managing workflows manually can become time-consuming and difficult to scale.

I'm interested in understanding how teams are automating their data operations, particularly when multiple systems and business processes need to work together.

A few areas I'd like to explore:

How do you automate repetitive data processing and validation tasks?
What approaches do you use to handle failed pipeline executions and retry mechanisms?
How do you coordinate notifications, approvals, and downstream business processes?
Are you using Microsoft Fabric, Power Automate, Azure services, or custom automation solutions?

I'd appreciate hearing about practical implementations, tools, and lessons learned from your experience.

What has worked best for your team?

Can a metric name form part of a composite business key in a Fabric Silver Lakehouse table?

SamyAbdul — Tue, 03 Nov 2026 14:40:54 GMT

Hi experts,I am preparing a Hotel Guest Return Metrics dataset for the Silver layer in Microsoft Fabric. The available columns are:

Hotel Name
Hotel ID
State
Metric Name
Number of Checkouts
Footnote
Excess Return Ratio
Predicted Guest Return Rate
Expected Guest Return Rate
Number of Returning Guests
Start Date
End Date

There is no separate metric ID or metric code in the source. A hotel can have records for multiple metrics and measurement periods.

The proposed candidate business key is:

Hotel ID + Metric Name + Start Date + End Date

Uniqueness has not yet been validated. My concern is using a descriptive text field such as Metric Name to identify records, particularly if its wording changes between source releases.

For Silver-layer deduplication and Delta MERGE operations:

Is this a defensible business key if profiling confirms uniqueness and non-null values?
What checks would establish whether Metric Name is sufficiently stable for this purpose?
If metric names can change and no source metric code exists, how should record identity be managed across releases? Would a maintained metric mapping help distinguish a renamed metric from a new metric?

I am asking about reliable business-record identification, rather than simply generating a unique surrogate ID for each row. Thank you in advance.

SAP S/4HANA and Dynamics 365 reporting in Fabric: sync the ERPs first or model on top?

kiara-mehra35 — Tue, 03 Nov 2026 14:32:36 GMT

Hi all,

While working on an SAP S/4HANA and Dynamics 365 integration project at DataToBiz, we ran into a reporting design question that I’d like to compare with how people here would approach it in Fabric.

The situation was two ERPs running side by side with manual data exchange between them, plants following non-standard workflows, and leadership lacking a single view across finance, procurement, inventory and production. We handled the integration with SAP Integration Suite, APIs and middleware, which synchronize master data, purchase orders, inventory records, financial transactions and production updates between the two systems, with automated pipelines for standardization, validation and transformation. Power BI sits on top for reporting, and RBAC, encrypted integrations and audit logging cover security and governance.

So the reporting side reads from data that has been synchronized at the integration layer. If I were designing it in Fabric, I’d want to hear whether you’d do it the same way, so here are my questions.

Would you still sync master data between the ERPs through middleware and report from there, or leave the two systems separate, bring both into a Lakehouse and build a conformed model there? I can see the argument for each, but I’m not sure which one holds up better as more plants are added.

For getting the data into Fabric, what has worked for you on the SAP S/4HANA side at real volumes: Data Factory’s SAP connectors, Dataflows Gen2, or something else? And on the Dynamics 365 side, how do you bring the data in without hurting the source system?

Where do you handle master data matching and de-duplication when plants and business units define things differently? Is a Spark notebook in the Silver layer enough, or do you keep a separate mastering process?

And for security, how do you keep role-based access consistent across SAP, Dynamics and Fabric? Do you map the same roles into RLS in the semantic model, or restrict access earlier in the pipeline?

If you’ve built reporting across two ERPs, or looked at it and kept the integration approach, I’d really like to know what worked for you.

Thanks, and I’d love to hear how you’ve handled it.

Copy Job from on premise Oracle DB causing discrepancy in number columns

mkj1213 — Tue, 03 Nov 2026 05:14:57 GMT

Copying numeric data from an Oracle view to a Fabric Lakehouse produces inconsistent results. Some Batch/Append tests preserve the values, while Snapshot + Incremental / Upsert tests and a separate cast-test destination contain incorrect numbers.

Source types

The four affected Oracle fields are NUMBER columns. Column1 and Column2 have scale 0; precision and scale are unspecified for Column3 and Column4.

Example

For one anonymized sample row:

Column	Oracle value	Incorrect Lakehouse value
Column1	23	1822247737828079764651510857728
Column2	16	1267650600228229401496703205376
Column3	4022.13	31866596929349802.016512092896493568
Column4	900	71305346262837.903834189555302400

The incorrect values for Column1 and Column2 equal the Oracle values multiplied by 2^96

The expected values appeared in Oracle query results and previews. The corrupted values were observed in the Lakehouse through its SQL analytics endpoint and Spark.

Tests and results

1-Snapshot + Incremental / Upsert, table source: A full-table copy produced correct sampled integer fields, but incorrect number columns values.

2- Snapshot + Incremental / Upsert, query source: A full-column SELECT * copy also produced corrupted numeric values.

3- Batch/full copy + Append, targeted casts: A one-row test cast ID-like fields to NUMBER(18,0) and amount/quantity fields to NUMBER(28,8); the sampled values matched Oracle.

4- Batch/full copy + Append, high-precision casts: Three quantity samples cast to NUMBER(38,18) matched Oracle.

5- Batch/full copy + Append, uncast tests: An uncast filtered query and an uncast seven-column table copy also preserved the tested values.

6- Snapshot + incremental /upsert, query source with Separate cast-test destination: The sample row remained corrupted there as well, so casts did not fix that tested path.

Is there any limitation related to read mode as watermark mode, and write mode as merge on key columns when using copy jobs from oracle DB's? and how can i resolve this issue.

Thanks

Odoo, SCADA and legacy data on Databricks and Delta: would you move it to Fabric?

kiara-mehra35 — Mon, 02 Nov 2026 10:30:30 GMT

Hi all,

While working on an Odoo-integrated data and AI platform at DataToBiz, we made some architecture choices that I’d like to compare with how people here would handle them in Fabric.

The goal was to bring Odoo ERP, SCADA and IoT data, and a set of legacy applications into one platform for near real-time operational reporting and predictive use cases. We built it on Azure: Data Factory and APIs for ingestion (batch and near real-time), Data Lake Storage Gen2 with a Delta Lake layout in Bronze, Silver and Gold, and Databricks with Spark for transformation, cleansing and business rules. Power BI semantic models with DAX and row-level security sit on top. Azure ML runs the Python-based machine learning and deep learning models for predictive maintenance, demand forecasting and anomaly detection. Purview, Azure AD, encryption and RBAC cover governance, and Azure DevOps handles CI/CD across Dev, Test and Production. We made no changes to core Odoo modules, so everything is done on the integration side.

If I were building it today, I’d want to rethink some of it, so here are my questions.

Since the lake is already Delta, how smooth would a move to a Fabric Lakehouse be? Would you keep Databricks and expose the data through OneLake shortcuts, or move the Spark work into Fabric notebooks? What would make you decide one way or the other?

With SCADA and IoT streams alongside batch ERP data, where would you put the line between Eventstreams with an Eventhouse and a Lakehouse with frequent micro-batches? For operational monitoring, is the extra complexity of real-time worth it?

For Odoo ingestion, do you pull through the API or replicate from the underlying database? How do you handle incremental loads and deletes without touching the Odoo side?

And for predictive maintenance, we trained and served the models in Azure ML and fed the results into Power BI. Would you keep that split, or run everything inside Fabric’s data science experience?

If you’ve made a similar move, or kept Databricks alongside Fabric on purpose, I’d really like to know what drove your decision.

Thanks, and I’d love to hear how you’ve approached it.

Fabric Warehouse blocking caused by a sleeping Spark session with an open transaction

BIstvan — Mon, 02 Nov 2026 08:26:48 GMT

Hi everyone,

I've been running a Fabric notebook that writes data to a Warehouse table for about 1.5 years without any issues.

The notebook uses this code:

sanitized_df_df_clean.write.mode("overwrite").synapsesql(target_tbl)

Recently, I had a problem where several Power BI refresh queries were blocked.

After checking the Warehouse sessions, I found that session 52 was sleeping but still had an open transaction (open_transaction_count = 1).

Looking into queryinsights.exec_requests_history, I noticed that the Spark connector created two sessions.

Session 52 executed SET IMPLICIT_TRANSACTIONS ON and some metadata queries (SELECT TOP(1) and sp_describe_first_result_set), but apparently never committed or rolled back.

Session 55 executed TRUNCATE TABLE, loaded the data using COPY INTO, and successfully committed the transaction.

Both sessions belonged to the same Spark application.

I ran KILL 52, and the blocking disappeared.

The strange part is that I reran the same pipeline afterward, without changing anything, and everything worked normally. No blocking and no open transactions.

Has anyone experienced something similar with the Fabric Spark-to-Warehouse connector?

Why would a session remain sleeping with an open transaction after the notebook finishes? Could this be related to a recent Fabric update? Is there a way to prevent this from happening again without changing the entire notebook or pipeline?

Since this has been working fine for 1.5 years, I'd really like to understand what might have changed.

Thanks in advance for any suggestions!

Single wide table and dimensional schema

SamyAbdul — Sat, 31 Oct 2026 18:36:49 GMT

Hi experts, I have asked similar question here,

https://community.fabric.microsoft.com/discussions/ac_dataengineering/layered-approach-for-lh/5378549

But the requirements have become clear now, for just one data source we have 4 data sets, some elements /fields are common but the granularity levels are different .We are considering two approaches:

Retain four cleaned, standardized Silver tables, preserving their respective grains and source releases. Build a shared Hotel dimension and separate fact tables in the Gold Warehouse.
Consolidate them into one wide Silver table, pivoting admissions measures into columns and selecting the source releases needed to produce one row per hotel per defined snapshot. Build the Gold dimensional model from that consolidated table.

How should differing observation periods and independent source releases influence the design? Would a shared Hotel dimension with separate Gold fact tables support this reporting requirement without an additional wide table? What would be your expert advice, just to move silver layer by standardizing the business naming conventions of columns or attributes, and do all the dimension and fact model in the gold which will be warehouse. Thank you

Lakehouse Lookup Activity No Longer Shows T-SQL Query Option

Alaahady — Fri, 30 Oct 2026 19:12:54 GMT

Hi,

I have a Microsoft Fabric pipeline that uses a Lookup activity to execute a SQL query against a Warehouse and return a single value.

When I use a Lakehouse connection in the same Lookup activity, I can only select a table. The T-SQL Query (Preview) option is currently disabled. I was able to use this option approximately a month ago.

Is this functionality no longer supported for Lakehouse connections? What is the recommended approach for executing a T-SQL query against a Lakehouse from a Fabric pipeline and returning a single value, such as:

SELECT MAX(rec_mod_dtz) AS last_update FROM fact_survey_detail;

Layered approach for LH

SamyAbdul — Fri, 30 Oct 2026 12:36:35 GMT

Hi experts, current we are ingesting the data in bronze layer as of the source format in Excel /CSV format. Now we have to transition to Silver layer and here lies the challenge, we are told by business the parquet format is preferred, and tabular structure is expected, I understand it is stored in par1 ,part 2... and transaction log. This is not warehouse to be in tabular format , how to navigate. Please help

30+ banking source systems on ADF and Synapse: would you rebuild on Fabric today?

kiara-mehra35 — Fri, 30 Oct 2026 12:35:00 GMT

Hi all,

While working on an enterprise banking data platform at DataToBiz, we made some architecture decisions that I’d like to compare with how people here would approach the same problem in Fabric.

The platform consolidates 30+ source systems (core banking, payments, lending and CRM) that used to sit in isolated on-prem environments, with fixed-schedule batch pipelines that always left risk and finance reporting a step behind. We built it on Azure: Data Factory with metadata-driven pipelines (scheduled and incremental loads, plus secure REST integrations), Data Lake Storage Gen2 with raw, curated and consumption layers, and Synapse with Spark for transformations, data quality checks and aggregations. Purview handles cataloging, lineage and classification, Power BI serves operational dashboards, regulatory reports and self-service, Key Vault and Azure AD cover security, Azure DevOps handles CI/CD, and Azure Monitor with Log Analytics covers alerting.

It runs reliably, but if I were starting this today, I’m not sure I’d build it the same way, so here are my questions.

Would you rebuild on Fabric, or keep ADF, Synapse and ADLS Gen2 in place? If you did migrate, is it realistic to do it in stages, for example by using shortcuts to the existing lake first, or does a half-migrated setup cause more problems than it solves?

We relied heavily on metadata-driven pipelines. How have you ported that pattern to Fabric Data Factory, and did you run into any limits with parameterization or config-driven orchestration at this number of sources?

On latency, we were still running scheduled batch. For a regulated environment, would you push for near real-time with mirroring or Eventstreams, or does a shorter batch window cover most risk and finance needs?

And for governance, how do you keep Purview, Fabric’s own lineage and access controls consistent when auditors want a clear, end-to-end answer? Does it hold up in practice, or do you still stitch it together?

If you’ve done this migration, or looked at it and decided to stay where you were, I’d really like to know what tipped it for you.

Notebook parameterization: parameter cells vs pipeline base parameters vs Variable Library?

jubinsoni — Fri, 30 Oct 2026 05:27:35 GMT

Hi everyone,

For Fabric notebooks that run across dev, test and prod workspaces, I am weighing three ways to pass configuration (lakehouse names, source paths, feature flags):

A parameters cell that the pipeline Notebook activity overrides with base parameters.
Reading a config table or JSON file from a lakehouse at the start of the run.
Variable libraries, resolved per workspace through deployment pipelines.

A minimal version of the first option looks like this:

# parameters cell (toggled as a parameter cell in the notebook) env = "dev" source_path = "Files/landing/orders/" full_reload = False

Questions:

Which option has held up best for you when promoting between workspaces?
How do you keep secrets and connection details out of notebook code in each approach?
Is there a downside to variable libraries that I should know about before standardizing on them?

Thanks in advance.

Reading on-prem SQL Server / Oracle from a Fabric notebook: is the gateway really not an option?

vedant_9 — Wed, 28 Oct 2026 07:15:15 GMT

Hi all,

We’re moving our ingestion framework from Databricks to Fabric and have run into a connectivity problem.

The setup is metadata-driven. A single PySpark notebook reads a control table in a lakehouse and loops through about 200 source tables. For each one it builds a JDBC connection, gets the credentials from Key Vault, pulls only the new rows using a watermark, and appends them to a Delta table in our bronze lakehouse. It’s worked well for years on Databricks, and in Fabric it’s fine for our Azure SQL and public sources.

The trouble is our on-prem databases (SQL Server, Oracle and a couple of MySQL). From the notebook, a simple read like this:

df = (spark.read.format("jdbc") .option("url", "jdbc:sqlserver://erp-sql.corp.local:1433;databaseName=ERP;encrypt=true;trustServerCertificate=true") .option("dbtable", "(SELECT * FROM sales.Orders WHERE RowVer > 1000 AND RowVer <= 2000) src") .option("user", user) .option("password", pwd) .load())

just times out: “The TCP/IP connection to the host erp-sql.corp.local, port 1433 has failed… connect timed out”.

We do have an on-premises data gateway (two nodes). It works fine from pipelines and Dataflows, but as far as I can tell notebooks can’t route through it. I found an idea on the Ideas forum asking for exactly this, still in “New” status.

The two options I can see:

Use a Copy activity through the gateway to land each table as Parquet in the lakehouse Files area, then have the notebook pick it up from there. This works, but it means a ForEach with a Copy activity per table. I’m not sure how well that scales to ~200 tables, or how best to pass each table’s incremental window (lower/upper watermark) into the source query.
Put a Private Link Service in front of the on-prem databases (we have connectivity via ExpressRoute) and create a managed private endpoint from the workspace, so Spark can connect over JDBC directly. I’ve seen a blog post about this, but parts of it seemed to be in preview.

A few questions for anyone who’s been through this:

Is gateway support for notebooks (Spark or Python) on the roadmap, or is there already a supported way I’m missing?
Has anyone run option 2 in production against on-prem SQL Server or Oracle? Was it reliable, and did you notice slower Spark session start-up once managed private endpoints were in place?
If you went with option 1 at this kind of scale, did you use a single parameterised Copy activity inside a ForEach, or Copy job? How did you handle incremental loads per table?
Roughly, which ended up cheaper in CUs for you?

We’re on an F64 capacity with schema-enabled lakehouses, if that matters.

Thanks in advance, any pointers appreciated!

Microsoft Fabric Notebook Alternatives to Private Endpoints and Custom Live Pools?

venkatasuresh_g — Tue, 27 Oct 2026 14:56:43 GMT

We are experiencing challenges connecting Microsoft Fabric notebooks to Azure Key Vault when the Key Vault is configured with public network access disabled.

Our organization follows strict cybersecurity policies requiring Azure Key Vault to remain private, without exposing it to public networks.

CURRENT CHALLENGES

1) Dynamic Outbound IP Addresses

Microsoft Fabric notebooks use outbound IP addresses that can change periodically.
Maintaining an IP allowlist for Fabric is not a sustainable solution, as the IP addresses may change frequently (approximately weekly in our experience).
Our cybersecurity team does not support enabling public network access to Key Vault or continuously updating firewall rules.

2) Private Endpoint Connectivity and Notebook Startup Time

We have explored using Microsoft Fabric Managed Private Endpoints to securely access Azure Key Vault.
However, this approach introduces a significant performance concern.
In our testing, notebook execution takes approximately 5 minutes to initialize, creating substantial overhead for workloads that otherwise complete quickly.
We have multiple scheduled notebooks and pipelines, so this additional startup time affects overall processing efficiency

3) Custom Live Pools and Additional Cost

We understand that Custom Live Pools may help reduce Spark session startup latency by keeping compute resources available.
However, maintaining these resources introduces additional capacity consumption and potentially higher operational costs.
We would prefer to avoid paying for continuously available Spark compute solely to retrieve secrets securely from Azure Key Vault.

OUR REQUIREMENTS

Azure Key Vault must remain private, with public network access disabled.
No dependency on maintaining dynamic Fabric outbound IP allowlists.
Secure authentication using managed identity or another Microsoft Entra ID-based approach.
Minimal notebook startup overhead, especially for short-running notebooks.
Cost-effective architecture that supports multiple production notebooks and scheduled pipelines.

Is there a recommended architecture for Microsoft Fabric notebooks to securely access a private Azure Key Vault without requiring Managed Private Endpoints and Custom Live Pools together?

Is there a way to use Managed Private Endpoints while retaining the faster startup experience of standard Fabric Starter Pools?

Does Microsoft Fabric provide any alternative private networking mechanism, such as trusted service access, workspace identity, or another supported integration, that would allow notebooks to retrieve Key Vault secrets without public network exposure?

Are there any recommended approaches for reducing Spark session initialization time when accessing private Azure resources?

Our primary objective is to maintain security compliance without introducing significant notebook startup delays or unnecessary Fabric capacity costs.

Any recommendations, architectural guidance, or real-world implementation experiences would be greatly appreciated.

Thanks,

Suresh Guddanti

Weaver — An open-source enterprise data engineering framework for Microsoft Fabric

matthiaswongdev — Tue, 27 Oct 2026 12:05:55 GMT

Hi all,

I want to tell you about Weaver, a free, open-source data engineering framework for Microsoft Fabric that we recently released.

I came to Fabric from a Microsoft SQL Server data warehousing background, where we'd spent a decade developing the engineering machinery for managing large, complex data estates.

When I started working with Fabric, I found that while it had many of the underlying capabilities, the engineering experience was fragmented. What I wanted was the same engineering discipline we'd enjoyed with traditional SQL warehouses: a single dependency graph, repeatable builds, incremental loading, fault tolerance, automated testing, production-ready CI/CD, performance optimisation and operational visibility.

I found this wasn't available. Instead, there were different approaches for Lakehouses, Warehouses and Power BI. Much of the tooling designed for SQL tables didn't extend to Lakehouse Files and Delta tables.

This was something I didn't understand. To me, a table is a table is a table. Whether it is loaded with Python, Spark SQL, or T-SQL, it should be able to participate in a single pipeline in the same way.

So we took the lessons from our previous framework, built a new one specifically for Fabric, and made it open source for everyone.

The idea is: Weaver makes Fabric easier to use, and safer to scale.

What does Weaver do?

Lots, some examples include:

Full parity between Lakehouses and Warehouses: Build and load tables or files using Python, Spark SQL and T-SQL using a consistent contract. Supports staging, change detection, incremental, fault tolerance etc.
A single DAG across Fabric: Connect Lakehouses, Warehouses, semantic models and reports in one dependency graph, coordinating their build, load and test workflows.
Enterprise Power BI: Extend semantic models and reports. Use standard TMDL annotations to apply modelling conventions, write automated DAX testing, and deploy different versions of the reports connected to different sources.
Fully Fabric native: No separate runtimes, no compiled code, no macros, nothing that doesn’t run on Fabric itself. All you need is pip install. Desktop CLI and Notebook have full parity so you can use either. CLI is cooler but not all government departments allow you to use desktop python.
UI & observability: Prepackaged Power BI report to visualise estate health, lineage graphs, ER diagrams, semantic-model bus matrices, load performance, execution timelines. It’s DirectLake so it’s “live” - skip the import and just refresh the visual to see what’s happening.
Performance-tuned for Fabric: Detailed performance tuning tailored to Fabric, including automatic parallel execution across the DAG, with separately configurable concurrency for Spark and T-SQL workloads, as well as other things you wish someone had done for you such as automatic background refresh for SQL endpoint.
Production grade CI/CD: Swap deployment targets with zero code change — just a config file swap. Deployment and orchestration can happen unattended via Notebook.
Developer and AI-agent workflows: A Python API and CLI with human-readable and structured JSON output, designed for rapid, repeatable engineering cycles.

Does it scale?

We stress-tested Weaver with a single dependency graph of approximately 3,000 objects and 4 billion rows, spanning one Lakehouse and two Warehouses. Having a mixture of files, tables, and views.

On an F64 Fabric trial capacity, we achieved approximately 60 minutes for a full load and 15 minutes for an incremental load.

How is it different from dbt?

There's certainly overlap, especially around SQL transformations, incremental loading and testing.

The main difference is scope. dbt primarily addresses data transformations, whereas Weaver is designed to engineer and operate an entire Fabric data estate, including Lakehouse Files, Delta tables, Warehouses and Power BI, within a single engineering framework.

Keen to hear what you think

Hopefully Weaver contributes towards bringing the engineering standards we've long expected of mature enterprise data platforms to Fabric.

Weaver is free and open source under MPL 2.0. pip install weaverstack

Website: https://weaverstack.dev/
Documentation: https://docs.weaverstack.dev/
GitHub: https://github.com/weaverstack/weaverstack

I'm particularly interested in how others are managing code-first data engineering across Lakehouses, Warehouses and Power BI.

Feedback, criticism and contributions are very welcome!

Screenshots

A single DAG across Lakehouse and Warehouse

Run time from 7 hours to 16 minutes at max concurrency 40 across Lakehouse & Warehouse

Mixture of files, Lakehouse tables and views, Warehouse tables and views . ~ 4 billion rows.

Best practices for integrating the Fabric ML Model endpoint with an application (preferably .NET)?

vkeda29 — Tue, 27 Oct 2026 06:42:40 GMT

Hi,

I'm working on a ML use case in Microsoft Fabric where we train models using Fabric notebooks, track experiments through MLflow, and register models for real-time predictions. We have already registered a model and activated its real-time inference endpoint in Fabric. We're now planning to integrate this endpoint with an application (preferably .NET).

I'd appreciate some guidance on the recommended integration approach, particularly around authentication, permissions, and API invocation.

A few questions:

Authentication: What is the recommended way for the backend (.NET) to authenticate with a Fabric ML Model endpoint? Does it support OAuth 2.0 Client Credentials using a Microsoft Entra service principal, and is Managed Identity also supported?
Permissions: What Fabric workspace or ML Model permissions are required for a service principal to invoke an activated endpoint? Are any additional tenant-level settings required?
API integration: What is the recommended approach for calling the endpoint from .NET application, including the request payload format, response handling, and access-token management?
Endpoint behavior: Are there any considerations around synchronous/asynchronous responses, latency, timeouts, or concurrent requests when integrating the endpoint with an application?
Model versioning: If we train and register an updated model version later, can we activate a new endpoint while keeping the existing version available? What is the recommended approach for switching between versions without affecting the application?

If anyone has implemented a similar integration, I'd appreciate any recommended architecture, documentation, or working. Also would like to understand whether there is a secure Fabric-native approach that would be more appropriate.

Thanks in advance!

Best approach to load report-style Excel files from a SharePoint folder into Lakehouse tables

vijith_mar — Mon, 26 Oct 2026 13:29:32 GMT

Hi all,

I’d like advice on the best pattern for loading business-maintained Excel files from SharePoint into a Fabric Lakehouse.

Scenario

Business users upload a monthly Excel file per department to a SharePoint Online document library folder (HSE first; Finance and Internal Audit later).
Each file is year-to-date (Jan up to the reported month). The plan is one file per month, named like HSE_Performance_YYYY_MM.xlsx.
The sheet is a report layout, not a flat table:
- two header rows: month names merged across 3 columns, then HQ / Marine / Contractor
- section header rows inside the data (e.g. “Incident Rates”, “Minor Incidents”)
- ~120 KPI rows, YTD total columns at the end
- formula cells that return #DIV/0!
The target is about 19 Delta tables (one per KPI section, with KPIs as columns), plus a load log table.

Current solution (working in DEV)

A PySpark notebook reads the workbook with openpyxl, detects sections, unpivots and pivots, and writes the 19 tables. This works well when the file is uploaded manually to Lakehouse Files.
Planned logic:
- each run, pick the latest file per year;
- skip files already loaded (name + modified time);
- replace only that year’s rows;
- write all tables or none;
- copy the processed file to an archive folder in Lakehouse Files;
- log success or failure and alert on failure.

Where I need advice

OneLake SharePoint shortcut: our service account has “Can view” access directly on the target folder only, not on the parent folders or the library. The shortcut wizard connects at the site URL and browses down. Is folder-level access enough to create the shortcut, or does the identity need access at library or parent-folder level? Has anyone used folder-only permissions successfully?
Moving files in SharePoint: as I understand it, SharePoint shortcuts are read-only (writing returns “This operation is not supported through shortcuts of account type OneDriveSharePoint”). Is that correct? Is the Graph API (service principal with Sites.Selected) the recommended way to move processed files to Archive / Error folders in SharePoint?
Dataflow Gen2 as a fetch step: Copy activity doesn’t support the SharePoint folder connector, only lists. Has anyone used Dataflow Gen2 just to land the raw sheet into a staging table (all columns as text, errors replaced), then a notebook for the transformation? Any issues with merged cells, error values or column types?
Production authentication: for unattended runs, what do you recommend for SharePoint access: workspace identity, a service account (organizational account) or a service principal? Any limitations with workspace identity for SharePoint shortcuts or the SharePoint folder connector?
Overall pattern: for monthly, business-maintained, report-style Excel files, would you choose:
- (a) shortcut + notebook,
- (b) Dataflow Gen2 fetch + notebook, or
- (c) notebook calling the Graph API directly?
Or is there a better pattern you’ve used?

Volumes are small (one file per department per month, under 1 MB), so simplicity and reliability matter more than performance.

Thanks in advance!

Microsoft Fabric Lakehouse Rename – SQL Endpoint Sync Failure (Error 911)

Murtaza_Ghafoor — Mon, 26 Oct 2026 11:57:17 GMT

I recently renamed my Lakehouses in Microsoft Fabric. After the rename, I'm unable to sync the SQL analytics endpoints, and the following error occurs:

Error message:
Database 'lh_silver' does not exist. Make sure that the name is entered correctly. (Code 911, State 1)

The Lakehouse has been refreshed, but the SQL endpoint synchronization is still failing.

Has anyone experienced this issue after renaming a Lakehouse? Is there a way to refresh or reinitialize the SQL endpoint metadata without recreating the Lakehouse?

Any suggestions or guidance would be appreciated.

Thanks!

Connect to an Azure SQL Server with no public access

nullabee — Fri, 23 Oct 2026 14:09:25 GMT

We have an Azure SQL Server that doesn't permit public access. When accessing it from a Fabric workspace, it looks like we have, essentially, 2 options:

either setup private endpoints and configure these. This has the (huge) disadvantage that startup time for spark environments goes from 5 seconds to 3 minutes. That means every time I use a spark notebook (even when it doesn't actually use the endpoints), it warms up for 3 minutes, which is a lot of overhead (we historically have a lot of short running notebooks, each now facing this slowdown).
or use a data gateway (virtual or on premise). However, when we connect to this same SQL Server from a notebook (using jdbc), it appears that this connection does not pick up on the data gateway (I don't see how it could, anyway). So this means my notebooks cannot communicate to this SQL Server?

(Note: when accessing an ADLS Gen 2 storage (also with public access disabled), there is the option to allow access to it with the workspace identity. A similar option seems not to be present for Azure SQL Server (there is only on option to "Allow Azure Services to access this", but that grants way too much access and probably doesn't affect Fabric connections)?)

Can anybody enlighten me on what is the right approach for this scenario?

So I hope you can tell me:

either you inform me that private endpoints are the way to go, and then have a solution to avoid the long warmup for notebooks
either you say a gateway is the solution and then I hope you can confirm that connections from a notebook straight to this SQL Server will indeed utilize this gateway
or who knows: you may have another option I'm not seeing right now

Proper way to connect to Azure SQL wrt Firewall

nullabee — Fri, 23 Oct 2026 11:39:35 GMT

Hi all

We have an Azure SQL Server that doesn't permit public access (as is the best security practice). When accessing it from a Fabric workspace, it looks like we have, essentially, 2 options (if we want to maintain that best practice):

we setup private endpoints and configure these. This has the (huge) disadvantage that startup time for spark environments goes from 5 seconds to 3 minutes (it took me a LOT of digging through documentation to figure the link between these settings out). That means EVERY time I use a spark notebook, it stands idle for 3 minutes, which is an insane overhead.
or we use a data gateway. However, when we connect to this same SQL Server from a notebook (by jdbc), it appears that this connection does not pick up on the data gateway (I don't see how it could, anyway). So this means my notebooks cannot communicate to this SQL Server?

Maybe I'm missing something, but does anybody have a clue? Also: this feels like a very basic scenario (getting data from an Azure SQL Server to Fabric). I know there are options to use mirroring, or I could get the data by a dataflow or some other non-notebook solution, but in our case: I have to call some stored procedures in the middle of the notebook execution. It would be a huge effort to refactor all of this, so I'm looking for a simple way to connect to an Azure SQL Server that has public access disabled, both from pipelines and notebooks, without a universal "overhead" (note: even if I never actually USE the private endpoint in my notebook, the overhead of 3 minutes is still present).

Note: when accessing an ADLS Gen 2 (also with public access disabled), there is the option to allow access to it with the workspace identity. A similar option seems not to be present for Azure SQL Server (there is only on option to "Allow Azure Services to access this", but that seems not fine grained enough)?

Context: we are migrating from an ADF setup where we had a rather large number of databricks notebooks (called from a load of different pipelines) for relatively small tasks (runtime in the order of seconds). You can see how the overhead of 3 minutes destroys this setup.

Engineering telemetry (commits, builds, AI usage) in Fabric: Eventhouse or Lakehouse?

kiara-mehra35 — Fri, 23 Oct 2026 11:37:54 GMT

Hi all,

We recently worked on an AI-assisted development rollout for a large, globally distributed engineering organization, and the analytics side raised some design questions I’d like to compare with how people here would solve them in Fabric.

The environment had thousands of code commits a day across teams in multiple time zones. To track how the rollout was going, we consolidated commit and build data with Azure Data Factory and Synapse, sent pipeline health and performance logs through Azure Monitor and Log Analytics, and built Power BI dashboards for development KPIs, productivity and AI usage. Purview was used to keep AI-assisted code traceable, with Azure AD and Azure Policy for access and compliance checks.

If I rebuilt it today, here’s where I’d want your opinions.

For commit, build and pipeline events, would you land them in an Eventhouse and query them with KQL, or in a Lakehouse and process them in batches? Since we were already working with Log Analytics data, KQL seems like a natural fit, but I’m not sure how well it works once you need to join with slower-changing data like teams and repositories.

Measuring AI usage is the part I find hardest. Counting suggestions accepted is easy, but tying that to anything meaningful (review time, rework, defects) is not. How have you modeled it so the dashboards don’t end up rewarding the wrong behavior?

Developer-level metrics are sensitive. How do you handle RLS and aggregation so leads can see team-level trends without exposing individual numbers?

And for traceability of AI-assisted code, Purview and Fabric lineage cover the data well, but how do you connect that to the code side? Is there a clean way to keep that link, or does it end up as a separate process?

If you’ve built something similar, or tried and dropped an approach, I’d really like to know what worked and what didn’t.