Medallion architecture in Microsoft Fabric D365,Onprem

Hi vijith_mar​,

I answered the overlapping questions (Fabric Link as Bronze, SinkModifiedOn, MERGE for IsDelete, where SCD2 goes, notebook design, Gold Lakehouse vs Warehouse) with sample code in your other thread. So here I'll focus on the questions only this post asks.

1. Lakehouse layout. I would use separate lakehouses per layer, in one workspace per environment:

ws-finance-dev / -test / -prod
  lh_bronze   -> Sybase + Excel landing, shortcuts to Fabric Link
  lh_silver   -> cleansed, conformed, current state
  lh_gold     -> star schema (or a Warehouse)

Separate items make permissions simple (report builders only ever get Gold), and keep Bronze maintenance from touching Gold. One lakehouse with schemas works for a small team, but you lose that item-level security boundary.

9. Incremental Gold with Change Data Feed. Yes for large facts; full rebuild for small dimensions is simpler and fine. With CDF enabled on Silver, Gold only reads what changed since the last processed version:

spark.sql("""
  ALTER TABLE lh_silver.sales_line
  SET TBLPROPERTIES (delta.enableChangeDataFeed = true)
""")

changes = (spark.read.format("delta")
           .option("readChangeFeed", "true")
           .option("startingVersion", last_version + 1)
           .table("lh_silver.sales_line")
           .filter("_change_type IN ('insert','update_postimage','delete')"))

Store the last processed _commit_version alongside your watermarks. The Delta change data feed docs describe the change types.

10. Late-arriving and back-dated transactions. Drive incremental loads by modified date, never by transaction date. Then recompute only the Gold periods those changes touch:

from pyspark.sql import functions as F

months = [r[0] for r in changes
          .select(F.date_format("PostingDate", "yyyy-MM"))
          .distinct().collect()]
cond = "PostingMonth IN ({})".format(",".join(f"'{m}'" for m in months))

(build_fact_sales(months)            # your Silver -> Gold logic
   .write.mode("overwrite")
   .option("replaceWhere", cond)     # replaces only those months
   .saveAsTable("lh_gold.fact_sales"))

A posting for March that arrives in October then simply rebuilds March and October in Gold.

13. Mapping files. This is a finance policy question rather than a technical one. Account groupings are usually restated (new grouping applies to all history), which is Type 1 overwrite in Gold. But keep every version of the Excel file in Bronze with a load date, so you can always reproduce what a past report showed.

14. Tool for SCD2. Notebook MERGE in a Lakehouse, or T-SQL in a Warehouse if that is where Gold lives. I would avoid Dataflow Gen2 for SCD2; the logic is hard to express and slow at volume.

15-16. Periodic snapshots. Build them in Gold. Freeze closed periods and recalculate open ones:

fact_balance_snapshot
  snapshot_month | account | balance | is_final

open month    -> rebuilt on every load (is_final = false)
after close   -> set is_final = true, never rebuilt
back-dated change to a closed month
              -> goes into the next open period as an adjustment,
                 unless finance explicitly reopens the period

That matches how finance treats closed periods, and keeps last month's board report reproducible.

So my overall approach would be:

separate bronze / silver / gold lakehouses per environment
-> MERGE with watermark + deletes into Silver
-> CDF-driven Gold for large facts, rebuild only touched months
-> SCD2 in Gold where finance needs history
-> snapshots in Gold, frozen after period close

Happy to go deeper on any of these if you share a bit more about your volumes.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi vijith_mar​,

Have you had a chance to review the solution shared by jubinsoni​ ? If the issue persists, feel free to reply so we can help further.

Thank you.

I’m building a metadata-driven Bronze → Silver ingestion framework in Microsoft Fabric and would like feedback on the approach before scaling it to a few hundred tables.

Setup

• Source: Dynamics 365 F&O tables land in a Bronze lakehouse as Delta tables through Fabric Link. Each row has a SinkModifiedOn timestamp and an IsDelete soft-delete flag.
• Config DB: a Fabric SQL Database stores per-table metadata (table name, schema, key column, load type, column list) and the last processed watermark.
• Orchestration: a Data Pipeline runs a Lookup on the config DB, then a ForEach that calls one generic parameterized notebook per table. The notebook returns the new watermark through notebookutils.notebook.exit(). A Script/Stored Procedure activity writes it back to the config DB only if the notebook succeeds.
• Target: a Silver lakehouse with one Delta table per source table.

Parameter cell (values passed from the pipeline)

python

table_name = "generaljournalentry" schema = "d365" key_col = "recid" load_type = "incremental" # full / incremental select_cols = "recid,accountingdate,...,SinkModifiedOn,IsDelete" # "" = all columns last_wm = "" # from config DB; "" = first run

Load logic

python

