Module 1 solutions

Solution 1: Grain and history decisions

A. Vaccinations per clinic per week. The event already exists: an invoice line. The grain is one row per invoice line, which is star.fact_invoice_line as it is. Filter on dim_treatment.category = 'vaccine', group by dim_clinic and by week from dim_date, and count rows or sum quantity. Vaccine type is the treatment itself (VAC-RAB, VAC-FCV, …). Treat its description as type 1: if the name "Feline core vaccination" is corrected, old vaccinations should show the corrected name. The code is what identifies the vaccine, and codes don't change. A new vaccine gets a new code.

B. Revenue per loyalty tier at invoice time. Same event and grain as A: one row per invoice line, with dim_owner, dim_date and whatever else is needed. The tier must be type 2. The request explicitly asks for the tier at the time of the invoice, so the fact must point to the owner version that was valid on the invoice date, exactly like city in lesson 2. With type 1, every past invoice would move to the owner's current tier.

C. Mailing list with phone numbers. This is an operational list, not a report about the past. The grain is one row per owner with a pet due for a booster, taken from the pet and owner dimensions plus vaccination facts. Phone is type 1: only today's number is useful, and history adds rows without value. That is why dim_owner in lesson 2 overwrote phone even though it tracked city.

The pattern: start from the business event (the grain), then ask for each attribute whether anyone will ever need its past value next to past facts. If yes, use type 2. If no, use type 1.

Solution 2: Count the animals, not the records

The approach has two steps. First harmonize: bring both sources into one shape with one vocabulary. Then match: decide which records are the same animal.

WITH all_pets AS (
    SELECT 'ClinicOS' AS source_system, pet_id AS source_pet_id, microchip, species
    FROM clinicos.pets
    UNION ALL
    SELECT 'VetBase', patient_no, chip_no,
           CASE species_code WHEN 'FEL' THEN 'cat'
                             WHEN 'CAN' THEN 'dog'
                             ELSE 'unknown' END
    FROM vetbase.patients
),
animals AS (
    -- The microchip identifies the animal in both systems. Pets without a chip
    -- cannot be matched, so they keep a system-specific key.
    SELECT coalesce(microchip, source_system || ':' || source_pet_id) AS animal_key,
           any_value(species) AS species,
           count(*)           AS records
    FROM all_pets
    GROUP BY 1
)
SELECT species, count(*) AS animals
FROM animals
GROUP BY species
ORDER BY species;

Output:

┌─────────┬─────────┐
│ species │ animals │
│ varchar │  int64  │
├─────────┼─────────┤
│ bird    │       1 │
│ cat     │       4 │
│ dog     │       5 │
│ rabbit  │       1 │
└─────────┴─────────┘

Mochi's two records share microchip 276098100000038, so they collapse into one animal. Kiwi and Pip have no microchip. Their fallback key (ClinicOS:P-102950, ClinicOS:P-102960) is unique, so each is still counted once. Prefixing with the system name matters: without it, a VetBase ID and a ClinicOS ID could collide one day.

Look at what you just decided. Which value identifies an animal across systems is a business key decision, and it is the foundation of every Data Vault. You also made it inside a reporting query, where it's invisible and would have to be repeated in every other query. Module 2 gives such decisions a permanent home.

The ELSE 'unknown' branch is deliberate too. A new VetBase code (say LEP for rabbits) should show up visibly as unknown rather than disappear.