critwire · Learn · Learn's data

Quality and provenance e4

Prerequisites: Pipelines you can re-run (e3)

Which checks stop a bad load, and which datasets must you credit? Data quality checks written as SQL, each a count that should be 0; a quarantine report that names every refused record; the ways a number breaks a check written < 0; a row no filter can catch; and the credits and derived-dataset registration for two species' records. Everything runs in your own PostgreSQL with PostGIS and the Python environment, on the lab tables and made-up records (the setup page).

Concept

Know what you loaded, and who to thank for it.

Data: occurrences, datasets, species.

Worked example

The checks, run on the lab sample:

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4; Python 3.12.3 with requirements.txt sha256 d5bbe28024cc.

e4-checks · PostgreSQL with PostGIS 

SELECT 'coordinates out of range' AS check_name, 'reject' AS action, count(*) AS records
FROM occurrences WHERE longitude NOT BETWEEN -180 AND 180 OR latitude NOT BETWEEN -90 AND 90
UNION ALL
SELECT 'coordinates exactly 0, 0', 'reject', count(*)
FROM occurrences WHERE longitude = 0 AND latitude = 0
UNION ALL
SELECT 'license not CC0 1.0 or CC BY 4.0', 'reject', count(*)
FROM occurrences WHERE license NOT IN ('CC0 1.0', 'CC BY 4.0')
UNION ALL
SELECT 'dataset with no title or publisher', 'reject', count(*)
FROM occurrences AS o JOIN datasets AS d ON d.dataset_key = o.dataset_key
WHERE coalesce(trim(d.title), '') = '' OR coalesce(trim(d.publisher), '') = ''
UNION ALL
SELECT 'GBIF country code with no countries row', 'reject', count(*)
FROM occurrences WHERE country_code_gbif IS NOT NULL AND country_code IS NULL AND country_code_gbif NOT IN ('ZZ', 'XZ')
UNION ALL
SELECT 'year after the download (2026)', 'reject', count(*)
FROM occurrences WHERE year > 2026
UNION ALL
SELECT 'year before 1700', 'flag', count(*)
FROM occurrences WHERE year < 1700
UNION ALL
SELECT 'coordinate uncertainty over 100 km', 'flag', count(*)
FROM occurrences WHERE coordinate_uncertainty_m > 100000
UNION ALL
SELECT 'no coordinate uncertainty', 'flag', count(*)
FROM occurrences WHERE coordinate_uncertainty_m IS NULL
UNION ALL
SELECT 'repeats a record: same species, dataset, place, year and month', 'flag', count(*) - count(DISTINCT (species_id, dataset_key, longitude, latitude, year, month))
FROM occurrences
ORDER BY records DESC, check_name;
-- result: e4-checks · 10 rows · sha256 2ac7773edbfd
check_name | action | records
no coordinate uncertainty | flag | 24002
repeats a record: same species, dataset, place, year and mon… | flag | 15643
coordinate uncertainty over 100 km | flag | 297
year before 1700 | flag | 10
GBIF country code with no countries row | reject | 0
… 5 more rows

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

Every reject check is 0: the sample was loaded with them, and they guard each refresh. The flags are what a reviewer reads:

The sample's table can't hold a negative, NaN or infinite uncertainty: its CHECK refuses them (learn_curriculum.sql, the lab data), so e4-checks has no row for them. A staging table, all text or without that CHECK, can.

