Change Tracking

Question 1

True or False: Any change to patient data in Clarity will generate a new row in PatientDim after the next extract.

Answer 1

False. Assuming the data is extracted to Caboodle, only changes to snapshot columns would generate a new row in PatientDim.

Question 2

What is a durable key?

Answer 2

Snapshot tables have a durable key column which stores the same value for all rows that correspond to the same entity. This allows for accurate grouping and summarizing.

Question 3

Consider the following query:
SELECT DurableKey FROM <TableName>
Which of the following statements are true?
A. This query will only run if <TableName> is a snapshot table
B. This query will run regardless of whether <TableName> has change tracking
C. The value in the DurableKey column could repeat in multiple rows of the results
D. There is no table in Caboodle for which this query will run</TableName></TableName></TableName>

Answer 3

a. This query will only run if <TableName> is a snapshot table
c. The value in the DurableKey column could repeat in multiple rows of the results</TableName>

Question 4

refers to the method used to record changes to data over time.

Answer 4

Change tracking

Question 5

Tracks changes to snapshot data, creating new rows for each change.

Answer 5

Snapshot Change Tracking (Type 2)

Question 6

No change tracking; the table reflects the current content of the source data at the time of the most recent extract.

Answer 6

None (Type 1)

Question 7

a table that stores both current and historical data. It captures changes to specific columns (snapshot columns) by creating new rows for each change, allowing the table to maintain a history of data changes over time.

Answer 7

snapshot table

Question 8

are columns in a snapshot table that retain previous values which have been extracted to Caboodle and have since changed in the source. Changes to these columns result in new rows being added to the snapshot table.

Answer 8

Snapshot columns

Question 9

a special surrogate key used in snapshot tables to identify unique entities

Answer 9

durable key column

Question 10

in a snapshot table stores the date when the snapshot data for a row became valid. It represents the beginning of the date range during which the data was current.

Answer 10

StartDate column

Question 11

column in a snapshot table stores the date when the snapshot data for a row ceased to be valid. It represents the end of the date range during which the data was current.

Answer 11

EndDate column

Question 12

is a flag that indicates whether a row holds the most current information

Answer 12

IsCurrent

Question 13

True or False: Durable keys are used only in tables that store snapshot (type 2) data.

Answer 13

True

Question 14

True or False: foreign key columns never contain null or unmatched values.

Answer 14

True

Question 15

True or False: Snapshot change tracking is also known as Type 1 change tracking.

Answer 15

False. Explanation: Snapshot change tracking is known as Type 2, while Type 1 refers to tables with no change tracking.

Question 16

True or False: Non-snapshot tables reflect the current content of the source data at the time of the most recent extract.

Answer 16

True. Explanation: Non-snapshot tables do not track historical changes and only show the latest data from the source.

Question 17

True or False: The DurableKey column in a snapshot table uniquely identifies each row in the table.

Answer 17

False. Explanation: The DurableKey identifies unique entities across multiple rows, not individual rows themselves.

Question 18

True or False: The IsCurrent column in a snapshot table indicates if the row holds the most current information.

Answer 18

True. Explanation: The IsCurrent column is a flag that shows whether a row contains the latest data (1 for current, 0 otherwise).

Question 19

True or False: Any change to a patient’s data in Clarity will generate a new row in PatientDim after the next extract.

Answer 19

False. Explanation: Only changes to snapshot columns will result in a new row being added to a snapshot table.

Question 20

True or False: Snapshot data tracks the exact date and time when a change occurred in the source system.

Answer 20

False. Explanation: Snapshot data only tracks when changes were captured in Caboodle ETL, not the exact time of the change in the source system.

Question 21

True or False: The StartDate and EndDate columns in a snapshot table represent the date range for the snapshot data.

Answer 21

True. Explanation: These columns indicate the period during which the snapshot data was valid in Caboodle.

Question 22

True or False: The condition IsCurrent = 1 should be used when joining to snapshot tables to return only the most current data.

Answer 22

True. Explanation: Including IsCurrent = 1 ensures that only the latest data is retrieved from snapshot tables.

Question 23

True or False: In Caboodle, tables with no change tracking are labeled as Type 2.

Answer 23

False. Explanation: Tables with no change tracking are labeled as Type 1, while Type 2 refers to snapshot change tracking.

Question 24

True or False: The DurableKey column can have the same value in multiple rows of a snapshot table.

Answer 24

True. Explanation: The DurableKey can repeat across multiple rows for the same entity, representing different snapshots over time.

Question 25

Which type of change tracking is known as Type 2 in Caboodle? A) Non-snapshot change tracking B) Snapshot change tracking C) Real-time change tracking D) Incremental change tracking

Answer 25

B) Snapshot change tracking

Question 26

What does the DurableKey column in a snapshot table identify? A) Unique rows in the table B) Unique entities across multiple rows C) The date range of the snapshot data D) The most current information

Answer 26

B) Unique entities across multiple rows

Question 27

Which column in a snapshot table indicates if the row holds the most current information? A) DurableKey B) StartDate C) EndDate D) IsCurrent

Answer 27

D) IsCurrent

Question 28

What is the purpose of the StartDate and EndDate columns in a snapshot table? A) To identify unique entities B) To store the date range for the snapshot data C) To indicate if the row is current D) To track changes in the source system

Answer 28

B) To store the date range for the snapshot data

Question 29

Which of the following statements is true about non-snapshot tables? A) They track historical changes B) They reflect the current content of the source data C) They use the DurableKey column D) They have multiple rows for the same entity

Answer 29

B) They reflect the current content of the source data

Question 30

What does the condition IsCurrent = 1 ensure when joining to snapshot tables? A) Only historical data is returned B) Only the most current data is returned C) All rows for an entity are returned D) The date range of the snapshot data is included

Answer 30

B) Only the most current data is returned

Question 31

Which type of change tracking was labeled as Type 1 in May 2020 and earlier versions of Caboodle? A) Snapshot change tracking B) Non-snapshot change tracking C) Real-time change tracking D) Incremental change tracking

Answer 31

B) Non-snapshot change tracking

Question 32

What should be used for accurate aggregation in snapshot tables? A) Primary key values B) StartDate and EndDate columns C) DurableKey column D) IsCurrent column

Answer 32

C) DurableKey column

Question 33

Which of the following is NOT true about snapshot data? A) It tracks when changes were captured in Caboodle ETL B) It tracks the exact date and time of changes in the source system C) It can have multiple rows for the same entity D) It requires additional filtering to return current data

Answer 33

B) It tracks the exact date and time of changes in the source system

Question 34

What is the primary purpose of the DurableKey column in snapshot tables? A) To store the date range for the snapshot data B) To indicate if the row is current C) To identify unique entities across multiple rows D) To track changes in the source system

Answer 34

C) To identify unique entities across multiple rows