SQL compilation
How Flax compiles the semantic model to dialect-correct SQL, pushes work to the warehouse, and uses embedded DuckDB when needed
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.
#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 — ad-hoc computation over query results.
- Uploaded CSV sources — data that has no warehouse to push down to (see 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.