Practice Questions Flashcards
True or False: The Cogito Data Dictionary documents the tables and columns in each organization’s Caboodle.
True
What schema does the Caboodle portion of the Cogito Data Dictionary document?
The dbo schema
How does data get into Caboodle?
Data gets into Caboodle through SSIS packages from Clarity or a 3rd party database.
True or False: More than one SSIS package can populate a Caboodle table.
True
True or False: In Caboodle, primary key columns store Chronicles identifiers, such as a record ID or a CSN.
False. All primary key columns in Caboodle are surrogate keys.
True or False: All Caboodle tables have a DurableKey column.
False. Only tables with snapshot change tracking will have a column named ‘DurableKey’ used to identify the entity.
True or False: To see if a table tracks changes, you should look for a chip that says Snapshot in the Cogito Dictionary.
True
If a Caboodle table uses snapshot change tracking, then all of the columns in that table are snapshot columns.
False. A table with snapshot change tracking will have at least one column that tracks changes, but not every column will track changes.
What databases comprise Caboodle?
Staging and Reporting
Collection of Database Objects
Schema
Epic Released Schemas
dbo, FullAccess, FilteredAccess
is the data source for SlicerDicer, Caboodle Dictionary in the Caboodle Console and the Caboodle portion of the Cogito Data Dictionary
dbo schema
everything for report writers from the dbo schema and more (transformation for 0/1 to No/Yes and other simple transformations)
FullAccess schema
Used for column and row level security (common in Community Connect orgs)
FilteredAccess schema
True or False: A column can either be a primary key or a surrogate key, but not both.
False
What extra columns do change tracked (snapshot) tables have?
StartDate, EndDate, IsCurrent, DurableKey (Separate from Key)
Does FilteredAccess enforce referential integrity?
No
Epic recommends using which schema as the default schema for writing reports?
FullAccess Schema on reporting schema
True or False: If a data points exists in Carity, but not in Caboodle, your organization’s Caboodle Developers can create a custom SSIS package to extract the data point to Caboodle?
True
How can you tell is a column is a lookup column in Caboodle
The ER diagram lists the column and what table it joins to
The Column will end in KEY but not be the primary key of the table
In the packages section, which type of package will be loading Epic data?
Clarity Packages. All EPIC data in Caboodle has a source of clarity.
Fact table generally have more ____than dimensional tables?
Lookup Columns
What type od columns exist in all fact and dimensional tables in Caboodle?
A surrogate key that acts as the primary key of the table
Zero or more lookup columns
Some tables in Caboodle retain values previously stored for columns. This describes what?
Change tracking
Table A has a lookup column directed to Table. An execution runs, which includes a package that loads data to Table A, but does not contain any packages which will populate Table B. Which table could have an inferred row added to as a result?
Table B
If the primary key of a row in a fact table is -1
the lookup columns will store a -1
columns with a data type of varchar will store unspecified
A combo key is used as a lookup column to what Caboodle tables?
Bridge
What is referential integrity?
A) Ensuring all tables have primary keys
B) Ensuring lookup columns always have a value and a matching value in the destination table
C) Ensuring all columns are indexed
D) Ensuring all tables are normalized
B) Ensuring lookup columns always have a value and a matching value in the destination table
What value does Caboodle assign to a lookup column when the source data is NULL?
A) 0
B) -1
C) -2
D) -3
B) -1
What does a value of -2 in a lookup column indicate?
A) The value is unspecified
B) The value is not applicable
C) The value is deleted
D) The value is inferred
B) The value is not applicable
Which schema in Caboodle does NOT enforce referential integrity?
A) dbo
B) FullAccess
C) FilteredAccess
D) Reporting
C) FilteredAccess
What are inferred rows in Caboodle?
A) Rows that are deleted
B) Placeholder rows created when a lookup column value does not have a match in the destination table
C) Rows with NULL values
D) Rows with duplicate values
B) Placeholder rows created when a lookup column value does not have a match in the destination table
How can you identify inferred rows in a table?
A) By checking the _IsInferred column
B) By checking the primary key
C) By checking the foreign key
D) By checking the timestamp
A) By checking the _IsInferred column
What does a value of -3 in a lookup column indicate?
A) The value is unspecified
B) The value is not applicable
C) The value is deleted
D) The value is inferred
C) The value is deleted
What happens when a record is hard-deleted in the source system?
A) The row is removed from Caboodle
B) The row is marked with default values representing the deletion
C) The row is archived
D) The row is ignored
B) The row is marked with default values representing the deletion
Why does Caboodle create three special rows with primary key values of -1, -2, and -3?
A) To represent actual entities
B) To satisfy referential integrity
C) To improve performance
D) To store metadata
B) To satisfy referential integrity
What should you do if many inferred rows persist in the database?
A) Ignore them
B) Delete them manually
C) Reach out to your Caboodle ETL Administrator
D) Update them with default values
C) Reach out to your Caboodle ETL Administrator
Which of the following scenarios would result in a lookup column being assigned a value of -1 in Caboodle?
A) A patient has been assigned a primary care physician
B) A medication dispense record has a valid FillPharmacyKey
C) A patient’s primary care provider is not specified in the source system
D) A record has been hard-deleted in the source system
C) A patient’s primary care provider is not specified in the source system
In the context of Caboodle, what does the term “data lineage” refer to?
A) The sequence of transformations applied to data from source to destination
B) The hierarchical structure of database tables
C) The indexing strategy used for lookup columns
D) The security schema applied to database access
A) The sequence of transformations applied to data from source to destination
How does Caboodle handle a situation where a lookup column value does not use one of the default values (-1, -2, -3) and also does not have a match in the destination table?
A) It deletes the row
B) It creates an inferred row
C) It assigns a value of -1
D) It ignores the value
B) It creates an inferred row
What is the significance of the _IsInferred column in Caboodle tables?
A) It indicates whether a row has been deleted
B) It identifies rows that are placeholders for missing references
C) It marks rows that have been updated
D) It flags rows that are not applicable
B) It identifies rows that are placeholders for missing references
Which of the following best describes the purpose of the three special rows with primary key values of -1, -2, and -3 in Caboodle tables?
A) To store metadata about the table
B) To represent actual entities in the database
C) To ensure referential integrity for default values
D) To improve query performance
C) To ensure referential integrity for default values
In which scenario would a value of -2 be assigned to a lookup column in Caboodle?
A) When the source data is NULL
B) When the column is not applicable for the row
C) When the record has been hard-deleted
D) When the value is inferred
B) When the column is not applicable for the row
How does Caboodle ensure that lookup columns always have a value, even when the source data is missing or incomplete?
A) By creating inferred rows
B) By assigning default values like -1, -2, and -3
C) By enforcing strict data validation rules
D) By ignoring rows with missing data
B) By assigning default values like -1, -2, and -3
What is the impact of referential integrity enforcement in the dbo and FullAccess schemas on report writing in Caboodle?
A) It requires the use of outer joins in all queries
B) It ensures that inner joins produce the same results as left outer joins
C) It prevents the use of default values in lookup columns
D) It restricts access to certain tables
B) It ensures that inner joins produce the same results as left outer joins
Why might differences in Clarity extract schedules lead to the creation of inferred rows in Caboodle?
A) Because Clarity does not enforce referential integrity
B) Because Caboodle uses a different schema for inferred rows
C) Because the data may not be synchronized between Clarity and Caboodle
D) Because inferred rows are created only during the backfill process
C) Because the data may not be synchronized between Clarity and Caboodle
True or False: Referential integrity ensures that lookup columns always have a value and that these values match in the destination table.
True
True or False: A value of -1 in a lookup column indicates that the value is not applicable for the specific row.
False. A value of -1 indicates that the value is unspecified, meaning the source data is NULL. A value of -2 indicates that the value is not applicable for the specific row.
True or False: Caboodle enforces referential integrity in the dbo and FullAccess schemas.
True
True or False: Inferred rows are created when a lookup column value does not have a match in the destination table.
True
True or False: A value of -3 in a lookup column indicates that the entity represented by the row itself was deleted.
True: A value of -3 indicates that the entity represented by the row itself was deleted, not the entity the lookup column is referencing.
True or False: The FilteredAccess schema in Caboodle enforces referential integrity.
False: Referential integrity is not enforced in the FilteredAccess schema, which removes rows based on the report writer’s security.
True or False: The _IsInferred column in Caboodle tables helps identify rows that are placeholders for missing references.
True: The _IsInferred column is used to identify inferred rows, which are placeholders for missing references.
True or False: When a record is hard-deleted in the source system, Caboodle removes the row from the database.
False: When a record is hard-deleted in the source system, Caboodle marks the row with default values representing the deletion, but the row itself is not removed from the database.
True or False: Caboodle creates three special rows with primary key values of -1, -2, and -3 to ensure that all lookup column values find a match.
True: These special rows exist to ensure that all lookup column values, including default values, find a match, maintaining referential integrity.
True or False: Differences in Clarity extract schedules can lead to the creation of inferred rows in Caboodle.
True: Differences in Clarity extract schedules can result in temporary placeholders (inferred rows) being created in Caboodle until the actual data is available and synchronized.
which cogito tools query from caboodle
slicerdicer and workbench
True or False: All tables and columns in caboodle are written in pascal case?
True
True or False: all key columns in caboodle are surrogate keys
true
what is a surrogate key?
the columns value does not exist in the source database and is created during the ETL process. Because caboodle contains epic and non-epic data
what is a lookup columns?
a column in one table whose value identifies at least one row in another table. Most tables in caboodle have at least one
compared to fact tables, dimension table usually
contains more data and fewer lookup columns
True or False: Lookup columns end in –KEY
True
Where do you look for identifying information for validation in Caboodle?
The epicID or CSN
What is a dimension table?
one row for each entity within the set
what is a fact table
one row for each occurrence of some significant, measurable event
What is a bridge table?
for many to many relationships
what is a data mart?
report on data points from several DMCs in one place
what would you find in the data dictionary overview section?
description, type of database object, granularity
what would you find in the data dictionary columns section?
name, data type, availability in slicerdicer, links to lookup columns, chronicles info, descriptions
what would you find in the data dictionary lineage section?
available in expanded section contains INI and item numbers and how the item can be used in slicerdicer (fds) records
what would you find in the data dictionary er diagram section?
Classic star schema for investigating joins. Only for Fact and DataMart. Non join columns can also be listed by clicking the key to expand into full column list
what would you find in the data dictionary dependencies section?
Indicates what database objects a table is dependent on. Clarity or caboodle tables and columns
what would you find in the data dictionary queries section?
Lists SSIS packages that populate the table and their queries
A property of a table that informs us how Caboodle will be updated when changes are made to the source databases
Change tracking
Change tracking types
type 1: none
type 2: snapshot
How to tell if a table is using snapshot change tracking?
Chip in the overview section of the table
what is a Non snapshot table?
Reflect the current content of the source data at the time of the most recent extract
what is Non Snapchat table granularity
One row per entity extracted to the table
What is the granularity of PatientDim?
A patient record for a date range
What does the DurableKey identify in a table?
Unique entities
What are the default start and end dates?
1/1/1979, 12/31/2099
Which columns show the date range for a Snapchat column?
StartDate and EndDate
True or false: for a given DurableKey value, there is exactly one row where IsCurrent = 1
True
What is the correct way to join a snapshot table?
Join on DurableKey and filter IsCurrent = 1
True or false: DurableKey only exist in snapshot tables
True
What is Referential Integrity?
Lookup columns will always have a value and that value will find a match in the destination table
True or false: due to referential integrity, there are no nulls in Caboodle tables
False. Nulls can exist in non-lookup columns
True or false: Caboodle enforces referential integrity in all 3 schemas
False, only dbo and FullAccess, not in FilteredAccess
What is the lookup value or unspecified values?
-1
What is the lookup value for non-applicable values?
-2
What is the lookup value for deleted values?
-3
How to filter out deleted data from a Fact table?
Use the Count column. It stores a 1 when the row should be included in a calculation and a 0 when not
What happens to the Durable Key when there is a deletion?
It remains the same in the new row
What happens string cells when there is a deletion?
*Deleted
What happens to all other cells when there is a deletion?
NULL
How can you tell a column is lookup?
Ends in -Key
True or false: in a Snapshot table, PrimaryKey and DurableKey remain untouched
True
Placeholder rows that are added to the destination table when the lookup column value does not have a match in the destination table
Inferred row
True or False: In Caboodle, if a lookup column value does not match any value in the destination table and is not one of the default values (-1, -2, -3), the ETL process will automatically create a new row in the destination table with the unmatched value to maintain referential integrity.
False, it creates an inferred row
