Change Tracking

Question 1

What are the two possible types of change tracking in Caboodle?

Answer 1

1. None (Type 1)
2. Snapshot Change Tracking (Type 2)

(Aug. 2021, 4.4)

Question 2

In the Cogito dictionary, what indicates that a TABLE is using snapshot change tracking (Type 2)?

Answer 2

There will be a chip in the overview section of the table that states this.

(Aug. 2021, 4.4)

Question 3

Does every table in Caboodle use change tracking?

Answer 3

No.

Aug. 2021, 4.5

Question 4

What does change tracking allow tables to do?

Answer 4

Change tracking allows snapshot tables/columns to store both current data and historical data.

(Aug. 2021, 4.6)

Question 5

What is the granularity of a snapshot table?

Answer 5

One row per entity for a data range.

Aug. 2021, 4.6

Question 6

What is the granularity of a non-snapshot table?

Answer 6

One row per entity extracted to the table.

Question 7

When does Caboodle create a new row for a snapshot column?

Answer 7

When any data stored in snapshot column changes a new row is created with the updated data while the old value is retained in the old row.

(Aug. 2021, 4.6)

Question 8

How would you ensure that only current data is returned when using snapshot tables?

Answer 8

By including the condition IsCurrent = 1 you will return only the most current data from your snapshot tables.

The condition IsCurrent = 1 is included when…

1. In the join condition when joining on the DurableKey column.
2. In the WHERE clause of the query.

(Aug. 2021, 4.10)

Question 9

What is a convenient way the Caboodle dictionary allows you to identify snapshot COLUMNS?

Answer 9

For snapshot tables in the Columns section of the dictionary, you will be able to sort by the Snapshot column.

(Aug. 2021, 4.6)

Question 10

Does snapshot data capture when a change occurred?

Answer 10

No. Snapshot data captures the date the value was loaded into Caboodle.

(Aug. 2021, 4.7)

Question 11

Is it possible to determine the date that a change took place?

Answer 11

Yes. Check the data lineage to determine the Chronicles item and/or Clarity table and column that store this information.

(Aug. 2021, 4.7)

Question 12

What additional columns do snapshot tables have to help make sense of the data?

Answer 12

1. The Durable Key
2. Start Date
3. End Data
4. IsCurrent

(Aug. 2021. 4.7-4.8)

Question 13

What does the DurableKey identify?

Answer 13

The DurableKey is used to identify unique entities in the table.

Every snapshot table in Caboodle has a column called DurableKey. 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.

(Aug. 2021, 4.8)

Question 14

What do the StartDate and the EndDate store for snapshot tables?

Answer 14

The date range for the snapshot data.

Aug. 2021, 4.8

Question 15

In a snapshot table what column stores the value of 1 if the row holds the most current information and 0 otherwise?

Answer 15

The IsCurrent column.

Aug. 2021, 4.8

Question 16

Do fact tables use change tracking?

Answer 16

No. Change tracking is only used in dimension tables.

Question 17

What is the correct way to join to a snapshot table?

Answer 17

1. Join on the DurableKey
2. Filter the IsCurrent column for 1

When joining to a snapshot table, use the DurableKey lookup column in the “starting” table to join to the DurableKey of the target table.

(Aug. 2021, 4.10)

Question 18

What ways can you include only the most current data for snapshot tables?

Answer 18

1. Include it in the join conditions
   Ex: BillingTransactionFact.EmployeeDurableKey = EmployeeDim.DurableKey AND EmployeeDim.IsCurrent = 1
2. Include it in the WHERE clause of the query

(Aug. 2021, 4.10)

Question 19

Why wouldn’t you use the primary key when aggregating data?

Answer 19

The primary key values would return inaccurate results, since there may be multiple primary key values representing the same entity.

(Aug. 2021, 4.11)

Question 20

What key column should you use when aggregating data?

Answer 20

The DurableKey. Using the primary key values would return inaccurate results, since there may be multiple primary key values representing the same entity.

(Aug. 2021, 4.11)

Question 21

What happens if a change is made to a non-snapshot column?

Answer 21

The data is overwritten.

Question 22

What is a durable key?

Answer 22

Snapshot tables have a durable key column which stores the same value for all rows that correspond to the same entity. This allows for accurate grouping and summarizing.