Trusted AI Answers For Leadership

AI needs a data contract, not a data dump.

Text-to-SQL systems translate a question into a query. They do not inspect a company and discover what its numbers should mean. If “engagement,” “campaign post,” or “latest performance” has several possible definitions, the model must choose one. Without rules, that choice is a guess — automation of ambiguity, not automation of insight.

A semantic layer is the contract between business language and warehouse structure. It says which tables represent posts, creators, channels, campaigns, and observations. It says what one row means, how tables may join, which fields are categories, which values may be added, and how a metric is calculated. Snowflake calls this a semantic view. The language model still reasons, but it reasons inside a smaller and safer space.

The model is the last layer, not the first.

“AI will automate everything” skips the stack. A useful build order runs from deterministic work to probabilistic work. First, clean and model the data with code that behaves the same way every time. Next, define the business meaning of that model. Then give the AI controlled tools and examples. A language model belongs at the top because language is fuzzy. Keys, joins, row rules, and arithmetic belong below it because they should not be fuzzy. Put the model first and you only get fluent wrong answers faster.

Start by defining what one row means.

The most important word in analytics engineering is grain. Grain means the thing represented by one row. A post table may have one row per post. An observation table may have one row per post, source, and collection time. A campaign mart may have one row per campaign and post. These are related datasets, but their numbers cannot be added or joined in the same way.

The original AI layer could see fields, but it did not have a safe map of these row types. That made reasonable-looking mistakes easy. Joining every historical observation to every campaign membership can copy the same post performance many times. Summing a lifetime post total across daily snapshots can turn one value into dozens. Filtering to the newest row after a join can select the newest duplicate rather than the newest real observation.

I organized the reporting foundation into nine grain-defined data objects. Dimensions described stable business things such as posts, channels, creators, and campaigns. Facts stored events or observations. A bridge stored campaign membership. Prepared marts served common reporting questions at the grain those questions needed. The AI-facing campaign model then exposed six focused logical tables rather than the full warehouse surface.

Prepared tables reduce the model's freedom.

Raw data keeps every detail because engineers may need it later. An AI assistant needs a narrower surface. The campaign mart already resolves identity, source priority, latest state, and campaign membership. That is not hiding the truth. It is placing repeatable work in tested SQL instead of asking the language model to rebuild it for every question.

Join rules protect numbers from multiplication.

Most damaging analytics errors are not syntax errors. They are relationship errors. A query can run successfully while a one-to-many or many-to-many join quietly copies rows. If the copied rows contain views, spend, or engagements, the final total grows even though the business did not.

A relationship therefore needs more than two matching column names. It needs cardinality: one-to-one, one-to-many, or many-to-many. It needs a stable key. It needs a decision about unmatched records. It also needs a rule for which table owns a measure. These facts tell Cortex which joins are allowed and tell reviewers what to inspect when a total looks wrong.

The model uses explicit relationships for its connected reporting paths. Campaign membership stays in a bridge because one post may appear in more than one campaign. Campaign reporting first filters the campaign-post set, then joins context through the governed post key. The order matters. It keeps the selected population clear before descriptive data is added.

When a bridge is not enough

A bridge records valid relationships; it does not decide how a metric should be shared across them. If one post belongs to two campaigns, reporting its full views in both campaigns may be correct for campaign performance but wrong for a portfolio total. The semantic layer must state the intended use. For a deduplicated portfolio total, calculate from the post grain. For campaign reporting, calculate from the campaign-post grain. The right query depends on the question.

Business meaning must be written down.

A database type tells us that a field contains text or a number. It does not tell us how that field should be used. A campaign key may be numeric but should never be summed. Engagement rate may be numeric but should not be added across rows. A date-looking text field may contain bad source values and should not be offered as a filter at all.

The exported Cortex model contained 319 fields. I checked their roles against the warehouse registry and corrected 114 assignments. Fields became dimensions, time dimensions, facts, or measures based on their meaning, not their storage type. One source field named like a call to action was excluded because populated values included dates. A confident model cannot repair a misleading contract; the contract has to tell the truth first.

