Referential Integrity Flashcards
Why does a row store a value of -1?
because that value was NULL in the source. When a value is NULL but expected for a lookup column in Caboodle, a -1 will be stored to maintain referential integrity.
Why does a row store a value of -2?
because that row is populated by a load package that is not applicable to that row.
A lookup column in Caboodle contains the value -3. What could this mean?
There are two possibilities: Either the data that once populated this row in Caboodle has been deleted from the source or this is the row with -3 as its primary key, for which all lookup columns are also -3. To know which is true, check the primary key value for the given row: a non-negative value indicates that this row represents deleted data in the source.
Suppose you’re creating a report using EncounterFact and ProviderDim that displays the name of each provider for each encounter. If an encounter’s data loads into EncounterFact, but that provider’s data hasn’t been extracted to ProviderDim, what value would appear in ProviderDim.Name for this encounter?
There would be an inferred row in ProviderDim, therefore ProviderDim.Name would show “*Unknown” for the encounter.
If a lookup column is null in Clarity, what will appear in the corresponding column in Caboodle?
If the lookup column was null in the source and the SSIS package defines the data lineage, then a -1 will appear in Caboodle to represent an unspecified value. If the look up columns’s data lineage was not defined by the SSIS package that loaded data for a particular row, then a -2 will appear in Caboodle to represent a value that is not applicable.
A row in a Caboodle table has a primary key value of -2. What value will be stored in columns with a data type of “Date” for this row that are not lookup columns?
A. -2
B. NULL
C. 12/31/1840
D. *Not Applicable
b. NULL
For the row with a primary key of -2, lookup columns will store a value of -2, string columns will store ‘*Not Applicable’, and all other data type columns will store NULL.
True or False: All inferred rows in Caboodle have a primary key of -1.
False. Inferred rows in Caboodle have non-negative primary key values because they represent an entity that Caboodle has inferred the existence of. The lookup columns for such rows will be set to -1 until the Caboodle ETL process updates the data.
is a placeholder row created in Caboodle to maintain referential integrity when a lookup column value does not have a match in the destination table.
inferred row
is a property of SQL databases that ensures relationships between tables remain consistent. It has two main principles:
1. Lookup Columns Always Have a Value
2. Lookup Column Values Always Find a Match
Referential integrity
What does referential integrity ensure in Caboodle?
A) Lookup columns will always have a value
B) Lookup column values will always have a matching value in the destination table
C) Both A and B
D) Neither A nor B
C) Both A and B
What value is assigned to a lookup column in Caboodle when the source data is NULL?
A) 0
B) -1
C) -2
D) -3
B) -1
Which schema in Caboodle enforces referential integrity?
A) dbo
B) FullAccess
C) FilteredAccess
D) Both A and B
D) Both A and B
What does the value -1 represent in a lookup column in Caboodle?
A) Unspecified value
B) Deleted value
C) Not applicable value
D) Unknown value
A) Unspecified value
What happens when a null or unmatched value is loaded into the Caboodle staging database?
A) The value is left as NULL
B) The ETL infrastructure assigns a default value
C) The row is deleted
D) The value is flagged for review
B) The ETL infrastructure assigns a default value
Which of the following is NOT a default value used by Caboodle to represent missing or incomplete references?
A) -1
B) -2
C) -3
D) -4
D) -4
What is the purpose of inferred rows in Caboodle?
A) To represent deleted data
B) To fill in missing references
C) To track changes over time
D) To store historical data
B) To fill in missing references
Which value is used to represent a deleted reference in Caboodle?
A) -1
B) -2
C) -3
D) -4
C) -3
What does the FilteredAccess schema do in Caboodle?
A) Enforces referential integrity
B) Removes rows based on the report writer’s security
C) Ensures lookup columns always have a value
D) Assigns default values to unmatched references
B) Removes rows based on the report writer’s security
Which of the following values would you use to interpret an unspecified value in a report?
A) *Unknown
B) *Unspecified
C) *Not Applicable
D) *Deleted
B) *Unspecified
True or False: Referential integrity ensures that lookup columns will always have a value, even if the source data is null.
True. Explanation: Referential integrity in Caboodle ensures that lookup columns are always populated, even if the source data is null, by assigning default values like -1, -2, or -3.
True or False: The value -2 in a lookup column indicates that the value is unspecified.
False. Explanation: The value -2 indicates that the value is not applicable, while -1 indicates an unspecified value.
True or False: Inferred rows are created in Caboodle to fill in missing references when there is no match in the destination table.
True. Explanation: Inferred rows act as placeholders to ensure referential integrity when there is no match in the destination table.
True or False: The FilteredAccess schema enforces referential integrity in Caboodle.
False. Explanation: The FilteredAccess schema does not enforce referential integrity because it filters rows based on the report writer’s security.
True or False: A value of -3 in a lookup column indicates that the entity represented by the row itself was deleted.
True. Explanation: The value -3 signifies that the entity represented by the row was deleted, not the entity the lookup column is referencing.
True or False: Inner joins and left outer joins produce different results when joining from a lookup column in Caboodle.
False. Explanation: In Caboodle, inner joins and left outer joins produce the same results when joining from a lookup column because referential integrity ensures that lookup columns always have a matching value.
True or False: The _IsInferred column can be used to identify inferred rows in a table.
True. Explanation: The _IsInferred column is used to flag inferred rows, indicating that they are placeholders for missing references.
True or False: Caboodle uses the same default values (-1, -2, -3) for all lookup columns, regardless of the circumstances behind the null or unmatched value.
True. Explanation: Caboodle assigns default values of -1, -2, or -3 based on the specific circumstances behind the null or unmatched value.
True or False: The dbo and FullAccess schemas in Caboodle do not enforce referential integrity.
False. Explanation: The dbo and FullAccess schemas enforce referential integrity, ensuring that lookup columns always have a value and a matching value in the destination table.
True or False: When writing SQL queries in Caboodle, it is necessary to account for the three special rows with primary key values of -1, -2, and -3.
True. Explanation: These special rows exist to satisfy referential integrity and do not represent actual entities, so they should be excluded from counts and other calculations in SQL queries.
In which scenario would a lookup column in Caboodle be assigned a value of -1?
A) When the source data is deleted
B) When the source data is not applicable
C) When the source data is null
D) When the source data is unknown
C) When the source data is null
True or False: The value -1 in a lookup column indicates that the entity the lookup column is referencing was deleted.
False. Explanation: The value -1 indicates that the lookup column’s value is unspecified, not that the entity was deleted.
True or False: When a patient’s primary care provider is not assigned in the source system, the PrimaryCareProviderKey column in Caboodle will be set to -1.
True. Explanation: If a patient does not have a primary care provider assigned, the PrimaryCareProviderKey column will be set to -1 to indicate the value is unspecified.
In which scenario would a lookup column in Caboodle be assigned a value of -2?
A) When the source data is null
B) When the source data is deleted
C) When the column is not applicable for the row
D) When the source data is unknown
C) When the column is not applicable for the row
Which of the following is true about the value -2 in Caboodle?
A) It indicates that the entity represented by the row was deleted
B) It is used when a lookup column’s value is unspecified
C) It is used for inferred rows
D) It indicates that the value is not applicable
D) It indicates that the value is not applicable
True or False: The value -2 in a lookup column indicates that the entity the lookup column is referencing was deleted.**
False. Explanation: The value -2 indicates that the lookup column’s value is not applicable, not that the entity was deleted.
In which scenario would a lookup column in Caboodle be assigned a value of -3?
A) When the source data is null
B) When the source data is not applicable
C) When the entity represented by the row itself was deleted
D) When the source data is unknown
C) When the entity represented by the row itself was deleted
True or False: The value -3 in a lookup column indicates that the entity the lookup column is referencing was deleted.**
False. Explanation: The value -3 indicates that the entity represented by the row itself was deleted, not the entity the lookup column is referencing.
True or False: When an entity in a snapshot table is deleted, a new row is created with the value -3 to indicate the deletion.**
True. Explanation: When an entity in a snapshot table is deleted, a new row is created with the value -3 to indicate the deletion, and the rules for change tracking still apply.
What is the primary purpose of the three special rows with primary key values of -1, -2, and -3 in Caboodle?
A) To store historical data
B) To represent actual entities
C) To satisfy referential integrity
D) To track changes over time
C) To satisfy referential integrity
True or False: The three special rows in Caboodle represent actual entities in the database.**
False. Explanation: The three special rows do not represent actual entities; they exist solely to satisfy referential integrity.
What is the primary purpose of inferred rows in Caboodle?
A) To store historical data
B) To represent deleted entities
C) To fill in missing references
D) To track changes over time
C) To fill in missing references
In which scenario would Caboodle create an inferred row?
A) When the source data is null
B) When the entity represented by the row is deleted
C) When a lookup column value does not have a match in the destination table
D) When the column is not applicable for the row
C) When a lookup column value does not have a match in the destination table
True or False: Inferred rows are permanent and will never be updated once created.**
False. Explanation: Inferred rows are placeholders and will be updated with actual data once it becomes available in the source system.
