Star schemas d6

• PostgreSQL with PostGIS
• Browser
• 2.5 hours

Prerequisites: Normalization (d2), Transactions (d4)

Shape the lab data for reports: facts and dimensions, the grain, unknown members, conformed dimensions, slowly changing dimensions (types 1 and 2), star or snowflake, and the export Power BI reads. The star is built in your own PostgreSQL, on the lab tables (the setup page); the export and the exercise run in this browser, on the tables the Schema page describes.

Concept

Reports ask "how many, by what": records by class and continent, habitat area by country, records per decade. A dimensional model answers them with a shape built for reading:

• Facts and dimensions. A fact table holds the events you count or measure, one row each. A dimension table holds one way to slice them: the species, the country, the dataset, the year. Each fact row carries a foreign key to every dimension it can be sliced by. Drawn, the fact sits in the middle with its dimensions around it: a star (the star schema draws critwire's).
• The grain comes first. The grain is what one fact row is, said in words before any column is chosen: "one sample record", "one country and one habitat class". Every column must be true at that grain; a table that mixes two grains (records and yearly totals in one table) counts things twice. Keep the finest grain you have: a coarser summary can always be built from it, never the reverse.
• Measures are the fact's numbers. Additive ones add up across every dimension (records, area in km²). Semi-additive ones add up across some (a yearly population count adds across countries, not across years). Non-additive ones never add up (ratios, averages, distinct counts): store their parts and compute them in the report. A record fact's measure is the row itself: each row counts one, count(*).
• Dimensions are wide and flat: everything you'd filter or group by, as readable text, with a hierarchy's levels as columns side by side (class, order, family, genus). That repeats values on purpose, the denormalizing that Normalization, d2 named: safe here because every critwire build rebuilds the star from the lab's normalized tables and nobody edits it.
• Star or snowflake. A snowflake normalizes the dimensions back into chains (species → genus → family → order → class, as taxa does). It stores each name once, but every report walks the chain and every reporting tool has to be taught it. A star needs one join per dimension. Snowflake a dimension only when it is huge or one level is shared by several dimensions.
• Keys. A dimension has a primary key; the fact's foreign keys point at it; the fact's own primary key is its grain (record_id; species_id, country_code). Warehouses usually give dimensions surrogate keys, integers the load makes up (Keys and constraints, d1), so that one species can have several versions (type 2, below) and a source system's codes can change underneath. This star uses critwire's ids, already surrogates, and natural keys an authority keeps stable (ISO country codes, GBIF dataset keys).
• Unknown members. A fact's foreign key is never NULL: an inner join drops the row, and a report tool shows a blank that no filter catches. Instead the dimension gets one row that means "unknown" (ZZ, year -1), and the load maps a missing value to it in exactly one place. Then the totals agree whichever dimension you slice by. When "unknown" and "doesn't apply" both occur, give each its own member.
• Degenerate dimensions are codes that stay on the fact as plain columns (basis of record, license) because there is nothing more to say about them. A factless fact has no measure at all; its rows are the facts (a species has open records in a country).
• Conformed dimensions are one dimension table shared by several facts, with the same keys and values. They let a report drill across: summarize each fact to the shared dimension's level, then join the summaries. Joining two facts row to row instead pairs every row of one with every matching row of the other (the fan-out of Joins, f3), and every total is wrong.
• Slowly changing dimensions. A dimension's attributes change: a common name is corrected, a species moves to another genus. Type 1 overwrites the value: history is restated, every past fact now reports under the new value; right for corrections. Type 2 keeps history: the change adds a new row, a version with its own surrogate key, the years it is valid and a current flag, and each fact points at the version in force at its date. A report can then group "as it was" (by the fact's version) or "as it is" (by the current version). Type 0 never changes a value; type 3 keeps one "previous value" column. The choice is made per attribute.
• Export. A star travels well as files: one CSV per table plus a manifest (columns, keys, rows, sha256, credits), rows in primary-key order so the same data always gives the same bytes. That is what critwire publishes for Power BI, under the lab data's star/ (Power Query, p1).

Worked example

