Reading compiled SQL to debug wrong answers

When a semantic query returns a number that feels wrong, the fastest fix is usually not another dashboard tweak. Read the compiled SQL. Engine logs and Query Runner audit trails expose that SQL so you can see exactly how names became joins, filters, and group keys.

Start from the catalog, not from blame

Before assuming the warehouse is wrong:

1. Confirm the metric exists and means what you think with describe_metric.
2. Confirm dimension and filter names with list_catalog.
3. Re-run a minimal run_semantic_query (one metric, one dimension, tight time window).
4. Inspect the compiled SQL in engine logs or Query Runner audit records.

Wrong answers are often correct answers to a slightly different question than the one you meant.

What to scan first in the SQL

Read in this order:

• FROM and JOINs. Are the expected fact and dimension tables present? Is there an unexpected many-to-many path?
• GROUP BY. Does it match the dimensions you requested and the metric grain from describe_metric?
• WHERE / filter predicates. Did every filter you sent appear? Are they on the right aliases?
• SELECT aggregates. Do the aggregate functions match the metric definition (sum vs count distinct vs derived ratio)?

If the join fan-out is visible in the SQL, you have likely found a double-counting path. Fix the model or the requested grain; do not paper over it in the client.

Common mismatch patterns

Grain drift. You asked for daily totals but the SQL groups by day and customer, then something downstream sums again. Check GROUP BY against the question you asked.

Filter on the wrong entity. A status filter applied to a dimension table that is left-joined can drop or keep rows differently than a filter on the fact. The SQL shows which alias received the predicate.

Derived metric expansion. Ratios expand into multiple aggregates. Compare the expansion to describe_metric. If the compiled SQL does not match the documented formula, file it as a compiler or catalog bug.

Time zone or time column surprise. The wrong timestamp column in the SQL explains many “off by one day” reports.

A disciplined debug loop

1. Minimal reproduce with run_semantic_query.
2. Diff the compiled SQL against a trusted hand-written query at the same grain (or against a previous known-good compile).
3. Adjust only one thing: filter, dimension, or metric.
4. Re-check describe_metric if the metric behavior still disagrees with docs.

Keep agents on this loop too. Check the compiled SQL in engine logs when agent answers look off, rather than letting agents invent new metric names.

What not to do

Do not disable the semantic layer and “just write SQL” as the first move for production metrics; you will fork definitions. If using rollups, check the query log to confirm whether a rollup served the answer. Do not ignore a missing filter in the SQL; missing predicates are defects, not optimizations.

Compiled SQL is the shared language between the catalog and the warehouse. Learn to read it, and wrong answers become short investigations instead of long debates. datahashi records that SQL in engine logs and Query Runner audit trails so debugging stays in the open.