recipe: gate-publish-on-checks (row count, nulls, value range)

[db-tycoon-stephen]

Motivation

Tycoon has dbt test, but it runs after the warehouse build — so a bad load can populate downstream tables before tests catch it. A common production pattern (well-illustrated in Matt Martin's DuckDB-on-AWS walkthrough) is to gate the publish step on a few cheap data-quality checks against the staged output, and fail the pipeline before anything user-facing changes.

Quote from the article:

Does 3 QA/validation checks: row counts, nulls, and checks where the total_price column is less than 100. If any of these QA checks fail, then the process errors out and sends an error notification to slack. If all QA checks pass, then the final file is written to the destination S3 bucket and a success notification is sent to Slack.

The pattern is so generic it deserves a documented recipe + (probably) a thin convention in tycoon, not a custom user implementation every time.

Proposal

A recipe under docs/recipes/ — gate-publish-on-checks.md — that shows the end-to-end pattern using only things tycoon already has:

1. Ingest to raw.
2. Run a "staging" dbt model (or a SQL query) that materializes the candidate output to a temp/staging schema.
3. Run a small set of assertions against the staging output:
   • row_count > N
   • null_count(<key>) == 0
   • count(*) where <col> violates <range> == 0
4. On pass → publish (swap / rename / copy to final). On fail → exit non-zero and (optionally, pairs with the notify issue) emit a structured failure.

Open question for the recipe: do we ship a tycoon data check convenience, or just show the SQL? Lean toward the SQL-only version first — fewer commitments, lets the pattern bake before we cement an API.

Acceptance criteria

• docs/recipes/gate-publish-on-checks.md published, linked from the recipe index.
• Worked example uses the csv-import template (so it runs offline in CI).
• Recipe doctest marker (mode=offline) covering the happy path + at least one deliberate-failure path.
• Cross-link from the README "Recipes" section.

Out of scope

• New CLI surface. If a tycoon data check command emerges from this, file it separately once the recipe has shaken out.
• Generalizing to arbitrary dbt projects beyond dbt-duckdb.

Related

• Pairs with the notify issue — "check failed → slack red message" is the canonical example.
• Possibly subsumes the long-term motivation behind tycoon data status becoming richer.

[db-tycoon-stephen]

Mirrored in Jira for roadmap tracking: PTC-19 (Epic: PTC-6 (Operator UX & demo recipes)). Dev work continues here; the Jira issue exists so PRs/commits referencing PTC-19 show up on the Releases roadmap.