Forum Discussion

Alaahady's avatar
Alaahady
Icon for Helper II rankHelper II
11 months ago
Solved

Best Incremental Refresh Strategy in Fabric for On-Prem SQL Server Source with Updateable Records

Hi Fabric Community, I'm currently working on optimizing an incremental refresh strategy in Microsoft Fabric and would appreciate your insights on the best combination of tools and destinations. 📌...
pipelines
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    11 months ago

    Hi Alaahady,

    Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

    Thank you.

Alaahady's avatar
Alaahady
Icon for Helper II rankHelper II
10 months ago

Hi,

Thank you all for your support. I was able to resolve the issue by following these steps:

  1. Created a Dataflow Gen2 to build the gold fact table in the Lakehouse.
  2. Created another Dataflow Gen2 to generate the silver table, filtered to include only the last 7 days of data.
  3. Developed a notebook to merge the silver data into the gold table.
  4. Built a pipeline that includes steps 2 and 3, and configured a schedule to automate the process.

 

here is the notebook code:

from delta.tables import DeltaTable
from pyspark.sql import SparkSession

# Start Spark session
spark = SparkSession.builder.getOrCreate()

# Load source and target tables (single-part names only)
source_df = spark.read.table("fact_survey_detail_silver")
target_table = DeltaTable.forName(spark, "fact_survey_detail")

# Merge: update matching records and insert new ones
target_table.alias("gold").merge(
    source=source_df.alias("silver"),
    condition="gold.id = silver.id"
).whenMatchedUpdateAll() \
 .whenNotMatchedInsertAll() \
 .execute()
 
I hope that can help other