# dbt Repo Validation Report **Repo:** `data-platform/dbt` **Generated:** 2026-05-15 **Generator:** `gemma-dbt:validate-repo` (executed manually from PR branch `feat/gemma-dbt-validate-repo` in `gemma-agentic-toolkit`) This report audits the `data-platform/dbt` project across six dimensions: Gemma SQL style, internal naming consistency, logic hygiene, target-structure doc alignment, CLAUDE.md alignment, and Kimball dimensional modelling. Six sub-agents ran in parallel; findings were consolidated, de-duplicated, and severity-sorted. The skill is read-only by design — no files in the audited repo were modified by the validation itself. This report file is the only artifact. --- ## Executive summary | Dimension | critical | major | minor | info | total | |---|---:|---:|---:|---:|---:| | Gemma SQL style | 2 | 0 | 13 | 5 | 20 | | Naming consistency | 0 | 2 | 2 | 1 | 5 | | Logic | 1 | 11 | 4 | 0 | 16 | | Target structure | 0 | 2 | 4 | 1 | 7 | | CLAUDE.md | 0 | 1 | 0 | 0 | 1 | | Kimball | 0 | 8 | 3 | 3 | 14 | | **Total** | **3** | **24** | **26** | **10** | **63** | **Top three priorities (highest severity × confidence, cross-agent overlap):** 1. **`fact_device_activations` has no PK tests on any column** — `models/marts/facts/_facts.yml:764` (logic, also gemma-style, target-structure, kimball) — critical. Duplicates undetectable; BI may double-count. 2. **`fact_sales_invoice_lines` has no PK column with `unique`+`not_null` tests** — `models/marts/facts/_facts.yml:4` (gemma-style, also logic, target-structure, kimball) — critical. Same risk on the BC revenue fact. 3. **Deprecated `freshness:` top-level property in 8 source YAML files (87 tables, 304 dbt-parse warnings)** — will become a hard error in a future dbt version. (logic) — major. One pattern, many occurrences. --- ## What was checked - **Files audited:** 143 SQL models, 18 schema YAMLs, 2 macros, 2 dbt-test SQLs, `dbt_project.yml`, `packages.yml`, two `CLAUDE.md` files, `docs/testing-strategy.md`. Seed CSVs were NOT read (data-protection policy). - **Dimensions evaluated:** Gemma SQL style, naming consistency, logic, target structure, CLAUDE.md, Kimball. - **References used:** - Gemma SQL Style Guide: `~/dev/internal/gemma-sql-style/README.md` - Target-structure doc: **substituted** — no doc matched skill's search patterns under `dbt/docs/`. Used `dbt/CLAUDE.md` (Model Layers section) + `dbt/docs/testing-strategy.md`. - `dbt/CLAUDE.md` and `CLAUDE.md` (repo root) - Kimball checklist: skill plugin reference file (`gemma-dbt/skills/validate-repo/references/kimball-validation-checklist.md`) - **dbt parse:** succeeded. 493 nodes, 164 sources, 349 tests in `target/manifest.json`. Logic agent used `manifest.json` for dependency graph analysis. --- ## Findings by severity ### Critical (3) #### 1. `fact_device_activations` has no `unique`+`not_null` tests on any column - **Dimension:** logic (also reported by Gemma SQL style, target-structure, Kimball) - **Rule:** `logic-pk-test-missing` / `primary-key-tests` / `target-structure-testing-rule-violation` / `kimball-dim-surrogate-key` - **Location:** `models/marts/facts/_facts.yml:764` - **Confidence:** high The model has no surrogate key column and no `unique`/`not_null` tests at all in `_facts.yml`. Grain is "one row per device shipment" per the description, but `serial_number` carries no uniqueness guarantee in the test suite. Duplicate activations would not be detected — BI dashboards on this fact may double-count. **Suggested fix:** Add a surrogate key — e.g. `dbt_utils.generate_surrogate_key(['serial_number'])` aliased as `device_activation_id` — and add `unique` + `not_null` tests on it in `_facts.yml`. --- #### 2. `fact_sales_invoice_lines` has no PK with `unique`+`not_null` tests - **Dimension:** Gemma SQL style (also logic, target-structure, Kimball) - **Rule:** `primary-key-tests` / `logic-pk-test-missing` - **Location:** `models/marts/facts/_facts.yml:4` - **Confidence:** high The natural compound key `(model_source, sales_order_id, item_id)` is implicit but not declared as a PK. The only test is a conditional `not_null` on `sales_invoice_id` (which is itself an FK). Without a unique constraint, fan-out from the LEFT JOIN on invoice lines could silently multiply revenue rows. Logic agent rated this `major`; Gemma-style rated `critical`. Promoted to critical for the consolidated report — silent revenue duplication is the highest-impact possible failure mode. **Suggested fix:** Add `dbt_utils.generate_surrogate_key(['model_source', 'sales_order_id', 'item_id']) AS sales_order_line_id` in the SQL, then `unique` + `not_null` tests in `_facts.yml`. (Alternative: declare composite key via `dbt_utils.unique_combination_of_columns`.) --- ### Major (24) #### 3. Deprecated top-level `freshness:` on source tables — 87 tables across 8 YAML files - **Dimension:** logic - **Rule:** `logic-deprecated-source-freshness-property` - **Locations:** `_business_central.yml` (50 tables), `_stripe.yml` (18), `_firestore.yml` (11), `_freshdesk.yml` (3), `_google_ads.yml` (2), `_google_sheets.yml` (1), `_jira_wehunt.yml` (1), `_onesignal.yml` (1) - **Confidence:** high dbt 1.10+ deprecates `freshness:` as a top-level table key. It must move under `config: freshness:`. Will become a hard error in a future dbt version. 304 individual warnings emitted during `dbt parse`. **Suggested fix:** One pattern, repeated. Apply this transform across all 8 YAML files: ```yaml # from: - name: customers freshness: error_after: {count: 28, period: day} # to: - name: customers config: freshness: error_after: {count: 28, period: day} ``` --- #### 4. Customer natural key drift: `customer_no` vs `customer_number` vs `customer_id` across BC models - **Dimension:** naming - **Rule:** `naming-fk-column-name-drift` - **Location:** (cross-file) - **Confidence:** high The same Business Central customer natural key (BC "No." field) appears as `customer_no` (OData/custom tables), `customer_number` (REST API tables), and `customer_id` (surrogate UUID). `fact_sales_invoice_lines` even has an explicit JOIN bridging `customer_no = customer_number`. Same three-way drift exists for item, invoice, order, vendor, account. **Suggested fix:** Pick a project-wide convention at the staging layer. Recommendation: `_id` for surrogate UUIDs, `_number` for BC natural keys ("No."). Alias OData-sourced `_no` columns to `_number` at staging so downstream models see one form. Document in `dbt/CLAUDE.md`. --- #### 5. `fact_device_activations` exposes mixed FK suffixes in one model - **Dimension:** naming - **Rule:** `naming-fk-column-name-drift` - **Location:** `models/marts/facts/fact_device_activations.sql` - **Confidence:** high Within a single mart, the same conceptual entities use inconsistent forms: `shipment_no` vs `sales_shipment_number` (same shipment), `ic_service_item_no` + `il_service_item_no` (table-alias-prefixed `_no`), `cus_customer_no` alongside `customer_number`. **Suggested fix:** Normalize FK column names in the mart's final CTE. Strongly tied to finding #4. --- #### 6. Seven Firestore staging models `ref` another staging model instead of `source()` - **Dimension:** logic - **Rule:** `logic-staging-not-from-source` - **Locations:** `models/staging/firestore/staging__firestore_wehunt__users_{hidden_group_ids, country_history, phones, folders, shown_tutorials, hunting_role_types, activities}.sql` - **Confidence:** high These models unnest nested JSON arrays from the parent `staging__firestore_wehunt__users`. Staging-layer convention says staging is 1:1 with source tables and selects only from `source()`. These are effectively intermediate-layer transforms living in `staging/`. **Suggested fix:** Either (a) move to `models/intermediate/product/firestore/` with `intermediate_` prefix, or (b) document the exception explicitly in `dbt/CLAUDE.md` ("nested-array unnesting of a parent staging model is permitted in `staging/` when the result is still 1:1 with logical source records"). Option (b) is lighter touch. --- #### 7. `staging__sharepoint__product_mapping` wraps a seed, not a source - **Dimension:** logic - **Rule:** `logic-staging-not-from-source` (medium confidence) - **Location:** `models/staging/sharepoint/staging__sharepoint__product_mapping.sql` - **Confidence:** medium The model references `{{ ref('product-mapping') }}` (a seed), not `{{ source(...) }}`. Both `fact_sales_invoice_lines` and `fact_device_activations` use it. A staging model wrapping a seed breaks the staging-only-from-source contract. **Suggested fix:** (a) Remove the staging wrapper and ref the seed directly from intermediate/mart, or (b) rename to `intermediate__sharepoint__product_mapping` and move to `intermediate/`. Also: the seed filename `product-mapping.csv` uses a hyphen — should be `product_mapping.csv` (see info finding below). --- #### 8–11. Kimball: measure columns lack additivity annotations (4 facts) - **Dimension:** Kimball - **Rule:** `kimball-facts-not-flagged-additivity` - **Locations:** `_facts.yml` for `fact_sales_invoice_lines`, `fact_active_users_daily`, `fact_daily_marketing_costs`, `fact_device_activations` - **Confidence:** high None of the four facts annotate additivity. Notable cases: - `fact_sales_invoice_lines`: revenue in mixed currencies. `order_net_amount_including_tax` is only additive within a single `currency_code`; the EUR-converted columns are additive across currencies. Not stated. - `fact_daily_marketing_costs.reach`: **semi-additive** (cannot sum across dates — same user reached on two days ≠ 2 unique users). Bug bait. - `fact_active_users_daily.session_length_seconds`: semi-additive (cross-date sum double-counts). - `fact_device_activations.days_to_activation`: non-additive. **Suggested fix:** Add an "Additivity" line to each measure description. Format suggestion: `"Fully additive within . Use for cross- aggregation."` --- #### 12. `fact_device_activations` grain declaration is ambiguous - **Dimension:** Kimball - **Rule:** `kimball-grain-not-declared` - **Location:** `_facts.yml:764` - **Confidence:** medium Description says "one row per device shipment from Tracker OY, enriched with FTU activation data" — but it mixes two business processes (shipment + activation) without stating a primary grain. If a device has multiple shipments (re-deliveries, repairs), the grain is undefined. **Suggested fix:** Replace with explicit grain statement, e.g. "One row per device serial number from Tracker OY. Each serial number appears at most once. Enriched with the first FTU activation event observed for that serial." --- #### 13. `fact_daily_marketing_costs` PK is NULL for date-spine rows - **Dimension:** Kimball - **Rule:** `kimball-snapshot-fact-time-key` - **Location:** `_facts.yml:991` - **Confidence:** high `marketing_cost_id` is NULL on dates with no ad activity (from the date spine). `unique` and `not_null` tests are gated on `platform IS NOT NULL`, so date-spine rows are untested. Periodic-snapshot facts should have a row-level identity at every grain point. **Suggested fix:** Either (a) drop date-spine rows with no ad activity from the final SELECT, or (b) compute the surrogate key even for empty rows (hash `report_date + 'no_data'`) and drop the conditional test. --- #### 14. `marts/reports/` folder exists but is undocumented - **Dimension:** target structure (promoted from minor to major because of compilation risk) - **Rule:** `target-structure-folder-undocumented` - **Location:** `models/marts/reports/.gitkeep` - **Confidence:** high `dbt/CLAUDE.md` defines exactly three layers and within `marts/` only `dims/` and `facts/`. A third subfolder `reports/` was created (empty placeholder). Currently harmless, but the `generate_schema_name` macro would fail for models added here unless `dbt_project.yml` covers the path (currently set to `+schema: undefined` for unconfigured paths → models would error at compile time). **Suggested fix:** Either (a) document the layer in `dbt/CLAUDE.md` + add `reports: {+schema: reports}` in `dbt_project.yml`, or (b) remove the empty folder if it was created in error. --- #### 15. Domain tags (marketing/product/finance) missing from all tests - **Dimension:** target structure - **Rule:** `target-structure-testing-rule-violation` - **Location:** project-wide - **Confidence:** high `docs/testing-strategy.md` says "Tags by domain (marketing, product, finance) on all tests for subset execution." No test or model carries a domain tag. Layer tags (`staging`, `intermediate`, `marts`) exist in `dbt_project.yml` but those aren't domain. **Suggested fix:** Add `+tags` per source/domain subfolder in `dbt_project.yml`, or remove the unimplemented item from `testing-strategy.md`. Pragmatic: punt to a future ticket since none of the current dashboards depend on domain-scoped test runs. --- #### 16. `ci-entrypoint.sh` invokes bare `python3` instead of `uv run python` - **Dimension:** CLAUDE.md - **Rule:** `claudemd-uv-only-violation` - **Location:** `dbt/ci-entrypoint.sh:22` - **Confidence:** high Line 22 runs `python3 -c "..."` to download a GCS manifest. Resolves to venv Python at runtime (PATH is set in the Dockerfile), but violates the explicit "never bare `python3`" rule in CLAUDE.md. **Suggested fix:** `uv run python -c "..."` — or extract into a helper script invoked via `uv run python download_manifest.py`. --- #### 17–24. Cross-agent duplicates resolved upstream Eight additional "major" findings overlap with critical findings #1–#2 (PK tests on the two facts) under different rule IDs: - `kimball-dim-surrogate-key` on `fact_sales_invoice_lines` and `fact_device_activations` - `target-structure-testing-rule-violation` on the same two facts - (logic and gemma-style angles already consolidated into the critical entries) Resolved: addressing critical #1 and #2 closes these eight related findings. --- ### Minor (26) — grouped by theme **SQL style nits (Gemma SQL style, 13 findings):** | Rule | Files | Fix | |---|---|---| | `keywords-uppercase` | `staging__facebook_ads__tracker.sql:20`, `staging__facebook_ads__wehunt.sql:20`, `fact_nps.sql:15` | `as` → `AS`; `false`/`true` → `FALSE`/`TRUE` | | `explicit-aliases` | `intermediate_fx_rates.sql:29-30`, `staging__google_sheets__fx_rates_input_sheet.sql:15`, `dim_dates.sql:24` | Add `AS` keyword on all table/column aliases | | `meaningful-table-aliases` | `fact_sales_invoice_lines.sql:63`, `fact_device_activations.sql:80,87`, `fact_daily_marketing_costs.sql:125` | Replace single-letter aliases (`i`, `c`, `s`, `d`) with business-meaningful names | | `line-length-88` | `fact_daily_marketing_costs.sql:96` (131 chars), `intermediate_facebook_ads_insights.sql:69,86` | Wrap `generate_surrogate_key` arguments across lines | | `no-select-star` | `fact_active_users_daily.sql:29`, `fact_device_activations.sql:145` | Enumerate columns in final SELECT | | `derived-column-description` | `_google_sheets.yml:31` | Uncomment the commented-out `description:` line on `date` | | `timestamp-suffix` (project-specific) | ~24 BC staging files | Wrap `CAST(... AS TIMESTAMP)` in `DATETIME(..., 'Europe/Berlin')` for consistency with non-BC staging | | Cross-layer skip (downgraded) | `fact_device_activations`, `fact_sales_invoice_lines`, `fact_active_users_daily` | Extract join logic into intermediate models | **Test/deprecation hygiene (logic, 4 findings):** - `logic-deprecated-test-arguments` in `_business_central.yml` (7), `_google_ads.yml` (4), `_jira_wehunt.yml` (1) — 12 generic-test invocations need `arguments:` wrapper. Pattern: ```yaml # from - dbt_utils.unique_combination_of_columns: combination_of_columns: [col_a, col_b] # to - dbt_utils.unique_combination_of_columns: arguments: combination_of_columns: [col_a, col_b] ``` - `logic-dead-model` — 111 staging models with no downstream `ref`. Almost entirely the pre-built stripe / jira / freshdesk / firestore / revenuecat / onesignal scaffolding for P2/P3 sources. Fix is low priority; tag with `enabled: false` per source-subdir, or document explicitly as "scaffolding" in `dbt/CLAUDE.md`. **Naming consistency (2 findings):** - `naming-date-suffix-mixed` — `_date` (7×), `_on` (3×), bare `date` (5×) coexist. Coding standard permits both `_date` and `_on`, so this is a "pick one going forward" item, not a bug. - `naming-boolean-prefix-mixed` — `blocked` (STRING, no `is_` prefix) in `item_cards`, `gl_account_card`, `vendors` because BC `blocked` is an enum, not a boolean. Rename to `blocked_type` or `blocked_reason`. **Target structure (4 findings):** - `target-structure-naming-violation` — `testing-strategy.md` uses `stg_`/`int_`/`fct_` prefixes in its examples; the repo uses `staging__`/`intermediate_`/`fact_`. Fix: update the doc. - `target-structure-config-not-inline` — 142/143 models have no inline `{{ config(...) }}`. CLAUDE.md says "always set config inline" but `dbt_project.yml` defaults cover almost everything. **Recommend:** loosen the CLAUDE.md rule to "set inline only when overriding project defaults". **Kimball (2 findings):** - `kimball-dimensions-unclear` on `fact_sales_invoice_lines` (no FK to `dim_dates` for `order_date`/`invoice_date`) and `fact_active_users_daily` (no `date_id` FK to `dim_dates`). - `kimball-semi-additive-not-flagged` on `fact_daily_marketing_costs.reach` (already covered under findings #8–11). --- ### Info (10) 1. **Gemma**: `intermediate_fx_rates.sql` missing trailing newline (low confidence). 2. **Gemma**: surrogate-key description in `_marketing.yml:13` could be tighter but is adequate. 3. **Gemma**: `_onesignal.yml:93` has `description: "TODO: add description"` placeholder on `version` column. 4. **Gemma**: `_google_ads.yml:43` — `google_ads_wehunt` source tables lack freshness blocks (inconsistent with `google_ads` source). 5. **Gemma**: `staging__sharepoint__product_mapping.sql` refs seed named `product-mapping` (hyphen) — should be `product_mapping`. (Tied to major finding #7.) 6. **Naming**: `dim_dates` is the only dim, so pluralization rule cannot be inferred. Decide before adding more dims. 7. **Target structure**: `intermediate_fx_rates.sql` lives at intermediate/ root, not in a domain subfolder like `marketing/` or `product/`. Consider `finance/` or `shared/`. 8. **Kimball**: `kimball-vs-style-conflict` — `dim_dates` is plural per Gemma style; Kimball convention prefers singular. Document the project's choice in `dbt/CLAUDE.md`. 9. **Kimball**: `dim_dates.date_id` uses YYYYMMDD smart-key (not `_key` suffix). Functionally fine for date dims — well-established pattern. No action. 10. **Kimball**: `fact_sales_invoice_lines` has 4 low-cardinality flag/status columns (`is_retailer`, `is_fully_shipped`, `order_status`, `customer_type`) — junk-dim candidate if more flags are added. --- ## Open questions 1. **Target-structure doc:** No file matched the skill's search under `docs/`. Should `dbt/docs/data-model.md` be created (describing the three layers + sub-folder conventions explicitly), or is `dbt/CLAUDE.md` the canonical source by design? 2. **`marts/reports/` folder:** Was this created on purpose for a future fourth mart sub-layer, or should it be removed? 3. **Domain tags in tests:** Is `testing-strategy.md`'s "tags by domain (marketing, product, finance)" still on the roadmap, or should it be removed from the doc until needed? 4. **Dim pluralization:** `dim_dates` is the only example. Lock in plural (Gemma SQL style — current path) or switch to Kimball-singular (`dim_date`)? 5. **`generate_schema_name` and `+schema: undefined`:** Models in unconfigured folders (e.g. `marts/reports/`) would fail compile. Is this intentional as a tripwire, or should `+schema: undefined` raise more clearly? --- ## Skipped checks - `target-structure-doc-search` — no file matched under `dbt/docs/`. Substituted `dbt/CLAUDE.md` + `dbt/docs/testing-strategy.md`. - `claudemd-no-secrets-in-profiles-yml` — file content read blocked by data-protection policy. Recommend a CI secrets-scanner (e.g. `gitleaks`) to enforce. - Several CLAUDE.md directives not statically checkable (collaboration style, conventional commits, "always pull main before work"). Recorded per-rule in the sub-agent's JSON. - `kimball-reporting-skips-analytics-layer` — suppressed because project has no `reporting/` layer by design (marts plays both roles). - `kimball-scd2-required-columns`, `kimball-bridge-table-needed`, `kimball-role-playing-dim-not-aliased`, `kimball-degenerate-dimension-as-fk`, `kimball-multiple-date-dims`, `kimball-conformed-mismatched-cols` — N/A given the current model inventory. - `logic-circular-dependency` — no cycles found. - `logic-suspicious-join` — only non-LEFT joins are `CROSS JOIN UNNEST` for JSON arrays, which is expected. - `logic-non-staging-from-source` — clean. --- ## Suggested next actions Ordered by impact / effort: 1. **Add PK tests to `fact_device_activations` and `fact_sales_invoice_lines`** — closes 8 findings across 4 dimensions, including the 2 critical ones. Generate surrogate keys and add `unique`+`not_null`. ETA: ~1h. 2. **Migrate `freshness:` → `config: freshness:` across 8 source YAMLs (87 tables)** — closes 8 major findings and 304 dbt-parse warnings. One sed/script pass. Pre-empts a future hard error. 3. **Wrap test arguments under `arguments:` in 3 YAMLs (12 occurrences)** — small but eliminates the remaining 12 dbt-parse deprecations. 4. **Normalize BC customer / item / invoice / order / vendor / account naming at staging boundary** — resolves naming findings #4 (multi-file FK drift) and #5 (mixed FK suffixes in `fact_device_activations`). Document the chosen convention in `dbt/CLAUDE.md`. ETA: ~3h including renames in downstream models + tests. 5. **Add additivity annotations to all measure columns on the 4 facts** — closes 4 major Kimball findings and prevents real BI bugs on `reach`, `session_length_seconds`, `days_to_activation`. ETA: ~1h. 6. **Decide on the small stuff (and document):** dim pluralization, `marts/reports/` purpose, target-structure doc path, domain-tag scope. ETA: 30 min discussion + 30 min doc updates. --- ## Methodology notes - Six validator sub-agents ran in parallel (Sonnet 4.6, general-purpose role) with strict read-only constraints. Total wall-time ~7.5 min; ~460k tokens combined. - The orchestrator (this report) performed Phase A (discovery + `dbt parse` + override summary), dispatched Phase B (parallel validation), and consolidated Phase C (de-duplication + sort + render). - Two project-specific overrides were applied: - **Layer naming:** `staging/` / `intermediate/` / `marts/` per `dbt/CLAUDE.md`, not the generic Gemma `base/` / `interim/` / `analytics/` / `reporting/`. Rules `base-model-naming`, `dbt-folder-structure`, `base-only-from-source`, `base-minimal-transformations`, `no-cross-layer-skip` were re-interpreted accordingly. - **Target-structure doc substitution:** `dbt/CLAUDE.md` + `dbt/docs/testing-strategy.md` used in lieu of a dedicated architecture doc. - `dbt show` and any data-returning command were prohibited per the Gemma data-protection policy. `dbt parse` (no data return) was the only dbt invocation used.