Analytical Data Modeling: 10 practice questions

Question 1 of 10

What does the grain of a fact table describe?

1. AThe schedule on which the source system is refreshed
2. BThe storage format chosen for the dimension rows
3. CThe level of detail that one row of the table stores
4. DThe number of dimension tables placed around the fact table

Question 2 of 10

Which table stores the values that describe each attribute recorded in a fact table?

1. AThe dimension table that the fact keys point to
2. BThe load checkpoint table that tracks progress
3. CThe fact table that stores the numeric values
4. DThe data cube that the analyst slices and dices

Question 3 of 10

What arrangement gives the star schema its name?

1. AOne source transaction copied to its replicas
2. BOne scheduler connected to the tasks of every pipeline run
3. COne fact table connected to several dimension tables
4. DOne raw file connected only to its own storage partitions

Question 4 of 10

Which item is a measure rather than a descriptive attribute?

1. ACustomer region
2. BCalendar month name
3. CProduct category
4. DTotal sales amount

Question 5 of 10

Why do analysts query a dimensional model instead of the transactional tables?

1. AIt drops the foreign keys that join facts to dimensions
2. BIt stores every measure inside the dimension tables instead of the fact table
3. CIt keeps the transactional insert, update, and delete path as the reporting path
4. DIt organizes facts and dimensions so grouping and aggregation stay simple

Question 6 of 10

Why can one dimension carry both a natural key and a surrogate key?

1. ATo force every fact to resolve to the current dimension row
2. BTo keep the source identifier while still identifying each stored row
3. CTo replace the descriptive attributes with additional numeric measures
4. DTo make the source system responsible for warehouse history

Question 7 of 10

A sales target table holds one row per subcategory per quarter. What is its grain?

1. AProduct and day
2. BCustomer and transaction
3. CSubcategory and quarter
4. DCategory and year

Question 8 of 10

An order-line fact stores one row per order and product. Joining it to the product dimension returns three fact rows for one original line, because the source identifier matches three stored versions. Totals must stay unchanged and the description must stay historically correct. What should the model do?

1. AMatch every fact to all dimension rows that share the source identifier
2. BChange the fact table so that one row represents one dimension version
3. CResolve each fact to the single version row that was valid for its event
4. DAggregate the multiplied rows and divide the totals by the version count

Question 9 of 10

A reporting team aggregates by customer, product, and date many times a day. The source is highly normalized, report authors struggle with the number of joins, and write efficiency is not a reporting priority. Which serving design fits better?

1. AA model of normalized entities holding no numeric values
2. BThe unchanged normalized source tables used as the reporting layer
3. CA star with a central fact table and directly related dimensions
4. DA separate secondary table for every level of every descriptive hierarchy

Question 10 of 10

A customer moves from Madrid to Lisbon. Finance needs last year's sales to stay attributed to Madrid while new sales use Lisbon, and the source customer identifier cannot change. Which design fits?

1. AIssue a new source identifier and rewrite the customer in the source system
2. BStore both city names on every fact and drop the customer dimension
3. CInsert a new version row with its own surrogate key and keep the natural key
4. DOverwrite the single dimension row so every fact now reports Lisbon