25 checks, all offline.

The practice

No query block joins two table expressions without an explicit linking predicate (ON/USING) unless the join is written as an explicit CROSS JOIN; comma-separated FROM lists contain at most one table expression. This record owns the comma-join ban for the whole pack.

Why it matters

A missing predicate multiplies row counts (m x n) while the query stays syntactically valid, so nothing fails loudly. At production scale this exhausts memory or disk and, worse, silently feeds wrong rows downstream.

Wrong

SELECT COUNT(*) FROM customers, orders;

Right

SELECT COUNT(*) FROM customers JOIN orders ON orders.customer_id = customers.id;

The practice

When a query reports a ratio (rate, share, conversion, margin) over more than one row, it divides the summed numerator by the summed denominator. It does not AVG a column or expression that is already a per-row ratio, unless the unweighted mean of row ratios is explicitly what is wanted and is labelled as such.

Why it matters

AVG(rate) gives every row equal weight regardless of its denominator, so a day with 2 visits counts as much as a day with 100. The result is a different number from the overall rate, often by a large margin, and nothing in the query looks wrong. This is the plain-SQL form of the rule sem/ratio-metrics-divide-after-aggregation enforces for governed metrics.

Wrong

SELECT AVG(conversions / visits) AS cvr FROM daily_funnel;

Right

SELECT SUM(conversions) / SUM(visits) AS cvr FROM daily_funnel;

The practice

A filter on a TIMESTAMP (or DATETIME) column uses a half-open range, ts >= start AND ts < end_exclusive, never BETWEEN with date-only bounds such as BETWEEN '2024-01-01' AND '2024-01-31'.

Why it matters

BETWEEN includes both endpoints, and a date-only bound compared with a timestamp means midnight. BETWEEN '2024-01-01' AND '2024-01-31' therefore keeps only the first instant of January 31 and drops the rest of that day. Widening the upper bound to '2024-02-01' over-counts instead, by including events at exactly midnight on February 1.

Wrong

SELECT SUM(amount) FROM orders WHERE order_ts BETWEEN '2024-01-01' AND '2024-01-31';

Right

SELECT SUM(amount) FROM orders WHERE order_ts >= '2024-01-01' AND order_ts < '2024-02-01';

The practice

A CASE expression whose result feeds an aggregate (SUM, AVG), arithmetic, or a filter has an explicit ELSE branch (ELSE 0, ELSE NULL, or ELSE an explicit bucket such as 'unknown'), so the value for unmatched rows is a visible decision.

Why it matters

A CASE with no ELSE returns NULL for rows that match no branch. SUM over a group with no matching rows is then NULL rather than 0, and NULL propagates through arithmetic: gross - refunds becomes NULL for that group. A later SUM across groups skips the NULL, so the group silently drops out of the total.

Wrong

SELECT region, SUM(CASE WHEN type = 'sale' THEN amt END) - SUM(CASE WHEN type = 'refund' THEN amt END) AS net FROM tx GROUP BY region;

Right

SELECT region, SUM(CASE WHEN type = 'sale' THEN amt ELSE 0 END) - SUM(CASE WHEN type = 'refund' THEN amt ELSE 0 END) AS net FROM tx GROUP BY region;

The practice

When a query LEFT JOINs a parent to a child table and counts children per parent, it counts a non-null child column (COUNT(child.key)), never COUNT(*) or COUNT(1).

Why it matters

A LEFT JOIN keeps each unmatched parent as one row with NULLs on the child side. COUNT(*) counts rows, so every parent with zero children is reported as having one. Zero-activity cohorts disappear from the data with no error.

Wrong

SELECT c.id, COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id;

Right

