The Source has too many files
Hi experts, we ingesting the data through API , for some data sources the business has shared nearly 150 files for one source, the schema is different sparing one or two similar columns , by going this way it will create too many tables in Lakehouse and it would jump exponentially considering all sources in bronze layer. Shall we have a schema broadly consists of all the fields mind you the metadata or columns are different. It is challenging scenario, appreciate expert's help. Thank you
Proper way to connect to Azure SQL wrt Firewall
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?
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.Replacing ADF and Synapse with Fabric for CRM, HR and inventory data: worth migrating?
Hi all, While working on a low-code and AI enablement project at DataToBiz, we ran into a data integration question that I’d like to compare with how people here would approach it in Fabric. The business workflows were spread across CRM, HR and inventory systems, which meant disconnected processes, manual handoffs and manual reporting cycles, so teams didn’t see their data in time. To fix the data side, we used Azure Data Factory and Synapse Analytics for ingestion and transformation, with Azure Data Lake and Dataverse as the unified storage layer. Power BI Copilot dashboards then gave teams real-time KPI views, and Purview provided lineage and governance, with RBAC and Azure Policy handling secure lifecycle management for the apps and automations. Starting from scratch today, I’m not sure I’d build it the same way, so here are my questions. Would you move the ingestion and transformation into Fabric (Data Factory pipelines, Lakehouse, notebooks) and retire Synapse, or is there a good reason to keep the existing setup? What would you check before migrating? With Dataverse in the picture, how would you bring Power Platform data and the CRM, HR and inventory sources into one place? Would you treat OneLake as the single storage layer, or keep Dataverse separate and just connect to it? For replacing manual reporting cycles, would you use Direct Lake or Import mode for the real-time dashboards? How do you decide when “real-time” is worth the extra complexity? On lifecycle management, how do you keep Power Platform solutions and Fabric items promoted consistently across environments? Are Fabric deployment pipelines enough, or do you run a separate process for the apps? And with reusable templates for the business teams, how do you keep the reports consistent? Do you rely on certified semantic models and endorsement, or something stricter? Would love to hear how you’d approach it.
Best orchestration choice to reduce Fabric capacity usage when transformations run in Snowflake
Hi, I’m looking for advice on reducing Microsoft Fabric capacity usage. The same Fabric capacity is used for BI, so I want to avoid unnecessary data integration workloads affecting BI. Our current flow is: Fabric Data Factory pipelines ingest data from external sources into ADLS. Fabric Data Factory pipelines load data from ADLS into Snowflake Silver. Fabric Data Factory pipelines trigger Snowflake Silver-to-Gold processing. Most transformations run inside Snowflake. Fabric Data Factory is mainly handling ingestion, loading, and orchestration. These pipelines currently run on Fabric’s native schedules. Would moving orchestration to a Fabric Apache Airflow Job reduce Fabric capacity consumption, or would it add more capacity usage while the Data Factory pipeline activities still run in Fabric? For this type of setup, what approach would you recommend to reduce Fabric capacity impact while keeping reliable scheduling and monitoring?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!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!
Best approach(es) to refresh Dev Warehouse from Prod Warehouse (Read-Write Copy) in Microsoft Fabric
We are currently evaluating the best practices for refreshing a Development Data Warehouse using data from a Production Data Warehouse within the same Fabric tenant. We have evaluated a few approaches based on the use case: Read-Only Access (Zero-Copy): If Dev only needs read access to Prod data, using OneLake Shortcuts is the clear choice. It provides instantaneous access with zero data duplication, no storage overhead, and minimal setup effort. Read-Write Access (Full Schema & Data Copy): If Dev requires a Read-Write copy (allowing developers to run DML operations like UPDATE, INSERT, or DELETE without impacting Production), our current options seem to be: Fabric Data Pipelines (Copy Activity): Scheduled or trigger-based pipelines to pull data from Prod to Dev. Cross-Database Querying / CTAS: Running T-SQL (CREATE TABLE AS SELECT ...) across workspaces. The Challenge: Both Read-Write options (Pipelines and CTAS) can become cumbersome to set up and maintain, especially as datasets grow. Additionally, duplicating large volumes of data incurs extra storage and CU (Capacity Unit) processing costs. Was wondering if there are better, more efficient options available in Microsoft Fabric to maintain or periodically refresh a Read-Write copy of a Warehouse in a Dev workspace? Is Table Cloning / Zero-Copy Clone supported or planned for cross-workspace scenarios? How are others managing periodic Dev environment refreshes from Prod without creating heavy data copy pipelines?Best approach for metadata-driven load of Sybase tables to Fabric Lakehouse
Hi all, We need to load multiple tables from an on-premises Sybase SQL Anywhere database into a Fabric Lakehouse (bronze layer) through an on-premises data gateway. The pipeline should be metadata-driven: a control table lists the source and destination tables, and a ForEach loop processes them. We tried a parameterized Dataflow Gen2 called from a ForEach. It failed on tables with different column structures, which seems consistent with the documented limitation that public parameters can't modify destination schema mappings. Questions: What is the recommended approach in Fabric for a dynamic, metadata-driven load of Sybase tables: Dataflow Gen2, Copy activity with the ODBC connector, or something else? If you've implemented this in production, what worked well and what limitations did you hit? Thanks!