Before You Model a Single Table: Data Modelling Questions to Ask for AI Agents in Snowflake

A Principal Architect’s guide to structuring tables, semantic views and business context so agents generate reliable SQL

Every few weeks someone asks me the same thing: “We want a Cortex Agent on top of our data. How should we model it?” The honest answer is that most of the questions behind that sentence are not about the agent. They are modelling questions we should have answered years ago, and an agent now exposes them in public.

A business analyst carries a lot of tacit knowledge: which date really means “sale”, which orders are cancelled, what “revenue” means for Finance versus Sales. An agent has none of that. It reads your schema, infers, and writes SQL. Every ambiguity becomes an inference, and every inference is a chance for a wrong join, filter or aggregation. In this article we will walk through the six questions I ask teams before they start modelling: grain, shape, semantic views, business meaning, performance and evaluation. We will close with a checklist you can take to your next design session.

Index

• 1. Why agents change the rules
• 2. Question 1: What is the grain, and does every column mean one thing?
• 3. Question 2: Star, snowflake or wide table, and how many joins are too many?
• 4. Question 3: How big should a Semantic View be?
• 5. Question 4: Who owns the meaning of a metric?
• 6. Question 5: Does physical performance still matter?
• 7. Question 6: How will we know it works?
• The Big Picture
• Conclusions
• References
• About Me

1. Why agents change the rules

The first thing I explain is that an agent solution is not one model; it is a stack of layers, each with a different job. Confusing them is the root of most disappointing proofs of concept.

• Curated physical model: tables, views, facts, dimensions, types, keys and quality tests. It represents the data faithfully and consistently.
• Semantic View: the executable semantic layer. It describes how to query a domain and how to calculate its metrics: logical tables, relationships, dimensions, facts, metrics, filters and descriptions.
• Business Ontology: the governed meaning of concepts (terms, metrics, entities) across models.
• Cortex Sense: AI-derived context, useful where data is not fully modelled yet.
• Cortex Agent: the orchestrator. It uses Cortex Analyst for structured data, Cortex Search for unstructured content and any other configured tools, and combines the results.

Key idea: none of these layers fixes physically incorrect data. Answer quality comes from four things together: correct grain and relationships, explicit semantic definitions, governed business context and systematic testing. A good design reduces the number of inferences the language model has to make. That is the guiding principle for everything that follows.

The five layers: each one has a different responsibility, and none replaces the layer below.

[↑Up]

2. Question 1: What is the grain, and does every column mean one thing?

The grain

Every fact table needs a grain written as a verifiable sentence: “one row per order line”, “one row per product, warehouse and day”. If the team cannot write it, the model is not ready.

• Do not mix grains: avoid headers and lines, detail and subtotals, or transactions and snapshots in the same consumption table.
• Separate current from history: with SCD Type 2, expose the current state and the full history through explicit views. Otherwise a query will happily count several versions of the same customer.
• Test uniqueness: on standard Snowflake tables, PRIMARY KEY and UNIQUE constraints are not enforced. Quality tests are still your responsibility.

Columns, additivity and dates

• Business names: net_amount_eur and invoice_date beat amt2 and dt.
• One meaning per column: state the unit and the rules (“net amount after discounts, in EUR”). Say what a NULL means: unknown, not applicable or anonymised.
• Codes with descriptions: expose status_code next to status_description.
• Flatten semi-structured data in the consumption layer: keep VARIANT in raw, expose typed columns for what matters.
• Hide technical columns: load_id, hash_row and etl_batch_id have no analytical value and only add noise.
• Know the additivity: a balance or a stock level is semi-additive and needs “last value of the period”, not a SUM. Distinct counts and historical metrics need specific logic too.
• Name every date: order, invoice, shipment, delivery and posting dates are different things. Put fiscal periods, weeks and holidays in a calendar dimension or an explicit semantic definition.

People always ask how many columns a table should have. There is no universal number. Relevance, clear descriptions and the absence of redundant or confusable attributes matter far more than any fixed limit.

[↑Up]

3. Question 2: Star, snowflake or wide table, and how many joins are too many?

Choosing the pattern

