Self-Assessment Quiz: Data Modeling

Twenty questions. Aim for 16 or more. This chapter is on every learning path; treat 18 as the bar before Chapter 19.

Question 1

The strongest reason to build a dimensional model rather than copying source tables is:

• A. Query performance
• B. Storage efficiency
• C. Semantic stability — a stable interface over an unstable source
• D. Regulatory compliance

Question 2

Normalization optimizes for:

• A. Read throughput
• B. Update integrity
• C. Storage compression
• D. Join elimination

Question 3

Kimball's four steps, in order, are:

• A. Choose tables, choose keys, choose measures, choose grain
• B. Select business process, declare grain, identify dimensions, identify facts
• C. Declare grain, select business process, identify facts, identify dimensions
• D. Identify facts, identify dimensions, declare grain, select business process

Question 4

Step 1 selects a business process rather than a report because:

• A. Reports change too often to model
• B. A process generates the data for every question about that activity, including unasked ones
• C. Business processes are easier to name
• D. Reports do not have measurable events

Question 5

The grain must be declared before choosing dimensions and facts because:

• A. It determines the table name
• B. It determines which dimensions are available and which measures are valid
• C. It is required by most warehouse engines
• D. It sets the partitioning scheme

Question 6

Why choose the finest grain the source supports?

• A. Finer grain compresses better
• B. You can aggregate up but never disaggregate
• C. Query engines prefer many small rows
• D. It reduces the number of dimensions needed

Question 7

on_hand_units_eod (inventory at end of day) is:

• A. Additive
• B. Semi-additive — not summable across time
• C. Non-additive
• D. A degenerate dimension

Question 8

Why does the chapter say semi-additive measures are dangerous in BI tools?

• A. BI tools cannot read integer columns
• B. Nearly every BI tool defaults to SUM for numeric columns
• C. They require a bridge table
• D. They cannot be indexed

Question 9

An accumulating snapshot fact table:

• A. Has one row per event, appended only
• B. Has one row per entity per period
• C. Has one row per entity, updated in place as milestones complete
• D. Has no measures, only keys

Question 10

The decisive reason for surrogate keys in dimensions is:

• A. Integer joins are faster
• B. Type 2 history makes the natural key non-unique
• C. Source keys are too long
• D. They are required by dbt

Question 11

Which is the deliberate exception to the surrogate-key rule in this book?

• A. dim_customer
• B. dim_product
• C. dim_date
• D. dim_promotion

Question 12

A customer moves from Colorado to Oregon. With a Type 2 dimension, an order placed before the move reports under:

• A. Oregon, because that is the customer's current region
• B. Colorado, because the order joins to the row valid at that time
• C. Both, producing two rows
• D. Neither, until the dimension is rebuilt

Question 13

A conformed dimension is one that:

• A. Has been normalized to third normal form
• B. Is shared identically across multiple fact tables
• C. Contains only Type 1 attributes
• D. Has a surrogate key

Question 14

The bus matrix has:

• A. Fact tables down the side, measures across the top
• B. Business processes down the side, dimensions across the top
• C. Source systems down the side, target tables across the top
• D. Dimensions down the side, SCD types across the top

Question 15

A factless fact table is characteristically used to:

• A. Store measures too large for a normal fact table
• B. Count non-events — things that did not happen
• C. Bridge many-to-many relationships
• D. Snapshot inventory levels

Question 16

A bridge table must carry:

• A. A surrogate key
• B. An allocation weight that sums to 1.0 per fact row
• C. Validity dates
• D. A degenerate dimension

Question 17

Revenue doubles when a promotion dimension is added to a report. This is:

• A. The chasm trap
• B. The fan trap
• C. A semi-additive error
• D. A null foreign key

Question 18

Joining two fact tables through a shared dimension without aggregating first produces:

• A. The fan trap
• B. The chasm trap
• C. Missing rows
• D. A snowflake schema

Question 19

Which mistake does the chapter call the most common and hardest to argue against?

• A. Mixed grain in one fact table
• B. Null foreign keys
• C. Snowflaking by default
• D. A model that mirrors the source

Question 20

Kestrel keeps both fct_order_item and fct_order because:

• A. Line grain is too fine for most queries
• B. Order-level measures like shipping cannot be honestly allocated to lines
• C. The BI tool requires two tables
• D. It halves storage cost

Answer Key

1. C — §6.1. Performance is the weakest of the three reasons. The refactor callout is the argument: one file changed, forty-one downstream objects untouched.

2. B — §6.2. If a value lives in one place, changing it is one write and cannot produce inconsistency. Analytics does not need that guarantee because it does not accept user writes.

3. B — §6.3. The order is the method.

4. B — §6.3. Model the process and you get the reports for free; model the report and you get one report and a request for another next week.

5. B — §6.3. At order-line grain you can attach product; at header grain you cannot. And shipping_cost is invalid at line grain without an allocation rule.

6. B — §6.3. A summary table saves nothing worth the questions it forecloses.

7. B — §6.4. 400 units Monday plus 400 Tuesday is 400 units, not 800.

8. B — §6.4, ⚠️ callout. Nothing errors; the chart renders; the number is roughly thirty times too large for a monthly view.

9. C — §6.4. The deliberate exception to append-only, and it earns it by making lag analysis a subtraction instead of a self-join.

10. B — §6.5. Which is why surrogate keys and slowly changing dimensions are always discussed together.

11. C — §6.5. YYYYMMDD as an integer. Safe because dates, uniquely, never change their history.

12. B — §6.5. Which is almost always what the business means by "revenue by region."

13. B — §6.6. Without them you have data marts that cannot talk to each other.

14. B — §6.6. Each row is a fact table; each column is a dimension; the shared columns are your conformed dimensions.

15. B — §6.7. Which eligible products were never ordered during a promotion — unanswerable from an order fact table, because non-events leave no rows.

16. B — §6.7. And the weight sum must be tested, or two promotions on one line double the revenue.

17. B — §6.9. A one-to-many join fanning out fact rows.

18. B — §6.9. A Cartesian product; numbers inflate by orders of magnitude. Aggregate to a common grain first.

19. D — §6.9. Hardest to argue against because the copy works — until the source refactor.

20. B — §6.8. An allocation rule is a business decision that will be revisited; better in one named place than embedded in a column everyone sums.

Topic map