Which change detection method should you use for each scenario?

DRAG DROP

You are designing the data warehouse to import data from three different environments. The sources for the data warehouse will be loaded every hour.

Scenario A includes tables in a Microsoft Azure SQL Database:

– Millions of updates and inserts occur per hour

– A periodic query of the current state of rows that have changed is needed.

– The change detection method needs to be able to ignore changes to some columns in a table.

– The source database is a member of an AlwaysOn Availability group.

Scenario B includes tables with status update changes:

– Tracking the duration between workflow statuses.

– All transactions must be captured, including before/after values for UPDATE statements.

– To minimize impact to performance, the change strategy adopted should be asynchronous.

Scenario C includes an external source database:

– Updates and inserts occur regularly.

– No changes to the database should require code changes to any reports or applications.

– Columns are added and dropped to tables in the database periodically. These schema changes should not require any interruption or reconfiguration of the change detection method chose.

– Data is frequently queried as the entire row appeared at a past point in time.

All tables have primary keys.

You need to load each data source. You must minimize complexity, disk storage, and disruption to the data sources and the existing data warehouse.

Which change detection method should you use for each scenario? To answer, drag the appropriate loading methods to the correct scenarios. Each source may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

View Answer

Answer:

Explanation:

Box A: System-Versioned Temporal Table

System-versioned temporal tables are designed to allow users to transparently keep the full history of changes for later analysis, separately from the current data, with the minimal impact on the main OLTP workload.

Box B: Change Tracking

Box C: Change Data Capture

Change data capture supports tracking of historical data, while that is not supported by change tracking.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios