How semantic queries become SQL

A semantic query is a request for metrics and dimensions by name. The warehouse still speaks SQL. This post walks through how datahashi turns a semantic request into compiled SQL you can inspect and trust.

The MCP entry points

The server exposes six tools. Ordinary questions still go through three of them:

• list_catalog shows the metrics, dimensions, measures, and rollups this credential may use.
• describe_metric returns the definition, grain, and allowed shapes for one or more metrics.
• run_semantic_query accepts a structured request (metrics, dimensions, filters, time range) and returns results.

drill_down, refine_semantic_query, and list_dimension_values are the other three. None of the six accept or return SQL.

You do not need to invent table joins by hand for ordinary questions. You name the concepts; the compiler produces the query plan as SQL.

From names to a plan

Compilation is roughly:

1. Resolve names. Metric and dimension identifiers map to catalog entries. Unknown names fail early instead of guessing columns.
2. Fix grain. The requested dimensions set the output grain. Metrics that cannot be computed at that grain error with a clear message rather than silently over-aggregating.
3. Build the join graph. Models and relationships in the catalog determine which tables participate and how they connect. Ambiguous paths are rejected when the catalog marks them as such.
4. Push filters. Filters attach to the entities they belong to, then translate into predicates on the right tables.
5. Emit SQL. The engine renders SELECT, FROM, JOINs, WHERE, GROUP BY, and ORDER BY in a stable, readable form.

That compiled SQL is not a black box. Reading it is the fastest way to verify that the semantic layer did what you intended.

What stays out of the request

Callers should not pass raw table names or arbitrary join hints for standard metric queries. The point of the semantic layer is shared definitions. If you need a one-off exploration outside the catalog, that is a separate SQL path, not a side channel through run_semantic_query.

Time grains, if requested, are expressed as semantic time dimensions, not as ad hoc date_trunc snippets in the request payload. Keeping time in the catalog keeps fiscal calendars and timezone rules consistent.

Why SQL still matters

Even with a strong catalog, SQL remains the audit trail. When a number looks off, inspect the compiled SQL in engine logs or Query Runner audit records. Check the joins and group by keys, and compare against describe_metric. Most “wrong answer” bugs are grain or filter mismatches that the SQL makes obvious in one glance.

datahashi aims for SQL that a human can read without a decoder ring: explicit aliases, predictable clause order, and no surprising subqueries unless the metric definition requires them.

A short example workflow

1. list_catalog to confirm the metric and dimension names.
2. describe_metric on each metric you plan to request.
3. run_semantic_query with those names plus filters.
4. Check the compiled SQL in engine or Query Runner logs, confirm the grain, then trust the numbers (or fix the model).

Semantic queries become SQL through resolution, grain checks, join planning, and filter placement. Learn that pipeline once and debugging gets much faster.