Forum Discussion
Advocate IIWhat’s the best for Metadata driven Pipeline?
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
Super UserHello 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.
SrisakthiHi Kuladeep ,
Could you please analyse the compute utilised for each, like may be your notebook utilised all CUs and enough CUs are not available for SQL.
To further investigate,
1. Check your spark compute usage for your notebook, there is recent feature(optmize spark usage) which you could leverage to understand your usage and it will also suggest recommendation
2. You can allocate specific compute for SQL(though it is in preview)
3. hope your notebook sessions are auto shut down after execution .
Regards,
Srisakthi
