Question

You maintain a DimCustomer table in a Fabric warehouse. Each nightly load brings a staging table of customer records that contains both brand-new customers and updated details for existing customers (matched on the business key CustomerID). You need a single set-based T-SQL statement that inserts new customers and updates changed attributes for existing ones in one operation. Which approach should you use?

Accepted Answer

Use a MERGE statement that matches DimCustomer to the staging table on CustomerID, updating matched rows and inserting rows that are not matched.

Answer

Run a DELETE against DimCustomer for all CustomerIDs found in staging, then an INSERT of every staging row.

Answer

Use a CROSS JOIN between DimCustomer and staging, then filter the result to keep only changed rows.

Answer

Create a view over both tables with UNION ALL and query it to see the combined customers.