• Star schema: my default starting point. Clear paths and joins that are easy to describe.
• Conformed dimensions: the right way to compare several processes (sales and returns, say) without joining transactional facts directly.
• Wide table: fine for a bounded case, provided there is a single clear grain and you control duplication.
• Snowflake / 3NF / Data Vault: more joins and more possible paths. Keep it underneath, and expose a simplified logical layer on top for better accuracy and performance responses.

Relationships that leave no room for doubt

• Declare them explicitly: never rely on similar column names. Cortex Agents will not join tables unless the relationship is defined.
• Watch for fan-out: especially between tables of different grain.
• Many-to-many: resolve it with an explicit bridge table and document how measures are distributed. Snowflake’s guidance is to use a shared bridge table rather than a direct M:N relationship.
• Role-playing dimensions: order date and ship date are two roles of the same calendar. Model each role separately.
• Avoid fact-to-fact joins: unless grain and cardinality are fully controlled. Shared dimensions and purpose-built metrics are safer.

So, how many joins are too many?

There is no magic number. What matters is cardinality, referential integrity, alternative paths, optional dimensions and grain compatibility. One simple join with the wrong relationship is far more dangerous than five well-defined ones. The goal is not to minimise joins at any price, but to offer an unambiguous query path that supports the metrics you want to compute. Weigh simplicity against the functionality your data product needs.

The same logic applies to aggregates. They can cut latency and cost, and can encapsulate complex calculations such as balance snapshots. Give them an explicit grain in the name (sales_monthly_by_region), validate additivity before reusing them, and keep physical optimisation below a stable Semantic View so the agent never has to choose between detail and aggregate tables.

[↑Up]

4. Question 3: How big should a Semantic View be?

Smaller than you think. Snowflake’s own modelling guidance suggests starting with a limited scope and a simple star schema, with roughly 5 to 10 tables for an initial proof of concept. That is a starting reference, not a ceiling. It also recommends keeping the semantic context under about 100,000 tokens, because oversized views add latency and can lead to context being pruned.

Scope and organisation

• One domain or use case per view: sales performance, finance, inventory, customer service. A single view for the whole company adds ambiguity and latency.
• Curated objects only: do not point the agent at raw or staging schemas.
• Cross-domain questions: build an integrated layer or a dedicated view for them.

What gives accuracy

• Descriptions: they have the biggest impact. Describe business meaning and important restrictions for every table, column, fact and metric.
• Synonyms: use sparingly, for real corporate vocabulary and tested alternative names.
• Metrics and filters: fix the logic once. “Net sales” must state whether cancelled orders, returns and taxes are in or out, and be validated. A filter such as “valid order” must not depend on the agent guessing from a previous question.
• Verified Queries: reviewed question and SQL pairs for frequent, ambiguous or complex questions. They help consistency, but they do not replace a correct model or continuous evaluation.

Creating and managing

Semantic View Autopilot is a great accelerator for the first draft, from tables, sample queries or imported BI models, but always review relationships, metrics and descriptions. For anything that goes to production, I manage Semantic Views as code with SQL DDL or YAML, using native Semantic View objects rather than the legacy stage-based YAML models. A minimal sketch (Illustrative only):

CREATE OR REPLACE SEMANTIC VIEW sales_sv
  TABLES (
    order_lines AS analytics.curated.fct_order_line
      PRIMARY KEY (order_line_id)
      COMMENT = 'One row per order line',
    customers AS analytics.curated.dim_customer
      PRIMARY KEY (customer_id)
      COMMENT = 'One row per current customer'
  )
  RELATIONSHIPS (
    lines_to_customers AS order_lines (customer_id) REFERENCES customers
  )
  FACTS (
    order_lines.valid_net_amount_eur AS IFF(COALESCE(order_status, '') <> 'CANCELLED', net_amount_eur, 0)
      COMMENT = 'Net amount after discounts, EUR, cancelled orders excluded'
  )
  DIMENSIONS (
    customers.segment AS segment_name
      COMMENT = 'Commercial segment of the customer',
    order_lines.order_date AS order_date
      COMMENT = 'Date the order was placed (not invoice or ship date)'
  )
  METRICS (
    order_lines.net_sales AS SUM(order_lines.valid_net_amount_eur)
      COMMENT = 'Net sales EUR: after discounts, excluding cancelled orders'
  );

[↑Up]

