# SQL compilation

> How Flax compiles the semantic model to dialect-correct SQL, pushes work to the warehouse, and uses embedded DuckDB when needed

*[View this page in the Flax docs](https://flax-analytics.com/docs/modeling/sql-compilation)*

When you run a query, Flax's own compiler turns your semantic model plus the query request into a single SQL statement, tailored to the target warehouse. This page explains what the compiler generates, how it stays safe, and when the embedded DuckDB engine runs instead. It is for anyone who wants to understand what executes against their data.

## Warehouse pushdown by default

Flax is warehouse-native: it pushes computation down to where your data already lives. The compiler resolves your query against the model graph, emits one SQL statement, and the warehouse does the aggregation and joins. Flax does not copy your tables out; only the aggregated result set comes back. This keeps large-data queries fast and governance in your warehouse.

## The compiler and its trust model

Flax uses its own compiler (not a templating layer) so it can reason about every fragment it emits. The core — model resolution, the join graph, fan-out checks, access filters — is dialect-independent; only the warehouse-specific SQL varies.

A strict trust boundary governs safety: SQL fragments authored in the model YAML are trusted org code, but every value coming from a query request becomes a **bind parameter**, never string-interpolated. Every identifier the compiler emits is quoted through the dialect. Request data is never concatenated into SQL.

## Per-dialect compilation

The same query compiles to different SQL per warehouse. Flax ships dialects for Postgres, Snowflake, BigQuery, Databricks, and the embedded DuckDB engine. Each dialect owns how it quotes identifiers, renders bind placeholders, truncates dates, and expresses aggregates. For example, date truncation to a grain:

| Warehouse | Generated expression |
| --- | --- |
| Postgres / Snowflake / DuckDB | `date_trunc('month', <expr>)` |
| BigQuery | `TIMESTAMP_TRUNC(<expr>, MONTH)` |
| Databricks | `date_trunc('MONTH', <expr>)` |

Placeholders differ too: Postgres uses `$1`, Snowflake, BigQuery, and DuckDB use `?`, and Databricks binds by name (`:p1`). Filtered measures use `FILTER (WHERE …)` where supported (Postgres, DuckDB) and a `CASE`-inside-aggregate form on Snowflake, BigQuery, and Databricks. Percentiles map to each warehouse's native function. Adding a warehouse means adding one dialect, not forking the compiler.

## Fan-out and symmetric aggregation

When a `one_to_many` or `many_to_many` join duplicates base rows, a naive `SUM` would over-count. If the base model declares a `primary_key`, the compiler emits a **symmetric aggregate** that counts each base row once. This is implemented for Postgres and DuckDB; on dialects without it, the compiler blocks the fan-out query rather than return a wrong number. See [Relationships and joins](/docs/modeling/relationships-and-joins).

## When the embedded DuckDB engine runs

Flax embeds DuckDB for work that can't or shouldn't be pushed to a warehouse:

- **Spreadsheet modelling** in [Sheets](/docs/sheets/overview) — ad-hoc computation over query results.
- **Uploaded CSV sources** — data that has no warehouse to push down to (see [CSV uploads](/docs/connections/csv-uploads)).
- **Blending** — combining results across connections in one place.

The choice of where a query runs (warehouse vs. embedded engine) is Flax's compute-placement decision; the same compiler and dialect machinery produce the SQL either way.

## Related

- [Relationships and joins](/docs/modeling/relationships-and-joins)
- [Row-access filters](/docs/modeling/row-access-filters)
- [Caching and performance](/docs/connections/caching-and-performance)
- [Building queries](/docs/exploring/building-queries)