Red Flag in SQL Analytics EndPoint - User Identity Access Mode
We are seeing some red flags when switching the default access mode "Delegated" to ""User's Identity" in a SQL Analytics EndPoint of lakehouse, but seeing red flags with message not relevant to the user who were given access to downstream lakehouse, below is the details. Current Configuration: LH1 is in the hub workspace/WS1 with Onelake security role where users were added. In the user workspace/WS2, a shortcut to LH2 the source data is created, user can access through LH2’s SQL Analytics Endpoint, data access mode is switched to User’s Identity, so that source lakehouse's security role control access fully. In SQL End, there are some red flags as below, but user can access data without issue, which is confusing: error details: User principal is not supported. Issue: Error details is specifically for an AD group which is a workspace contributor role in the source workspace role (default reader of all tables in the source lakehouse), and it doesn't have access to the user's lakehouse. Question: Does anyone see the same? or it's a known issue?Upsert implementation in Fabric Lakehouse
Hi Fabric Community, We are building a data platform in Microsoft Fabric using Salesforce and Marketo data, following the Medallion Architecture (Bronze / Silver / Gold). We are currently considering whether to use Replace or Upsert when writing data from Bronze to Silver and from Silver to Gold. Our current assumptions are: Bronze → Silver Salesforce Lead / Contact / Account / Opportunity, etc.: Upsert, because existing records can be updated Marketo Person, etc.: Upsert, because existing records can be updated Marketo Activity: Deduplicate and Append, since it is event/log data Silver → Gold Silver data is transformed and joined to create Gold tables optimized for AI use. Dataflow Gen2 with Replace is one option, but we are concerned about processing time and Capacity Unit (CU) consumption as the data volume grows in the future. My understanding from Microsoft Learn is that Dataflow Gen2 supports Replace / Append when writing to a Lakehouse, while Pipeline Copy Activity supports Upsert to a Lakehouse Table and allows key columns to be configured through the UI. Therefore, we would like to avoid using Notebook / Spark Delta MERGE where possible and are considering a low-code/no-code approach where Dataflow Gen2 handles transformations and Pipeline / Copy Activity handles the Upsert into the Lakehouse table. For example: Bronze → Silver Bronze → Dataflow Gen2 (filter/transform changed records) → temporary staging table → Copy Activity (Upsert) → Silver Silver → Gold Silver → Dataflow Gen2 (Gold transformations) → temporary staging table → Copy Activity (Upsert) → Gold However, we do not assume that a staging table is necessarily required. If there is a simpler no-code/low-code pattern for taking the output of Dataflow Gen2 and Upserting it with Copy Activity, we would prefer that approach. I would appreciate your advice on the following questions: Is using Dataflow Gen2 for transformation and Pipeline / Copy Activity for Upsert into a Lakehouse table a recommended pattern in Fabric? We are considering using a temporary staging table between them, but is there a simpler approach? Also, are there any important limitations or considerations compared with using Notebook / Delta MERGE? When the number of changed records per day is small compared with the total table size, can we generally expect Copy Activity Upsert to consume less processing time and fewer CUs than a full Replace? Since Upsert also has costs for key matching and updating Delta files, are there any guidelines or practical thresholds for choosing between Replace and Upsert based on table size or percentage of changed records? Would you also recommend the same Upsert approach for Silver → Gold? If a Gold table is created by joining multiple Silver tables, a change to one Silver record may affect multiple Gold rows. In such cases, is it generally better to Replace the Gold table rather than identify all affected rows and Upsert them? If the goal is to prioritize no-code/low-code implementation while also considering future data growth and CU consumption, which write method and Fabric feature would you recommend for Bronze → Silver and Silver → Gold? Any best practices or real-world experience with similar Fabric architectures would be greatly appreciated. Thanks in advance!
How to efficiently maintain complex multi-table aggregations incrementally in Microsoft Fabric?
How to efficiently maintain complex multi-table aggregations incrementally in Microsoft Fabric? Comparison with Snowflake Dynamic Tables Hi everyone, I have a scenario where source data is continuously/incrementally loaded into Microsoft Fabric. We have multiple entities such as Customer, Product, Orders, Sales, Inventory, Invoices and Payments. We need to create business-ready/precomputed tables by joining multiple entities and performing aggregations. For example, a simplified transformation could be Customer + Orders + Product, followed by joins and aggregation by Customer, Product and Month. The challenge is not simply adding new records. A new incremental record can change an existing aggregation group. For example, suppose we already have Customer C001, Product P001, Month Jan and Sales of 1,000. If a new order arrives for the same Customer, Product and Month with Sales of 250, the existing aggregate needs to become 1,250. This becomes more challenging when the transformation involves multiple joins, GROUP BY, aggregations, DISTINCT counts, multiple related entities, and updates or deletes in source data. If we rebuild the complete precomputed table during every refresh, a large amount of historical data may need to be recomputed. As data volume and the number of entities increase, this could result in more data, larger transformations, more compute and longer refresh times. We are therefore trying to determine the best incremental maintenance pattern for these types of tables in Microsoft Fabric. SNOWFLAKE COMPARISON One approach we have been looking at in Snowflake is Dynamic Tables. Dynamic Tables provide a declarative way to define transformed or materialized results and allow Snowflake to manage refreshes based on changes and the configured refresh mode and target lag. The capability we are particularly interested in is maintaining the derived result incrementally instead of treating every refresh as a complete rebuild. We also understand that Snowflake Dynamic Tables do not make every transformation automatically incremental. Query shape, joins, aggregations and unsupported constructs can affect whether incremental refresh is possible. FABRIC OPTIONS WE ARE EVALUATING The first option we are evaluating is Lakehouse Materialized Lake Views (MLVs). MLVs appear to provide a similar architectural pattern where source tables are transformed into a persisted materialized result and the platform manages refresh. We are particularly interested in the optimal refresh and incremental refresh capabilities. We would like to understand how well MLVs handle transformations involving multiple joins, GROUP BY, aggregations, large historical datasets and new records that affect existing aggregation groups. For example, if Customer, Orders and Product are joined and aggregated by Customer, Product and Month, and a new order affects an existing historical Customer + Product + Month group, how does MLV incremental refresh handle this? The second option we are considering is maintaining physical precomputed or serving tables in Fabric Warehouse. The architecture would be roughly Lakehouse, Clean and Validated Data, Transformation and Aggregation, Warehouse Precomputed or Serving Table, Semantic Model and Power BI. One possibility is to use CTAS or staging-based patterns to generate the serving tables. However, if the transformation is rebuilt from the complete historical dataset, we may still end up recomputing a large amount of data during every refresh. We would therefore like to understand whether there is a recommended Fabric pattern for maintaining these physical tables incrementally, particularly when new or changed source records can affect existing aggregate groups. WHAT WE ARE TRYING TO DETERMINE We are not trying to claim that one approach is better than another. We are trying to identify the closest Fabric architecture to the incremental-maintenance capability we are familiar with from Snowflake Dynamic Tables. Can MLVs efficiently maintain complex multi-table joins and aggregations incrementally as data grows? What types of SQL transformations cause MLVs to fall back to full refresh? How does MLV incremental refresh behave when new records modify an existing aggregation group? For complex transformations, is it better to break the logic into multiple MLVs or intermediate layers? If using Fabric Warehouse physical serving tables, what is the recommended pattern for avoiding full historical recomputation? Are there Fabric-native patterns for identifying and recomputing only the affected partitions, keys or aggregation groups? For large enterprise datasets, what approach have others found most scalable and maintainable? OUR CURRENT ARCHITECTURE Our architecture is roughly Source Systems, Bronze, Clean and Validated Data, Precomputed or Serving Tables, Gold or Consumption Layer, Semantic Model and Power BI. The objective is to perform expensive joins and aggregations during data processing rather than repeatedly during interactive BI queries. The open question is how best to maintain the Precomputed or Serving layer incrementally as source data continues to grow and change. I would really appreciate input from anyone who has implemented this at scale in Microsoft Fabric, particularly comparisons between Fabric MLVs, Warehouse-based serving tables and other Fabric-native incremental processing patterns. Thanks in advance!
Variable Library binding to OneLake SharePoint Shortcut Target Subpath is not persisted
Hi, I'm trying to use a Fabric Variable Library to manage SharePoint Shortcut paths across DEV, QA and PRD. I use the same SharePoint connection in all environments, but different document libraries: DEV: Shared Documents QA: Quality PRD: Production For example, I created a variable for the dimensoes shortcut with: DEV: /sites/Analytics/Shared Documents/dimensoes QA: /sites/Analytics/Quality/dimensoes PRD: /sites/Analytics/Production/dimensoes In DEV, I go to Manage shortcut → Edit target → Target subpath, select the variable and save. However, after saving, the variable association seems to disappear and the shortcut keeps only the resolved DEV path. When deploying to QA, even though the QA Value Set is active, the shortcut still points to the DEV path. If I change it manually to the QA path, it works. Is this a known issue with Variable Libraries and SharePoint Shortcuts, or am I missing a configuration step? Thanks!Writing and deleting files in SharePoint shortcut
I have been trying to write a file to a path in OneLake that is a shortcut pointing to a SharePoint location but received the error message below. I have looked through Microsoft Documentation but cannot find any documentation that states that OneLake SharePoint shortcuts are Read-Only, however, neither could I find any direction on writing or deleting a file from OneLake to a SharePoint shortcut. Error: Py4JJavaError: An error occurred while calling z:notebookutils.fs.put. Operation failed: "Method Not Allowed", "This operation is not supported through shortcuts of account type OneDriveSharePoint." Could anyone direct on how to go about writing or deleting a file write through a SharePoint shortcut?
Microsoft Fabric Airflow DAG Queued for 30 Minutes on Starter Pool
Hi everyone, I am trying to run a simple data ingestion DAG using Apache Airflow Jobs in Microsoft Fabric. The purpose of the DAG is to import data into a Microsoft Fabric Lakehouse. The DAG is intentionally simple, and I am currently trying to understand an issue related to DAG execution and queuing. Environment: Orchestration: Apache Airflow Job in Fabric Target: Microsoft Fabric Lakehouse Schedule: Manual (schedule=None) Authentication: OneLake token through an environment variable Compute/Pool: Starter Pool Issue: When I manually trigger the DAG, the DAG run remains in the Queued state for approximately 30 minutes. During this time, the task does not appear to start executing. After approximately 30 minutes, the DAG run eventually changes to Failed.Fabric Copy Job Incremental Load returns 0 rows even when new source records exist
Hi everyone, I’m testing Microsoft Fabric Data Factory Copy Job with an ODBC source and Fabric Warehouse as the destination. I Have been using fabric Trial License. I’m trying to implement an incremental load using a watermark column, but the Copy Job consistently reports 0 rows read / 0 rows written, even though new records exist in the source. My setup: Source: MySQL database accessed through ODBC Source table: ac_gl_companies Destination: Microsoft Fabric Warehouse Copy Job: Incremental load Incremental/watermark column: company_id company_id is an integer and increases for newly inserted records Update method: Merge For testing, the source initially contained: company_id ---------- 1 2 3 4 I then inserted a new record: company_id = 5 I verified directly against the source: SELECT MIN(company_id) AS min_company_id, MAX(company_id) AS max_company_id, COUNT(*) AS total_rows FROM ac_gl_companies; SELECT * FROM ac_gl_companies WHERE company_id > 4 ORDER BY company_id; The second query correctly returns: company_id = 5 So the new record definitely exists in the source However, when I run the Copy Job again, the monitoring result shows: Load type: Incremental load Rows read: 0 Rows written: 0 Lower bound: company_id Upper bound: company_id Status: Succeeded There is no error, but the new record is not picked up. What I have already checked company_id is an integer column. The source contains the new record. The source query correctly returns company_id = 5. The Copy Job is configured for incremental loading. The job completes successfully, but reads 0 rows. I also tried resetting the incremental state and testing again. My question: Could this be related to the ODBC connector, incremental watermark state, or the way Fabric Copy Job determines the lower/upper watermark values? I would especially appreciate feedback from anyone who has successfully implemented incremental Copy Jobs using an ODBC/MySQL source. Is there anything specific I should check in the Copy Job configuration or source metadata? Thanks in advance! 🙏
Fabric IQ: Do Ontology entity synonyms work with Data Agent?
Hi, everyone! Short intro Currently I'm using the "super-duper-mega-nano-ultra" product - Microsoft Fabric to build natural language processing flow on-top of Microsoft Fabric Warehouse data. As for now It's rather a POC than production solution. I found a lot of the official Microsoft' documentation related to my task, but I have a little problem... The solution architecture (high-level) I made some investigations and as the result is the following architecture, which I want to implement (picture below): The idea is the next: the Microsoft Fabric Warehouse schema is connected to Microsoft Fabric Lakehouse, using shortcut; the Microsoft Fabric Ontology consumes the Microsoft Fabric Lakehouse as a data source for data binding; the Microsoft Fabric Data Agent uses a Microsoft Fabric Ontology (enriched with business context) to process natural language questions. The Microsoft Fabric Warehouse contains the following (dummy) objects (picture below): Tenant settings Microsoft documentation says, that specific tenant configurations should be applied to use Microsoft Fabric Ontology with Microsoft Fabric Data Agent (https://learn.microsoft.com/en-us/fabric/data-science/data-agent-tenant-settings) - everything is configured properly. Ontology configuration My Microsoft Fabric Ontology is configured as below (the configuration is influenced by Microsoft Fabric Ontology tutorial, which can be found here - https://learn.microsoft.com/en-us/fabric/iq/ontology/overview). Main view - two entities with a single relationship: ETLEntity entity configuration - the entity has the description, one synonym, metadata: ETLEntityRun entity configuration - the entity has the description, one synonym, metadata: As for now the Microsoft documentation says, that Microsoft Fabric Ontology descriptions, synonyms, metadata help Microsoft Fabric Data Agent to better understand the context (https://learn.microsoft.com/en-us/fabric/iq/ontology/how-to-add-semantic-enrichment). The problem My Microsoft Fabric Data Agent is connected to my Microsoft Fabric Ontology, which is described above, but the agent can't answer the simple questions about entities and the questions examples are provided below (the Microsoft Fabric Ontology Graph model was refreshed successfully before questions were asked): ETLEntity successful question without synonym usage: ETLEntityRun successful question without synonym usage: ETLEntity failed question with synonym usage: ETLEntityRun failed question with synonym usage: Looks like Microsoft Fabric Data Agent can't figure out, which entities are unicorn/wizard, even if they have appropriate synonyms. It's not my first iteration - I tried a lot, but result still the same every time. I feel like I missed something obvious in my configuration, but what... What are your thoughts? P.S.: the provided configuration is simple; objects and their metadata has no business context - It's just a sample, which I built to test some scenario; I think It's enough to check such use-case.