SELECT c.id, COUNT(o.order_id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id;

The practice

SELECT DISTINCT over a join is rejected unless the author records the business key it dedupes by and why duplicates are legitimate; the default fix is correcting the join grain, pre-aggregating, or using EXISTS.

Why it matters

DISTINCT dedupes whole rows, not business keys. Used to undo a fanout, it also collapses legitimately identical rows (two orders of the same amount), so totals computed over the "deduplicated" set are silently low; and it hides the fanout that created the duplicates in the first place.

Wrong

SELECT SUM(amt) FROM (SELECT DISTINCT o.cid, o.amt FROM orders o JOIN tags t ON t.cid = o.cid);

Right

SELECT SUM(amt) FROM orders o WHERE EXISTS (SELECT 1 FROM tags t WHERE t.cid = o.cid);

The practice

On engines that silently accept ungrouped columns in an aggregate query (MySQL with ONLY_FULL_GROUP_BY disabled, SQLite), every projected column under GROUP BY is a grouping key, wrapped in an aggregate, or wrapped in an explicit arbitrariness marker such as ANY_VALUE(). Engines that reject the query (DuckDB, Postgres, BigQuery, Snowflake, MySQL in its default mode) need no lint: the error already stops it.

Why it matters

Where the engine accepts it, a bare column returns the value from one input row of the group, chosen by the engine, and reads as meaningful. Reports then show a plausible but arbitrary representative per group. MySQL documents the chosen values as nondeterministic, and adding ORDER BY does not influence the choice.

Wrong

SELECT dept, emp, COUNT(*) FROM staff GROUP BY dept;

Right

SELECT dept, MIN(emp), COUNT(*) FROM staff GROUP BY dept;

The practice

Every INSERT INTO names the target columns explicitly and projects the source in the same order (or uses a by-name form such as DuckDB's INSERT INTO ... BY NAME); positional INSERT without a column list is rejected.

Why it matters

Positional inserts bind by declared column order, not by name. When the source has the same number of compatible columns in a different order, the insert succeeds and silently writes values into the wrong columns; the defect surfaces far from the schema change that caused it.

Wrong

INSERT INTO customers SELECT * FROM staging_customers;

Right

INSERT INTO customers (first_name, last_name) SELECT first_name, last_name FROM staging_customers;

The practice

In dialects where integer / integer yields an integer (Postgres, SQL Server, SQLite), a division whose operands are both integer-typed casts one operand to NUMERIC/DECIMAL/DOUBLE (or multiplies by 1.0) before dividing. In DuckDB, the integer-division operator // (and divide()) is used only when truncation is intended.

Why it matters

Postgres, SQL Server and SQLite truncate integer division toward zero, so a ratio of counts (conversions / visits) is silently 0 for every row where the numerator is smaller than the denominator. Dashboards show 0 percent and nothing errors. Portable SQL that is correct on BigQuery, Snowflake, MySQL or DuckDB (where / returns a fractional result) becomes wrong when moved to Postgres or SQL Server.

Wrong

SELECT conversions / visits AS cvr FROM daily_funnel;  -- Postgres, SQL Server, SQLite

Right

SELECT conversions::numeric / visits AS cvr FROM daily_funnel;

The practice

Queries that page through a table that can change between page fetches use a keyset predicate on a unique ordered key (WHERE key > :last_seen ORDER BY key LIMIT n) instead of LIMIT plus OFFSET, and page ordering always includes a unique tiebreaker. Large literal OFFSETs (default threshold 10,000) are flagged for cost as well.

Why it matters

Rows inserted or deleted ahead of the cursor between page fetches shift every OFFSET, so rows are skipped or delivered twice and the export still reports success. OFFSET also forces the engine to produce and discard the skipped rows, so cost grows with page depth.

Wrong

SELECT id FROM p ORDER BY id LIMIT 2 OFFSET 2;

Right

SELECT id FROM p WHERE id > 2 ORDER BY id LIMIT 2;

The practice

Predicates on columns of the nullable side of a LEFT, RIGHT, or FULL join appear in the ON clause; the WHERE clause never null-rejects those columns unless the inner-join conversion is intended and recorded.

Why it matters

A WHERE predicate that null-rejects eliminates the unmatched rows the outer join was written to keep, silently converting it to an inner join. Reports undercount with no error.

Wrong

SELECT c.id FROM c LEFT JOIN o ON o.cid = c.id WHERE o.status = 'done';

Right

SELECT c.id FROM c LEFT JOIN o ON o.cid = c.id AND o.status = 'done';

The practice

Any query block with LIMIT or FETCH FIRST also has an ORDER BY, unless the limit is a bare existence probe (LIMIT 1 whose projected values are not used). The ORDER BY must also be over a key that is unique in the result. These are two separate checks: the detector flags only a MISSING ORDER BY; proving that the ORDER BY keys are unique needs declared uniqueness facts and is left to contract mode.

Why it matters

Without ordering, which rows come back is unspecified and can change between runs after reloads, compaction, or parallelism changes. Dashboards, samples, and tests built on unordered limits flap. A non-unique ORDER BY has the same problem at the cut-off row.

Wrong

SELECT id FROM t LIMIT 2;

Right

SELECT id FROM t ORDER BY id LIMIT 2;

The practice

Joins name their keys with an explicit ON or USING list; NATURAL JOIN is rejected. (Comma joins without a predicate are covered by sql/accidental-cross-join, not by this record.)

Why it matters

NATURAL JOIN infers keys from shared column names, so adding an audit column to both tables silently rewrites join semantics with no query edit. If the tables share no column names it degrades to a CROSS JOIN. The query text stops describing the grain it computes.

Wrong

SELECT * FROM a NATURAL JOIN b;

Right

SELECT * FROM a JOIN b ON a.id = b.id;

The practice

A negative predicate on a nullable column (col <> 'x', col != 'x', col NOT LIKE 'p%', col NOT IN ('a', 'b')) makes an explicit NULL decision in the same query: IS DISTINCT FROM 'x', OR col IS NULL, AND col IS NOT NULL, or a COALESCE. This is a heuristic: excluding NULLs may be intended, and the rule only asks that the choice be visible.

Why it matters

"Not cancelled" reads as including rows with an unknown status, but under three-valued logic NULL <> 'cancelled' is unknown, so those rows are dropped. The query runs, the count looks plausible, and the NULL rows vanish from the answer with no error. Analysts write these filters constantly during exploration.

Wrong

SELECT COUNT(*) FROM orders WHERE status <> 'cancelled';

Right

SELECT COUNT(*) FROM orders WHERE status IS DISTINCT FROM 'cancelled';

The practice

Merge keys, dedupe keys, and incremental watermarks never call per-row nondeterministic functions (RAND, RANDOM, UUID, GEN_RANDOM_UUID, NEWID); wall-clock calls in keys are replaced by deterministic business keys, and templates are checked pre-render so Jinja markers like is_incremental do not hide the call.

Why it matters

A key that changes per row or per run defeats matching by construction: every rerun produces new keys, so a MERGE inserts instead of updating and reruns are never idempotent. The table silently grows by one full copy of the batch per rerun.

Wrong

MERGE INTO t USING (SELECT uuid() AS uid, order_id, amt FROM s) s ON t.uid = s.uid WHEN NOT MATCHED THEN INSERT VALUES (s.uid, s.order_id, s.amt);

Right

MERGE INTO t USING s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET amt = s.amt WHEN NOT MATCHED THEN INSERT (order_id, amt) VALUES (s.order_id, s.amt);

The practice

Any NOT IN whose subquery column is nullable is rewritten as NOT EXISTS (correlated) or the subquery explicitly excludes NULLs.

Why it matters

Three-valued logic poisons the whole predicate: a single NULL in the subquery makes NOT IN unknown for every row that is not otherwise matched, so the query returns zero rows. It runs fine and the answer is silently empty.

Wrong

SELECT id FROM users WHERE id NOT IN (SELECT user_id FROM banned);

Right

SELECT id FROM users u WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.id);

The practice

A comparison against the NULL literal is written IS NULL / IS NOT NULL (or IS [NOT] DISTINCT FROM for a nullable value); the forms = NULL, <> NULL and != NULL never appear in a predicate.

Why it matters

An ordinary comparison with NULL yields NULL (unknown), never true, so WHERE x = NULL and WHERE x <> NULL both filter out every row. The query runs without error and silently returns nothing; a JOIN ON a.x = NULL matches nothing either.

Wrong

SELECT id FROM customers WHERE email = NULL;

Right

SELECT id FROM customers WHERE email IS NULL;

The practice

Every ROW_NUMBER used to pick one row per group (QUALIFY rn = 1 or equivalent) has an ORDER BY whose columns are unique within the partition.

Why it matters

Without a stable order the surviving row is arbitrary; with a non-unique order key, tied rows can swap winners between runs or reloads. Golden-record tables then flap and downstream diffs never settle.

Wrong

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY ts) AS rn FROM t) WHERE rn = 1;

