warehouse

262 Topics

Near-real-time CDC into Fabric Warehouse: MERGE vs Mirroring vs Lakehouse?
Hi all, I need operational data from an Azure SQL Database available in Fabric for reporting with roughly 5 to 15 minutes of latency. There are about 30 tables, mostly updates, with some deletes. Business users will query it in T-SQL and Power BI. I see three main approaches and would like to understand the trade-offs: Mirroring the source database into Fabric, then building curated tables in a Warehouse on top of it Incremental MERGE into Warehouse tables from a pipeline or copy job that reads changed rows (CDC or a watermark column) Eventstream CDC → Lakehouse Delta tables, queried through the SQL analytics endpoint Questions: Which approach is recommended at this latency and scale, and where does each one break down? How are deletes handled in each? Mirroring is the most direct, but what about MERGE-based loads? How much does running MERGE frequently cost on a Warehouse (capacity usage, small-file or fragmentation effects)? If you mirror, is it better to build curated tables in a Warehouse or use views over the mirrored database? Thanks!
jubinsoni
17 hours ago Place Data Warehouse
Views
1like
Comment
Data Warehouse deployment fails when new NOT NULL column added in schema
Greetings! We have a deployment pipeline that we are attempting to move a warehouse schema change through, we are adding some not null columns. The deployment from our Dev environment to Test is failing with the following error: Microsoft SqlClient Data Provider: Msg 24735, Level 16, State 1, Line 1 Only nullable columns can be added to an existing table. SqlMSBuild: Script execution error. The executed script: ALTER TABLE [DM_Schema].[SummaryTable] ADD [NewColumn] INT NOT NULL,CONSTRAINT [SD_SummaryTable_2d66d69a5ee8421398ab13068f0c28b4] DEFAULT 0 FOR [NewColumn] A couple of questions: 1) Why is this blocking? I can't seem to locate where NOT NULL columns are not supported by deployment pipeline? Is this a legitimate bug? 2) Why is does it appear to (rightly) add a DEFAULT 0 constraint even though no such constraint was defined in DEV environment? 3) Our current process with complicated schema changes is to use Schema Compare tool in VS Code, and promote complicated schema changes outside of the deployment pipeline, adding a column generally is not considered a complicated schema change, so just wondering if there is a way to do this within deployment pipeline? Thanks!
anon97242
1 day ago Place Data Warehouse
Views
0likes
Comments
Views in Warehouse Breaks Source Control Sync
When I create a view in a warehouse and have it point to a table in a corresponding lakehouse in the same workspace, I am unable to sync a new workspace from a git branch. Reason is that the lakehouse is also new and tables/fields have not been established yet. This causes the dependency check to fail a git update. Adding a selective sync feature to Fabric will help to alleviate this problem as I would be able to do an update in two passes. Alternatively, having the ability to turn off dependency checking when updating from git would work as well. But currently, using views in warehouses that point to anything outside of that warehouse is absolutely broken from a CICD perspective. Has anyone run into this problem and found a solve for it? According to Microsoft docs, views can only point to lakehouses in the same workspace, otherwise I would have put these warehouses in a different workspace and mitigate the issue that way.
Solved
kozman
1 day ago Place Data Warehouse
Views
0likes
Comments
How are teams using AI agents to automate modern data warehouse workflows?
I am exploring how AI agents can help improve data warehouse operations by automating repetitive tasks and assisting data teams with faster decision-making. Some areas I am interested in: Automating data pipeline monitoring and issue detection AI-assisted SQL generation and optimization Automatically identifying data quality issues Triggering workflows based on business events or data changes Generating documentation and insights from warehouse metadata With platforms like Microsoft Fabric Data Warehouse, how are teams approaching AI integration? Are you using: Fabric pipelines with AI-powered automation? Copilot or LLM-based assistants for warehouse development? Custom AI agents connected with data warehouse APIs? Would love to hear real-world architecture patterns and best practices from data engineers working with Fabric.
codeautomation
1 day ago Place Data Warehouse
Views
2likes
Comments
Varchar(MAX) in SQL Endpoint
I'm testing VARCHAR(MAX) support in a Fabric Lakehouse SQL Analytics Endpoint and discovered that it only worked after: 1. Enabling New Metadata Sync (Preview) 2. Creating a new Lakehouse after enabling the setting Existing Lakehouses didn't seem to pick up the capability. Is this officially expected, or is there a migration/upgrade path for existing SQL Analytics Endpoints?
fabanalytics
1 day ago Place Data Warehouse
Views
1like
Comments
updating .sqlproj file (version issue with git)
We recently had a couple issues syncing from main with INFORMATION SCHEMA errors. When we reviewed the documentation Upgrade Fabric Data Warehouse System File Version in a Git Integrated Fabric workspace - Microsoft Fabric | Microsoft Learn, we noticed that we had two errors. The first is that we were self referencing in a couple PROCS, eg WH.dbo.Table from current WH. We fixed that problem, and th INFORMATION SCHEMA sync still persisted, but the self ref error went away. Then we noticed that the sqlproj file is listed as a possible cause. We have the old preview version listed in git. However, if you export the warehouse project, the actual wh sqlproj file shows up to date. So there is a difference b/w git and what is actually in the workspace. We have tried to PR the change to this file, update it directly in git, make a small change in the wh and commit directly to main to see if we could trigger the change. In the one case, we went into ADO, we updated this file directly, committed to it. It actually SHOWED as an update for a hot second, BEFORE converting to a change and the change was showing the old definition. In that referenced article, we have tried a and b, and we cannot for whatever reason get this file to update in git, which then causes sync issues. Does anyone have experience with this, a recommendation? Thanks J.
justfetz
2 days ago Place Data Warehouse
Views
1like
Comments
Switch workspace from west US to east US
Wondering how to switch WS and all contents from west US to east US. Tried backup to Git repo and restore from there - Does not work synch to a new empty workspace. Original workspace was west US, (contains pipelines, notebook, warehouse) synched WS to git repo, disconnected WS Created new WS east US tried to synch git repo to new empty WS created on east US errored out on every level and nothing got synched. It just sucks with no import export option. Any other suggestions.
AJAJ
9 days ago Place Data Warehouse
Views
2likes
Comments
Onelake Storage Report
Hi All, I checked the OneLake storage Report for the first time, and it blew my mind... I have one warehouse that is > 1+ TB, while it only contains two (!!) tables. - 700K rows - 1.2M rows I dived deeper into it by connecting the warehouse to blob storage, and I noticed in the subfolder onelake/xxxxx/xxxxxx/Files/ ; there is OVER 800 GB OF FILES ! The stored procedures of those two are quite complex with different update statements, but this should not generate so much files ; as we are paying them as well. I already changed the time_travel_retention_cutoff_date, from 30 --> 5 days, but this has no impact on the /files, only the /tables from what I've read (after 36h still no impact there as well though) The files are kept into that folder going two months back; - Is there a way to change this setting? - Is there a way to reduce all those files that are written? - Anyone else noticing this?
JVDA98
19 days ago Place Data Warehouse
Views
2likes
Comments
The last mile of Fabric: getting business edits back into your warehouse
Every Fabric implementation I have worked on hits the same wall at roughly the same moment. The pipelines are running, the semantic model is clean, the reports look sharp. Then someone in finance says: "the cost centre mapping is wrong for three rows, can you fix it?" And the elegant architecture answers with a spreadsheet attached to an email. This post is about that last mile, why it is harder in Fabric than people expect, and what the realistic patterns are for solving it. Why the last mile is genuinely hard Fabric is built around analytical read paths. That is the right design for the workloads it targets, but it means the write path for small, human-scale corrections is not obvious. The options most teams land on: A staging table plus a pipeline. Someone uploads a CSV to a Lakehouse folder, a pipeline picks it up, a notebook merges it. Works, but you have built a bespoke ingestion system for what is functionally a typo fix, and now you own it forever. A Power App over the table. Good fit for structured, form-shaped entry. Poor fit for the case where a controller wants to see two hundred rows at once, sort them, and fix the eight that are wrong. Direct SQL access. Fast, but you are handing UPDATE rights to people who do not write SQL, and there is no validation layer between intent and damage. Nobody fixes it. More common than anyone admits. The mapping stays wrong and a filter gets added to the report. The spreadsheet keeps winning because the shape of the work is a spreadsheet: a grid, a lot of rows, a few edits, sorted and filtered by someone who knows the domain. The bit people get wrong: Warehouse and SQL Database are not the same target If you are building or buying anything that writes back into Fabric, this distinction matters more than any other, and it is the thing I see glossed over most often. Fabric SQL Database behaves the way a transactional developer expects. Foreign keys are enforced. You get rowversion for optimistic concurrency. A multi-statement transaction commits or rolls back as a unit. If two people edit the same row, you can detect it and refuse the second write. Fabric Warehouse does not give you those guarantees. Constraints exist for query optimisation but are not enforced. There is no rowversion equivalent for conflict detection. Isolation-level hints are accepted and ignored. A multi-step write sequence can leave you partially applied if something fails midway. Neither of these is a defect. Warehouse is an analytical engine and those trade-offs are why it scales. But it means a write-back tool that promises "all your changes commit together, or none of them do" is telling the truth against SQL Database and stretching it against Warehouse. Practical consequences if you are building this yourself: Do not rely on the database to catch bad references. Against Warehouse you must validate foreign keys in your own layer, before you write, or you will silently create orphans. Build your own conflict detection. With no rowversion, the honest fallback is comparing the primary key plus the values you read, and refusing the write if the row moved underneath you. Decide what a partial failure means, and say it out loud. If step four of six fails, steps one to three are already committed. Your user needs to know that, in the moment, in plain language. Validate before you touch the database, not after. A whole-batch gate (nothing writes unless every row passes) is far kinder than discovering row 147 is bad after 146 rows have landed. What good looks like Whatever route you take, the same handful of properties separate a write-back path that survives contact with real users from one that gets quietly abandoned: It uses the caller's identity, not a service account. If the tool connects as a shared principal, you have lost your audit trail and your permission model in one move. Entra ID passthrough means the database's own security is still doing its job, and SUSER_NAME() in an audit trigger still means something. Validation is authored, not hardcoded. Required fields, ranges, allowed values, regex patterns, uniqueness. These change constantly and should not require a deployment. Type enforcement happens before the write. Text in a numeric column, four decimals in a decimal(9,2), a date that Excel helpfully reinterpreted. Catch these in the client, where the user can still see what they typed. Errors name the row and the reason. "Constraint violation" sends the user to IT. "Row 42: Region must be one of North, South, East, West" gets fixed in ten seconds. Nothing is installed server-side. The moment your write-back solution needs stored procedures or schema changes deployed into the warehouse, it becomes a change-management conversation and the timeline triples. Where we landed We ended up building this as an Excel add-in, because that removed the training problem entirely. The user opens a workbook, picks a table from the catalogue their credentials can see, edits the grid, and clicks publish. Validation runs as a whole-batch gate before anything is written, so a failed row blocks the commit instead of half-applying it, and the failures come back as a plain-language list. Against Fabric SQL Database that commit is a single transaction. Against Fabric Warehouse we run a separate execution path and tell customers plainly what it can and cannot promise, for exactly the reasons above. Making that distinction visible turned out to be a feature, not a caveat, because the alternative is a data engineer discovering it at 2am. It is called Workbook Connect (workbookconnect.com) and there is a free tier if you want to try the pattern rather than build it. But the honest summary of this post is not "use our thing." It is: the last mile is a real architectural problem, it deserves a deliberate answer, and Warehouse and SQL Database need different answers. Curious how others are handling this. Staging tables and pipelines? Power Apps? Something cleverer? Would genuinely like to hear it. Sander Allert works at Plainsight (plainsight.pro), a Belgian Data and AI consultancy.
Solved
sanderallert
1 month ago Place Data Warehouse
Views
1like
Comments
publicly available data
I am taking a class on Fabric and we are copying publicly available data into our warehouse using the SQL endpoint. We are using these two tables. 'https://worldwideimp.blob.core.windows.net/sampledata/WideWorldImportersDW/tables/dimension_city.parquet' 'https://worldwideimp.blob.core.windows.net/sampledata/WideWorldImportersDW/tables/fact_sale.parquet' My query runs just as the video says it should, however, I'm only getting 10 and 100 rows, respectively, yet the city table has over 100K and the fact_sale has over 5MM. Anyone know why and what I can do to fix it?
Solved
trindadsth
1 month ago Place Data Warehouse
Views
1like
Comments