Data modeling

Question 1

A bike-share app is adding a staging model for its raw rides table. Which work belongs in that staging model?

Accepted Answer

Renaming columns, casting types, and light cleanup of that table.

Question 2

At a county library, analysts query the raw loan tables directly. Every time the source system renames a column, several dashboards break. What do you do first?

Accepted Answer

Add staging models so downstream work reads stable, cleaned sources.

Question 3

What is the main job of an intermediate model in a layered transform project?

Accepted Answer

Hold reusable transformation steps that several marts build on.

Question 4

A payroll service has a mart named for its finance team. Which description best fits a mart model?

Accepted Answer

A table shaped for one business area, ready for analysts to query.

Question 5

A developer at a grain co-op adds a join to the farmers table inside the staging model for deliveries. What is the concern?

Accepted Answer

Staging should stay one-to-one with its source; joins come later.

Question 6

A food bank's distribution mart selects straight from a raw source table and skips staging. What is the main risk?

Accepted Answer

Source cleanup gets repeated in many places and drifts apart.

Question 7

A team builds staging models as views and marts as tables. Why is this a common choice?

Accepted Answer

Staging logic is light, while marts are queried often.

Question 8

Why declare raw tables as named sources in a transform project instead of hard-coding table names in each model?

Accepted Answer

Lineage, freshness, and source renames are handled in one place.

Question 9

What does it mean to state the grain of a fact table?

Accepted Answer

Say exactly what one row represents, such as one line per order item.

Question 10

A ferry operator's ticket table has one row per ticket. A developer joins in passenger meal orders, which can be several per ticket. What happens to the grain?

Accepted Answer

It becomes one row per meal, so ticket counts and fares inflate.

Question 11

A fitness studio table mixes daily attendance rows with monthly summary rows, marked by a row type column. What is the core risk?

Accepted Answer

Summing the table double counts unless users filter on type.

Question 12

You are designing an order fact for a bakery chain. Analysts need sales by product and also order totals. What grain do you pick?

Accepted Answer

One row per order line, since totals roll up from lines.

Question 13

A courier's daily shipment table should have one row per shipment per day. Which test best confirms that grain?

Accepted Answer

A uniqueness test on the pair of shipment id and date.

Question 14

A water utility wants each meter's balance at the end of every day, even on days with no new readings. Which kind of fact table fits?

Accepted Answer

A periodic snapshot fact, with one row per meter per day.

Question 15

A loan servicer keeps one row per application, with a date column for each step: submitted, approved, funded, and closed. Rows are updated as steps finish. What is this?

Accepted Answer

An accumulating snapshot fact, one row per application.

Question 16

A hotel group's mart has one row per booked night. An analyst averages the nightly rate column to report average booking value. What is wrong?

Accepted Answer

Averaging nights is not per booking; roll nights up to bookings first.

Question 17

A new mart for a museum's memberships has no documented grain, and two analysts disagree on what a row means. What do you do first?

Accepted Answer

Check the keys and data, agree on grain, then document and test.

Question 18

An online thrift shop stores cart items as an array on each cart event. You unnest the array in a model. What is the new grain?

Accepted Answer

One row per item per event, so events need a distinct count.

Question 19

In a star schema, what usually goes in a dimension table?

Accepted Answer

Descriptive attributes for filtering and grouping, like region.

Question 20

Which measure in a hardware store's daily sales fact is fully additive across every dimension, including time?

Accepted Answer

Units sold

Question 21

A credit union keeps a daily snapshot of account balances. Summing balances across all days in a month gives a huge number. Why?

Accepted Answer

Balance sums across accounts but not across time.

Question 22

Why do many teams prefer a star schema over a heavily snowflaked one for analytics marts?

Accepted Answer

Fewer joins make queries simpler for analysts and often faster.

Question 23

A plumbing supply wholesaler keeps the invoice number on its sales fact table, with no separate invoice dimension. What is this called?

Accepted Answer

A degenerate dimension

Question 24

A freight broker's shipment fact has both a pickup date and a delivery date. How do you model them?

Accepted Answer

Join the same date dimension twice, once per role.

Question 25

A home solar installer has sales and support marts. Each builds its own customer dimension, and cross-mart reports never match. What fixes this?

Accepted Answer

Build one conformed customer dimension that both marts share.

Question 26

A school district wants to record which students attended which class sessions. There is no numeric measure. Which design fits?

Accepted Answer

A factless fact, one row per student per session attended.

Question 27

Analysts at a pet insurance company want one wide table for a claims dashboard. A star schema for claims already exists. What is a reasonable response?

Accepted Answer

Build it as a mart on top of the star, so logic lives once.

Question 28

A help desk ticket fact has five low-cardinality yes or no flags that clutter it. What is a common design option?

Accepted Answer

Group the flags into one small junk dimension.

Question 29

A garden center's fiscal year starts in February, and analysts keep computing fiscal quarters wrong in their own queries. What fixes this?

Accepted Answer

Add fiscal year and quarter columns to a shared date dimension.

Question 30

What does a type 1 slowly changing dimension do when an attribute changes?

Accepted Answer

Overwrites the old value, so only the current value is kept.

Question 31

A seed supplier's customers sometimes move between sales regions. Finance wants past sales to stay credited to the region at the time of sale. What fits?

Accepted Answer

A type 2 customer dimension, joined by sale time.

Question 32

Which set of columns is typical in a type 2 dimension?

Accepted Answer

Valid from, valid to, and a current row flag.

Question 33