Right

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY ts DESC, capture_id DESC) AS rn FROM t) WHERE rn = 1;

The practice

Every branch of a UNION, INTERSECT, or EXCEPT projects the same columns in the same order with compatible types, verified by alias and type comparison across branches; branches never use SELECT * and branches with mismatched names fail review.

Why it matters

Set operations pair columns by position, not by name. A reordered branch silently merges codes into names, and the query returns wrong rows with no error whenever the swapped types are compatible.

Wrong

SELECT name, code FROM a UNION ALL SELECT code, name FROM b;

Right

SELECT name, code FROM a UNION ALL SELECT name, code FROM b;

The practice

Every UPDATE or DELETE has a WHERE clause (or WHERE CURRENT OF), or carries an explicit full-table marker such as a deliberate TRUNCATE or a reviewed full_refresh flag; unguarded whole-table mutations are rejected.

Why it matters

A missing filter applies the mutation to every row. In a warehouse this is large-scale data loss or corruption, recoverable only through time travel or rebuild, and the statement itself reports success.

Wrong

UPDATE acct SET bal = 0;

Right

UPDATE acct SET bal = 0 WHERE id = 1;

The practice

UNION and UNION ALL answer different questions, and which one is right depends on intent. UNION (DISTINCT) is correct when the author wants the set of distinct rows across branches; UNION ALL is correct when appending rows that can legitimately repeat (events, transactions, log lines). A bare UNION is flagged until the author records that dedup is intended; appends use UNION ALL.

