The compiler nobody sees - InsightViz Blog

Every BI tool has one and almost none of them talk about it, because a compiler is not a demo. But it is where the security properties actually live, and if you are evaluating a platform it is the component whose design you should ask about first.

What goes in

Not SQL. A logical query — a small structured object naming things from your model, with no notion of tables, joins or syntax.

The how-it-was-computed panel showing the logical query as readable JSON with limit, filters, metrics and dimensions, alongside the SQL that ran and the row returned — **The top block is the input; the SQL below it is the output.** A business analyst can read the input and say whether it asks the right question, which is not true of the output. That is the whole reason for the split.

Key	What it holds	Example
`dimensions`	What to group by, optionally with a time grain after a dot.	`["region", "order_date.month"]`
`metrics`	What to aggregate. Named, never expressed.	`["net_revenue", "orders"]`
`filters`	Field, operator, value. Values are always bound, never spliced into text.	`{field, op, value}`
`order_by` / `limit`	Sort and cut. A limit is always applied, with a ceiling.	`500`
`table_calcs`	Running totals, ranks, moving averages — declared, not written.	`running_total`

Every name in that object is checked against the model's catalogue before anything runs. A name that is not there is refused and reported. That single check is what makes an invented field impossible rather than unlikely, whether the query came from a dashboard, from Explore, or from the assistant.

A worked example

One logical query, four times

The same model throughout: an orders table with a declared left join to customers. Every statement below is real output from the compiler, pasted unedited — not an illustration of what it might produce.

1. The plain case. Net revenue by region.

{ "dimensions": ["region"], "metrics": ["net_revenue"], "limit": 500 }

SELECT orders.region AS "region", SUM(orders.amount - orders.discount) AS "net_revenue" FROM "orders" GROUP BY 1 ORDER BY "net_revenue" DESC LIMIT 500 -- parameters: []

Note what is not there. The model declares a join to customers and the compiler did not include it, because no named field needed it. Joins are pulled in, never chosen.

2. The same query, from someone restricted to the West. The person changed; the logical query did not.

SELECT orders.region AS "region", SUM(orders.amount - orders.discount) AS "net_revenue" FROM "orders" WHERE (orders.region = ?) GROUP BY 1 ORDER BY "net_revenue" DESC LIMIT 500 -- parameters: ['West']

The policy is a WHERE clause and the value is bound, not spliced. Two consequences: this person’s total is genuinely their own region rather than a national figure with rows hidden, and a cache keyed on this statement plus its parameters is automatically keyed on the policy.

3. Add a filter on a joined dimension. Now the join is needed.

… "filters": [{ "field": "segment", "op": "=", "value": "SMB" }]

SELECT orders.region AS "region", SUM(orders.amount - orders.discount) AS "net_revenue" FROM "orders" LEFT JOIN "customers" ON orders.customer_id = customers.id WHERE (customers.segment = ?) GROUP BY 1 ORDER BY "net_revenue" DESC LIMIT 500 -- parameters: ['SMB']

The join appeared because a named field required it, on the condition a person wrote once when the model was built. Nothing at query time decided which key to join on.

4. A time grain. The dot suffix is the whole syntax.

{ "dimensions": ["order_date.month"], "metrics": ["net_revenue"] }

SELECT date_trunc('month', orders.order_date) AS "order_date__month", SUM(orders.amount - orders.discount) AS "net_revenue" FROM "orders" GROUP BY 1 ORDER BY "order_date__month" ASC LIMIT 500

Illustrative composite. The figures are internally consistent and every step is one the product supports today.

Four statements, one input shape, and every difference between them came from either the model or the person — never from something deciding in the moment.

What the compiler does with it

flowchart TD
    LQ["Logical query"] --> V["Resolve every name
against the catalogue"]
    V -->|"unknown name"| X["Refused and named"]
    V --> J["Pull in only the joins
the named fields need"]
    J --> P["Apply row policies → WHERE
column policies → removed"]
    P --> D["Derived fields: bins,
groups, sets, parameters"]
    D --> G["GROUP BY at the model's grain"]
    G --> W["Window pass for table calcs,
outside the grouping"]
    W --> S["SQL + bound parameters"]
    style P fill:#fff4e6,stroke:#f0cb9a
    style X fill:#fdecec,stroke:#eab6b6
    style S fill:#e6f7f2,stroke:#9fd8c9

Seven steps, all deterministic. The same logical query produces byte-identical SQL every time, which is what makes it testable — and what makes a cache key safe.

Three of those steps are worth expanding, because they are where the interesting decisions are.

Joins are pulled in, never chosen

The model declares its relationships once, written by someone who knows the grain. The compiler includes a join only when a named field needs it — so a query touching one table produces a single-table statement, and a filter on a joined dimension pulls that join in by itself.

Nothing at query time decides which key to join on. That decision was made once, is reviewable, and cannot be got wrong differently on Tuesday.

Policies go in here, not afterwards

FILTER THE RESULTSApply access afterwards

Run the query, then remove rows the person may not see. Totals are computed on everything and then presented to someone entitled to a subset — so the sum of the visible rows does not match the total on the card.

COMPILE IT INAccess is part of the statement

A row policy becomes a WHERE clause; a column policy removes the dimension from the catalogue before resolution. Totals are genuinely the person's own, and a hidden field cannot be named at all.

The property this buys you for free

Because the policy is inside the SQL, a cached result keyed on the SQL and its bound values is automatically keyed on the policy. Two people with different access compute different keys and can never be served each other’s numbers. We did not add a check for that — it falls out of doing the compilation in the right order, which is the best kind of security property.

Table calculations happen outside the grouping

A running total or a percent-of-total is not an aggregate; it is a window over the aggregated rows. Getting this wrong — computing it inside the GROUP BY — produces numbers that look plausible and are wrong.

So the compiler emits a second pass: aggregate first, then window over the result. Seven calculations are declarable, and they are declared by name rather than written:

Declared as	What it emits
`running_total`	A cumulative sum ordered by the query’s own ordering
`percent_of_total`	The value over the window sum, at the partition you name
`rank` / `dense_rank`	Ranking within the partition
`difference` / `percent_difference`	Against the previous row
`moving_average`	Over a window of rows you specify

Values are bound, always

This is the unglamorous rule that matters most. Every value from a filter, a parameter, a group definition or a set becomes a bound parameter. None of it is formatted into the statement text.

A dashboard parameter is a one-row CTE cross-joined into the query rather than a string substitution — which is more work than interpolation and is the difference between a parameter control and an injection point.

Anything a person can type is a bound value. There is no path from a filter box to the text of a statement, and that is a structural property rather than an escaping function somebody has to remember to call.

Why this is where the AI safety actually is

The assistant produces a logical query and nothing else. It never holds a connection, never writes SQL, and cannot reach around the compiler because there is nowhere to reach.

1 inputa logical query, from every surface

0 joinschosen at query time

Always boundevery value a person supplies

Deterministicsame input, same SQL, every time

That is why the answer to “what stops the AI doing something stupid?” is not a prompt. It is that the assistant's entire output surface is a small object whose every field is validated before anything executes. The full mechanism →

What it costs

A compiler is a constraint, and it is honest to name what the constraint excludes.

You cannot write arbitrary SQL in a panel. Deliberate — an arbitrary statement cannot have policies compiled into it, so allowing one would put a hole in every property above. Arbitrary SQL belongs in the model, where it is reviewed once.
Some things the model cannot express are genuinely not expressible. Correlated subqueries across grains, certain window-of-window constructions. These are real gaps and the honest answer is to model the thing you need rather than to escape the compiler.
Adding a capability means adding it properly. Every new construct has to compose with policies, parameters, drill and caching. That makes the compiler the slowest-moving part of the product, which is correct and occasionally frustrating.

What to ask any vendor

Where are row policies applied — in the query, or to the results? If the answer is the results, ask what the totals show.
Is a cached panel keyed on the person’s access? If the key is the query alone, two people with different entitlements share a cache entry.
Can a user or an AI supply raw SQL? If yes, ask how policies are enforced on it. There is usually no good answer.
Show me the query behind this number. If the product cannot, nobody can audit it.

Why every panel shows its working → · How the policies are set →

← All posts Request a demo