coalesce-quality-test-authoring
Decide which data quality tests a table, view or model needs, and write them in whatever test format the project already uses (dbt, SYNQ YAML, SQLMesh audits, Soda, Great Expectations, custom SQL, and so on). Use it when asked to add, suggest, review or extend data tests for a warehouse asset, or when a change to a model should come with tests.
- License 许可
- MIT
- 最近更新
- 2026-11-11
- Stars
- ★ 0
- Forks
- 0
01 前言 Front Matter
- name
- coalesce-quality-test-authoring
- description
- Decide which data quality tests a table, view or model needs, and write them in whatever test format the project already uses (dbt, SYNQ YAML, SQLMesh audits, Soda, Great Expectations, custom SQL, and so on). Use it when asked to add, suggest, review or extend data tests for a warehouse asset, or when a change to a model should come with tests.
03 SKILL.md 正文
<!-- coalesce-node-managed: true -->
Data test authoring
A data test is a deterministic assertion over the rows of one asset: a query that returns the rows breaking a rule. If it returns no rows, the test passes. This skill covers which assertions to write and with what parameters. The syntax comes from the project you are working in.
Stance
- Suggest fewer, better tests. Every test costs warehouse compute and adds alert noise. One test that catches a real failure mode is worth more than five that restate the schema. Suggesting nothing is a valid result.
- Keep each test to one concern. To check the same thing on several columns, write one test per column. Combine columns only when the rule needs them together, such as a composite key or a cross-column formula. A multi-column not-null or empty check is fine when the columns form one concern (for example, "both location IDs present").
- Never guess values. Any test whose parameters depend on what the data holds (allowed values, bounds, keys) needs evidence from the code, the schema, documentation or a profiling query. If you cannot get evidence, drop the test or ask for the value.
- Write rules, not snapshots. A test should hold for tomorrow's data as well as today's. A bound or value list copied from a sample of current data is a snapshot and will fail on legitimate new data.
- Don't duplicate. Check what already exists before adding anything: tests, schema constraints, and earlier suggestions.
Workflow
- Find the project's test format. Look for existing tests next to the asset or
elsewhere in the repo, such as
schema.yml/*.ymlwithtests:/data_tests:, SYNQ*.yamlcheck configs, SQLMeshaudits, Sodachecks.yml, GE suites ortests/*.sql. Copy their structure, naming, file placement and severity conventions. If there is no format and the user named none, ask before inventing one. If the project uses Coalesce Quality YAML (a file withversion: v1beta2andentities:), follow Coalesce Quality (SYNQ) YAML below for field names and behavior. Do not write that format from memory. - Understand the asset.
- Schema: column types, nullability, primary or unique keys.
- Code: the SQL or model that builds it. CASE branches, filters, joins, COALESCEs and derived formulas are where real business rules live.
- Grain: what one row represents. This decides the uniqueness key.
- Load pattern: full refresh, incremental or append-only, and the refresh schedule. This decides freshness and the time windows.
- Immediate lineage: which upstream tables its foreign keys point to.
- SQL dialect of the warehouse.
- Collect existing coverage. Gather existing tests, monitors and constraints for the asset, plus any pending suggestions. Drop any candidate that duplicates or closely resembles one of them, or whose concern they already cover.
- Outline candidates. List possible tests with one line of reasoning each, and note what evidence each one still needs.
- Gather evidence, only what the candidates need. Read the code first, because it is
free. Profile the data only where parameters depend on it:
- distinct values and their counts → accepted values
- min and max → bounds
- duplicate count over the candidate key → unique
- null and empty rate → not null or empty
- a sample of rows → relative time or a business rule
- Decide. Confirm or reject each candidate against the evidence. Reject it if the data already violates it with no sign that this is a bug, if cardinality is too high, or if the rule is not really a constraint.
- Write the tests in the project's format, each with a short explanation (see Explanations).
- Validate with the project's own tooling if there is any, such as compile, parse or
dry-run (for Coalesce Quality,
synqcli deploy <files> --dry-run). Run the new tests once if you can. A test that fails on its first run needs a look: it has either found a real issue or encoded a wrong rule. Report which.
Test catalog
These are the kinds of test to reason with. Map each one onto the closest construct in the project's format (see Mapping onto the project's format).
Not null
- Checks that: none of the listed columns holds NULL.
- Use for: identifiers, foreign keys, required business attributes, timestamps the rest of the pipeline relies on.
- Don't use for: columns the schema already declares NOT NULL or as a primary key, or
columns that are optional by design (for example, a LEFT JOIN result or a
cancelled_atthat is null until the event happens). - Evidence: the schema, plus the code or a profile when you are unsure. A column that is null today can still be required, but say so in the explanation.
Empty
- Checks that: none of the listed text or array columns is empty, meaning NULL, an empty string, whitespace only, or an empty array.
- Use for: text fields where
''is as bad as NULL (names, codes, emails). - Note: in the common implementation this check also catches NULL, so don't pair it with a not-null test on the same column. Use one or the other.
Accepted values
- Checks that: every value in the column is in a complete, closed list.
- Use for: enum-like columns with a small, stable set of values, roughly 20 to 30 or fewer: statuses, types, flags, codes.
- Don't use for: high-cardinality columns, or open sets that grow with the business (country lists your product expands to, product SKUs).
- Evidence: required. Prefer values defined in code (CASE branches, enum definitions, upstream accepted-values tests) over values seen in samples. If you rely on profiling, profile the whole column's distinct values, not a sample. The list must be complete.
- Note: this check is usually
col NOT IN (…), so NULL passes it. Add a not-null test separately if NULL is invalid too.
Rejected values
- Checks that: the column never holds any value from a forbidden list.
- Use for: known sentinel or junk values such as
'UNKNOWN','TEST','N/A',-1or'1970-01-01', especially when the set of valid values is open. - Evidence: the forbidden values must be named by the user, the code or the documentation, or seen in data and confirmed to be junk. No speculative lists.
Unique
- Checks that: no two rows share the same value, or combination of values, in the listed columns.
- Use for: the asset's grain: primary keys, natural keys and composite business keys.
- Don't use for: columns already declared as primary or unique keys, where the warehouse actually enforces them.
- Evidence: know the grain from the code (GROUP BY, DISTINCT, join cardinality) or profile the duplicate count. If duplicates exist today, find out whether that is a bug before you encode the test.
- Scale rule: on large or growing tables, limit the check to recent rows using the row-creation or load timestamp (for example, the last 30 days or the last week). If there is no clear creation timestamp, check the whole table. The window misses duplicates where one copy falls outside it. That is an accepted trade-off.
Relationships (referential integrity)
- Checks that: every non-null value of a foreign-key column, or a tuple of columns, exists in the referenced column of another asset.
- Use for: foreign keys you can see in the schema, the join conditions in the code, or immediate upstream lineage.
- Parameters: say whether null foreign keys are allowed, which is usually yes, because optional relations are null. On large tables, scope the check to a time window as you would for unique.
- Evidence: read the referenced asset's schema to confirm the column exists and its type matches.
Freshness
- Checks that: the most recent value of a timestamp column is no older than a window.
The test fails when
max(ts) < now() - window. - Use for: assets that are loaded on a schedule or continuously.
- Parameters: pick a timestamp that moves on every load (
loaded_at,created_at, the event time). Set the window to the expected refresh interval plus slack for normal delay, for example daily load → 26 to 36 hours, not exactly 24. - Don't use for: static reference tables, one-off backfills, or demo and archive data.
Minimum value / maximum value
- Checks that: the column is ≥ a minimum, or ≤ a maximum. The bound is inclusive unless the format offers strict comparison and you need it.
- Use for: physical or logical bounds such as amounts ≥ 0, quantities ≥ 0, a percentage ≤ 100, or an age ≤ 150.
- Rule: if you don't know the exact range, or it changes over time, assert only the bound you are sure of. A non-negative amount gets a minimum of 0 and no maximum.
Range (min and max)
- Checks that:
min ≤ col ≤ max. - Use only when both bounds are true by definition: percentages 0 to 100, probabilities 0 to 1, hour 0 to 23, a rating scale of 1 to 5.
- Don't use for: a range copied from observed min and max ("0 to 6 passengers because that is what the profile showed"). That snapshots the data. Use a one-sided bound, or nothing.
Relative time (ordering)
- Checks that: one timestamp column is not later than another, so the test fails when
first > second. Equal values and NULLs pass. - Use for: lifecycle pairs such as
start < end,created_at ≤ updated_at,ordered_at ≤ shipped_at, orpickup ≤ dropoff. - Evidence: both columns exist, are date or time types, and a sample confirms the order you expect.
Business rule (custom SQL)
- Checks that: an expression in the warehouse's SQL dialect, which evaluates to TRUE for invalid rows, never matches a row.
- Use for: cross-column logic that no other test can express, such as
total ≠ subtotal + tax(with a tolerance for floating point), conditional requirements (status = 'shipped' AND shipped_at IS NULL), or mutually exclusive flags. - Last resort: if a catalog test above fits, use it instead.
- Rules:
- Use only functions from the asset's SQL dialect.
- Compare floating-point values with a tolerance.
- Remember that an expression involving NULL evaluates to NULL, which counts as not flagged. Handle NULLs explicitly if they matter.
- If the format can deploy one test onto many tables, use its table placeholder rather than a hard-coded name.
- Check the polarity: some frameworks want "returns failing rows", others want "the condition that must hold". Invert accordingly.
Choosing between overlapping tests
| Situation | Choose |
|---|---|
| A text column must be non-blank | Empty only (it already covers NULL) |
| A column must be present, and blank text is fine | Not null |
| Small, closed set of valid values | Accepted values (+ not null if NULL is invalid) |
| Open set, but specific junk values are known | Rejected values |
| Only one bound is certain | Minimum value or Maximum value |
| Both bounds are true by definition | Range |
| Two timestamps with a lifecycle order | Relative time |
| A foreign key to another asset | Relationships |
| Multi-column arithmetic or conditional logic | Business rule |
| Grain or key of the asset | Unique (time-windowed on large tables) |
Signals in the asset that point to a test
- A column named
*_id,*_keyor*_codethat is the table's grain → unique (+ not null if the schema does not enforce it). - A
*_idcolumn that joins to another asset, in the code or the lineage → relationships. status,type,kind,category, a boolean-like*_flagholding'Y'/'N', or a CASE expression that produces a fixed set of labels → accepted values.amount,price,quantity,count,durationordistance→ minimum 0, if negative values are impossible. Check for refunds and adjustments first.*_pct,*_rateor*_ratio→ a range, but only after you confirm whether the scale is 0 to 1 or 0 to 100.- Pairs such as
*_start/*_end,created_at/updated_atoropened_at/closed_at→ relative time. - A derived column computed in the code from others (
total = a + b + c) → business rule. - An incremental or scheduled load with a load or event timestamp → freshness.
- A COALESCE to a sentinel in upstream code (
COALESCE(x, 'UNKNOWN')) → rejected values on that sentinel downstream, if the sentinel should not reach this asset.
Scale and cost
- Use time windows on unique and relationships checks for large or append-heavy tables. Anchor the window on the row-creation or load timestamp, not on a business date that can be backdated.
- Don't profile the whole table when a sample or column statistics will answer the question. Profile only the columns the candidates need.
- Don't include personal data found while profiling in tests, explanations or summaries. If a rule really needs a sensitive value list, say so and let the user supply it.
Explanations
Every test carries a one-sentence explanation of what it guards against and why it matters, in business terms. Say where the rule comes from (the code, the schema, the user, or a profile), especially for any value that came from the data.
- Good: "Order totals must equal the sum of line items plus tax. This is how
fct_orders.sqlderives them, so a mismatch means a broken join upstream." - Bad: "Checks total_amount."
Mapping onto the project's format
You will not always know the target format ahead of time. Map concepts onto it like this:
- Use the format's native construct when one exists (a dbt
not_null, a SYNQaccepted_values, a Sodainvalid_count, and so on). Match its parameter names, value types and quoting exactly as existing tests do. - If the format has no native construct for a concept, express it as the format's custom SQL or assertion construct, with the semantics defined above.
- If the format cannot express it at all, such as a time window on uniqueness, say so and either drop the refinement and explain the trade-off, or skip the test. Don't skip a test only because you are unsure of the syntax. Look it up, or use the format's custom SQL construct.
- Keep the inclusive or exclusive and NULL semantics the same as above. When the native construct behaves differently (for example, an accepted-values test that fails on NULL), adjust or note it.
- Put tests where the project keeps them and follow its naming, tagging, ownership and severity conventions.
Coalesce Quality (SYNQ) YAML
Coalesce Quality tests live in YAML files deployed with synqcli deploy. The editor
schema is https://schemas.synq.io/synq-monitors/v1/config.schema.json. The schema
rejects unknown fields, so one wrong key fails the whole deploy.
# yaml-language-server: $schema=https://schemas.synq.io/synq-monitors/v1/config.schema.json
version: v1beta2
namespace: analytics-orders
defaults:
severity: ERROR
entities:
- id: <entity id, copied exactly from existing config or the Coalesce Quality UI>
tests:
- type: not_null
columns: [customer_id]
monitors:
- id: fct_orders_freshness
type: freshness
expression: loaded_at
- Add tests to the existing entry for the asset under
entities[].tests. Keepversion: v1beta2. A file without it is read in a legacy layout. - Never guess an entity
id, including the one arelationshipstest points at. Copy it from another config file in the repo. If no file names it, ask for it. monitors:on the same entity are existing coverage too. Afreshnessmonitor already covers freshness, so don't add afreshnesstest next to it.
Fields per test type
Every test also accepts id, name, description, severity (INFO, WARNING or
ERROR, not WARN), select_columns, schedule, timezone and save_failures.
| Concept | type |
Required fields | Optional fields |
|---|---|---|---|
| Not null | not_null |
columns: [..] |
|
| Empty | empty |
columns: [..] |
|
| Accepted values | accepted_values |
column, values: [..] |
|
| Rejected values | rejected_values |
column, values: [..] |
|
| Unique | unique |
columns: [..] |
time_partition_column, time_window_seconds |
| Relationships | relationships |
references (see below) |
ignore_nulls, time_partition_column, time_window_seconds |
| Freshness | freshness |
time_partition_column, time_window_seconds |
|
| Minimum value | min_value |
column, min_value |
strictly |
| Maximum value | max_value |
column, max_value |
strictly |
| Range | min_max |
column, min_value, max_value |
strictly |
| Relative time | relative_time |
column, relative_column |
|
| Business rule | business_rule |
sql_expression |
|
| Custom query | business_query |
sql_query |
evaluators |
Field names to get right: columns is a list and column is a single name. The custom
SQL field is sql_expression, not sql or expression. There is no custom_sql type.
- type: relationships
references:
- entity: <id of the referenced asset>
columns:
- source: customer_id
reference: customer_id
ignore_nulls: true
time_partition_column: ordered_at
time_window_seconds: 2592000 # 30 days
How each type behaves
These come from the SQL that Coalesce Quality generates for each type. Where the public examples disagree, these are correct.
relative_timefails whencolumn > relative_column. Put the earlier timestamp incolumnand the later one inrelative_column:column: ordered_at,relative_column: shipped_at. Some published examples show the reverse (column: ship_date,relative_column: order_date). That version flags every correctly ordered row. Rows where either side is NULL pass.relationshipsfails on a NULL foreign key unlessignore_nulls: trueis set. Optional relations needignore_nulls: true. Leave it off only when NULL is invalid too. That also covers not-null, so don't add a separate not-null test.business_rulereports rows wheresql_expressionis TRUE. Write the condition for a bad row, such asstatus = 'shipped' and shipped_at is null. An expression that describes a good row flags every good row. Write{{ table }}to refer to the asset in a subquery. It is the only placeholder allowed.accepted_valueslets NULL through (col NOT IN (..)). Add anot_nulltest if NULL is invalid.rejected_valueslets NULL through as well.emptycatches NULL as well as blank text, so it never needs anot_nullpartner.min_value,max_valueandmin_maxare inclusive unlessstrictly: true. NULL passes.uniqueandrelationshipswindows keep only rows wheretime_partition_column >= now - time_window_seconds. Use the row-creation or load timestamp.freshnessfails when the newesttime_partition_columnvalue is older thantime_window_seconds.
Worked example
The asset
analytics.fct_orders has one row per order. It is loaded incrementally once a day at
02:00.
select
o.id as order_id,
o.customer_id,
case o.state
when 'new' then 'open'
when 'paid' then 'open'
when 'shipped' then 'shipped'
when 'cancelled' then 'cancelled'
end as status,
o.subtotal,
o.tax,
o.total as order_total,
o.discount_pct,
o.created_at as ordered_at,
s.shipped_at,
o.coupon_code,
current_timestamp() as loaded_at
from raw.orders o
left join raw.shipments s on s.order_id = o.id
where o.created_at > (select max(ordered_at) from analytics.fct_orders)
What the steps turn up
- Schema:
ordered_atis declared NOT NULL. No other constraints are declared. - Existing coverage: there is already a not-null test on
order_id. - Lineage:
customer_idjoins upstream toanalytics.dim_customers.customer_id. - Profile, last 30 days:
- 0 duplicate
order_idvalues and 0 nullcustomer_idvalues. order_totalranges from 0.00 to 18,400.00.discount_pctranges from 0.00 to 0.35, so the scale is 0 to 1.coupon_codehas 4,210 distinct values and is 61% null.
- 0 duplicate
- Sample:
shipped_atis never earlier thanordered_at.order_totalmatchessubtotal + taxto within a cent.
Candidates and decisions
| # | Candidate | Decision | Why |
|---|---|---|---|
| 1 | Unique order_id |
Keep, windowed on ordered_at, last 30 days |
This is the grain. The LEFT JOIN to shipments fans out if an order has two shipments. ordered_at is the creation timestamp. |
| 2 | Not null order_id |
Drop | Already covered by an existing test. |
| 3 | Not null ordered_at |
Drop | The schema already declares it NOT NULL. |
| 4 | Not null customer_id |
Keep | It is required for every order. The profile shows no nulls today. |
| 5 | Relationships customer_id → dim_customers.customer_id |
Keep, windowed | It is a foreign key visible in the lineage. Nulls are already handled by #4. |
| 6 | Accepted values status ∈ {open, shipped, cancelled} |
Keep | This is a closed set. The values come from the CASE expression, not from a sample. |
| 7 | Not null status |
Keep | The CASE has no ELSE, so a new upstream state turns into NULL. Accepted values lets NULL through, so #6 alone would miss it. |
| 8 | Not null shipped_at |
Drop | It is null by design for orders that have not shipped (LEFT JOIN). |
| 9 | Business rule: a shipped order has no ship date | Keep | This is the conditional form of #8. Invalid when status = 'shipped' AND shipped_at IS NULL. |
| 10 | Relative time ordered_at ≤ shipped_at |
Keep | This is a lifecycle order and the sample confirms it. |
| 11 | Range order_total 0 to 18,400 |
Reject | The upper bound is copied from today's data. |
| 12 | Minimum value order_total ≥ 0 |
Keep | Refunds are modelled elsewhere, so a negative total is a bug. |
| 13 | Range discount_pct 0 to 1 |
Keep | Both bounds are true by definition for a fraction. Use 1, not the observed 0.35. |
| 14 | Business rule: order_total = subtotal + tax |
Keep | The total comes from the source rather than being computed here, so this checks real data. Invalid when abs(order_total - (subtotal + tax)) > 0.01. |
| 15 | Accepted values coupon_code |
Reject | 4,210 distinct values is an open set. |
| 16 | Empty coupon_code |
Reject | Coupons are optional, so a blank value is not an error. |
| 17 | Freshness on loaded_at |
Keep, 36-hour window | The load runs daily. The extra 12 hours allow for a late run without alerting. |
Result
The result is 11 tests. The schema and existing tests already cover two more candidates, and four were rejected. Written out in neutral notation:
unique columns=[order_id] window=ordered_at, 30d
— order_id is the grain; the shipments join can fan out an order into duplicates
not_null columns=[customer_id]
— every order belongs to a customer
relationships customer_id → analytics.dim_customers.customer_id window=ordered_at, 30d
— orders must not point at customers missing from the dimension
accepted_values status ∈ {open, shipped, cancelled}
— the only labels the model's CASE produces
not_null columns=[status]
— an unmapped upstream state turns into NULL, which accepted_values lets through
business_rule invalid when status = 'shipped' AND shipped_at IS NULL
— a shipped order without a ship date means the shipments join lost its row
relative_time ordered_at ≤ shipped_at
— an order cannot ship before it was placed
min_value order_total ≥ 0
— refunds are modelled separately, so a negative total is a bug
min_max 0 ≤ discount_pct ≤ 1
— a discount is a fraction of the order
business_rule invalid when abs(order_total - (subtotal + tax)) > 0.01
— the source total must agree with its components
freshness loaded_at, window 36h
— daily load plus 12h of slack
Mapping onto a format (dbt as an example)
uniqueandrelationshipshave no time-window parameter in dbt. Express the window as the test'swhereconfig, written in the warehouse dialect.dbt_utils.expression_is_trueasserts the valid condition. Invert the invalid-row expressions, for examplenot (status = 'shipped' and shipped_at is null).- Freshness on a model, rather than a source, has no native test in dbt core. Use
dbt_utils.recencyif the package is installed, or a singular SQL test otherwise.
Before you finish
- [ ] Each test has a single, clear concern and an explanation of why it exists.
- [ ] No test duplicates an existing test, a schema constraint or another suggestion.
- [ ] No not-null test on a column that is NOT NULL or a primary key, and no not-null + empty pair on the same column.
- [ ] Every value list and bound came from the code, the docs, the user or a profile, and none came from a guess.
- [ ] Ranges use both bounds only when both are true by definition.
- [ ] Accepted-values lists are complete.
- [ ] Unique and relationships checks on big tables are time-windowed on a creation or load timestamp.
- [ ] Freshness windows include slack over the refresh interval.
- [ ] Custom SQL uses the asset's dialect, flags invalid rows (or is inverted correctly for the format) and handles NULLs.
- [ ] The tests parse or compile with the project's tooling.
- [ ] Coalesce Quality YAML: each field matches the table in Fields per test type,
relative_timeputs the earlier column incolumn, optional foreign keys setignore_nulls: true, and every entity id is copied, not guessed.
04 同仓库更多 Skills
coalesce-transform-mcp-install
Use when installing, connecting, or troubleshooting the hosted Coalesce Transform MCP server in an MCP client (Claude Code, Claude Desktop, Cursor, VS Code, Cortex Code) — endpoint URL, access token handling, registration, and verification. Not for coa CLI auth (use coalesce-cloud-api).
coalesce-sql-transformation
Use when editing the SQL inside existing Coalesce V2 node files (.sql) — changing column expressions, joins, filters, CTEs, inline column annotations, or ref() macros. Not for creating/deleting node files (use coalesce-pipeline-structure).
coalesce-review-risk
Use when reviewing Coalesce pipeline changes for correctness, impact, and risk before approval — READ-ONLY analysis of diffs, coa validate/dry-run evidence, and downstream blast radius. Never edits files or runs warehouse-mutating commands.
coalesce-rename-node-cascade
Rename a Coalesce node and cascade the new name through every downstream ref(), job selector, and subgraph selector, with quote-agnostic matching and validation.
coalesce-pipelines
Use when working in a Coalesce Transform repository (data.yml + nodes/ with <LOCATION>-<NAME>.sql/.yml files) — building, editing, validating, or running data transformation pipelines with the coa CLI, including editing V1 .yml nodes and shared config (data.yml, locations, environments, jobs, node types). Start here; it routes to the more specific coalesce-* skills.
coalesce-pipeline-structure
Use when creating, deleting, renaming, or rewiring Coalesce nodes, subgraphs, or jobs — changing the structural topology of the transformation DAG. Not for editing SQL inside an existing node (use coalesce-sql-transformation).