CDW 100V Caboodle Flashcards

Question

Snapshot change tracking

Answer 1

have extra rows to store old data, snapshots of entities in a time period - can identify them in the data dictionary by the CHIP don't have granularity of 1:1 -

Answer 2

if source data gets updated, then the data shown in the table gets updated as well (onedrive file) snapshot tracking- shows old rows with old data all items in the column will change in each row, not just the most recently changed stuff

Answer 3

retains old data in a snapshot table Will have multiple rows and variable results in each row post change. Will not have all the same info like non snapshot would (Name change)

Answer 4

does not show when new data was input, just the ETL date - need to check the DATA LINEAGE to determine the chronicles item or clarity table and column that store this info

Answer 5

surrogate key in caboodle identifies unique entities in a table - ex. pts with the same name, would have unique ids to show theyre 2 diff people only found in snapshot tables

Answer 6

have both snapshot and non snapshot columns - if its a snapshot column, new data will keep the old rows - if its a non snapshot column, no new row added, all the data in that column gets updated

Answer 7

1. Lookup columns (key columns) will always have a value (isnotnull) 2. Lookup column values will always find a matching value in the destination table Not enforced in filteredaccess schema

Answer 8

-1; expected, but null in the source = unspecified -1 contains nulls ONLY in KEY COLUMNS *Unspecified in Strings "NULL" = date, integer columns

Answer 9

-2 ALSO ONLY IN KEY COLUMNS- IF COLUMN ENDS IN KEY

Answer 10

Created to maintain 2nd part of referential integrity- WHEN THERE IS A MISMATCH - that a column will always find a match Does this by creating an INFERRED ROW when needed ( when one table has more up to date info than the other) Add 3 special rows to every table

Answer 11

a table with a LOOKUP COLUMN goes through ETL , not when the table itself goes through ETL - some tables updated less often than nightly bc theyre not used as much

Answer 12

Only thing that will remain is the PRIMARY key will show as -3 for lookup columns *deleted (star deleted) for strings metadata column _isdeleted column = binary 1 date/ time = "NULL"

Answer 13

1. First, outline the tables that you will need that has all the columns needed - can search data dictionary or anal cat to find the tables - this is your select clause- "Table.column" check to see and label which is a snapshot table vs non snapshot table 2. From _Table 3. Join Clause a. map out tables to find where you will join: "key to key" b. see what tables are snapshot and what are non snapshot - join durable key to durable key for snapshot tables - typically the key table is the center of the star schema to link to other dim tables c. Join all the above tables.columns in the select clause laid out 4. write in condition to iscurrent=1 to consider snapshot table data that may have duplicate older rows b. could also do a where clause "where table.count = 1 - snapshot table that would have the rows = 1 count only exists in FACT tables, not dim tables

Answer 14

used to model (in joins) many to many relationships ex pt and dx

Answer 15

2 columns 1. Combokey- lookup column for the fact 2. key - lookup column for the dimension

Answer 16

know youll need a bridge table when you see a combokey column bridged to bridge tables

Answer 17

collection of data on a topic - makes it easier to write reports - designed for a specific reporting need Should save time to write a report group of data/topics together

Answer 18

Tables with lists in them almost like what we're doing with groupers Ex. Give me a list of all meds that put me to sleep - tranqs Ex. Give me a list of all outpatient departments