Architectural Advice: D365 load - Cross Region Fabric link Issue
Hi everyone, We are facing a dilemma due to a shift in our production infrastructure strategy and would love some architectural advice to avoid major rework. Our Current Setup (Dev/UAT): • Dataverse / D365 Environment: Located in Europe. • Fabric Capacity & Workspaces: Located in Europe. • Status: We have already completed the configuration and development in this environment using the native Link to Microsoft Fabric feature. Everything is working perfectly. The Production Challenge: For our Production rollout, leadership wants our main Fabric capacity to reside in the UAE region because Fabric capacity has recently become available there. However, our Production Dataverse environment will remain in Europe. Because the native Link to Microsoft Fabric strictly requires Dataverse and the Fabric capacity to be in the same Azure geography, we cannot link Prod Dataverse directly to a UAE Fabric workspace. Changing our entire integration approach right now would mean throwing away our completed development and starting over. To leverage our current work, we are considering this workaround: 1. Create a Staging Workspace in Europe (matching the Dataverse region) using a small/minimal capacity. 2. Establish the native Link to Microsoft Fabric from the Prod Dataverse environment into this European Staging Workspace. 3. In our main UAE Production Workspace, create OneLake Shortcuts pointing back to the tables synced in the European Staging Workspace. Before we commit to this, we need clarity on a few points: • Is this the best workaround to preserve our current development effort, or does spanning data across Europe and the UAE via OneLake shortcuts introduce severe bottlenecks? • Direct Lake & Query Performance: If we build Power BI reports using Direct Lake mode out of the UAE workspace, will the cross-region network latency (Europe to UAE) degrade report loading times for our end-users? • Licensing Cost Overhead: Does the European staging workspace require a high-tier capacity just to pass data through, or can we run it on a minimal F-SKU (like an F2) just to anchor the Dataverse link? • Alternative Pipelines: If shortcuts across these specific regions are discouraged, should we bite the bullet and rewrite the backend using a Fabric Data Factory Copy Pipeline to physically move data from Europe to the UAE? We would appreciate any insights from anyone who has managed a Europe-to-UAE fabric bridge, or from the Microsoft product team on how to handle this gracefully. Thanks in advance!Git integration and feature branching strategy across Dev Test Prod workspaces in Microsoft Fabric
Hi everyone, We’re setting up a development workflow in Microsoft Fabric with three workspaces: Dev, Test, and Prod. Our items include lakehouses, PySpark notebooks, data pipelines, and semantic models / Power BI reports. We’re using Azure DevOps Git (open to GitHub if it matters). I’d like to understand how teams are handling Git integration and feature development in practice. Git integration across workspaces Do you connect only the Dev workspace to Git, or all three (Dev → dev branch, Test → test, Prod → main)? For promotion to Test and Prod, are you using Fabric Deployment Pipelines, Git-based promotion (PR merges into environment branches), or the fabric-cicd Python library / REST APIs? How do you handle environment-specific values such as lakehouse IDs, connections, and notebook default lakehouse bindings across stages? Feature branches with multiple developers When several features are developed in parallel, how do you set up feature branches? Since a workspace can only be connected to one branch at a time, do you create a separate feature workspace per developer using “Branch out to new workspace”, or does everyone work directly in the shared Dev workspace? If you use feature workspaces, how do you handle lakehouse data? Do notebooks point to the Dev lakehouse, or does each feature workspace get its own lakehouse with sample data? How do you clean up feature workspaces after the PR is merged? Merging and conflicts What’s your PR/merge flow back into the dev branch, and how do you sync the Dev workspace afterward? Have you hit merge conflicts on notebooks, pipelines, or semantic models (TMDL)? How did you resolve them? Gotchas Any item types or limitations that caused problems (unsupported items, shortcuts, SQL endpoint objects, connections)? A short description of your branch structure and promotion flow would help a lot. Thanks in advance!Best approach to load Sybase views into a Bronze Lakehouse: overwrite, append or daily snapshots?
Hi all, We’re loading data from an on-premises Sybase (SQL Anywhere) database into a Fabric Bronze Lakehouse using a metadata-driven pipeline with the Copy activity over an ODBC connection. The sources are views, not tables, so we have no primary keys or modified-date columns for incremental loads. We’re unsure how Bronze should be maintained for these views: Full overwrite: replace the table with the current view data on every run. Append daily snapshots: load the full view every day with a load date. For example, 100,000 rows today and the same 100,000 again tomorrow under tomorrow’s date, so the table keeps growing. Something else: for example, overwrite the table and also keep dated raw files for history. Questions: What is the recommended or best-practice approach for Bronze when the sources are views without change tracking? Is keeping daily snapshots in Bronze common, or is Delta time travel considered enough for history and recovery? If you use full overwrite, how do you protect against a bad load, such as the view returning 0 rows? Thanks!Feedback on a generic Bronze → Silver load pipeline for multiple sources (D365, Sybase, Excel)
Hi everyone, I’m building the Silver layer in Fabric and I’d like a quick sanity check on my approach before scaling it up. What I’ve built One general pipeline for all sources. A ForEach loop runs over a pipeline parameter (a JSON array of tables). Each item has the source schema (d365, sybase, excel), table name, key column, load type (full or incremental) and the columns to select. One generic, parameterised notebook. The pipeline passes values into the parameter cell, and a %run helper notebook resolves lakehouse ABFSS paths, so no lakehouse is attached. Bronze and Silver use the same source-based schemas. Full load: small tables, Excel mappings and Sybase tables are overwritten in Silver on every run. Incremental load (currently for D365 tables via Fabric Link): The watermark is taken from the Silver table itself: max(SinkModifiedOn) of what’s already loaded, with a 1-hour overlap. Only Bronze rows newer than that are read, deduplicated to the latest version per Id, and then MERGEd into Silver (delete if IsDelete, otherwise update or insert). On the first run the Silver table doesn’t exist yet, so everything is loaded. The same setting works for both the first and later runs. Later, I plan to move the table list and watermarks into a Fabric SQL database and replace the pipeline parameter with a Lookup activity. My questions Is one general pipeline and notebook for all sources a good pattern, or is it better to have separate pipelines per source system? Is deriving the watermark from max(SinkModifiedOn) in the Silver table reasonable, or is a separate watermark table clearly better, even at this stage? Is a 1-hour overlap on SinkModifiedOn enough for Fabric Link, and does Fabric Link reliably update SinkModifiedOn when it marks a record as deleted (IsDelete)? For non-D365 sources that have no change column (Sybase, Excel), is a full reload the usual approach, or do you use something like hash-based change detection? Thanks in advance for any feedback!Medallion architecture in Microsoft Fabric D365,Onprem
Hi all, We're designing a medallion architecture (Bronze → Silver → Gold) in Microsoft Fabric for finance and operational reporting. The sources are D365 F&O via Fabric Link, Sybase (through shortcuts) and business-maintained Excel files. We'd appreciate guidance on the overall design, especially where incremental loads and SCD belong. Overall architecture 1. What's the recommended end-to-end structure: separate lakehouses per layer, or one lakehouse with schemas per layer? 2. Should the Fabric Link lakehouse act as Bronze, or should we maintain a separate Bronze layer? 3. What's the recommended role of each layer: Bronze (raw), Silver (cleansed and conformed), Gold (star schema and KPIs)? 4. Lakehouse or Warehouse for the Gold layer? Incremental load: where and how 5. At which layer should incremental loading start: Bronze → Silver, Silver → Gold, or both? 6. Should dimensions be full loads and facts incremental, or should it depend on table size and change frequency? 7. Is SinkModifiedOn a reliable watermark for incremental loads from Fabric Link? 8. In which layer should updates and deletes (IsDelete) be applied using MERGE? 9. Should Gold be built incrementally (for example, with Delta Change Data Feed), or fully rebuilt from Silver? 10. How should incremental loads handle late-arriving or back-dated transactions? SCD: where and how 11. In which layer should SCD be implemented: Silver or Gold? 12. How should we decide which dimensions need SCD Type 2 and which can stay Type 1? 13. Should business-maintained mapping files (for example, account groupings) be tracked with SCD2 or simply overwritten? 14. What's the recommended tool for SCD2 in Fabric: notebook MERGE, Dataflow Gen2, or a Warehouse stored procedure? Facts and snapshots 15. In which layer should periodic snapshot facts (for example, month-end balances) be created? 16. Should snapshots be frozen once created, or recalculated when back-dated changes arrive?Validating a metadata-driven Bronze → Silver incremental load pattern in Fabric
Hi all, I’m building a metadata-driven Bronze → Silver ingestion framework in Microsoft Fabric and would like feedback on the approach before scaling it to a few hundred tables. Setup Source: Dynamics 365 F&O tables land in a Bronze lakehouse as Delta tables through Fabric Link. Each row has a SinkModifiedOn timestamp and an IsDelete soft-delete flag. Config DB: a Fabric SQL Database stores per-table metadata (table name, schema, key column, load type, column list) and the last processed watermark. Orchestration: a Data Pipeline runs a Lookup on the config DB, then a ForEach that calls one generic parameterized notebook per table. The notebook returns the new watermark through notebookutils.notebook.exit(). A Script/Stored Procedure activity writes it back to the config DB only if the notebook succeeds. Target: a Silver lakehouse with one Delta table per source table. Parameter cell (values passed from the pipeline) python table_name = "generaljournalentry" schema = "d365" key_col = "recid" load_type = "incremental" # full / incremental select_cols = "recid,accountingdate,...,SinkModifiedOn,IsDelete" # "" = all columns last_wm = "" # from config DB; "" = first run Load logic python table_name = "generaljournalentry" schema = "d365" key_col = "recid" load_type = "incremental" # full / incremental select_cols = "recid,accountingdate,...,SinkModifiedOn,IsDelete" # "" = all columns last_wm = "" # from config DB; "" = first run df = spark.read.format("delta").load(src_path) if select_cols: df = df.select(*[c.strip() for c in select_cols.split(",")]) new_wm = None # ---------- FULL ---------- if load_type == "full": w = Window.partitionBy(key_col).orderBy(F.col("SinkModifiedOn").desc()) df = (df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn") .withColumn("silver_loaded_on", F.current_timestamp())).cache() new_wm = df.agg(F.max("SinkModifiedOn")).first()[0] (df.filter("coalesce(IsDelete, false) = false") .write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(tgt_path)) # ---------- INCREMENTAL ---------- else: target_exists = DeltaTable.isDeltaTable(spark, tgt_path) # Watermark from config DB, with a 1-hour safety overlap if last_wm: df = df.filter(F.col("SinkModifiedOn") > F.to_timestamp(F.lit(last_wm)) - F.expr("INTERVAL 1 HOURS")) # Keep the latest version per key w = Window.partitionBy(key_col).orderBy(F.col("SinkModifiedOn").desc()) df = (df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn") .withColumn("silver_loaded_on", F.current_timestamp())).cache() # New watermark = max of the batch (deleted rows included) new_wm = df.agg(F.max("SinkModifiedOn")).first()[0] if new_wm: if not target_exists: df.filter("coalesce(IsDelete, false) = false").write.format("delta").save(tgt_path) else: (DeltaTable.forPath(spark, tgt_path).alias("t") .merge(df.alias("s"), f"t.{key_col} = s.{key_col}") .whenMatchedDelete("coalesce(s.IsDelete, false) = true") .whenMatchedUpdateAll("s.SinkModifiedOn > t.SinkModifiedOn") .whenNotMatchedInsertAll("coalesce(s.IsDelete, false) = false") .execute()) # Return the new watermark to the pipeline notebookutils.notebook.exit(json.dumps({ "table": f"{schema}.{table_name}", "new_watermark": str(new_wm or last_wm) })) Questions Watermark handling. The watermark is passed in from the config DB and written back from the notebook’s exit value only after a successful run, so a failed run never advances it. Is a 1-hour overlap a sensible safety margin for SinkModifiedOn? Is there a cleaner way to keep the watermark update and the Delta MERGE consistent if the pipeline fails between the two steps? SinkModifiedOn reliability. For D365 data landed through Fabric Link, is SinkModifiedOn reliable as a monotonic incremental column? Are there known cases of records arriving with an older timestamp? Deduplication. I keep the latest version per key with row_number() ordered by SinkModifiedOn. Should I add a tiebreaker column, such as versionnumber, to handle identical timestamps? Orchestration at scale. For around 200+ tables, is ForEach → Notebook activity per table reasonable? Or are notebookutils.notebook.runMultiple or high-concurrency sessions the better choice to avoid repeated Spark startup overhead? Soft deletes. Is physically deleting rows in Silver on IsDelete = true the common approach, or do people usually keep soft-deleted rows in Silver and filter them in Gold? Table maintenance. Any recommended settings for frequently merged Silver tables, such as OPTIMIZE or V-Order schedule, deletion vectors, or partitioning? Any feedback, gotchas or alternative patterns would be appreciated. Thanks!
How should Microsoft Fabric data pipelines handle AI-driven validation ?
I’m exploring patterns for adding AI-assisted validation into Microsoft Fabric data engineering workflows before transformed data is written to a Lakehouse. For example: Source → Fabric Pipeline / Notebook → Transformation → AI Validation → Lakehouse → Semantic Model I’m particularly interested in: validating schema changes before writes detecting unusual or inconsistent values deciding when to fail the pipeline vs continue with warnings logging validation results for later review retrying failed validation steps keeping the process efficient for larger datasets Would you normally implement this through Fabric notebooks, pipeline activities, or a separate validation service?How are AI agents changing modern data engineering workflows?
I am exploring how AI agents can assist data engineering teams in managing complex data workflows and reducing repetitive operational tasks. Some areas I am interested in: AI agents for monitoring data pipelines and detecting failures Automated data quality checks and anomaly detection Intelligent assistance for ETL/ELT workflow optimization Generating documentation for datasets and transformations Using AI with Fabric pipelines, notebooks, and data workflows With the growth of AI-powered automation, I would like to understand how data engineers are approaching these patterns in real-world environments. What approaches, architectures, or best practices are teams using to combine Microsoft Fabric capabilities with AI agents?