# Checks: freshness and completeness (FRS), grain, keys and values (KEY)

How to read each check:

- Impact class: A (wrong money, conversion or identity data in a decision or customer action, or restricted data exposed), B (a key report wrong or a missing control that lets the next failure through), C (maintainability). The class sets the weight in [scoring-and-verdicts.md](scoring-and-verdicts.md).
- Thresholds are defaults. An agreed value in `metrics.md` or `decisions.md` replaces them; an override in `audit-config.json` needs a reason and is printed in the report.
- N/A is never allowed for an in-use output unless the check says otherwise.
- NOT CHECKED means the evidence route was unavailable. Record which access or export would close it.
- Query IDs (Q01 to Q23) refer to [../assets/bigquery-audit-queries.sql](../assets/bigquery-audit-queries.sql) and [../assets/snowflake-audit-queries.sql](../assets/snowflake-audit-queries.sql).

## Restatement against reload windows (checked 2026-11-01)

- GA4 BigQuery export: Google updates a daily table for up to 2 calendar days plus today after its date (the table for 2026-10-29 can change through 2026-11-01); events arriving later are not recorded. Any model that reads GA4 must re-read at least the last 3 daily tables, and the audit window ends at run date minus 4 days, the newest table that can no longer change.
- Google Ads conversions: each conversion action has its own click-through window, from 1 to 90 days. Conversions are reported against the click date by default, so old days keep growing until the window closes.
- BigQuery Data Transfer Service for Google Ads: refresh window 7 days by default, configurable from 1 to 30.
- Fivetran Google Ads: incremental syncs cover the past 3 days; a daily rollback sync covers the Conversion Window Size, 1 to 90 days, default 30. Changing it does not change the Google Ads account.
- Fivetran Facebook Ads: prebuilt reports roll back 9 days; custom reports follow the longest selected attribution window (28-day click gives 28).
- Meta Insights: since 12 January 2026 the 7-day view and 28-day view windows are no longer returned. 1-day click, 7-day click, 28-day click, 1-day engaged view and 1-day view remain.
- CRM and billing: records change long after creation (stage moves, refunds, credit notes). The reload rule is the connector's change capture plus deletes; check KEY-06 for the delete side.

Rule: for every restating source behind a critical output, reload window >= restatement window, or a deep refresh that covers the restatement window runs at least weekly.

## Manifest and spine method

- The manifest lists every required slice: system, account or property, environment, native time zone, currency, connector, reload window, owner.
- Build it from the platforms' own account lists. A manifest built from warehouse distinct values cannot show an account that never loaded.
- The spine is every manifest slice times every day in the window (Q04). Join loaded rows to it from the staging table, because a zero-filling mart hides missing days.
- Count coverage: loaded active slices divided by expected active slices.
- Value-weighted coverage: platform value of loaded slices divided by platform value of all active slices, from a platform export through reconcile_exports.py. One missing large account can matter more than many small ones; report both numbers.
- A slice without activity is a confirmed zero only with a load log, a cursor, explicit zero rows or the platform's own report. The Google Ads API does not return rows whose metrics are all zero, so absence proves nothing either way.

## FRS-01 Source manifest built from the platforms (B)

- Evidence: `stack.md` source list; Google Ads MCP `list_accessible_customers`, GA4 MCP `get_account_summaries`, Meta business settings or an Ads Manager export, LinkedIn and Microsoft account lists, HubSpot portal settings, Stripe account list. Q03 extras only find accounts the manifest misses.
- Manager accounts: `list_accessible_customers` returns the accounts the identity can open directly, so under a manager it returns the manager, not the client accounts that spend. If an ID is a manager account, list its clients by running `search` on the manager (`SELECT customer_client.id, customer_client.descriptive_name, customer_client.currency_code, customer_client.time_zone, customer_client.manager, customer_client.status FROM customer_client WHERE customer_client.level <= 1`). Repeat for sub-managers, confirm the field names with `get_resource_metadata`, then keep the accounts with cost in the window, using a `customer` query per account.
- PASS: every in-use output has a manifest of required slices with owners, and every platform account with spend or conversions in the window is in it.
- WARN: the manifest was derived from warehouse distinct values, a slice has no owner, or retired accounts are still listed.
- FAIL: an account or property with spend or conversions in the window is missing from both the manifest and the warehouse.
- N/A: never. NOT CHECKED: no platform account list through any read route or export.
- Wrong fix: SELECT DISTINCT account_id from the warehouse and calling it the manifest.

## FRS-02 Source-through age per required slice (A)

- Evidence: Q03 at the consumer deadline in the business time zone; latest platform activity from the read route.
- PASS: every required slice is within the contract's maximum age at the deadline. Default for daily outputs: the prior business day is present (lag of 1 day or less).
- WARN: the worst slice is one day past the contract (default lag 2 days), or no contract exists and the default was applied.
- FAIL: any slice is two or more days past the contract (default lag 3 days or more), or has no rows in the last 7 days while the platform shows activity.
- Compute: lag per slice, never one MAX(date) across slices. Intraday outputs use hours.
- Wrong fix: raising the threshold until the dashboard turns green.

## FRS-03 Expected-slice coverage over the window (A)

- Evidence: Q04 spine against loaded rows; reconcile_exports.py `missing_in_b` with the platform value of each missing key.
- PASS: every slice with platform activity is loaded; slices without activity are confirmed zero.
- WARN: missing active slices total under 0.5% of the window's platform value and under 1% of expected slices.
- FAIL: missing active slices reach 0.5% of platform value or 1% of expected slices.
- Precedence: missing slices score here, never in REC-02 or REC-03.
- Wrong fix: back-filling zeros or copying platform totals into the mart.

## FRS-04 Freshness signal read from data, per slice (B)

- Evidence: check_dbt_artifacts.py with `--config` (thresholds, `loaded_at_field` or `loaded_at_query`, wildcard identifiers, for every source the output models read, tagged or not); monitor configuration in other tools; Q03b metadata time against data time.
- PASS: every critical source's monitor reads a source-through or event timestamp, per slice where slices exist.
- WARN: a monitor reads an ingestion timestamp (`_fivetran_synced`, `_loaded_at`) or one maximum across slices.
- FAIL: a critical source has no freshness monitor, or a metadata-based one (dbt freshness without `loaded_at_field`, Snowflake LAST_ALTERED, BigQuery partition `last_modified_time`), or metadata freshness on a BigQuery wildcard source such as `events_*`.
- Why metadata fails: Snowflake LAST_ALTERED moves on DDL, on DML that affects no rows and on background maintenance. dbt computes freshness from warehouse metadata when `loaded_at_field` is absent, and documents metadata freshness on BigQuery wildcard sources as unreliable (behaviour flag `bigquery_reject_wildcard_metadata_source_freshness`, dbt 1.12 and later; verify for the installed version). `_fivetran_synced` is not updated for TRUNCATE, mass deletes during a re-sync or history-mode deletes. BigQuery PARTITIONS `last_modified_time` may not reflect deletions.
- Precedence: a sparse check schedule scores in OPS-03, not here.
- Wrong fix: trusting a green freshness job without asking what it measured.

## FRS-05 Reload window covers source restatement (A)

- Evidence: connector and model settings (Fivetran Conversion Window Size, Data Transfer refresh window, dbt incremental lookback) against the restatement windows above; the REC-03 method run on the month just older than the reload window.
- PASS: reload window >= restatement window, or a covering deep refresh runs at least weekly, and the older-month comparison is within 2%.
- WARN: the reload window is shorter but recent days are labelled provisional and a deep refresh runs at least monthly, or the older month is off by over 2% and up to 5%.
- FAIL: the reload window is shorter with no deep refresh or labelling, a GA4 model re-reads fewer than the last 3 daily tables, or the older month is off by more than 5%.
- Compute: older-month gap = (warehouse minus platform) / platform for the same action set, column and date basis. In the worked example September conversions are 387 against 412 on the platform (-6.1%).
- Precedence: stale older days score here; REC-03 compares only the days inside the reload window at the last successful sync.
- Wrong fix: switching reports to conversion-time columns to hide the lag.

## FRS-06 Export and connector health (B)

- Evidence: Q01 daily and intraday tables per day; Q02 exported events per daily table; connector logs and alerts (sync-blocking errors against warnings); paused connectors.
- PASS: one daily table per day, no intraday table counted with its daily table, peak daily events under 90% of the 1,000,000-event standard export limit (or an Analytics 360 property), no sync-blocking error or paused connector on a critical source.
- WARN: peak daily events at 90% of the limit or more, connector warnings open longer than 7 days, or streaming (intraday) tables used for completed days.
- FAIL: a missing daily table in the window, intraday and daily rows summed for one date, a paused export, or a sync-blocking error on a critical source for more than 24 hours.
- Why it matters: a paused GA4 export is not reprocessed. An invalid payment method and a dataset region change also leave permanent gaps.
- Wrong fix: filling a gap with streaming tables as if they were complete.

## FRS-07 Empty loads told apart from missing loads (C)

- Evidence: load manifests, sync logs, cursors or explicit zero rows; the mart's zero-fill logic.
- PASS: zero-activity days are evidenced by a load log, a cursor or explicit zero rows.
- WARN: the model zero-fills missing days without evidence, so a missing load reads as a real zero.
- FAIL: not used; the loss itself scores in FRS-03.
- Wrong fix: removing the date spine. Keep it and join it to a load manifest.

## Grain conventions

- Report excess rows: keyed rows minus distinct keys. This is the number KEY-01 thresholds use.
- Two other measures exist and must not be mixed with it: rows in duplicated groups, and keys duplicated. For keys A x3, B x2, C x1 (6 rows): excess rows 3, rows in duplicated groups 5, keys duplicated 2.
- The declared grain comes from `data-dictionary.md`, dbt YAML or a model contract. If none exists, infer it from the model name and tests, mark KEY-01 WARN "grain inferred" and confirm with the owner.