Lab

  1. Quarantine a batch. Six made-up staged records, each check run on each record, one report row per failure:

    e4-quarantine · PostgreSQL with PostGIS 

    WITH staged (gbif_id, longitude, latitude, coordinate_uncertainty_m, year) AS (
  VALUES (9000000001, 146.8, -41.4, CAST(30 AS double precision), 2019),
         (9000000002, 146.8, -41.4, CAST(-30 AS double precision), 2019),
         (9000000003, 146.8, -41.4, CAST('NaN' AS double precision), 2019),
         (9000000004, 146.8, -41.4, CAST('Infinity' AS double precision), 2019),
         (9000000005, 0.0, 0.0, CAST(10 AS double precision), 2019),
         (9000000006, 146.8, -41.4, CAST('-Infinity' AS double precision), 2031)
)
SELECT s.gbif_id, c.check_name, c.value
FROM staged AS s
CROSS JOIN LATERAL (VALUES
  -- NaN sorts above Infinity in PostgreSQL, so this range fails negative, NaN, Infinity and -Infinity; NULL is a flag, not a reject
  ('coordinate uncertainty negative, NaN or infinite',
   NOT (s.coordinate_uncertainty_m >= 0 AND s.coordinate_uncertainty_m < 'Infinity'),
   CAST(s.coordinate_uncertainty_m AS text)),
  -- this dataset's rule: none of its records lies near 0, 0, so there two zeros mean a missing coordinate
  ('coordinates exactly 0, 0', s.longitude = 0 AND s.latitude = 0, s.longitude || ', ' || s.latitude),
  ('year after the download (2026)', s.year > 2026, CAST(s.year AS text))
) AS c (check_name, failed, value)
WHERE c.failed
ORDER BY s.gbif_id, c.check_name;
    -- result: e4-quarantine · 6 rows · sha256 786d6242a0ea
gbif_id | check_name | value
9000000002 | coordinate uncertainty negative, NaN or infinite | -30
9000000003 | coordinate uncertainty negative, NaN or infinite | NaN
9000000004 | coordinate uncertainty negative, NaN or infinite | Infinity
9000000005 | coordinates exactly 0, 0 | 0.0, 0.0
9000000006 | coordinate uncertainty negative, NaN or infinite | -Infinity
… 1 more row

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

    Each made-up record breaks one check except the last, which breaks two and gets two report rows; record 9000000001 passes them all and would load. A test written coordinate_uncertainty_m < 0 catches 9000000002 and 9000000006's -Infinity, and passes 9000000003 and 9000000004: NaN and Infinity compare above 0. The report keeps the source's key and the value as delivered, so the fix can go back to the source.

  2. A row no filter can catch. Five made-up rows from four made-up lists about one made-up animal, in two made-up places. Each row is kept or dropped by its list's license, then by whether a person gave the list a role:

    e4-list-rows · PostgreSQL with PostGIS 

    -- Made up: one animal, places XA and XB, four lists. A person read each whole list and gave it a role, or none
WITH lists (list_title, license, has_role) AS (
  VALUES ('List A', 'CC0 1.0', true),
         ('List B', 'CC BY 4.0', true),
         ('List C', 'CC BY-NC 4.0', true),
         ('Catalogue D', 'CC BY 4.0', false)
), staged (row_no, list_title, place, origin) AS (
  VALUES (1, 'List A', 'XA', 'native'),
         (2, 'List A', 'XB', 'native'),
         (3, 'List B', 'XB', 'introduced'),
         (4, 'List C', 'XB', 'introduced'),
         (5, 'Catalogue D', 'XA', 'introduced')
)
SELECT s.row_no, s.list_title, l.license, s.place, s.origin,
       CASE WHEN l.license NOT IN ('CC0 1.0', 'CC BY 4.0') THEN 'dropped: license'
            WHEN NOT l.has_role THEN 'dropped: list has no role'
            ELSE 'kept' END AS decision
FROM staged AS s
JOIN lists AS l USING (list_title)
ORDER BY s.row_no;
    -- result: e4-list-rows · 5 rows · sha256 a4db17261e6f
row_no | list_title | license | place | origin | decision
1 | List A | CC0 1.0 | XA | native | kept
2 | List A | CC0 1.0 | XB | native | kept
3 | List B | CC BY 4.0 | XB | introduced | kept
4 | List C | CC BY-NC 4.0 | XB | introduced | dropped: license
5 | Catalogue D | CC BY 4.0 | XA | introduced | dropped: list has no role

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

    Rows 2 and 3 disagree about XB, and both stay: each is its own list's claim, and a load doesn't pick a winner. Row 4 goes for its license: CC BY-NC 4.0 bars commercial use, and this load keeps only CC0 1.0 and CC BY 4.0. Row 5 is the hard one. Its license is open and every field is well formed, yet it says the animal came from elsewhere to XA, the place List A says it comes from. No filter on a license or a field's value catches that. Only a person who reads the whole list sees it says this about animals in their home countries, and gives it no role. critwire's listings work this way: it reads a dataset only once a person has checked it and given it a role.

  3. Who to credit. Which datasets the Tasmanian devil's and the jaguar's records come from, and under which license:

    e4-credits · PostgreSQL with PostGIS 

    SELECT d.title, d.publisher, d.doi, o.license, count(*) AS records
