Architecture design question

1) Append or merge?

• Copy activity supports append and overwrite

• No native merge

• Merge must be done later using Spark or SQL

2) Deletes?

• Copy activity does not handle deletes

• Deleted rows in source will remain in Lakehouse

• Use CDC or soft delete if deletes matter

3) CDC vs watermark

• Watermark: simple, needs timestamp, no deletes

• CDC: captures insert/update/delete, more complex

• Choose CDC if correctness matters, watermark if reporting-only

4) What gets created in Lakehouse?

• Delta tables

• Stored as Parquet in OneLake

• Tables are auto-created, no manual linking

5) SHIR vs Power BI gateway

• Same runtime

• Different name

• In Fabric it is called SHIR

• Use Copy activity from Fabric Data Factory

• Do not use gen2 dataflow for SQL Server ingestion

Scheduler

• Use pipeline triggers

• Time-based schedule (for example every 5–15 minutes)

• No separate scheduler needed

 "Another questions is, shall I use a service principal to secure this? which permissions shall it has in SQL Server on-prem and on Fabric this service principal? last, but not less important, where do I store the :  SHIR ? does it needt o be installed on the SQL Server on-prem machine? And the coonnection shall be encypted? "

Yes, use it

Permissions : SQL Server onprem

• Read-only

• SELECT on required tables

• SELECT on CDC tables if CDC is used

• No write, no db_owner

Permissions -> Fabric

• Workspace role: Contributor

• Lakehouse: Write

• Permission to run pipelines

SHIR:

• Install on a separate VM

• Do not install on the SQL Server machine

• Must have network access to SQL Server

• Can scale with multiple nodes if needed

Encryption

• Yes

• SQL connection uses TLS (Encrypt=True)

• SHIR -> Fabric uses HTTPS (outbound 443 only)