Why it matters

UNION silently removes duplicate rows across and within branches. When the rows are separate real-world facts that happen to be identical (two identical payments, two webhook deliveries), that removal is data loss and every downstream count and sum is low. The linter cannot know the intent, so it asks for it rather than declaring UNION wrong.

Wrong

SELECT payment_id, amount FROM card_payments UNION SELECT payment_id, amount FROM wallet_payments;

Right

SELECT payment_id, amount FROM card_payments UNION ALL SELECT payment_id, amount FROM wallet_payments;

The practice

Every declared primary key or unique identifier has a check asserting zero duplicate values (COUNT(*) minus COUNT(DISTINCT key) equals zero), enforced on every write to the table.

Why it matters

Duplicate keys fan out every downstream join, double-counting revenue, users, or events. The failure is multiplicative and silent: each dup row inflates all measures built on the table.

Wrong

SELECT order_id, COUNT(*) AS n FROM fct_orders
GROUP BY order_id HAVING COUNT(*) > 1;
-- query exists in a notebook but blocks nothing

Right

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

The practice

Every source consumed by a pipeline MUST declare the timestamp column that defines its freshness (a loaded-at or event-time field) and a maximum acceptable data age with a warn and an error limit, and the pipeline MUST evaluate that threshold on each run, reporting the run time, the newest data timestamp and the resulting lag. Static reference tables that never change are marked frozen instead. Sources that feed critical outputs additionally declare a criticality tier, an owner and an alert route, so a breach pages a person rather than landing in an unmonitored channel; non-critical sources need the thresholds only.

Why it matters

Without a declared freshness limit, a silently stalled connector looks identical to a healthy one: downstream models keep building on increasingly stale data and nobody is paged. Declared thresholds turn "is this data fresh?" from tribal knowledge into a checkable property and draw the line between "late" and "fine". A freshness check nobody owns fires into a void, so critical sources need an owner and a paging path.

Wrong

sources:
  - name: shopify
    tables:
      - name: orders   # no freshness block; staleness is invisible
  - name: payments     # feeds the board pack; check but no owner or route
    freshness: {error_after: {count: 24, period: hour}}

Right

sources:
  - name: shopify
    loaded_at_field: _etl_loaded_at
    tables:
      - name: orders
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
  - name: payments
    loaded_at_field: _etl_loaded_at
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    meta: {criticality: tier-1, owner: payments-data, alert_route: pagerduty://data-oncall}

The practice

Every model using a merge or delete-plus-insert incremental strategy must declare its merge key (unique_key) and every key column must be NOT NULL with one row per key value in each processed batch.

Why it matters

Null or duplicated merge keys make upserts nondeterministic: matched rows update the wrong target, or one source row silently wins among duplicates. The result is duplicated or missing rows in production tables.

Wrong

{{ config(materialized='incremental', incremental_strategy='merge') }}
SELECT email, name FROM raw_customers;  -- no unique_key, nulls allowed

Right

{{ config(materialized='incremental', incremental_strategy='merge', unique_key='customer_id') }}
SELECT customer_id, email, name FROM raw_customers WHERE customer_id IS NOT NULL;

The practice

Every scheduled Airflow DAG MUST pass `catchup` explicitly (False, or True with a bounded start_date and rerun-safe logic) instead of inheriting `[scheduler] catchup_by_default`. The inherited value is version- and config-dependent: it defaulted to True in Airflow 2.x and defaults to False from Airflow 3.0, and any deployment can override it.

Why it matters

With catchup on, unpausing or first deploying a DAG creates a run for every missed interval since start_date. The same DAG file therefore behaves differently across Airflow 2 and 3 or across deployments with different config; writing the value in the DAG makes the replay behaviour reviewable and stable through upgrades.

Wrong

DAG("x", schedule="@daily", start_date=pendulum.datetime(2024, 1, 1, tz="UTC"))

Right

DAG("x", schedule="@daily", start_date=pendulum.datetime(2024, 1, 1, tz="UTC"), catchup=False)