FROM occurrences AS o
JOIN species AS s ON s.id = o.species_id
JOIN datasets AS d ON d.dataset_key = o.dataset_key
WHERE s.scientific_name IN ('Sarcophilus harrisii', 'Panthera onca')
GROUP BY d.title, d.publisher, d.doi, o.license
ORDER BY records DESC, d.doi, o.license;
    -- result: e4-credits · 33 rows · sha256 80800d03f9ae
title | publisher | doi | license | records
Jaguar Movement Database: a GPS-based movement dataset of th… | Instituto Chico Mendes de Conservação da Biodiversidade - IC… | 10.71819/lr0yrg | CC BY 4.0 | 1948
Tasmanian Natural Values Atlas | Department of Natural Resources and Environment Tasmania | 10.15468/rtnb4m | CC BY 4.0 | 1413
Tasmanian Museum and Art Gallery provider for OZCAM | Tasmanian Museum and Art Gallery | 10.15468/ijp8p9 | CC BY 4.0 | 569
Feline and other mammals monitoring 2025 in Tres Mesas wind … | ENGIE | 10.15468/ze7rdr | CC BY 4.0 | 8
ICMBio \| Dados de ocorrência de espécies do Sistema de Autor… | Instituto Chico Mendes de Conservação da Biodiversidade - IC… | 10.71819/wrldzz | CC BY 4.0 | 8
… 28 more rows

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

  4. The derived-dataset registration for that subset: the request body, built but never posted.

    e4-derived · Python

    Runs after: py-connect

    import json

DOWNLOAD_DOI = "10.15468/dl.fbpk9w"  # the GBIF download the records came from

with connect() as conn:
    counts = dict(conn.execute("""
        SELECT CAST(o.dataset_key AS text), count(*)
        FROM occurrences AS o JOIN species AS s ON s.id = o.species_id
        WHERE s.scientific_name IN ('Sarcophilus harrisii', 'Panthera onca')
        GROUP BY o.dataset_key ORDER BY o.dataset_key""").fetchall())

# The body of POST https://api.gbif.org/v1/derivedDataset: what GBIF needs to mint a DOI that credits each dataset.
body = {
    "title": "Tasmanian devil and jaguar records used in my analysis",
    "description": "A subset of GBIF download 10.15468/dl.fbpk9w: open records of two species.",
    "originalDownloadDOI": DOWNLOAD_DOI,
    "sourceUrl": "https://example.org/where-my-subset-is-published",
    "relatedDatasets": counts,
}
print(f"relatedDatasets: {len(counts)} datasets, {sum(counts.values())} records")
for key, n in sorted(counts.items(), key=lambda kv: (-kv[1], kv[0]))[:3]:
    print(f"  {key}: {n}")
print(f"body: {len(json.dumps(body))} bytes, ready to POST with your GBIF account")
    -- result: e4-derived · python · sha256 da576d8cb1de
relatedDatasets: 33 datasets, 4000 records
  06e98c3e-2801-4d29-8e81-943a13bb3785: 1948
  2985efd1-45b1-46de-b6db-0465d2834a5a: 1413
  4ffe66a0-6c8b-11de-8226-b8a03c50a862: 569
body: 1718 bytes, ready to POST with your GBIF account

    In e4.py and e4.ipynb, with the blocks it runs after.

    The devil and the jaguar have 4,000 records from 33 datasets. 29 datasets supply CC BY 4.0 records (3,992) and must be credited, led by the Jaguar Movement Database (1,948 GPS-collar records), the Tasmanian Natural Values Atlas (1,413) and the Tasmanian Museum and Art Gallery (569). The 4 CC0 datasets (8 records) ask for no credit, and are credited anyway. The registration body lists the same 33 datasets and 4,000 records, with originalDownloadDOI set to the download's DOI. These counts are records in a capped sample: the Jaguar Movement Database supplies 1,948 of the jaguar's 2,000 because a GPS collar records the same animal again and again, not because it saw most jaguars. They say whom to credit and for how much of the sample, and nothing about where either animal is common.