A metric is a contract, not a column label.

The semantic layer publishes 24 reusable metrics. Each metric carries a formula and an expected grain. Engagements use the governed sum of likes, comments, saves, and shares. Engagement rate divides summed engagement by summed views for the selected population. Post counts use distinct post keys. Paid-boost status stays separate from paid-versus-earned classification because the two fields answer different questions.

Custom instructions cover choices that a formula alone cannot settle. They tell the system which reporting table to use, how to handle latest and pre-boost states, which source has priority, how to treat missed Stories, and where media valuation is valid. Good instructions are narrow. “Be accurate” gives the model no action. “For post count, count distinct governed post keys” gives it a rule that can be checked.

Verified queries turn expert work into examples.

A verified query pairs a real business question with SQL that a human has inspected and run. When a new question resembles that example, Cortex can use the accepted query as a pattern. This is more useful than a general prompt because the example shows the exact tables, joins, filters, metrics, and grain that produced the answer.

I wrote and tested 32 verified questions against live Snowflake data. They covered campaign summaries, platform breakdowns, post rankings, content filters, latest performance, pre-boost performance, and governed valuation. The SQL uses logical names from the semantic model, filters campaign populations early, counts distinct posts, and keeps metric-source rules visible.

Examples improve coverage, but they are not a guarantee.

Thirty-two passing examples prove that those known questions worked at the time of testing. They do not prove that every future question will be correct. A new request may introduce an unseen metric, ambiguous wording, or a relationship outside the tested path. Verified queries reduce the search space and make regressions visible. They do not remove the need for review, monitoring, and clear limits.

Give each tool a narrow job.

A single giant assistant is tempting because it appears simple to users. Behind the interface, it creates a hard choice: either expose everything and accept unsafe freedom, or bury every rule in one long instruction. I split the work into focused text-to-SQL tools. Campaign questions go to the campaign model. Broader organic questions go to the universe model. Administrative questions are rejected because neither analytics tool should answer them.

Routing matters because two questions can use the same words and require different grains. “Which posts performed best in this campaign?” starts with campaign membership. “Which posts performed best on this channel?” starts with the organic post universe. Choosing the tool chooses the valid reporting population before SQL generation begins.

Trust includes showing the work.

The answer keeps its generated SQL visible. That matters even when the user cannot write SQL. An analyst can audit a surprising answer, reproduce it, and improve the semantic layer. Agent observability also records prompts, planning steps, tool choices, generated SQL, query identifiers, results, latency, and status. A wrong answer becomes a traceable system event rather than a mysterious chat failure.

Test the whole path, not just the final sentence.

AI evaluation often asks whether the final wording sounds correct. A data assistant needs deeper checks. The selected tool must match the question. The generated SQL must use the right grain and joins. The result must reconcile with an accepted query. The explanation must not claim more than the result contains. A fluent answer can fail any one of these stages.

1. Check the populationConfirm that the query starts from the right posts, campaigns, channels, and dates.
2. Check the grainWrite down what one output row means and make sure joins do not change it by accident.
3. Check the metricCompare formulas, filters, null handling, and source priority with the governed definition.
4. Check the SQL resultRun the generated query and compare it with an analyst-written reference or a known total.
5. Check the explanationMake sure the words describe the returned data and state any important limit.
6. Save the lessonAdd a verified query, instruction, field fix, or test so the same failure is harder to repeat.

Accuracy is maintained, not completed.

A semantic model can drift even if its file never changes. New source values appear. A table changes grain. A business definition moves. Users ask questions the test set did not cover. The operating loop is therefore continuous: inspect real questions, trace weak answers, repair the lowest layer that caused the failure, rerun known questions, and add a new example only after its SQL is correct.

Fixing the lowest layer is important. If the source relationship is wrong, do not hide it with prompt wording. If the metric is missing, define the metric. If two questions need different populations, route them to different tools. Prompts should guide judgment; deterministic data work should enforce facts.