Module 1 exercises

Exercise 1: Grain and history decisions

Goal: practise declaring a grain and choosing an SCD type. · Time: ~5 min · Starter code: none

Northpaw's analysts bring you three requests. For each one, write down:

  1. the business event the fact table records, and its grain in one sentence ("one row per …");
  2. the dimensions it needs;

  3. for the attribute in italics, whether it should be type 1 or type 2, and why.

  4. A. "Number of vaccinations given per clinic per week, split by vaccine type."

  5. B. "Revenue per owner loyalty tier (bronze/silver/gold), where the tier is the one the owner had when the invoice was issued."
  6. C. "A mailing list of owners with pets due for a booster, with each owner's phone number."

You're done when: you have a grain sentence, a dimension list and a justified SCD type for A, B and C.

Hints

Hint 1

Request A doesn't need a new business event. Look at what one row of star.fact_invoice_line already represents, and at the category column of dim_treatment.

Hint 2

Request C is not a report about the past. Ask yourself: would anyone ever want the phone number that was valid last January?

Exercise 2: Count the animals, not the records

Goal: feel the integration problem from lesson 3 in your own SQL. · Time: ~10 min · Starter code: code/module-01/exercise-2/start/

Finance wants to know how many animals Northpaw treats per species, across ClinicOS and VetBase. Complete count-animals.sql:

  1. Combine the pet records of both systems into one list with the columns source_system, source_pet_id, microchip and species. Translate VetBase's species codes (FEL, CAN) to ClinicOS's words.
  2. Reduce the list to one row per real animal. Decide which column identifies an animal across both systems, and what happens to pets that don't have that value.

Run it from the code/ folder after the module 1 scripts 01 and 07:

duckdb northpaw.duckdb -f module-01/exercise-2/start/count-animals.sql

You're done when: the query reports 4 cats, 5 dogs, 1 bird and 1 rabbit, and you can explain in one sentence why Kiwi the budgerigar and Pip the rabbit are still counted correctly.

Hints

Hint 1

UNION ALL the two sources in the first CTE. A CASE expression translates the species codes.

Hint 2

Group by coalesce(microchip, <something unique per system record>). The fallback has to be unique across both systems, not just within one.

Stuck or finished? Open the solutions.