Result

Every reject check passes on the sample, and four flags are left for a reviewer: unknown precision, repeats, coarse places, and dates before 1642. A quarantine report names each refused record by its key, with the check and the value, and an uncertainty check written as a range catches negative, NaN and infinite values that < 0 lets through. A license filter drops what you may not publish; only a person's check of a whole list drops a row that passes every filter. The devil and the jaguar need credits to 29 datasets, and a derived dataset lists all 33 in one DOI.

Exercise

Turn the row-level reject checks into a gate a load calls before its COMMIT. For each check, count the staged rows breaking it and keep the first key; the load goes ahead only if every count is 0. Use e4-checks' coordinate and year checks and e4-quarantine's uncertainty check. Run the gate on a TEMP copy of the devil's 2,000 records, then again after inserting two made-up records: one at 0, 0, and one dated 2031 with NaN uncertainty.

Show answer

e4-ex · Python

Runs after: py-connect

# The row-level reject checks: each one's name, and the condition a breaking row meets.
REJECTS = [
    ("coordinates out of range", "longitude NOT BETWEEN -180 AND 180 OR latitude NOT BETWEEN -90 AND 90"),
    ("coordinates exactly 0, 0", "longitude = 0 AND latitude = 0"),
    ("coordinate uncertainty negative, NaN or infinite",
     "NOT (coordinate_uncertainty_m >= 0 AND coordinate_uncertainty_m < 'Infinity')"),
    ("year after the download (2026)", "year > 2026"),
]


def failures(cur):
    """Each reject check the staged rows break: its name, how many rows, the lowest key."""
    found = []
    for name, condition in REJECTS:
        n, first = cur.execute(f"SELECT count(*), min(id) FROM staged WHERE {condition}").fetchone()
        if n:
            found.append((name, n, first))
    return found


def report(label, cur):
    rows = cur.execute("SELECT count(*) FROM staged").fetchone()[0]
    found = failures(cur)
    print(f"{label} ({rows:,} rows): {'refused' if found else 'every reject check 0: load'}")
    for name, n, first in found:
        print(f"  {name}: {n} (first {first})")


with connect() as conn, conn.cursor() as cur:
    # CREATE TABLE AS copies no CHECK, as a staging table wouldn't have the target's
    cur.execute("""CREATE TEMP TABLE staged AS
                   SELECT o.id, o.longitude, o.latitude, o.coordinate_uncertainty_m, o.year
                   FROM occurrences AS o JOIN species AS s ON s.id = o.species_id
                   WHERE s.scientific_name = 'Sarcophilus harrisii'""")
    report("the devil's records", cur)
    cur.execute("""INSERT INTO staged VALUES (9000000001, 0, 0, NULL, 2019),
                                             (9000000002, 146.8, -41.4, 'NaN', 2031)""")
    report("with two made-up records", cur)
    conn.rollback()
-- result: e4-ex · python · sha256 9fac0647c0e1
the devil's records (2,000 rows): every reject check 0: load
with two made-up records (2,002 rows): refused
  coordinates exactly 0, 0: 1 (first 9000000001)
  coordinate uncertainty negative, NaN or infinite: 1 (first 9000000002)
  year after the download (2026): 1 (first 9000000002)

In e4.py and e4.ipynb, with the blocks it runs after.

Check

The devil's records pass every check, as the whole sample does in e4-checks. With the two made-up records the gate refuses, and names three checks, each with its count and first key: 9000000001 breaks the 0, 0 rule, and 9000000002 breaks both the uncertainty and the year checks. In a real load the refusal raises an error inside the transaction, so nothing commits (e2), and e4-quarantine's report is what you send back to the source.

Dialects

Lab schema: the PostGIS tables

critwire Learn lab database, PostGIS schema learn_curriculum

Every column, in words · The diagram alone