5. Question 4: Who owns the meaning of a metric?

“Revenue” can mean invoiced sales for Sales, sales net of discounts for Commercial, or recognised revenue under an accounting rule for Finance. All three can be right in their own domain. The problem starts when a question does not say which one applies, and the agent silently picks one.

Business Ontology

Business Ontology is a governed business-meaning layer within Snowflake Horizon Catalog. Data owners declare an approved definition for a term, metric or entity, model its relationships, and link it to the tables, views, columns or Semantic Views that implement it.

• Term: a business concept in plain language, such as “Net sale”.
• Metric: a concept defined by a formula or KPI, such as “Net Revenue”.
• Entity and Relationship: business objects and typed links, such as “an order contains order lines”.
• Representation: the link between the concept and an asset that implements it, for example a metric in a Semantic View.

The value is separating the canonical business concept from any single technical implementation. Net Revenue can have representations in several assets, with one approved definition as the common reference.

Availability note: at the time of writing (October 2026), Business Ontology, its Cortex Agents integration and Cortex Sense are in Private Preview for selected accounts. Check availability for your account before designing around them.

How the layers differ

• Semantic View: governs how a domain is queried and how metrics are calculated. It is not an enterprise-wide glossary.
• Business Ontology: governs approved definitions, relationships and links to assets. It does not execute a metric’s formula.
• Cortex Sense: brings AI-inferred context. It is not the same as a definition approved by a business owner.

The critical limit: documented formula versus executed formula

In the documented integration, the formula stored in the ontology is semantic documentation. Cortex Analyst computes the value using the expression in the Semantic View. If the two drift apart, the agent explains one thing and calculates another. Keep them aligned and test them.

Governance practicalities

• Lifecycle: nodes are Draft, Approved or Deprecated. Only approved definitions are served as canonical.
• Separate permissions: ontology domain access and privileges on Semantic Views and tables are different controls. Test them per role.
• Links are not revalidated: if a target asset is dropped or renamed, validate the representations in your deployment process.
• Agent tool descriptions: if the agent covers several domains, configure one Analyst tool per Semantic View, with a description that clearly states what data it covers, and test tool selection.
• Ambiguity: if two definitions are equally plausible, the agent should surface the difference and ask, not guess.

[↑Up]

6. Question 5: Does physical performance still matter?

Absolutely, but do not confuse it with correctness. A query can be semantically right and expensive, and a fast query can be wrong because the model duplicates rows or applies the wrong filter. Both need to be designed.

• Types and predicates: keep types consistent with the content and avoid casts on filtered columns.
• Pruning and clustering: justify clustering with real access patterns and volume. “Always cluster by date” is not a rule.
• Pre-aggregation: Dynamic Tables, materialised views or other structures, when they remove repeated work and the refresh cost is reasonable.
• Concurrency and latency: size warehouses and evaluate execution options against your SLA and query pattern.
• High-cardinality text: Cortex Search helps to find entities by names, aliases or fuzzy text. It does not replace relational keys or metric logic.
• Measure: profile the generated SQL, bytes scanned, execution time and warehouse behaviour under realistic load.

Validate on representative business questions, not on a synthetic query. And measure the whole chain: tool selection, SQL generation, execution and answer composition.

[↑Up]

7. Question 6: How will we know it works?

If you cannot answer this, you are not ready to publish the agent. Build an evaluation set from real business questions, recording for each one the question, the domain, the applicable metric definition, the expected result and, where relevant, the validated SQL. Snowflake’s guidance suggests starting with around ten representative benchmark questions and using the native Cortex Analyst evaluations to measure SQL accuracy. These are the categories I always cover:

• Simple: filters, groupings and aggregations on one table.
• Relational: several joins, composite keys, date roles and shared dimensions.
• Double-counting risk: facts of different grain, N:M, snapshots and semi-additive measures.
• Semantically ambiguous: alternative names, homonymous metrics and per-domain rules.
• Security: authorised and unauthorised users, domain access and restricted objects.
• No definition: the agent must recognise it lacks context and must not invent a formula.

Re-run the evaluation whenever the Semantic View, ontology, instructions, tools, permissions or agent configuration change.

Who owns what