A scooter rental app has a type 2 vehicle dimension. A new model joins trips to it on vehicle id alone, and trip totals jump. Why?

Accepted Answer

Each trip matches every version of its vehicle.

Question 34

A test on a payroll service's type 2 employee dimension finds two rows for one employee with overlapping valid from and valid to ranges. Why does this matter?

Accepted Answer

A point-in-time join can match both rows and duplicate facts.

Question 35

When is a type 1 change a sensible choice for an attribute?

Accepted Answer

When the change is a correction, like fixing a misspelled name.

Question 36

How do many teams represent valid to on the current row of a type 2 dimension?

Accepted Answer

A null or a fixed far-future date, applied the same way everywhere.

Question 37

An orchard co-op's supplier system only stores current values and overwrites them on change. You now need supplier history. What do you do?

Accepted Answer

Capture regular snapshots and build type 2 history from them.

Question 38

A climbing gym's member dimension tracks phone number as type 1 and membership tier as type 2. A member changes only their phone. What happens?

Accepted Answer

The phone is overwritten, and no new version is created.

Question 39

A pharmacy chain joins sales to a type 2 product dimension to get the price tier at the time of sale. Which join condition is right?

Accepted Answer

Product key matches and sale time is within the validity window.

Question 40

Why add a surrogate key to a dimension instead of relying only on the source system's id?

Accepted Answer

It stays stable across sources and can tell type 2 versions apart.

Question 41

After a merger, a vet clinic network has two patient systems whose ids overlap, since both start at 1. How do you key the combined patient dimension?

Accepted Answer

Build the key from the source name plus the source id, or a hash of both.

Question 42

A team builds surrogate keys as a hash of natural key columns instead of an auto-increment sequence. What is the main benefit?

Accepted Answer

The same input always yields the same key on rebuilds.

Question 43

A model builds a hashed key by concatenating three columns. Some keys come out null, and a few different rows share a key. What fixes both problems?

Accepted Answer

Coalesce each column and add a separator before hashing.

Question 44

Which is the weakest choice of natural key for a customer dimension at a meal-kit service?

Accepted Answer

Email address, since customers can change it.

Question 45

At a print shop, some order rows arrive with a null product key because the product record has not loaded yet. What is a common approach?

Accepted Answer

Point them at a reserved unknown row in the product dimension.

Question 46

Why store the dimension's surrogate key on the fact, rather than the natural key, when the dimension is type 2?

Accepted Answer

It pins each fact to the exact version valid when it happened.

Question 47

A taco truck chain joins orders to payments, where some orders are split across several payments. Summing order amount after the join gives too much revenue. Why?

Accepted Answer

Orders with several payments repeat, so amounts sum per payment.

Question 48

A plant nursery has the same fan-out from joining orders to payments. What is the best fix?

Accepted Answer

Aggregate payments to one row per order before the join.

Question 49

How can you detect fan-out from a new join while developing a model?

Accepted Answer

Compare row counts and key uniqueness before and after the join.

Question 50

A theater has ticket sales and concession sales, each with many rows per show. Joining both to shows in one query and summing gives inflated totals. Why?

Accepted Answer

Each ticket row pairs with each concession row for that show.

Question 51

A developer fixes doubled revenue at a toy store by changing sum of revenue to sum of distinct revenue. Why is this risky?

Accepted Answer

Two different orders with the same amount are counted only once.

Question 52

At an urgent care group, a visit can have several diagnoses. Leaders want visit cost by diagnosis. What is an honest approach?

Accepted Answer

Use a bridge table and allocate cost, or label totals as overlapping.

Question 53

After a change to the store dimension, a coffee roaster's revenue mart is up 3 percent with no business reason. Which test should you check first?

Accepted Answer

That the store key is unique in the store dimension.

Question 54

What does an incremental model do on a normal scheduled run?

Accepted Answer

Processes only new or changed rows and merges them into the table.

Question 55

A podcast app's events can arrive up to three days late. The incremental model loads rows where event time is greater than the max event time already in the table. What happens?

Accepted Answer

Late events with older timestamps are skipped and never loaded.

Question 56

For the same late-arriving events, what is the best fix?

Accepted Answer

Reprocess a recent lookback window and merge on a unique key.

Question 57

A smart thermostat model reprocesses the last three days on each run but uses an append strategy. What is the result?

Accepted Answer

Rows in the window get inserted again, creating duplicates.

Question 58

When should you run a full refresh of an incremental model?

Accepted Answer

When its logic changes in a way that affects rows already built.

Question 59

A grocery delivery source sends the same order event several times with different ingest timestamps. How do you dedupe it?

Accepted Answer

Use row number per order id by ingest time, keeping one row.

Question 60

A dedupe step uses row number over customer id ordered by updated at. Two rows for one customer share the same updated at. What is the issue?

Accepted Answer

The kept row can change between runs, so add a tiebreaker column.

Question 61

A dental office chain's incremental model merges on patient id, but the new batch has two rows for some patients. What can happen?

Accepted Answer

The merge can fail or update unpredictably, so dedupe the batch first.

Question 62

A sporting goods store sells a new product before that product reaches the type 2 product dimension. What is a common approach?

Accepted Answer

Insert a placeholder product row and fill it in later.

Question 63

An event ticketing marketplace marks deleted listings with an is deleted flag. Its incremental model only loads rows whose created at is newer than the last run. What goes wrong?

Accepted Answer

Deletions never reach the model, so removed listings keep counting.

Question 64

Why should each run of a transform be idempotent?

Accepted Answer

Reruns on the same input give the same result, so retries are safe.