# Production failure modes

Each entry: what goes wrong, how to detect it with this skill's queries and scripts, the check that scores it, and the fix that makes it worse.

## Freshness and completeness

1. Global freshness hides a missing account.
   - Mechanism: MAX(date) across all accounts is current while one account stopped loading.
   - Detect: Q03 lag per manifest slice; Q04 spine. Checks: FRS-02, FRS-03.
   - Wrong fix: raising the freshness threshold.
2. A manager account hides its client accounts.
   - Mechanism: `list_accessible_customers` returns the accounts the identity can open directly, so under a Google Ads manager the manifest lists the manager and misses the clients that spend.
   - Detect: the `customer_client` query on each manager and sub-manager ([read-only-access.md](read-only-access.md#platform-reads)), then cost per client in the window. Check: FRS-01.
   - Wrong fix: filling the gap from warehouse distinct values, which cannot show an account that never loaded.
3. Metadata freshness passes on empty writes.
   - Mechanism: Snowflake LAST_ALTERED moves on DML that affects no rows and on background maintenance; dbt freshness without `loaded_at_field` uses metadata; metadata freshness on BigQuery wildcard sources is unreliable.
   - Detect: check_dbt_artifacts.py flags metadata-based sources; Q03b compares metadata time with data time. Check: FRS-04.
   - Wrong fix: trusting a green freshness job.
4. Untagged sources behind critical models.
   - Mechanism: the project tags some models and sources critical, but the outputs also read models and sources nobody tagged, so their freshness and tests go unchecked.
   - Detect: check_dbt_artifacts.py `--config` takes the models from the audit config and adds every source they read through depends_on; compare with the `tag:critical` count. Checks: FRS-04, OPS-01.
   - Wrong fix: auditing only what the tags cover.
5. A zero-filled spine turns missing loads into real zeros.
   - Mechanism: a date spine with COALESCE(spend, 0) shows a failed load as a day without spend.
   - Detect: days at zero in the mart with platform spend in the export (reconcile_exports.py `missing_in_b` on the staging table). Checks: FRS-07, FRS-03.
   - Wrong fix: removing the spine. Keep it and join it to a load manifest.
6. Reload window shorter than the conversion window.
   - Mechanism: conversions keep arriving for up to 90 days after the click while the connector reloads 7 or 30 days, so older days freeze below the platform.
   - Detect: the REC-03 method on the month just older than the reload window; connector settings. Check: FRS-05.
   - Wrong fix: switching to conversion-time columns to hide the lag.
7. GA4 export gaps and double counting.
   - Mechanism: a daily table is missing (limit pause, billing problem, region change) or intraday rows are unioned with daily rows for one date.
   - Detect: Q01 one daily table per day; Q02 events against the 1,000,000 standard limit. Check: FRS-06.
   - Wrong fix: filling gaps with streaming tables as if they were complete.
8. The API omits zero rows.
   - Mechanism: the Google Ads API does not return rows whose metrics are all zero, and LinkedIn's adAnalytics returns an empty response with no activity, so a quiet day looks like a missing load.
   - Detect: the platform's own report confirms zero activity, or a load log exists. Checks: FRS-07, FRS-03.
   - Wrong fix: treating every absent day as a failure.

## Grain, keys and values

9. Fan-out after a join.
   - Mechanism: deals joined to contacts or line items, or invoices joined to payments, multiply amounts.
   - Detect: Q08 totals at fact grain against the published model, same filters. Check: KEY-04.
   - Wrong fix: COUNT(DISTINCT) or SUM(DISTINCT amount), which drops legitimate equal amounts.
10. Deleted and historical versions counted.
    - Mechanism: `_fivetran_deleted` rows or history-mode versions (`_fivetran_active` false) stay in current-state models.
    - Detect: Q09 with and without the filters. Check: KEY-06.
    - Wrong fix: deleting rows in the warehouse.
11. Micros and minor units mixed.
    - Mechanism: Google Ads `cost_micros` not divided by 1,000,000; Stripe minor units summed with major units; zero-decimal currencies divided by 100.
    - Detect: Q11 magnitude probe against the platform export. Check: KEY-08.
    - Wrong fix: a blanket divide by 100.
12. Currency summed without conversion, or labelled wrong.
    - Mechanism: a EUR ad account summed into USD spend, or labelled USD; a warehouse that stores converted spend compared with the platform's native amounts.
    - Detect: reconcile_exports.py lists currency-mismatched keys apart and never subtracts their values; Q11 currency per account against the manifest. Checks: KEY-08, MET-05.
    - Wrong fix: inferring currency from country.
13. Time zone drift.
    - Mechanism: the Google Ads account time zone, the GA4 property time zone, UTC billing, Microsoft Advertising downloads relative to UTC and a Snowflake session default of America/Los_Angeles put one event on different days.
    - Detect: Q10 day-shift sample. Check: KEY-07.
    - Wrong fix: shifting a source by a fixed offset that ignores daylight saving.
14. Test and live data mixed.
    - Mechanism: Stripe test-mode rows, sandbox portals or test GA4 streams land in production marts.
    - Detect: KEY-05 namespace and `livemode` checks. Check: KEY-05.
    - Wrong fix: filtering by name patterns only.

## Identity

15. Shared identifiers over-link people.
    - Mechanism: a role address such as billing@ or a test email merges many contacts or companies in an identity map.
    - Detect: Q13b identifier fan-in by salted reference with a role-address flag. Check: IDN-05.
    - Wrong fix: keeping the newest merge.
16. Lowercased click IDs.
    - Mechanism: a CRM workflow or form script lowercases gclid, so offline uploads cannot match.
    - Detect: Q12 lowercase-only share. Check: IDN-01.
    - Wrong fix: re-uploading the same lowercased IDs.
17. Email used as the billing join.
    - Mechanism: billing customers matched to the CRM by email misattribute shared or changed addresses.
    - Detect: IDN-03 share mapped by explicit ID against share mapped by email. Check: IDN-03.
    - Wrong fix: fuzzy matching presented as fact.

## Metric definitions

18. Overlapping platform action types summed.
    - Mechanism: an aggregate Meta action type plus its component types, or Google Ads `all_conversions` where `conversions` was meant.
    - Detect: distinct action types and their sums against the platform UI column. Check: MET-07.
    - Wrong fix: dividing by a fudge factor.
19. Platform claims summed into a company total.
    - Mechanism: several platforms claim the same outcome and the sum is presented as total conversions.
    - Detect: the tile definition against backend outcomes. Check: MET-04.
    - Wrong fix: de-duplicating platform claims by guesswork.
20. Two implementations of one metric drift.
    - Mechanism: a BI calculated field and a dbt metric share a name but not filters or currency rules; or a GA4-report field is compared with an export model on sessions, which reports estimate.
    - Detect: run both on the same closed period and compare by date (Q20, reconcile_exports.py). Check: MET-02.
    - Wrong fix: picking the larger number.
21. CAC built on different numerators.
    - Mechanism: the dashboard divides platform cost while finance divides invoiced media or fully loaded cost, both called CAC; invalid-activity credits, tax, fees and FX then look like data errors.
    - Detect: the numerator field of the CAC contract; the platform-cost-to-finance bridge ([reconciliation-bridges.md](reconciliation-bridges.md)). Checks: MET-02, REC-07.
    - Wrong fix: a plug line that forces the dashboard onto the finance number.
22. Removed Meta windows read as zero.
    - Mechanism: models still request 7-day view or 28-day view windows, which Meta stopped returning on 12 January 2026; nulls become zeros.
    - Detect: null rates on those columns since 2026-01-12. Check: MET-07.
    - Wrong fix: back-filling the old windows with estimates.

## Channels

23. Unknown coalesced to Direct.
    - Mechanism: COALESCE(source, 'direct') hides missing attribution and inflates Direct.
    - Detect: SQL review and classify_channels.py `--warehouse-map` ("unknown mapped to Direct"); Q17 for CRM fields. Checks: CHN-07, CHN-08.
    - Wrong fix: relabelling Direct as organic.
24. Stale channel CASE logic.
    - Mechanism: warehouse rules miss the AI Assistant medium (a GA4 channel since 13 May 2026) or hyphenated paid media, or map paid-social to Organic Social.
    - Detect: classify_channels.py `--warehouse-map` agreement. Check: CHN-07.
    - Wrong fix: copying GA4's numbers into the warehouse.
25. Unresolved macros in UTMs.
    - Mechanism: a tracking template sends `{{campaign.name}}` or `{campaignid}` literally; campaign rows split and cost joins break.
    - Detect: classify_channels.py macro flags (bracketed campaign names such as [Brand] are not macros); Q18 join coverage. Checks: CHN-05, CHN-06.
    - Wrong fix: mapping the literal to a campaign by hand, forever.

## Controls and consumers

26. Publication without a gate.
    - Mechanism: a dbt platform job's freshness checkbox does not stop later steps, and a reverse ETL or upload schedule runs after a failed build on yesterday's table.
    - Detect: job and trigger review plus a drill on a safe copy. Check: OPS-04.
    - Wrong fix: adding an alert and calling it a gate.
27. The sync succeeds, the destination does not accept.
    - Mechanism: the reverse ETL run reports success while the ad platform rejects rows (no time zone offset, conversion before click, expired clicks) or receives them without `consent.ad_user_data`, so they may not be attributable.
    - Detect: rows selected, rows sent and rows accepted for the same days; the Google Ads upload summaries through the MCP `search` tool; rows without consent by region. Check: REC-08.
    - Wrong fix: reading the sync's success status as proof of acceptance.
28. Failure evidence overwritten.
    - Mechanism: dbt `store_failures` replaces the previous failure set on every run, including clean runs, and failure tables can hold personal data.
    - Detect: OPS-02 evidence retention; Q21, Q22a and Q22b on failure schemas. Checks: OPS-02, OPS-08.
    - Wrong fix: widening access to the audit schema.