Forum Discussion
Fabric SQL Database vs Warehouse
I am building a Metadata driven ETL Framework in Fabric and I am confused on where I should create my metadata storage and pipeline logs. As warehouse specializes in OLAP requirements, by definition I am leaning towards SQL Database which is suggested for OTLP requirements.
Hi VaibhavTiwari ,
As my colleague mentioned tayloramy , I would also go with a SQL Database, mainly because this kind of metadata-driven framework behaves much more like an OLTP workload.
A Warehouse can work, and in fact I used one for similar application scenarios when SQL Database in Fabric was not GA yet. In the end, I migrated it to Database.
The main issue is that when you start having many repetitive processes, writes, status updates, logs, etc., the CU consumption in the Warehouse becomes noticeably higher. You can optimize queries and processes, but there comes a point where, with enough frequency and concurrency, capacity usage still increases because it is not the type of workload the Warehouse is primarily designed for.
For this use case, I would go directly with SQL Database.
If this helped, please consider giving it a Like. If it solved your issue, please mark it as the Accepted Solution to help others facing the same problem.
Thanks
10 Replies
- tayloramy
Super User
Hi VaibhavTiwari,
A SQL Database is likely the better choice, as the metadata driven framework is more of an OLTP process.
A warehouse will run into issues when you have multiple processes trying to update the same table at the same time, a SQL database will not have that problem.
- arabalca
Hi VaibhavTiwari ,
As my colleague mentioned tayloramy , I would also go with a SQL Database, mainly because this kind of metadata-driven framework behaves much more like an OLTP workload.
A Warehouse can work, and in fact I used one for similar application scenarios when SQL Database in Fabric was not GA yet. In the end, I migrated it to Database.
The main issue is that when you start having many repetitive processes, writes, status updates, logs, etc., the CU consumption in the Warehouse becomes noticeably higher. You can optimize queries and processes, but there comes a point where, with enough frequency and concurrency, capacity usage still increases because it is not the type of workload the Warehouse is primarily designed for.
For this use case, I would go directly with SQL Database.
If this helped, please consider giving it a Like. If it solved your issue, please mark it as the Accepted Solution to help others facing the same problem.
Thanks
- Kagiyama_yutaka
Continued Contributor
SQL Database fits this use case… Fabric puts small OLTP‑style writes (like ETL metadata/logs) on SQL DB, and Warehouse is meant for analytic workloads, so just start with one tiny table and see how it behaves.
rizalard0684Resolver IV
Hey VaibhavTiwari as the other gentlemen pointed above, SQL DB > Warehouse (nine out of then) especially for your metadata driven pipeline use case.
If your metadata volumes are small (thousands of records instead of millions) and data are updated infrequently with low concurrency operations, then both Warehouse and SQL DB are okay to use.
v-aatheequeCommunity Support
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
- Prince0011
Solution Sage
Hi,
I've thought through this exact decision before, for metadata/logging specifically, SQL Database is generally the better fit, your metadata tables (pipeline configs, run logs, watermark tracking) are transactional in nature, frequent small inserts/updates, not big analytical scans, so OLTP characteristics matter more here than OLAP.
Warehouse is better suited for your actual data outputs/reporting layer, not for operational metadata like this.
If this helped, feel free to give it a kudos or mark it as solution, helps others find it too.
You should opt for a SQL Database specifically to fulfill your OLTP needs, and if you're aiming to create an analytical layer on top of that, you should definitely opt for a Warehouse, as that will be a better option for OLAP needs.
Yeah, either approach can work. We use a Fabric Warehouse for both metadata storage and pipeline logging, and we haven't experienced any performance issues.
In most metadata-driven ETL frameworks, the volume of metadata and log records is relatively small compared to fact and reporting data, so it's unlikely to stress a Warehouse. The Warehouse also provides a familiar SQL experience, making it easy to manage configuration tables, execution logs, audit history, and monitoring queries in a single place.
While SQL Database is often associated with OLTP workloads, I would choose the platform that best aligns with your team's skills and overall architecture. For metadata and pipeline logs, a Warehouse is generally more than capable and keeps everything within the Fabric ecosystem.
If this helped, feel free to give it a kudos or mark it as solution, helps others find it too.
Thanks!
- v-aatheeque
Hi VaibhavTiwari
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support. Based on the requirements for building a metadata-driven ETL framework in Fabric, the Fabric SQL Database is the more appropriate choice for the metadata storage layer. It is better suited for OLTP (transactional) needs, while the Warehouse is specifically designed for OLAP analytics. Metadata and logs are OLTP — constant small inserts, updates (status flags, watermarks), and deletes.
