Referential Integrity

Question 1

What is Referential Integrity?

Answer 1

Lookup columns never contain null values.

Every value in a lookup column, has a match in the destination table.

Question 2

What were the three mechanisms Caboodle uses to enforce referential integrity?

Answer 2

Negative surrogate keys. Every table has the three negative surrogate keys.

Default values. Caboodle supplies a default value (-1,-2,-3) when Source data is unavailable.

Inferred rows (with -1, *Unspecified) which act as placeholders until the relevant info is loaded into Caboodle.

Question 3

-1 surrogate key equates to what in the source system?

Answer 3

NULL = *Unspecified

Question 4

What does the WHERE clause look like to eliminate these negative surrogate keys?

Answer 4

WHERE ________Key > 0

Question 5

-2 surrogate key equates to what in the source system?

Answer 5

*Not applicable (string), -2 (foreign keys)

Question 6

-3 surrogate key

Answer 6

*Deleted

Question 7

What kind of row is created by the ETL if the referenced row does not exist yet?

Answer 7

Inferred Row and all it’s string values are set to *Unknown

Question 8

All Fact tables have a Count column which normally stores a 1, when will it store a 0?

Answer 8

When the rows are negative or deleted