• Data engineering / architecture: grain, quality, keys, relationships, physical performance and integrity tests.
• Technical semantic owner: Semantic Views, executable metrics, filters, Verified Queries and deployments.
• Data steward / business: canonical definitions, synonyms, domains and approval of ontology concepts.
• AI / application team: agent tools, orchestration instructions, tool-selection tests and behaviour under ambiguity.
• Security and governance: role permissions, access reviews and tests of allowed and restricted requests.

[↑Up]

The Big Picture: From Tables to Trusted Answers

Each layer reduces ambiguity for the one above it, and evaluation closes the loop.

[↑Up]

Conclusions

Modelling for AI agents is not a new discipline, it is the old one with the tolerance for ambiguity removed. The physical model must be correct and at a clear grain. The Semantic View must make the query path and the metric logic explicit. The business meaning must be owned by someone who can approve it. And everything must be tested, repeatedly, with questions your users actually ask.

Key takeaway: before you open the Semantic View editor (Semantic Studio), answer the six questions: what is the grain, which shape and which joins, how big is each view, who owns the meaning, how will it perform, and how will we know it works. If you cannot answer one of them, that is where the next sprint should go. A quick checklist to take with you:

• ☐ The grain of every fact table is written down and validated with data.
• ☐ Keys, cardinalities, fan-out and double-counting have specific tests.
• ☐ Visible tables and columns are relevant and have business descriptions.
• ☐ Metrics have an executable, validated definition, and relationships and date roles are explicit.
• ☐ Cross-domain concepts have a canonical definition and an owner, and documented and executed formulas are aligned.
• ☐ Agent tools describe clearly what data they cover, and ambiguity is surfaced to the user.
• ☐ Permissions on ontology, Semantic Views and tables are tested per role.
• ☐ An evaluation set measures accuracy, latency, cost and regressions after every change.

One last word of caution: some of the capabilities discussed here are still in preview at the time this article was written and could evolve quickly. Treat these notes as design principles, re-check availability for your account, and let your domain, workload and enabled features drive the final decisions.

[↑Up]

References

This list contains most of the key content in the documentation, that I make reference in this article and it could help you:

Business Ontology (Preview) — Concepts, relationships, representations, governance, and state of functionality.

Use Business Ontology with Cortex Agents (Preview) — Agent integration, definition query, Cortex Analyst, permissions, and limitations.

Cortex Sense (Private Preview) — Working model of your business from metadata your account already produces.

Best practices for modeling semantic views — Scope, descriptions, synonyms, relationships, metrics, filters, and tests.

Cortex Agents — Orchestration of tools for structured and unstructured data.

Overview of Semantic Views — Introduction to Semantic Views.

CREATE SEMANTIC VIEW — A SQL reference for creating Semantic Views.

Cortex Analyst evaluations — Cortex Analyst quality and results evaluation.

Semantic View Autopilot — Assisted creation of Semantic Views.

Creating semantic views — Authoring options using Autopilot, SQL, YAML, or existing models.

YAML specification for semantic views — YAML specification and differences from legacy semantic models.

Using SQL commands to create and manage semantic views — Managing Semantic Views with DDL.

Get started with Cortex Agents — Initial guide to creating and configuring agents.

Routing mode for Cortex Analyst — Query generation strategy with Semantic Views and semantic guardrails.

Snowflake CoWork: Build agents — Configuration of agents, tools, instructions, and resources.

Snowflake CoWork: Troubleshooting — Common issues, response consistency, and optimization.

Cortex Analyst — Overview of natural language queries on structured data.

[↑Up]

About Me

As a Snowflake Principal Architect & SME on different Data Technologies at SDG Group and 20+ years of experience in several Data Adventures, I lead and advise teams to get the best approaches and apply best practices.

I am a Snowflake Data Superhero since 2025, Snowflake Certified on Advanced Architecture + GenAI Specialty, Snowflake Spotlight Squad Team Member 2024 and Snowflake Barcelona User Group Lead Chapter. As a Data Vault Certified Practitioner, I have been leading Data Vault architectures using Metadata Driven methodologies.

If you want to know more in detail about the aspects seen here, or other ones, you can follow me here on Medium || LinkedIn.

[↑Up]

Before You Model a Single Table: Data Modelling Questions to Ask for AI Agents in Snowflake was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.