df = spark.read.format("delta").load(src_path) if select_cols: df = df.select(*[c.strip() for c in select_cols.split(",")]) new_wm = None # ---------- FULL ---------- if load_type == "full": w = Window.partitionBy(key_col).orderBy(F.col("SinkModifiedOn").desc()) df = (df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn") .withColumn("silver_loaded_on", F.current_timestamp())).cache() new_wm = df.agg(F.max("SinkModifiedOn")).first()[0] (df.filter("coalesce(IsDelete, false) = false") .write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(tgt_path)) # ---------- INCREMENTAL ---------- else: target_exists = DeltaTable.isDeltaTable(spark, tgt_path) # Watermark from config DB, with a 1-hour safety overlap if last_wm: df = df.filter(F.col("SinkModifiedOn") > F.to_timestamp(F.lit(last_wm)) - F.expr("INTERVAL 1 HOURS")) # Keep the latest version per key w = Window.partitionBy(key_col).orderBy(F.col("SinkModifiedOn").desc()) df = (df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn") .withColumn("silver_loaded_on", F.current_timestamp())).cache() # New watermark = max of the batch (deleted rows included) new_wm = df.agg(F.max("SinkModifiedOn")).first()[0] if new_wm: if not target_exists: df.filter("coalesce(IsDelete, false) = false").write.format("delta").save(tgt_path) else: (DeltaTable.forPath(spark, tgt_path).alias("t") .merge(df.alias("s"), f"t.{key_col} = s.{key_col}") .whenMatchedDelete("coalesce(s.IsDelete, false) = true") .whenMatchedUpdateAll("s.SinkModifiedOn > t.SinkModifiedOn") .whenNotMatchedInsertAll("coalesce(s.IsDelete, false) = false") .execute()) # Return the new watermark to the pipeline notebookutils.notebook.exit(json.dumps({ "table": f"{schema}.{table_name}", "new_watermark": str(new_wm or last_wm) }))

Questions

1. Watermark handling. The watermark is passed in from the config DB and written back from the notebook’s exit value only after a successful run, so a failed run never advances it. Is a 1-hour overlap a sensible safety margin for SinkModifiedOn? Is there a cleaner way to keep the watermark update and the Delta MERGE consistent if the pipeline fails between the two steps?
2. SinkModifiedOn reliability. For D365 data landed through Fabric Link, is SinkModifiedOn reliable as a monotonic incremental column? Are there known cases of records arriving with an older timestamp?
3. Deduplication. I keep the latest version per key with row_number() ordered by SinkModifiedOn. Should I add a tiebreaker column, such as versionnumber, to handle identical timestamps?
4. Orchestration at scale. For around 200+ tables, is ForEach → Notebook activity per table reasonable? Or are notebookutils.notebook.runMultiple or high-concurrency sessions the better choice to avoid repeated Spark startup overhead?
5. Soft deletes. Is physically deleting rows in Silver on IsDelete = true the common approach, or do people usually keep soft-deleted rows in Silver and filter them in Gold?
6. Table maintenance. Any recommended settings for frequently merged Silver tables, such as OPTIMIZE or V-Order schedule, deletion vectors, or partitioning?

Thanks for the follow-up, v-saisrao-msft​.

Hi vijith_mar​, if you get stuck on any part of the setup, just reply here and I'm happy to go deeper on that layer. Typical sticking points are the Fabric Link tables in Bronze, the Sybase shortcut refresh pattern, or how to structure the SCD/incremental logic in Silver. If it helped, please consider marking it as the solution so others with a similar D365 + on-prem setup can find it.

For this setup, I would keep Bronze as close to the source as possible, use Silver for cleansing and history management, and build Gold around business reporting requirements.

1. I would prefer separate lakehouses for Bronze and Silver, especially if multiple teams or workloads are involved. Gold can be a Fabric Warehouse if the main use case is structured reporting and star-schema models.
2. Fabric Link can serve as the raw landing layer for D365 F&O, but I would avoid treating it as the only Bronze layer if you need independent retention, recovery, or consistent ingestion across all sources.
3. Bronze should preserve source data, Silver should handle data quality, deduplication, conformed keys and change processing, and Gold should contain business-ready dimensions, facts and KPIs.
4. For incremental loads, I would implement change processing at both Bronze-to-Silver and Silver-to-Gold where it adds value. The approach should depend on source capabilities, table size and change frequency.
5. Dimensions do not always need full loads. Use incremental processing where reliable change tracking is available, and full loads for smaller tables when they are simpler and safer.
6. I would not rely on SinkModifiedOn as a universal watermark without validating its behaviour for each Fabric Link table, including updates, deletes and late-arriving changes.
7. Apply deletes and updates in Silver using the source change information. Keep Bronze as an auditable landing layer wherever possible.
8. Gold can be incremental using Delta Change Data Feed or other change-tracking logic, but only when the pipeline can correctly handle updates, deletes and dependencies. Full rebuilds are perfectly reasonable for smaller tables.
9. For late-arriving transactions, use a rolling reprocessing window or source change tracking, and periodically reconcile historical data to catch changes outside that window.
10. I would generally manage SCD Type 2 in Silver when historical dimension versions are needed. Gold can expose the resulting dimensions and facts through the reporting model. Type 1 is sufficient when only the latest attribute value matters.
11. For business-maintained mapping files, the decision depends on whether historical reporting must reflect the mapping that was valid at the time. If yes, track effective dates or use SCD Type 2. Otherwise, overwrite the mapping and document that historical results may change.
12. For SCD Type 2, I would lean towards Fabric notebooks with Delta MERGE for more complex transformations and control over effective dates. Dataflow Gen2 can work for simpler scenarios, while Warehouse stored procedures are a reasonable choice when the transformation is implemented in the Warehouse.
13. Periodic snapshots should generally be created in Gold as fact tables designed for reporting, with the required source history and reconciliation logic maintained upstream.
14. I would not automatically freeze month-end snapshots forever. Define a financial close policy: preliminary periods can be recalculated when back-dated transactions arrive, while closed periods should follow an approved restatement process.

The main principle is to make incremental processing and historical tracking explicit. Avoid adding complexity such as SCD Type 2 or CDF to every table by default. Choose the approach based on business history requirements, source change guarantees and the cost of rebuilding data.

HI vijith_mar​,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you