What is one record fact's grain? The reports to support are records by species, place, source and time, so the grain is one sample record, one row of occurrences, and the dimensions are what a record can be sliced by: its species, country, dataset and year. The habitat estimates add a second fact at another grain, one country and one habitat class, that shares dim_country.

These blocks are the one definition of the star: standard SQL with no schema names, so the same text runs in PostgreSQL (this lab, in learn_curriculum) and in DuckDB (critwire's build, which writes the CSV files Power BI reads). Each block creates one table, fills it from the lab tables and reports what it holds. Keys are declared in each CREATE TABLE, because DuckDB accepts foreign keys only there. The tests run each block in its own transaction, after the blocks it builds on, and roll it back; in psql, type BEGIN;, run them in order, and ROLLBACK; when you are done (or keep them until the next reload replaces learn_curriculum).

dim_species flattens the taxonomy into columns: a report filters by class or family without walking taxa (a star, not a snowflake).

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4.

CREATE TABLE dim_species (
  species_id      INTEGER PRIMARY KEY,
  scientific_name VARCHAR NOT NULL,
  common_name     VARCHAR NOT NULL,
  class           VARCHAR NOT NULL,
  order_name      VARCHAR NOT NULL,
  family          VARCHAR NOT NULL,
  genus           VARCHAR NOT NULL
);
INSERT INTO dim_species
SELECT s.id, s.scientific_name, s.common_name, t.class, t.order_name, t.family, t.genus
FROM species AS s
JOIN species_taxonomy AS t ON t.species_id = s.id;
SELECT count(*) AS species, count(DISTINCT class) AS classes, count(DISTINCT family) AS families
FROM dim_species;

-- result: d6-star-dim-species · 1 row · sha256 f55e25393aeb
species | classes | families
76 | 3 | 50

dim_country is every country plus one unknown member, ZZ. A record with no country gets ZZ in the fact table, so every foreign key is filled and a report's totals still add up; the browser schema's nullable country_code becomes this one row.

CREATE TABLE dim_country (
  country_code VARCHAR PRIMARY KEY,
  name         VARCHAR NOT NULL,
  continent    VARCHAR NOT NULL,
  region       VARCHAR NOT NULL,
  subregion    VARCHAR NOT NULL
);
INSERT INTO dim_country
SELECT code, name, continent, region, subregion FROM countries
UNION ALL
SELECT 'ZZ', 'Unknown or outside any country', 'Unknown', 'Unknown', 'Unknown';
SELECT count(*) AS countries, count(*) FILTER (WHERE country_code = 'ZZ') AS unknown_members
FROM dim_country;

-- result: d6-star-dim-country · 1 row · sha256 c47786e15c16
countries | unknown_members
251 | 1

dim_dataset carries each dataset's credit (title, publisher, DOI, license), so the credits travel with the star into any report built on it.

CREATE TABLE dim_dataset (
  dataset_key VARCHAR PRIMARY KEY,
  title       VARCHAR NOT NULL,
  publisher   VARCHAR NOT NULL,
  doi         VARCHAR,
  license     VARCHAR NOT NULL
);
INSERT INTO dim_dataset
SELECT CAST(dataset_key AS VARCHAR), title, publisher, doi, license
FROM datasets;
SELECT count(*) AS datasets, count(doi) AS with_doi, count(DISTINCT publisher) AS publishers
FROM dim_dataset;

-- result: d6-star-dim-dataset · 1 row · sha256 3e8e7544bbbe
datasets | with_doi | publishers
400 | 400 | 217

dim_year has the years that occur, plus -1 for a record with no year: the same unknown-member rule.

CREATE TABLE dim_year (
  year   INTEGER PRIMARY KEY,
  decade INTEGER,
  label  VARCHAR NOT NULL
);
INSERT INTO dim_year
SELECT DISTINCT year, year - year % 10, CAST(year AS VARCHAR)
FROM occurrences
WHERE year IS NOT NULL
UNION ALL
SELECT -1, NULL, 'Unknown';
SELECT count(*) AS years, min(year) FILTER (WHERE year > 0) AS first_year, max(year) AS last_year
FROM dim_year;

-- result: d6-star-dim-year · 1 row · sha256 fcdad55612e8
years | first_year | last_year
177 | 1599 | 2026

fact_records: the grain is one sample record. coalesce is the one place a missing country or year becomes its unknown member. basis_of_record and license stay on the fact as plain columns (degenerate dimensions): they have no attributes of their own to describe.

CREATE TABLE fact_records (
  record_id                BIGINT PRIMARY KEY,
  species_id               INTEGER NOT NULL REFERENCES dim_species (species_id),
  country_code             VARCHAR NOT NULL REFERENCES dim_country (country_code),
  dataset_key              VARCHAR NOT NULL REFERENCES dim_dataset (dataset_key),
  year                     INTEGER NOT NULL REFERENCES dim_year (year),
  month                    INTEGER,
  basis_of_record          VARCHAR NOT NULL,
  license                  VARCHAR NOT NULL,
  longitude                DOUBLE PRECISION NOT NULL,
  latitude                 DOUBLE PRECISION NOT NULL,
  coordinate_uncertainty_m DOUBLE PRECISION
);
INSERT INTO fact_records
SELECT id, species_id, coalesce(country_code, 'ZZ'), CAST(dataset_key AS VARCHAR), coalesce(year, -1), month,
       basis_of_record, license, longitude, latitude, coordinate_uncertainty_m
FROM occurrences;
SELECT count(*) AS records,
       count(*) FILTER (WHERE country_code = 'ZZ') AS unknown_country,
       count(*) FILTER (WHERE year = -1) AS unknown_year,
       count(coordinate_uncertainty_m) AS with_uncertainty
FROM fact_records;

-- result: d6-star-fact-records · 1 row · sha256 808a805569b8
records | unknown_country | unknown_year | with_uncertainty
40395 | 0 | 2289 | 16393

fact_species_country is a factless fact: it has no measure, only the pairs critwire publishes (a country where a species has open records). It shares dim_species and dim_country with fact_records, so one country slicer filters both (conformed dimensions).

CREATE TABLE fact_species_country (
  species_id   INTEGER NOT NULL REFERENCES dim_species (species_id),
  country_code VARCHAR NOT NULL REFERENCES dim_country (country_code),
  PRIMARY KEY (species_id, country_code)
);
INSERT INTO fact_species_country
SELECT species_id, country_code FROM species_countries;
SELECT count(*) AS pairs, count(DISTINCT species_id) AS species, count(DISTINCT country_code) AS countries
FROM fact_species_country;

-- result: d6-star-fact-species-country · 1 row · sha256 e67b39a44d4e
pairs | species | countries
472 | 72 | 107

The habitat classes have two levels; dim_habitat_class flattens them, and a level-1 class is its own parent, so a report can group by level1_name with no gaps. The habitat areas are estimates from a map, not measured; both tables are published with the rest of the star.

CREATE TABLE dim_habitat_class (
  habitat_code INTEGER PRIMARY KEY,
  name         VARCHAR NOT NULL,
  level1_code  INTEGER NOT NULL,
  level1_name  VARCHAR NOT NULL
);
INSERT INTO dim_habitat_class
SELECT h.code, h.name, coalesce(h.level1_code, h.code), coalesce(p.name, h.name)
FROM habitat_classes AS h
LEFT JOIN habitat_classes AS p ON p.code = h.level1_code;
SELECT count(*) AS classes, count(DISTINCT level1_code) AS level1_classes
FROM dim_habitat_class;

-- result: d6-star-dim-habitat-class · 1 row · sha256 37ad5647623e
classes | level1_classes
75 | 13

fact_country_habitat: the grain is one country and one habitat class, with a measure that adds up across classes, est_area_km2. It shares only dim_country with the other facts; it has no species. The CHECK keeps an area at least 0 and at most the largest value DECIMAL(12, 2) holds. The same text runs in PostgreSQL and in DuckDB, which writes the published star: DuckDB can't compare a decimal with 'Infinity', and in PostgreSQL a decimal can't be infinite while NaN fails the upper bound.

CREATE TABLE fact_country_habitat (
  country_code VARCHAR NOT NULL REFERENCES dim_country (country_code),
  habitat_code INTEGER NOT NULL REFERENCES dim_habitat_class (habitat_code),
  est_area_km2 DECIMAL(12, 2) NOT NULL CHECK (est_area_km2 >= 0 AND est_area_km2 <= 9999999999.99),
  PRIMARY KEY (country_code, habitat_code)
);
INSERT INTO fact_country_habitat
SELECT country_code, habitat_code, est_area_km2 FROM country_habitats;
SELECT count(*) AS country_classes, count(DISTINCT country_code) AS countries, sum(est_area_km2) AS total_km2
FROM fact_country_habitat;

-- result: d6-star-fact-country-habitat · 1 row · sha256 80b11c41e5b3
country_classes | countries | total_km2
5578 | 246 | 146579613.97

Now a report is one join per dimension it slices by. Records since 2000 by class and continent:

SELECT sp.class, c.continent, count(*) AS records
FROM fact_records AS f
JOIN dim_species AS sp ON sp.species_id = f.species_id
JOIN dim_country AS c ON c.country_code = f.country_code
JOIN dim_year AS y ON y.year = f.year
WHERE y.decade >= 2000
GROUP BY sp.class, c.continent
ORDER BY sp.class, c.continent;

-- result: d6-report · 18 rows · sha256 1b76ca3b5cff
class | continent | records
Amphibia | Africa | 103
Amphibia | Asia | 16
Amphibia | Europe | 2
Amphibia | North America | 343
Amphibia | South America | 411
… 13 more rows

The query never mentions genus, family or taxa: the dimension already holds the class. Every join is from the fact to one dimension by its key, which is the shape reporting tools expect (Power BI draws each as a one-to-many relationship: The data model, p2). Seven seas (open ocean) is Natural Earth's continent for island countries far from any continent, such as Mauritius and Réunion. Records with no year fall out here only because the WHERE asks for a decade; they still join, to year -1.

Lab

1. Star or snowflake. The same count by class, once from the star and once by walking the normalized tables, species → genus → family → order → class in taxa:

   d6-snowflake · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d6-star-dim-species, d6-star-dim-country, d6-star-dim-dataset, d6-star-dim-year, d6-star-fact-records

   SELECT star.class, star.records AS star_records, snowflake.records AS snowflake_records
   FROM (
     SELECT sp.class, count(*) AS records
     FROM fact_records AS f
     JOIN dim_species AS sp ON sp.species_id = f.species_id
     GROUP BY sp.class
   ) AS star
   JOIN (
     SELECT c.name AS class, count(*) AS records
     FROM occurrences AS o
     JOIN species AS s ON s.id = o.species_id
     JOIN taxa AS g ON g.name = s.genus
     JOIN taxa AS fam ON fam.name = g.parent_name
     JOIN taxa AS ord ON ord.name = fam.parent_name
     JOIN taxa AS c ON c.name = ord.parent_name
     GROUP BY c.name
   ) AS snowflake ON snowflake.class = star.class
   ORDER BY star.class;

   -- result: d6-snowflake · 3 rows · sha256 3ba889abc98e
   class | star_records | snowflake_records
   Amphibia | 4776 | 4776
   Mammalia | 31905 | 31905
   Reptilia | 3714 | 3714

   In d6.sql, with the blocks it runs after.

   Same answer, one join against five. The snowflake stores each family's name once; the star repeats it on each of its species' rows, 76 rows in all, which costs nothing. The snowflake's real cost is that every report, and every person writing one, has to know the chain.

2. Conformed dimensions. Which dimensions does the record fact share with habitat area per country? Only dim_country: fact_country_habitat has a country and a habitat class, fact_records a species, country, dataset and year. So a report that shows both goes through the country. Take two countries: Åland (AX), with no records in the sample, and Trinidad and Tobago (TT), with two. First the wrong way, joining the facts to each other, then the right one:

   d6-drill-across · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d6-star-dim-species, d6-star-dim-country, d6-star-dim-dataset, d6-star-dim-year, d6-star-fact-records, d6-star-dim-habitat-class, d6-star-fact-country-habitat

   SELECT 'facts joined to each other' AS way, f.country_code, count(*) AS records, sum(h.est_area_km2) AS habitat_km2
   FROM fact_records AS f
   JOIN fact_country_habitat AS h ON h.country_code = f.country_code
   WHERE f.country_code IN ('AX', 'TT')
   GROUP BY f.country_code
   UNION ALL
   SELECT 'each fact summed, then joined', c.country_code, coalesce(r.records, 0), h.habitat_km2
   FROM dim_country AS c
   JOIN (SELECT country_code, sum(est_area_km2) AS habitat_km2
         FROM fact_country_habitat GROUP BY country_code) AS h ON h.country_code = c.country_code
   LEFT JOIN (SELECT country_code, count(*) AS records
              FROM fact_records GROUP BY country_code) AS r ON r.country_code = c.country_code
   WHERE c.country_code IN ('AX', 'TT')
   ORDER BY way, country_code;

   -- result: d6-drill-across · 3 rows · sha256 ad182fd5050b
   way | country_code | records | habitat_km2
   each fact summed, then joined | AX | 0 | 761.16
   each fact summed, then joined | TT | 2 | 5067.71
   facts joined to each other | TT | 44 | 10135.42

   In d6.sql, with the blocks it runs after.

   Trinidad and Tobago has 2 records and 22 habitat classes, so joining the facts gives 44 "records" and twice the habitat area, and Åland, with no records, vanishes. Summing each fact to the country first gives each number once. Reporting tools do this for you when both facts relate to the same dimension table and you never relate the facts to each other.

   

3. Where does a record with no country go? The sample has none (GBIF's geospatial filter removed them when the sample was drawn), but a refresh can bring one: a record at sea has GBIF code XZ and no country_code. Add a made-up one, in Bass Strait between Victoria and Tasmania, with no year either:

   d6-at-sea · PostgreSQL with PostGIS Copy Lab schema

   INSERT INTO occurrences (id, species_id, dataset_key, license, basis_of_record, longitude, latitude,
                            coordinate_uncertainty_m, country_code_gbif, country_code, year, month)
   SELECT 1, species_id, dataset_key, license, basis_of_record, 146.5, -39.5, NULL, 'XZ', NULL, NULL, NULL
   FROM occurrences
   WHERE species_id = 48
   ORDER BY id
   LIMIT 1;
   SELECT id, species_id, country_code_gbif, country_code, year FROM occurrences WHERE id = 1;

   -- result: d6-at-sea · 1 row · sha256 63fdf4312748
   id | species_id | country_code_gbif | country_code | year
   1 | 48 | XZ | NULL | NULL

   In d6.sql, with the blocks it runs after.

   Then build the star after it (in psql: ROLLBACK; a star you already built, BEGIN;, run d6-at-sea, then the star's blocks) and count the records each way:

   d6-unknown · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d6-at-sea, d6-star-dim-species, d6-star-dim-country, d6-star-dim-dataset, d6-star-dim-year, d6-star-fact-records

   SELECT 1 AS step, 'occurrences' AS counted, count(*) AS records FROM occurrences
   UNION ALL
   SELECT 2, 'occurrences joined to countries', count(*)
   FROM occurrences AS o JOIN countries AS c ON c.code = o.country_code
   UNION ALL
   SELECT 3, 'fact_records joined to dim_country', count(*)
   FROM fact_records AS f JOIN dim_country AS c ON c.country_code = f.country_code
   UNION ALL
   SELECT 4, 'of them in ZZ', count(*) FROM fact_records WHERE country_code = 'ZZ'
   UNION ALL
   SELECT 5, 'of them in year -1', count(*) FROM fact_records WHERE year = -1
   ORDER BY step;

   -- result: d6-unknown · 5 rows · sha256 bda14c66315b
   step | counted | records
   1 | occurrences | 40396
   2 | occurrences joined to countries | 40395
   3 | fact_records joined to dim_country | 40396
   4 | of them in ZZ | 1
   5 | of them in year -1 | 2290

   In d6.sql, with the blocks it runs after.

   The normalized tables lose the record as soon as a report joins countries; the star keeps it, under "Unknown or outside any country", and under year -1 with the 2,289 records that have no year. The coalesce in d6-star-fact-records is the one place this happens, so no report has to remember a LEFT JOIN.

4. Type 1: overwrite. "Temminck's ground pangolin" is the name more lists use now. Correct it in the dimension and look at the records:

   d6-scd1 · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d6-star-dim-species, d6-star-dim-country, d6-star-dim-dataset, d6-star-dim-year, d6-star-fact-records

   UPDATE dim_species SET common_name = 'Temminck''s Ground Pangolin' WHERE species_id = 63;
   SELECT sp.common_name, count(*) AS records, min(f.year) FILTER (WHERE f.year > 0) AS first_year
   FROM fact_records AS f
   JOIN dim_species AS sp ON sp.species_id = f.species_id
   WHERE f.species_id = 63
   GROUP BY sp.common_name;

   -- result: d6-scd1 · 1 row · sha256 bf8b1f306b36
   common_name | records | first_year
   Temminck's Ground Pangolin | 9 | 1930

   In d6.sql, with the blocks it runs after.

   All 9 records, back to 1930, now report under the new name, and nothing remembers the old one. For a correction that is what you want. In critwire's build the dimension is rebuilt from species each time, so the correction belongs in the source, and the star picks it up.

5. Type 2: keep history. Older lists, such as Mammal Species of the World (2005), put all eight pangolins in genus Manis; the African ground pangolins have been Smutsia in general use since a 2018 study of the pangolins' family tree (Gaubert et al., Journal of Heredity), and critwire uses Smutsia. To report records under the name in use when each was made, the dimension keeps both versions, each valid for a range of years, both ends included (0 and 9999 stand for "always" and "until further notice"). In this lab, version 1 is valid to 2017 and version 2 from 2018 (by year, since a record's date here is its year); a record with no year gets the current version. The 2018 boundary is a teaching convention, not the day the name changed: a study is published, then each checklist, museum and portal adopts it when it chooses, so a real dimension takes its dates from the list it follows. First the versions, and a check before anything is loaded:

   d6-scd2-versions · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d6-star-dim-species, d6-star-dim-country, d6-star-dim-dataset, d6-star-dim-year, d6-star-fact-records

   CREATE TABLE dim_species_history (
     species_key     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     species_id      INTEGER NOT NULL,
     scientific_name VARCHAR NOT NULL,
     genus           VARCHAR NOT NULL,
     valid_from      INTEGER NOT NULL,
     valid_to        INTEGER NOT NULL,
     is_current      BOOLEAN NOT NULL,
     UNIQUE (species_id, valid_from)
   );
   CREATE UNIQUE INDEX ON dim_species_history (species_id) WHERE is_current;
   INSERT INTO dim_species_history (species_id, scientific_name, genus, valid_from, valid_to, is_current)
   SELECT species_id, scientific_name, genus, 0, 9999, true FROM dim_species;
   UPDATE dim_species_history SET valid_from = 2018 WHERE species_id IN (55, 63);
   INSERT INTO dim_species_history (species_id, scientific_name, genus, valid_from, valid_to, is_current)
   VALUES (55, 'Manis gigantea', 'Manis', 0, 2017, false),
          (63, 'Manis temminckii', 'Manis', 0, 2017, false);
   SELECT
     (SELECT count(*)
      FROM (SELECT f.record_id
            FROM fact_records AS f
            LEFT JOIN dim_species_history AS h
              ON h.species_id = f.species_id
             AND CASE WHEN f.year = -1 THEN h.is_current ELSE f.year BETWEEN h.valid_from AND h.valid_to END
            GROUP BY f.record_id
            HAVING count(h.species_key) <> 1) AS wrong) AS records_without_one_version,
     (SELECT count(*)
      FROM (SELECT species_id
            FROM dim_species_history
            GROUP BY species_id
            HAVING count(*) FILTER (WHERE is_current) <> 1) AS wrong) AS species_without_one_current;

   -- result: d6-scd2-versions · 1 row · sha256 acb944054e01
   records_without_one_version | species_without_one_current
   0 | 0

   In d6.sql, with the blocks it runs after.

   Both counts must be 0 before the load. The first applies the load's own rule (the CASE below) to every record and counts those that match no version, a gap between one version's valid_to and the next one's valid_from, or more than one, an overlap: a gap drops the record from a report by version, an overlap counts it twice. UNIQUE (species_id, valid_from) stops two versions starting in the same year, not an overlap. The partial unique index allows at most one current row per species; it can't require one, since a species whose versions were all closed has none. The second count catches that, so together they prove exactly one.

   Then the load: each record gets the key of the version in force in its year, and once every record has one, species_key becomes NOT NULL, so no record can later be left without a version:

   d6-scd2 · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d6-star-dim-species, d6-star-dim-country, d6-star-dim-dataset, d6-star-dim-year, d6-star-fact-records, d6-scd2-versions

   ALTER TABLE fact_records ADD COLUMN species_key INTEGER REFERENCES dim_species_history (species_key);
   UPDATE fact_records AS f SET species_key = h.species_key
   FROM dim_species_history AS h
   WHERE h.species_id = f.species_id
     AND CASE WHEN f.year = -1 THEN h.is_current ELSE f.year BETWEEN h.valid_from AND h.valid_to END;
   ALTER TABLE fact_records ALTER COLUMN species_key SET NOT NULL;
   SELECT f.species_id, version.scientific_name AS name_then, current_version.scientific_name AS name_now,
          count(*) AS records
   FROM fact_records AS f
   JOIN dim_species_history AS version ON version.species_key = f.species_key
   JOIN dim_species_history AS current_version
     ON current_version.species_id = f.species_id AND current_version.is_current
   WHERE f.species_id IN (55, 63)
   GROUP BY f.species_id, version.scientific_name, current_version.scientific_name
   ORDER BY f.species_id, name_then;

   -- result: d6-scd2 · 4 rows · sha256 68ebdb65533e
   species_id | name_then | name_now | records
   55 | Manis gigantea | Smutsia gigantea | 6
   55 | Smutsia gigantea | Smutsia gigantea | 4
   63 | Manis temminckii | Smutsia temminckii | 7
   63 | Smutsia temminckii | Smutsia temminckii | 2

   In d6.sql, with the blocks it runs after.

   Grouped by the fact's species_key, 13 of the 19 pangolin records report as Manis, "as it was"; grouped through the current version, all 19 are Smutsia, "as it is". The fact holds the version key, set once when the record is loaded, so the lookup by date runs at load time, never in a report. Every version of a species keeps its species_id, so a report can still ask about the animal across versions. ALTER TABLE … ADD COLUMN and the UPDATE stand in for that load here; a real build sets species_key in the INSERT and declares it NOT NULL from the start.

   Which date picks the version? Here, the year the animal was seen, so "as it was" means the name in use when it was seen. That is not the name the record was filed under: an animal seen in 2010 and identified, or re-identified, in 2020 is Smutsia in its source. Occurrence data keep the two times apart (Darwin Core's eventDate and dateIdentified); the sample has only the first, so this lab keys versions by observation year. Decide which question a report answers, the name when the animal was seen or the name the record was given, and key the versions by that date.

6. Export. In DuckDB a table becomes a file with one COPY; it runs here in the browser, which has the same countries table:

   Query d6-export COPY ( SELECT code AS country_code, name, continent, region, subregion FROM countries ORDER BY code ) TO 'dim_country.csv' (FORMAT csv, HEADER); SELECT count(*) AS rows, min(country_code) AS first_code, max(country_code) AS last_code FROM read_csv('dim_country.csv', header = true, all_varchar = true);

   RunStopReset databaseSchema

   critwire's build exports every star table this way, COPY (SELECT * FROM <table> ORDER BY <primary key>) TO '<table>.csv' (FORMAT csv, HEADER), and writes manifest.json beside the files (the lab data). In PostgreSQL, psql's \copy does the same from your own computer, \copy (SELECT * FROM dim_country ORDER BY country_code) TO 'dim_country.csv' WITH (FORMAT csv, HEADER); plain COPY … TO 'file' writes on the server and needs a server-file privilege. Read the files back with all_varchar (or explicit types) so a code like Namibia's NA stays text.

Result

The record fact's grain is one sample record: 40,395 rows, one per row of occurrences, sliced by species, country, dataset and year, each one join away. The star and the snowflake give the same counts by class (Amphibia 4,776, Mammalia 31,905, Reptilia 3,714); only the joins differ. The two facts share dim_country, so habitat area and records meet by country: joined to each other, Trinidad and Tobago's 2 records became 44. A record with no country goes to ZZ and one with no year to -1, so every total still adds up. Type 1 restated all 9 of Temminck's pangolin's records under the corrected name; type 2, after checking that every record matched exactly one version and every species had exactly one current row, kept 13 of the 19 pangolin records under Manis "as it was", while "as it is" shows all 19 under Smutsia.

Exercise

Big facts make slow dashboards, so warehouses add aggregate facts: a coarser grain, built from the fine one. In the browser (DuckDB), build fact_species_decade at the grain one species and one decade, with a measure records, and a dimension dim_decade (the decades that occur, labelled like 1990s, plus an unknown member). Use species as the species dimension. Then prove it reconciles: its records must add up to the records in occurrences, and say how many are in the unknown decade.

CREATE TABLE dim_decade (
  decade INTEGER PRIMARY KEY,
  label  VARCHAR NOT NULL
);
INSERT INTO dim_decade
SELECT DISTINCT year - year % 10, CAST(year - year % 10 AS VARCHAR) || 's'
FROM occurrences
WHERE year IS NOT NULL
UNION ALL
SELECT -1, 'Unknown';
CREATE TABLE fact_species_decade (
  species_id INTEGER NOT NULL REFERENCES species (id),
  decade     INTEGER NOT NULL REFERENCES dim_decade (decade),
  records    INTEGER NOT NULL,
  PRIMARY KEY (species_id, decade)
);
INSERT INTO fact_species_decade
SELECT species_id, coalesce(year - year % 10, -1), count(*)
FROM occurrences
GROUP BY species_id, coalesce(year - year % 10, -1);
SELECT count(*) AS fact_rows, sum(records) AS records,
       sum(records) = (SELECT count(*) FROM occurrences) AS reconciles,
       sum(records) FILTER (WHERE decade = -1) AS unknown_decade
FROM fact_species_decade;

Check

The answer: the decade dimension follows dim_year's rule, with -1 for "no year"; the fact's primary key is its grain, and its foreign keys are never NULL. 645 rows replace 40,395, and the measure is additive, so any report by species, decade or class (through species) gives the same totals from either table. What the aggregate can't answer is anything below its grain: a month, a country, a dataset, a basis of record. Keep the fine fact and build summaries from it.

fact_species_decade has 645 rows, its records add up to 40,395 (reconciles is true), and 2,289 are in the unknown decade. If the total is short, a NULL year was grouped away or dropped by a join; if the INSERT fails on the primary key, two rows share a species and decade, so the GROUP BY and the key disagree about the grain.

Dialects

• PostgreSQL: nothing star-specific: plain tables, foreign keys (which some warehouses drop on facts for load speed and check in the load instead), identity columns for surrogate keys, MERGE (15+) for type 2 loads, a partial unique index for "at most one current version", an exclusion constraint for "no overlapping versions" (EXCLUDE USING gist (species_id WITH =, int4range(valid_from, valid_to, '[]') WITH &&), with the btree_gist extension), \copy for files on your computer.
• DuckDB: foreign keys only in CREATE TABLE, no identity columns (a SEQUENCE with DEFAULT nextval(…) instead), and COPY … TO writes CSV or Parquet directly; column storage makes wide dimensions and large facts quick to scan, which is why critwire's build runs the star there.
• SQL Server: IDENTITY surrogate keys, clustered columnstore indexes for large facts, MERGE for type 2 loads, and temporal tables (2016+) that keep a row's history automatically, by when it was changed rather than when it was true.

Lab schema: the PostGIS tables

Every column, in words · The diagram alone

Close

Schema: the browser's tables

Every column, in words · The diagram alone

Close

Mark done