Second Brain

cortex-analyst-392700-wrapper-view-pattern-validator

2 min read

Cortex Analyst 392700: wrapper-view pattern + validator

Cortex Analyst semantic-model validator rejects column names outside ^[A-Z][A-Z0-9_]*$ — ALWAYS use wrapper views

Discovered: 2026-05-08, RNWL_SALES_DASHBOARDS / Sales+CRM Cortex Analyst alignment session.

Pattern: Snowflake DDL accepts double-quoted column names with spaces, hyphens, mixed case (e.g. "Opp ID", "Flag", "Name-2"). Cortex Analyst’s semantic-model parser rejects ALL of them with error_code: 392700, error_message: 'invalid column name "X": name must start with an underscore or a letter, and only contain letters, underscores, decimal digits (0-9), and dollar signs ($)'. The error blocks the ENTIRE semantic model — even metrics that don’t reference the offending column won’t validate. Burned 5 deployment cycles of debug to discover this.

Fix path (mandatory for any semantic view bound to upstream views with non-clean columns):

  1. Create wrapper view in YOUR target schema (<DB>.<SCHEMA>.<VIEW>_C convention): 
    CREATE OR REPLACE VIEW <YOUR_DB>.<YOUR_SCHEMA>.<UPSTREAM_NAME>_C AS
SELECT * EXCLUDE ("Opp ID", "Flag", "Date", "Name-2", ...),
  "Opp ID" AS OPP_ID, "Flag" AS FLAG, "Date" AS DATE, "Name-2" AS NAME_2, ...
FROM <UPSTREAM_DB>.<UPSTREAM_SCHEMA>.<UPSTREAM_NAME>;
  2. Bind the semantic view to <UPSTREAM_NAME>_C (not the upstream view directly)
  3. Reference clean aliases (OPP_ID, FLAG) in metric expressions / dimensions / facts / PK
  4. Suffix _X for collision avoidance (e.g. "Year Opp" AS YEAR_OPP_X if YEAR_OPP exists upstream)
  5. Run mcp__snowflake-mcp__validate_semantic_view_for_cortex(ddl) BEFORE CREATE OR REPLACE — pure-regex validator, sub-50ms, catches all 5 blocking patterns

Codified at:

  • ~/.claude/skills/snowflake-report-mart/SKILL.md v1.1.0 — Hard Rules 6/7/8 + § Cortex Analyst formatting rules (auto-loaded for every report-mart workflow)
  • snowflake-mcp v22.0.9 — validate_semantic_view_for_cortex MCP tool

Companion gotchas (same Cortex Analyst parser strictness):

  • CREATE OR REPLACE SEMANTIC VIEW <NAME> (unqualified) silently lands in CURRENT_SCHEMA (often PUBLIC), reports “successfully created” while the original view is untouched. Always fully-qualify: CREATE OR REPLACE SEMANTIC VIEW <DB>.<SCHEMA>.<NAME>.
  • SUM(CASE WHEN ... THEN col END) value-bound predicates get silently stripped by Snowflake’s normalizer. Use SUM(IFF(... AND col < CAP, col, 0)) for sanity caps. (Different normalization paths for logical-table-qualified metrics vs single-alias metrics.)

Reach: any Snowflake Cortex Analyst work, any division. Future RD/EA/DEV agents working on report marts will hit this if they ignore the skill.

Graph View

Backlinks