Forum Discussion
Advocate IIWhat’s the best for Metadata driven Pipeline?
Hi Community,
I am trying to optimize our existing pipeline. A former colleague built our metadata-driven pipelines using a Fabric SQL Database.
However, we have been facing some issues with this approach. At times, the SQL Database becomes inaccessible and returns the error message: “Capacity is full”, even though our F8 capacity appears to have sufficient available resources. To restore functionality, we often have to restart the capacity, then SQL Database starts working again within the pipeline.
I am therefore looking for recommendations on the best way to build reliable, metadata-driven pipelines in Microsoft Fabric.
I am also hesitant to use a Lakehouse because I have experienced frequent concurrent write conflicts on Delta tables. I have noticed several concurrent write failures when multiple processes attempt to update the same Delta table in another Lakehouse.
Currently, we have around 4-6 pipelines utilising SQL DB as part of metadata
Has anyone implemented a metadata-driven framework in Fabric that has proven to be stable and scalable? Any suggestions or best practices would be greatly appreciated.
So the pattern that I follow and tend to advocate is:
- Yaml configuration files that store the config attributes and values
- Benefits:
- config and and values are all source controlled and tokenizable for environment specific releases
- Release Pipelines deploys configurations to config lakehouse
- Schema is easily evolved, new datasources and additional configuration attributes are easily created without database schema changes and management
- No Dependency on additional Fabric or external SKU
- Potential Con - Notebook or Fabric Function required to generate dictionary that is useable by ADF if data is being moved using copy command
- Benefits:
- Orchestration Notebooks and Executor Notebooks for data Orchestration and Execution
- Orchestrator gets list of configs to run and executes through notebookUtils.notebook.runMultiple or bespoke parallel procesing implementation
- All Pipeline and Process Metadata generated and potentially required by future/upstream/downstream processes stored in KQL database
Hope that is a little Helpful
- Yaml configuration files that store the config attributes and values
13 Replies
- deborshi_nag
Super User
Hello Kuladeep I would recommend using an Azure SQL database for your use case, as this will allow you to avoid being limited by the same Fabric capacity. Your pipelines and notebooks can access data from an Azure SQL database without needing significant changes.
KuladeepIf so, how canwe handle CICD and Scalability (adding new configurations rows)?
currently we build SQL proj in DevOps and deploying that to Fabric (new rows are being added to the script manually though)
A guide on complete revamp could also help
- apturlov
Hi Kuladeep, yes you can manage your SQL database via SQLProj and deploy it to your SQL server via CI/CD pipelines in both Azure and Fabric. Keep in mind that SQLProj only manages the Database schema and not data and in the pipeline you'd need to use a SqlPackage utility. To manage data you'll need to use Sqlcmd utility to update data in your metadata database.
Alternatively, and it strongly depends on your metadata structure and control pattern, you can consider separating your entire metadata into bundles:
- static read only configuration metadata that does not need updating during execution and can be stored as a JSON file(s) in a Lakehouse, which is perfectly accessible by a Lookup activity in a data pipeline.
- operational metadata the needs to be updated during execution like watermarks, locks, etc. and therefore should use a transactional database storage such as a Lakehouse table or a SQL table. Keep in mind, for SQL storage you could also use a Fabric Warehouse, depending on your operational metadata needs. Such metadata can be processed via a Script activity for SQL storage or a Notebook for a Lakehouse.
- logging for execution statistics and errors - ideally done in the Eventhouse KQL database that is optimized for timeseries data and is supported by a KQL activity in a data pipeline.
Ideally, you would keep all your metadata inside Fabric for easier access, management, security, cost control, and analytics (logs, errors).Having multiple types of metadata optimized for each purpose is a classic separation of concern pattern so don't be trapped by "I have to choose a single data storage pattern for all my metadata". Fabric gives you many choices that you can mix and match.
Hope this can be helpful. If so, feel free to give kudos or mark as a solution.
- NandanHegde
Ideally Fabric SQL database should be your operational database for logs and meta data if your entire ecosystem is built within Fabric rather than relying on some additional Azure cost with other sources.
Can you confirm that when the Fabric SQL database is inaccessible, are you able to execute any queries across lakehouse or warehouse etc? And what is the CU consumption?
Also what is the amount of data within your Fabric SQL database?- Kuladeep
Hi Nandan,
- Yes, I would also prefer to keep the entire architecture under one platform rather than splitting it between Azure and Fabric, unless there is a strong technical reason to do so.
When the SQL database failed with the error:
"SQL database failed to load. Your organization's Fabric compute capacity has exceeded its limits. Try again later."Other processes, such as notebooks and Data Lake Gen2 operations, completed successfully without any issues.
- Yes, CU consumption was higher at that time (more than 100%, I believe, as shown in the screenshot). I would understand if the SQL database had failed only on that particular day. However, it continued to fail from that day onward until I restarted the capacity. Apart from that one peak day, the capacity had sufficient free resources on the remaining days. And this behaviour ist not the firt time but happened in the past. I expected the SQL DB to pick it up again rather than giving up until someone restarts the capacity.
- The SQL database contains exactly 42 rows and 25 columns of data.
- alicegray
Helper I
Hey Kuladeep
Implementing a metadata-driven framework in Fabric requires configuring:- Metadata Store
- Control (Orchestrator) Pipeline
- Generic Child Pipelines
- Watermark Management
- Error Handling & Retry Logic
These elements ensure smooth querying and updates. Would you like to know about specific implementation, such as the SQL schema for the control tables, a sample pipeline expression, or a notebook-based metadata approach? Please let me know!
- Kuladeep
Hi Alicegray,
We have all the set up that you mentioned (orchestration pipeline, child pipelines, watermark management, error handling and retry logic) and it has been working smooth except these unexpected hick ups in middle. We are using the SQL DB for Metadata Store at the very first step and it is giving up on us easily. There is nothing to retry except force restart of the capacity.
So the pattern that I follow and tend to advocate is:
- Yaml configuration files that store the config attributes and values
- Benefits:
- config and and values are all source controlled and tokenizable for environment specific releases
- Release Pipelines deploys configurations to config lakehouse
- Schema is easily evolved, new datasources and additional configuration attributes are easily created without database schema changes and management
- No Dependency on additional Fabric or external SKU
- Potential Con - Notebook or Fabric Function required to generate dictionary that is useable by ADF if data is being moved using copy command
- Benefits:
- Orchestration Notebooks and Executor Notebooks for data Orchestration and Execution
- Orchestrator gets list of configs to run and executes through notebookUtils.notebook.runMultiple or bespoke parallel procesing implementation
- All Pipeline and Process Metadata generated and potentially required by future/upstream/downstream processes stored in KQL database
Hope that is a little Helpful
- Yaml configuration files that store the config attributes and values
Hi Kuladeep,
I would also take a moment to thank kr_richbenmintz, apturlov , alicegray for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Regards,
Community Support team.Hi Kuladeep,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Community Support Team.
SHELLA2Advocate I
Use Fabric warehouse
