Caboodle Data Model Exam Questions with Correct Answers Graded A+
What is a primary reason that snapshot data should not be used for audit reporting? - Answers Snapshot
data is tracked by the Caboodle ETL process and does not track when a specific change took place.
Snapshot columns retain... - Answers Previous values which been extracted to Caboodle and have since
changed in the source.
How might you be able to determine the date when a change took place? - Answers Check the data
lineage to determine the Chronicles item and/or Clarity table and column that store this information.
For non‐Epic data, work with your Caboodle developers to determine relevant data lineage.
What 4 columns do snapshot tables include? - Answers Iscurrent, StartDate, EndDate, DurableKey
DurableKey - Answers While there may be multiple rows in a snapshot table for a given entity, each with
a unique primary key value, the DurableKey column stores the same value for all rows in the table that
refer to the same entity.
The StartDate and EndDate columns refer to: - Answers Store the date range for the snapshot data in
the table. For the rows that hold the earliest and most recent versions of the data recorded in Caboodle,
the StartDate and EndDate are set to a default start and end date value of 1/1/1979 and 12/31/2099.
Lookup columns will join to either the surrogate key or DurableKey depending upon: - Answers Whether
that table uses change tracking.
When joining to a snapshot table do these two things: - Answers Use the durable key lookup column in
the starting table to join to the durable key of the target table. Then, filter to the current row of the
snapshot table
Foreign keys that join to the surrogate key of a snapshot table point to the row that... - Answers Was
current at the time the data was loaded. Therefore, such foreign keys could point to stale data in
snapshot tables.
True or False: There will rarely be a lookup column to the durable key of a snapshot table - Answers
False. There will always be.
When comparing durable key values, be sure to remove what? - Answers The negative values: ‐1, ‐2, ‐3.
Neglecting to do so may result in false positives.
True or False: Any change to patient data in Clarity will generate a new row in PatientDim after the next
extract. - Answers False. Assuming the data is extracted to Caboodle, only changes to snapshot columns
would generate a new row in PatientDim.
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. - Answers A and C
Databases that use referential integrity will always have 2 properties: - Answers 1. Foreign key columns
will always have a value even if the source data is null
2. Foreign key column values will always have a matching value in the destination table/column
What value indicates unspecified? - Answers -1
What value indicates Not Applicable? - Answers -2
What value indicates Deleted? - Answers -3
What does -1 indicate? - Answers A NULL value in the source system
Since many date columns in Caboodle are foreign keys to the DateDim table ... ? - Answers NULL dates
from Clarity are often represented with a ‐1 value DateKey columns in Caboodle.
Give the example of an unspecified data point that would show as -1. - Answers If a patient has not been
assigned a primary care physician. When the patient's record is loaded into Caboodle, the
PrimaryCareProviderKey column is set to ‐1.
What example does the text provide for a Not Applicable value? - Answers A non-Epic source will not
have an EpicCsn.
To know why a ‐2 value is in a key column, what should you do? - Answers You should investigate that
column's data lineage in the Caboodle Dictionary.
A value of ‐3 in a foreign key column indicates what? - Answers That the entity represented by the row
itself was deleted, not that the entity the foreign key is pointing to was deleted.
True or False: Only snapshot tables in Caboodle has the 3 special rows. - Answers False
What are the 3 special rows in Caboodle tables? - Answers Rows with primary keys of -1, -2, and -3.
In the special row of -1 primary key, what value do strings show? - Answers *Unspecified
In the special row of -3 primary key, what value do strings show? - Answers *Deleted
In the special row of -2 primary key, what value do strings show? - Answers *Not Applicable
In the 3 special rows, other than keys and strings, what are the values in the other columns? - Answers
NULL
What is a primary reason that snapshot data should not be used for audit reporting? - Answers Snapshot
data is tracked by the Caboodle ETL process and does not track when a specific change took place.
Snapshot columns retain... - Answers Previous values which been extracted to Caboodle and have since
changed in the source.
How might you be able to determine the date when a change took place? - Answers Check the data
lineage to determine the Chronicles item and/or Clarity table and column that store this information.
For non‐Epic data, work with your Caboodle developers to determine relevant data lineage.
What 4 columns do snapshot tables include? - Answers Iscurrent, StartDate, EndDate, DurableKey
DurableKey - Answers While there may be multiple rows in a snapshot table for a given entity, each with
a unique primary key value, the DurableKey column stores the same value for all rows in the table that
refer to the same entity.
The StartDate and EndDate columns refer to: - Answers Store the date range for the snapshot data in
the table. For the rows that hold the earliest and most recent versions of the data recorded in Caboodle,
the StartDate and EndDate are set to a default start and end date value of 1/1/1979 and 12/31/2099.
Lookup columns will join to either the surrogate key or DurableKey depending upon: - Answers Whether
that table uses change tracking.
When joining to a snapshot table do these two things: - Answers Use the durable key lookup column in
the starting table to join to the durable key of the target table. Then, filter to the current row of the
snapshot table
Foreign keys that join to the surrogate key of a snapshot table point to the row that... - Answers Was
current at the time the data was loaded. Therefore, such foreign keys could point to stale data in
snapshot tables.
True or False: There will rarely be a lookup column to the durable key of a snapshot table - Answers
False. There will always be.
When comparing durable key values, be sure to remove what? - Answers The negative values: ‐1, ‐2, ‐3.
Neglecting to do so may result in false positives.
True or False: Any change to patient data in Clarity will generate a new row in PatientDim after the next
extract. - Answers False. Assuming the data is extracted to Caboodle, only changes to snapshot columns
would generate a new row in PatientDim.
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. - Answers A and C
Databases that use referential integrity will always have 2 properties: - Answers 1. Foreign key columns
will always have a value even if the source data is null
2. Foreign key column values will always have a matching value in the destination table/column
What value indicates unspecified? - Answers -1
What value indicates Not Applicable? - Answers -2
What value indicates Deleted? - Answers -3
What does -1 indicate? - Answers A NULL value in the source system
Since many date columns in Caboodle are foreign keys to the DateDim table ... ? - Answers NULL dates
from Clarity are often represented with a ‐1 value DateKey columns in Caboodle.
Give the example of an unspecified data point that would show as -1. - Answers If a patient has not been
assigned a primary care physician. When the patient's record is loaded into Caboodle, the
PrimaryCareProviderKey column is set to ‐1.
What example does the text provide for a Not Applicable value? - Answers A non-Epic source will not
have an EpicCsn.
To know why a ‐2 value is in a key column, what should you do? - Answers You should investigate that
column's data lineage in the Caboodle Dictionary.
A value of ‐3 in a foreign key column indicates what? - Answers That the entity represented by the row
itself was deleted, not that the entity the foreign key is pointing to was deleted.
True or False: Only snapshot tables in Caboodle has the 3 special rows. - Answers False
What are the 3 special rows in Caboodle tables? - Answers Rows with primary keys of -1, -2, and -3.
In the special row of -1 primary key, what value do strings show? - Answers *Unspecified
In the special row of -3 primary key, what value do strings show? - Answers *Deleted
In the special row of -2 primary key, what value do strings show? - Answers *Not Applicable
In the 3 special rows, other than keys and strings, what are the values in the other columns? - Answers
NULL
