ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-767 All Questions

View all questions & answers for the 70-767 exam
Go to Exam

Exam 70-767 topic 1 question 88 discussion

Actual exam question from Microsoft's 70-767
Question #: 88
Topic #: 1
[All 70-767 Questions]

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.
Select and Place:

Show Suggested Answer Hide Answer
Suggested 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
by als2kool at April 18, 2020, 4:53 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
CGLUE
Highly Voted 4 years, 11 months ago
A: Change Tracking because "AlwaysOn Availability group", current state of rows B: CDC because -> All transactions must be captured, including before/after values for UPDATES, asynchronous! C: System-Versioned Temporal Table because "queried as the entire row appeared at a past point in time"
upvoted 17 times
...
DudeHere
Most Recent 4 years, 5 months ago
CDC CDC Temporal if you reference Lil Kendra's resource: https://littlekendra.com/2010/06/23/cdcvsct
upvoted 2 times
...
DudeHere
4 years, 5 months ago
Change Tracking (CT) Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred. It is recommended to use snapshot isolation with Change Tracking! (See the links below for details on why.) Change Data Capture (CDC) Change Data Capture is asynchronous and uses the transaction log in a manner similar to replication. Past values of data are maintained and are made available in change tables by a capture process, managed by the SQL Agent, which regularly scans the T-Log. As with replication, this can prevent re-use of parts of the log. https://littlekendra.com/2010/06/23/cdcvsct/ Temporal tables are the only option that allows to query historical data. Where were these when I started with SQL in HR? lol
upvoted 3 times
...
Dieter
5 years ago
Join in with als2kool. In addition: Scenario A: A periodic query of the current state of rows that have changed is needed. => if only the current state is needed, Change tracking would be enought IMHO. Scenario B: All transactions must be captured, including before/after values for UPDATE statements. => that is only possible with CDC (not CT). ✑ To minimize impact to performance, the change strategy adopted should be asynchronous. => another point for CDC. I am unsure about scenario C. who can help?
upvoted 2 times
...
als2kool
5 years ago
CDC is asynchronous so what gives with B?
upvoted 1 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago