Mixed-grain tables: diagnose and fix wrong agent answers with a GRAIN dimension and verified queries¶

An agent double-counts a table that holds daily and monthly rows. Diagnose it from wrong answers, fix the semantic view with a GRAIN enum, rules, and verified queries, re-test.

This recipe is a reference implementation: review the code and adapt it to your environment before production use.

Many summary tables store more than one grain side by side: daily rows next to monthly roll-ups, districts next to their region totals. Point a semantic view at one of these without saying so, and a Cortex Agent returns fluent, confident answers that are exactly twice the truth. This recipe reproduces that failure on a small fictional dataset, scores the agent on four questions (it gets 0 or 1 right), traces the wrong numbers to the SQL it wrote, and fixes the semantic view, not the agent and not the data, until the same agent gets all four right.

When to use this¶

• An agent or Cortex Analyst answer is off by a clean multiple (2x, 3x), or an average is skewed high.
• A table mixes row types (daily and monthly, detail and subtotal, account and account group) and the rows share keys and dates.
• You want a repeatable before and after test for a semantic view change instead of spot-checking in a chat.

When not to use it:

• You can change the data model. Splitting the grains into separate tables (or a view per grain) removes the trap entirely; do that when you can.
• The wrong numbers come from joins (fan traps, multiple paths). That is a relationship problem; see the sv_diagnostics snippet under Related skills.

How it works¶

• The trap. STORE_SALES_SUMMARY has 360 daily rows and 12 monthly rows that each equal the sum of their month. Only PERIOD_START = PERIOD_END tells them apart. A first-draft semantic view that calls PERIOD_START “sale date” invites SUM(net_sales) WHERE sale_date BETWEEN ..., which adds every sale twice.
• Test through the agent, score against the data. Expected answers are computed from the daily rows in setup. The harness asks the agent each question with DATA_AGENT_RUN, stores the answer and the SQL the agent wrote, and scores it: numbers within 0.5 percent, or an AI_COMPLETE judge for the “which stores” question.
• Fix the semantic view only. A GRAIN dimension with IS_ENUM names the row types, grain rules in AI_SQL_GENERATION say which grain answers which kind of question, and a few verified queries show the right shape. The agent spec is never touched.
• Verified queries that differ from the test. The verified queries use other stores and months than the test questions, so a passing re-test shows the rules generalize rather than that a stored query was replayed.
• CREATE OR ALTER keeps grants. The fix is applied in place; the analyst role’s SELECT grant on the view is still there afterwards.

Prerequisites¶

Quickstart¶

Run in order. Each script sets its own role, warehouse, and database, so separate snow sql -f calls work:

1. sql/01_setup.sql: roles, warehouse, database, the mixed-grain table, and the test questions.
2. sql/02_semantic_view_before.sql: the first-draft semantic view and an analyst grant.
3. sql/03_agent_and_test_harness.sql: the agent and RUN_AGENT_TESTS.
4. sql/04_test_before.sql: score the agent (a few minutes).
5. sql/05_diagnose.sql: find the cause.
6. sql/06_fix_semantic_view.sql: apply the fix with CREATE OR ALTER.
7. sql/07_test_after.sql: re-score the same agent and compare.
8. sql/99_teardown.sql.

Walkthrough¶

1. The questions and the right answers¶

2. The symptom¶

Against the first-draft view, two test runs scored 1 of 4 and 0 of 4. Q1 and Q2 were wrong the same way both times; Q3 is from the first run and Q4 from the second:

Nothing in the answers signals a problem. Q4 is the most damaging: doubled sales against single-counted targets make every store look like it beat plan. In the first run the agent noticed an empty result on Q4, investigated, and got it right, which is why a scored test beats a single spot check.

3. The diagnosis¶

05_diagnose.sql goes from symptom to cause. The ratios above are the first clue: a SUM that is off by exactly 2.00 is two grains added together. The SQL the agent wrote confirms it, with a date filter and no grain filter:

SELECT SUM(net_sales) AS total_net_sales
FROM __sales
WHERE store_name ILIKE '%Harbor Street%'
  AND sale_date >= '2026-02-01' AND sale_date < '2026-03-01'

And the table itself shows the two grains, each summing to the full total:

4. The fix¶

06_fix_semantic_view.sql replaces the definition in place. The parts that matter for grain:

sales.grain AS IFF(PERIOD_START = PERIOD_END, 'DAILY', 'MONTHLY')
  COMMENT = 'Row type. DAILY = one store, one day. MONTHLY = one store, one calendar month, equal to the sum
             of that month''s DAILY rows, and the only rows with SALES_TARGET. Every query must filter to exactly one GRAIN.'
  SAMPLE_VALUES ('DAILY', 'MONTHLY')
  IS_ENUM,
...
AI_SQL_GENERATION
  '... Every query must filter to exactly one grain; never aggregate across both. Totals for whole calendar months,
   quarters, or years: use MONTHLY rows. Anything about targets: use MONTHLY rows. Daily figures, averages per day,
   or partial-month ranges: use DAILY rows. ...'
AI_VERIFIED_QUERIES (
  vq_store_month_total AS (
    QUESTION 'What were total net sales for Mill Road in January 2026?'
    VERIFIED_BY '(STEWARD = retail_analytics)'
    SQL 'SELECT store_name, period_start AS month, net_sales FROM sales
          WHERE grain = ''MONTHLY'' AND store_name = ''Mill Road'' AND period_start = ''2026-01-01''' ),
  ...
)

Also in the fix: the misleading sale_date name becomes period_start plus period_end, every fact and metric comment states which grain it is valid in, and store and region get IS_ENUM sample values. VERIFIED_BY names a team contact, not a person. Synonyms are limited to the two terms users actually use for the grain.

5. The re-test¶

The same agent, the same four questions, both test runs: 4 of 4. The agent’s SQL now filters on grain every time, and picks the grain the rules call for:

-- Q2: quarter total from monthly rows
SELECT SUM(net_sales) FROM __sales WHERE grain = 'MONTHLY' AND period_start BETWEEN '2026-01-01' AND '2026-03-01'
-- Q3: daily average from daily rows
SELECT AVG(net_sales) FROM __sales WHERE grain = 'DAILY' AND store_name = 'Canal Park' AND period_start BETWEEN '2026-01-01' AND '2026-01-31'
-- Q4: target comparison on the monthly row
SELECT store_name, net_sales, sales_target FROM __sales WHERE grain = 'MONTHLY' AND period_start = '2026-03-01' AND net_sales < sales_target

None of the test questions matched a verified query word for word.

Verify¶

07_test_after.sql ends with a summary:

and the side-by-side table shows each answer and the agent’s SQL. Also check that the grant survived:

SHOW GRANTS ON SEMANTIC VIEW COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES.RETAIL.STORE_SALES_SV;
-- SELECT ... COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES_ANALYST

Agent answers vary between runs, so treat a single run as a sample. The “after” result was 4 of 4 in every run while this recipe was built.

Gotchas¶

Teardown¶

-- sql/99_teardown.sql
DROP DATABASE IF EXISTS COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES;     -- agent, semantic view, tables, procedure
DROP WAREHOUSE IF EXISTS COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES_WH;
DROP ROLE IF EXISTS COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES_ANALYST;
DROP ROLE IF EXISTS COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES_ROLE;

Adapt this recipe¶

Keep these, so a customized version keeps the recipe’s security model:

• Least privilege: ACCOUNTADMIN only for the scripts listed under Privileges; everything else runs as the recipe’s own role.
• No grants to PUBLIC.
• Caller’s rights: procedures run as EXECUTE AS CALLER, so each user’s own grants apply.
• Credentials: create keys, tokens, and secrets yourself, in a separate terminal, and read them from environment variables. Never put them in a script or give them to an agent.
• Dynamic SQL, if you add any, uses bind variables or an allowlist of identifiers, never text concatenated from user or model input.

Change these to fit your use case:

• Object names. Rename them in every script, including teardown, then check that teardown still removes everything.
• Roles, personas, and users: map them to your own, each granted no more than the recipe grants.
• Data: point the scripts at your tables instead of the sample data, then run Verify again.
• Models and warehouse sizes.

To have a coding agent adapt it:

Adapt the mixed-grain-and-verified-queries recipe in mixed-grain-and-verified-queries/ (unzipped from mixed-grain-and-verified-queries.zip) to <describe your use case>.
Read README.md first. Keep everything the "Adapt this recipe" section says to keep.
Change only names, roles and personas, data, and models.
Show me each changed file before you run anything, then run the recipe's Verify steps.

Run it with a coding agent¶

Install or enable the semantic-view-patterns skill from Snowflake-Labs/coco-skills, and the bundled agent-studio skill.

Run the recipe in mixed-grain-and-verified-queries/ on my current Snowflake connection.
1. Confirm the account and role with SELECT CURRENT_ACCOUNT(), CURRENT_ROLE() and show me before continuing.
2. Show me the ACCOUNTADMIN statements at the top of sql/01_setup.sql (two roles, a warehouse, a database, and
   their grants) and ask before running them. Then run sql/01 through sql/04 in order. Report the before score and
   each wrong answer.
3. Run sql/05_diagnose.sql and explain the cause in two sentences using its output.
4. Run sql/06 and sql/07. Report the after score and show the agent SQL for each question.
5. Then apply the same approach to one of my semantic views: use semantic-view-patterns in apply mode with the
   ai_metadata and sv_diagnostics snippets to check it for mixed grains, and propose a GRAIN dimension, rules, and
   verified queries as a diff. Do not deploy without asking.
6. Ask me before running sql/99_teardown.sql. Only drop objects named COOKBOOK_MIXED_GRAIN_AND_VERIFIED_QUERIES*.

Related skills¶

• semantic-view-patterns (coco-skills): the ai_metadata snippet teaches AI_SQL_GENERATION, AI_QUESTION_CATEGORIZATION, and AI_VERIFIED_QUERIES syntax; the sv_diagnostics snippet covers other ways semantic views produce wrong numbers (fan traps, multiple paths). Apply mode adapts either to your own view.
• agent-studio (bundled): creating, auditing, and optimizing semantic views, verified queries, and agents.

References¶

• Using SQL commands to create and manage semantic views
• CREATE SEMANTIC VIEW
• Best practices for modeling semantic views
• Optimize an existing semantic view or model with verified queries
• Create and manage agents