Second Brain

funnel-scope-canonical-exclusion-framework-config-table-cano

2 min read

Funnel-scope canonical-exclusion framework — config table + canonical view + wrapper filter

Funnel-scope canonical-exclusion framework

Problem

Cortex Analyst returned FY 25-26 gross sales = ₹2,357 Cr while Tableau showed ₹2,573.9 Cr. Root cause was funnel-scope drift: Tableau’s “Gross Sales” KPI queries RNWL_RAW.RNWL_SALESFORCE_QUICKSTART.OPPORTUNITY directly and applies 4 categorical exclusions absent from the semantic view’s logical-table backing views.

The 4 exclusions:

  1. PROJECT_NAME_C NOT LIKE '%MHADA%' — affordable housing reported separately
  2. PROJECT_NAME_C <> 'Runwal Garden City' — TBD per Sales BI
  3. RW_REASON_FOR_CANCELLATION_C <> 'Unit Transfer' — inventory reassignment, not real cancellation
  4. SALES_ORDER_NUMBER_C NOT IN (<23 IDs>) — opaque blacklist (governance smell)

Anti-pattern

Inline-hardcode the 4 filters into all 48 funnel metric formulas across 7 logical tables. High blast radius, fragile, opaque blacklist hidden inside metric expressions, no audit trail, no toggle-without-DDL.

Pattern (Hard Rule 10 in snowflake-report-mart)

Three components:

1. Governance config table

CREATE OR REPLACE TABLE <DB>.<SCHEMA>.SALES_FUNNEL_EXCLUSIONS_CONFIG (
  exclusion_id    NUMBER AUTOINCREMENT PRIMARY KEY,
  exclusion_type  VARCHAR(40),  -- PROJECT_NAME_LIKE | PROJECT_NAME_EQUAL | CANCELLATION_REASON | SALES_ORDER_NUMBER
  value           VARCHAR(200),
  reason          VARCHAR(500),
  owner           VARCHAR(100),
  effective_from  DATE,
  active          BOOLEAN
)

Toggle rules without DDL. Full audit trail. Per-rule reason + owner.

2. FY-generic canonical-scope view

CREATE OR REPLACE VIEW <DB>.<SCHEMA>.VW_<DOMAIN>_CANONICAL_OPPS_C AS
SELECT o.ID AS OPP_ID
FROM RAW_OPPORTUNITY o
WHERE NOT EXISTS (SELECT 1 FROM cfg WHERE active AND type='PROJECT_NAME_LIKE' AND o.proj LIKE cfg.value)
  AND NOT EXISTS (SELECT 1 FROM cfg WHERE active AND type='PROJECT_NAME_EQUAL' AND o.proj = cfg.value)
  AND ...

Reads filter values dynamically from config. NO date hardcode → every FY inherits automatically.

3. Each _C wrapper inherits via WHERE filter

CREATE OR REPLACE VIEW <_C wrapper> AS
WITH base AS (<original wrapper SELECT>)
SELECT * FROM base
WHERE OPP_ID IS NULL
   OR OPP_ID IN (SELECT OPP_ID FROM VW_<DOMAIN>_CANONICAL_OPPS_C)

Funnel-Opp rows filtered; non-Opp rows (FLAG=Target/Spend with NULL OPP_ID) pass through. All downstream funnel metrics auto-inherit.

Live result (2026-05-09)

  • 26 rules govern Tableau-canonical scope
  • 10 wrappers updated in single op
  • 48 funnel metrics auto-inherit
  • FY 25-26 gross = ₹2,573.9 Cr / 2,016 units → byte-exact Tableau parity
  • FY 24-25 generalizes to ₹2,726.5 Cr without code change
  • INVENTORY/ORM/Target metrics regression-safe (NULL OPP_ID pass-through)

When to apply

Any time a Cortex Analyst (or downstream BI) needs to reconcile with a Tableau/Power BI dashboard whose “official” KPI definition applies categorical exclusions. The framework generalizes to any funnel domain (sales, marketing, support).

Reference

  • ~/.claude/skills/snowflake-report-mart/SKILL.md v1.1.0 § Hard Rule 10
  • RNWL_PROD.RNWL_SALES.SALES_FUNNEL_EXCLUSIONS_CONFIG
  • RNWL_PROD.RNWL_SALES.VW_SALES_CANONICAL_OPPS_C
  • Session 2026-05-09

Graph View

Backlinks