Hi @tan_thiamhuat , Thank you for reaching out to the Microsoft Community Forum.
Create a Watermark table in SQL Server to track the last processed timestamp. Each pipeline run should extract only rows newer than this value, enabling incremental processing and reducing unnecessary data movement. Use a Lookup activity in Microsoft Fabric to retrieve the current watermark value. Use a Copy activity to query new rows from the source based on the watermark and write them to a Fabric Lakehouse (Delta table) or a staging SQL Server table. This activity streams data efficiently and does not load it into pipeline memory, making it suitable for large datasets.
Once the incremental data is staged, use Dataflows Gen2 for low-code, visual transformations such as deduplication, joining with Dimension tables, assigning surrogate keys and aggregating measures. For more complex or custom transformation logic, use a Spark Notebook, which offers full control through code. Write the transformed outputs directly to the Dimension and Fact tables in SQL Server. After loading, use a Script activity to update the watermark with the latest processed timestamp. This ensures that only new data is picked up in future runs, keeping the process efficient and reliable.
Alternatively, if the transformation logic can be handled in T-SQL, use Fabric to orchestrate a stored procedure that performs the transformations and loads directly within SQL Server. This approach reduces Fabric compute usage and keeps processing close to the data.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
