In a warehouse dimension table, what do not-null and accepted-values tests each catch?

## Two assertions, two different failures Column-level tests are the cheapest tests in a warehouse and the ones most often written carelessly. Two carry most of the weight on a dimension table: not-null and accepted-values. ## Not-null: which columns actually need it A not-null assertion says the column is populated on every row. The mistake is applying it either to everything or to nothing. Columns that genuinely need it: - **The surrogate key.** It is the join target for every fact; a NULL there means fact rows can never resolve to that dimension row. - **The natural key** (the source system's identifier). Without it you cannot match a source record to its dimension row on the next load, so history breaks. - **Validity dates on a versioned dimension.** A row whose effective-from is NULL cannot be placed in time, and every point-in-time lookup will skip or double-count it. - **Attributes reports group by.** A NULL in a `country` or `segment` column does not error — it creates a blank row in every chart, and stakeholders read that blank as a real category. Columns that should stay nullable: genuinely optional attributes, where NULL carries a meaning the business accepts. The important discipline is to decide which of "unknown", "not applicable" and "not yet collected" a NULL represents and write that down; a column where NULL means three different things is a defect no test can fix. A distinct and very common convention is to avoid NULL keys altogether by pointing unmatched facts at a reserved default dimension row instead. Where that convention is in force, the not-null test on the foreign key becomes enforceable rather than aspirational, and unmatched rows stay visible in reports rather than vanishing from inner joins. ## Accepted-values: an executable enumeration An accepted-values test lists the legal contents of a low-cardinality column and fails when anything else appears: ```sql select status_code, count(*) as row_count from dim_order where status_code not in ('NEW','PAID','SHIPPED','CANCELLED') or status_code is null group by status_code ``` Note the explicit NULL branch: `not in` against a NULL yields unknown rather than true, so a NULL would otherwise slip past. That is the single most common bug in a hand-written enumeration test. What it is really for is **detecting source change**. Upstream systems add statuses, rename channels, split a segment in two. None of those changes announce themselves. Downstream, your model almost certainly has a `CASE` expression that maps codes to labels or to a boolean, and an unmapped code silently falls to the `ELSE` branch, gets bucketed as "other", or is dropped by a `WHERE` filter that lists the statuses it wants. The number stays plausible and becomes wrong. Because of that, an accepted-values test's list should be kept in sync with the `CASE` expression that consumes it. If they disagree, the test is enforcing a domain the model does not actually handle. ## Severity: warn or fail? This is where judgment enters. A new enumeration value is usually *unexpected* rather than *corrupt*, and failing the whole pipeline over a new status code can be disproportionate — the existing rows are still fine. A common posture: not-null on keys and validity dates blocks the run, while accepted-values raises a warning that is triaged the same day, escalating to blocking on columns where an unmapped value produces a materially wrong number (revenue recognition status, refund flag). What you must not do is set every test to warn and then stop reading warnings. A test nobody acts on is worse than no test, because it manufactures the feeling of coverage. ## Cardinality is the sanity check Accepted-values only makes sense on genuinely bounded columns — a dozen statuses, two hundred countries. Applying it to a column with thousands of distinct values produces a list nobody maintains, and it will be silenced within a month. For high-cardinality columns the useful assertions are different in kind: not-null, a format or length check, or a distinct-count range that fails when cardinality jumps unexpectedly. ## Why these belong on every load Both tests are cheap — a single scan, often on a small dimension — and both catch problems at the boundary where the data enters, before anything joins to it. Running them only on the published mart tells you a number is wrong; running them on the staging model tells you the source changed, which is the actual news.