Referential Integrity Flashcards
(44 cards)
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.