## KEY-01 Unique at the declared grain (A)

- Evidence: Q05 on each critical fact, mapping and dimension table.
- PASS: zero excess rows in money and conversion facts and in mapping tables used in joins.
- WARN: duplicates only in a dimension not used in money joins, at most 0.1% of rows, with a dedupe rule pending; or the grain was inferred.
- FAIL: any excess row in a spend, order, invoice, payment, deal or conversion fact, or in a mapping table used in a join.
- Precedence: duplicates at the declared grain score here; fan-out from a join scores in KEY-04.

## KEY-02 Required keys present and valid (A)

- Evidence: Q06 null, blank, whitespace and placeholder keys (unknown, none, null, n/a, (not set), 0, -1, test markers).
- PASS: none in money facts and identity or mapping keys; at most 0.1% in event-level behavioural tables.
- WARN: over 0.1% and up to 0.5% in behavioural tables or dimensions.
- FAIL: any in money facts or mapping keys, or over 0.5% elsewhere.

## KEY-03 Join coverage without silent drops (B)

- Evidence: Q07 orphans by rows and by value per currency; inner joins in published models.
- PASS: orphans at most 0.5% of rows and 0.5% of value.
- WARN: orphans at most 2% of rows and 2% of value.
- FAIL: orphans over 2% of rows or value, or a published model drops orphans through an inner join so their value disappears.

## KEY-04 No fan-out after joins (A)

- Evidence: Q08 measure totals and distinct fact keys at fact grain against the published model, with the published model's own filters on both sides.
- PASS: totals equal to the minor unit and distinct keys equal.
- WARN: not used.
- FAIL: any money or conversion measure inflated or deflated after a join.
- Typical causes: deals joined to contacts or line items, invoices joined to payment lines, campaigns joined to a dimension that keeps history rows.
- Wrong fix: COUNT(DISTINCT) or SUM(DISTINCT amount), which drops legitimate equal amounts. Aggregate each measure at its own grain first, then join the aggregates.

## KEY-05 Namespaces and environments separated (B)

- Evidence: key columns carry source system, account and environment; Stripe `livemode`, sandbox portals, test streams, internal and test accounts.
- PASS: every joined key carries its namespace and test or sandbox data is excluded by a documented flag.
- WARN: the namespace is implied by the table but not carried into joined models, or test exclusion relies on name patterns only.
- FAIL: a key collision across namespaces is observed, or test or sandbox rows are in a production money metric.

## KEY-06 Deleted and historical versions handled (A)

- Evidence: Q09 counts and values with and without `_fivetran_deleted` (Salesforce `is_deleted`), `_fivetran_active` for history-mode tables, merged and archived CRM records.
- PASS: current-state models exclude deleted, merged-away and inactive versions by a documented rule.
- WARN: the rule is undocumented but no affected rows exist in the window.
- FAIL: deleted, merged-away or historical versions are counted in a published metric.
- Note: Fivetran history mode drops `_fivetran_deleted` and adds `_fivetran_active`, `_fivetran_start` and `_fivetran_end`; raw counts then count versions, not entities, and a deleted record is one with no active row.
- Wrong fix: deleting rows in the warehouse. Filter in the model and keep history.

## KEY-07 Time zone and date basis consistent (B)

- Evidence: native time zones (GA4 property, Google Ads account, Meta ad account, CRM portal; billing is usually UTC); warehouse defaults (BigQuery starts each script at UTC; Snowflake TIMEZONE defaults to America/Los_Angeles); Q10 day-shift sample.
- PASS: each metric's date basis and time zone are documented and applied; day-level joins align time zones or document the offset.
- WARN: sources differ and the basis is undocumented while under 2% of a sample day's value moves between days, or a Snowflake session sits at the default time zone where TIMESTAMP_LTZ conversions happen.
- FAIL: within one published daily metric, day assignment differs by source and moves 2% or more of a sample day's value.
- Wrong fix: shifting a source by a fixed number of hours, which breaks at every daylight saving change.

## KEY-08 Currency and units correct (A)

- Evidence: Q11 currency per account and money column, value magnitudes (Google Ads `cost_micros`, Stripe minor units, zero-decimal currencies), conversion order in SQL; reconcile_exports.py currency mismatches.
- PASS: every money column has a currency and unit, units are normalised, and currencies are converted before summing.
- WARN: the currency code is missing but a single currency is confirmed from source configuration.
- FAIL: micros or minor units summed with major units, different currencies summed without conversion or labelled wrongly, or currency inferred from country.
- Precedence: currency and unit errors score here; REC-02 notes them and compares values only on matched keys whose currency agrees. reconcile_exports.py lists currency-mismatched keys apart, with each side's total in its own currency, and never subtracts one from the other.
- Wrong fix: a blanket divide by 100, which breaks zero-decimal currencies such as JPY.