Change Tracking Flashcards
True or False: Any change to patient data in Clarity will generate a new row in PatientDim after the next extract.
False. Assuming the data is extracted to Caboodle, only changes to snapshot columns would generate a new row in PatientDim.
What is a durable key?
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.
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>
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>
refers to the method used to record changes to data over time.
Change tracking
Tracks changes to snapshot data, creating new rows for each change.
Snapshot Change Tracking (Type 2)
No change tracking; the table reflects the current content of the source data at the time of the most recent extract.
None (Type 1)
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.
snapshot table
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.
Snapshot columns
a special surrogate key used in snapshot tables to identify unique entities
durable key column
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.
StartDate column
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.
EndDate column
is a flag that indicates whether a row holds the most current information
IsCurrent
True or False: Durable keys are used only in tables that store snapshot (type 2) data.
True
True or False: foreign key columns never contain null or unmatched values.
True
True or False: Snapshot change tracking is also known as Type 1 change tracking.
False. Explanation: Snapshot change tracking is known as Type 2, while Type 1 refers to tables with no change tracking.
True or False: Non-snapshot tables reflect the current content of the source data at the time of the most recent extract.
True. Explanation: Non-snapshot tables do not track historical changes and only show the latest data from the source.
True or False: The DurableKey column in a snapshot table uniquely identifies each row in the table.
False. Explanation: The DurableKey identifies unique entities across multiple rows, not individual rows themselves.
True or False: The IsCurrent column in a snapshot table indicates if the row holds the most current information.
True. Explanation: The IsCurrent column is a flag that shows whether a row contains the latest data (1 for current, 0 otherwise).
True or False: Any change to a patient’s data in Clarity will generate a new row in PatientDim after the next extract.
False. Explanation: Only changes to snapshot columns will result in a new row being added to a snapshot table.
True or False: Snapshot data tracks the exact date and time when a change occurred in the source system.
False. Explanation: Snapshot data only tracks when changes were captured in Caboodle ETL, not the exact time of the change in the source system.
True or False: The StartDate and EndDate columns in a snapshot table represent the date range for the snapshot data.
True. Explanation: These columns indicate the period during which the snapshot data was valid in Caboodle.
True or False: The condition IsCurrent = 1 should be used when joining to snapshot tables to return only the most current data.
True. Explanation: Including IsCurrent = 1 ensures that only the latest data is retrieved from snapshot tables.
True or False: In Caboodle, tables with no change tracking are labeled as Type 2.
False. Explanation: Tables with no change tracking are labeled as Type 1, while Type 2 refers to snapshot change tracking.
True or False: The DurableKey column can have the same value in multiple rows of a snapshot table.
True. Explanation: The DurableKey can repeat across multiple rows for the same entity, representing different snapshots over time.
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
B) Snapshot change tracking
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
B) Unique entities across multiple rows
Which column in a snapshot table indicates if the row holds the most current information?
A) DurableKey
B) StartDate
C) EndDate
D) IsCurrent
D) IsCurrent
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
B) To store the date range for the snapshot data
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
B) They reflect the current content of the source data
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
B) Only the most current data is returned
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
B) Non-snapshot change tracking
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
C) DurableKey column
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
B) It tracks the exact date and time of changes in the source system
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
C) To identify unique entities across multiple rows
