Forum Discussion
Looking for dbt-Like Lineage-Aware Refreshes in Microsoft Fabric
Hi all,
I have worked with `dbt-databricks` in previous projects, and one of the features I really appreciate is its built-in lineage management and orchestration. It allows you to run a specific part of a pipeline independently while automatically resolving and executing the required dependencies based on the DAG and model relationships.
I'm curious whether a similar capability exists in Microsoft Fabric using the currently available orchestration options such as Pipelines, Notebooks, Dataflows, or other Fabric-native approaches. I am familiar with creating DAG-like workflows using pipeline activities and dependency conditions (success/failure), but what I'm looking for is something more lineage-driven.
For example, in an end-to-end data engineering solution spanning ingestion → transformation → data marts → reporting, is there a way to selectively refresh a specific table or object and automatically execute only its relevant upstream or downstream dependencies, without impacting unrelated objects in the pipeline?
I'd love to hear how others are approaching this in Fabric and whether there are any recommended patterns, tools, or best practices to achieve similar behavior.
Hi tsingh,
Here's a high level overview:
The control tables sit in a SQL Database. These contain all the definitions for connections, entities, and lookups.
The orchestration pipeline will read the SQL database and then loop through each active connection, kicking off the bronze notebook for that connection.
Data is then loaded from source to bronze, and each table/entity logs the number of rows it read, and the number it loaded.
After that, the silver notebook is run for that connection, which applies some type casting and cross table lookups. Here is where I check if a bronze table loaded anything, and if it did not, then I don't bother touching the silver table.
Lastly the gold layer runs with the same logic, if the downstream silver tables were not updated, no need to update the gold table.
7 Replies
- tayloramy
Super User
Hi tsingh,
The sort answer is no, Fabric doesn't have this natively.
Though you can build it in Fabric, it's a bit of work. I've built a fully metadata driven ETL process that can achieve this, I keep track of what each dependency is when I register a source system or register a new gold table, and every time the process runs, if there's no new data in bronze, then it doesn't update the silver or gold tables.
Hi tayloramy
Thanks for the reply. Would it be possible for you to share what you have currently designed with a simple example or some information on the setup. I'm in a similar ETL kind of build and would be really helpful if I can get some knowledge on this.
Thanks!Hi tsingh , I have also done metadata driven ETL process similar to what tayloramy is talking
Core pieces:
- Metadata/control table (I keep mine in a Lakehouse table, e.g. cfg.object_dependencies): object_name, layer (bronze/silver/gold), depends_on (comma-separated or a child table for many-to-many), is_active.
- Watermark/run-log table: object_name, last_source_updated_ts, last_run_ts, last_run_status. Every load updates last_source_updated_ts for that object once it lands.
- Orchestrator notebook: reads the metadata table, builds the dependency graph in memory (just a dict of object → depends_on list), does a topological sort, then walks it — for each object, compares its depends_on objects' last_source_updated_ts against its own last_run_ts. If nothing upstream is newer, skip it. If something is newer, run it and update its own last_source_updated_ts so downstream objects pick up the change.
- Selective/targeted refresh: pass a parameter (e.g. target_object) into the orchestrator. It does a graph walk starting from that node , upstream only (to backfill dependencies) or downstream only (to propagate impact) , instead of the full run.
Example: bronze_customers → silver_customers → gold_sales (which also depends on silver_orders). If only bronze_customers gets new data, the watermark check means silver_customers and gold_sales fire, but silver_orders and anything downstream of it that doesn't touch gold_sales doesn't re-run.
Orchestration mechanics: I use a PySpark notebook for the graph logic (it's just a small topological sort, nothing fancy), then either mssparkutils.notebook.run() to chain notebooks in dependency order, or a Data Pipeline with a ForEach + dynamic "Invoke Pipeline" activity if some steps are pipeline-based rather than notebook-based.
Honest caveat: this is basically hand-rolling the bit of dbt/Dagster you're missing , you own the graph maintenance, so every time you add/remove a table you have to update the metadata table too. It's worth it once you're past a handful of objects, but there's no free lunch here like there is with dbt's ref()-driven DAG.
hope this helps
hi tsingh , i have also done the metadata driven ETL process , it is similar
Core pieces:
- Metadata/control table (I keep mine in a Lakehouse table, e.g. cfg.object_dependencies): object_name, layer (bronze/silver/gold), depends_on (comma-separated or a child table for many-to-many), is_active.
- Watermark/run-log table: object_name, last_source_updated_ts, last_run_ts, last_run_status. Every load updates last_source_updated_ts for that object once it lands.
- Orchestrator notebook: reads the metadata table, builds the dependency graph in memory (just a dict of object → depends_on list), does a topological sort, then walks it , for each object, compares its depends_on objects' last_source_updated_ts against its own last_run_ts. If nothing upstream is newer, skip it. If something is newer, run it and update its own last_source_updated_ts so downstream objects pick up the change.
- Selective/targeted refresh: pass a parameter (e.g. target_object) into the orchestrator. It does a graph walk starting from that node , upstream only (to backfill dependencies) or downstream only (to propagate impact) , instead of the full run.
Example: bronze_customers → silver_customers → gold_sales (which also depends on silver_orders). If only bronze_customers gets new data, the watermark check means silver_customers and gold_sales fire, but silver_orders and anything downstream of it that doesn't touch gold_sales doesn't re-run.
Orchestration mechanics: I use a PySpark notebook for the graph logic (it's just a small topological sort, nothing fancy), then either mssparkutils.notebook.run() to chain notebooks in dependency order, or a Data Pipeline with a ForEach + dynamic "Invoke Pipeline" activity if some steps are pipeline-based rather than notebook-based.
Honest caveat: this is basically hand-rolling the bit of dbt/Dagster you're missing — you own the graph maintenance, so every time you add/remove a table you have to update the metadata table too. It's worth it once you're past a handful of objects, but there's no free lunch here like there is with dbt's ref()-driven DAG.
hope this helps.
v-aatheequeCommunity Support
Hi tsingh
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.
- v-aatheeque
Hi tsingh
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
