M↓ View as Markdown ↗

Compilation & Dialects

A SemQL query doesn't run directly on your database. AI Analyst first compiles it: it checks the query, resolves your semantic references to physical columns, and writes SQL in your database's own dialect. Then it runs that SQL on your database.

How SemQL is compiled

Every time AI Analyst runs a SemQL query, it:

1. Parses the query and checks safety. Only read queries (SELECT) are accepted. A fixed list of known dangerous functions (for example pg_sleep and dblink) and nested aggregates are blocked. The list does not cover every function of every database.
2. Resolves and validates names. Model, Dimension, Measure and Filter names are resolved against the current semantic layer of the active Workspace, with suggestions for close matches. Filters are expanded into conditions. Semantic and type rules are checked, for example Measures can't be re-aggregated.
3. Emits SQL directly in your database's dialect.
4. Checks access. Every Model and field the query uses must be granted to the AI Analyst you chose for the conversation. Otherwise the query is refused with an "Access denied" error before anything is sent to your database.
5. Runs the SQL on your database. A query that runs for more than 60 seconds is cancelled.

There is no cache of compiled queries. Compilation runs on every query against the current semantic layer, so changes to Models, Measures or Filters take effect immediately.

Automatic correction of database errors

If your database rejects the generated SQL with a dialect or syntax error, AI Analyst can make one automatic correction to the SQL and retry it. To make the correction, AI Analyst sends the generated SQL and the database's error message to the language model configured for your organization. The prompt contains only the SQL, the error and the database dialect. It does not contain query results. The generated SQL includes your table and column names and any values written in the query.

The correction is guarded:

• It can't reference tables or columns that the generated SQL didn't already use, and it can only add functions from a known-safe list.
• It can't drop or change filter conditions.
• A correction that breaks these rules, or that can't be checked, is not run.
• The corrected SQL must still be a read-only SELECT.

This behaviour is on by default. Self-managed deployments can turn it off.

Compile errors (for example an unknown field, or a construct your database doesn't support) are never corrected this way. They are returned as errors.

Name matching

• Unquoted Model and field names match regardless of case: Region, REGION and region all find the region Dimension.
• Quoted names, such as "Region" or o."Region", must match exactly.
• After you give a Model an alias (FROM orders o), you can still refer to it by its Model name, unless the query joins that Model to itself.
• When a name isn't found, the error suggests the closest names in the query's scope.

Grouping and joins

• Implicit grouping. If a query aggregates (uses a Measure or an aggregate function) and has no GROUP BY, SemQL groups by every non-aggregated expression in SELECT. GROUP BY ALL does the same. If you write your own GROUP BY, it must cover every non-aggregated column; SemQL doesn't fill in missing ones. See Core Syntax.
• Fan-out guard. When a join on the keys of a relationship repeats a Model's rows (because the relationship is to-many as seen from that Model), SUM, COUNT and AVG over that Model would be inflated. SemQL refuses them. COUNT(DISTINCT …), MIN and MAX are allowed. For Measures, the check applies to those defined with SUM, COUNT or AVG. See Dimensions, Measures & Filters.
• One data source. All Models in a query, including its joins, CTEs, set-operation branches and subqueries, must belong to the same data connection.

Supported databases

SemQL compiles to every supported database. Standard queries work everywhere unchanged, but some advanced constructs aren't available on every database. When you use one, you get a clear compile error instead of a database error.

Dimension and Measure expressions in your Models are written in your database's own SQL dialect and are used as written. A Model is therefore not portable between databases with different dialects.

Database	Not available in SemQL on this database
PostgreSQL	No known limitations from this list
Amazon Redshift	INTERSECT ALL / EXCEPT ALL
MySQL, MariaDB	ROLLUP, CUBE, GROUPING SETS; PERCENTILE_CONT / PERCENTILE_DISC
Microsoft SQL Server, Microsoft Fabric	INTERSECT ALL / EXCEPT ALL; PERCENTILE_CONT / PERCENTILE_DISC; REGEXP_LIKE
Azure Synapse	OFFSET; CUBE, GROUPING SETS (ROLLUP works); INTERSECT ALL / EXCEPT ALL; PERCENTILE_CONT / PERCENTILE_DISC; REGEXP_LIKE
Snowflake	INTERSECT ALL / EXCEPT ALL
BigQuery	INTERSECT ALL / EXCEPT ALL; PERCENTILE_CONT / PERCENTILE_DISC
Databricks	No known limitations from this list
ClickHouse	INTERSECT ALL / EXCEPT ALL; PERCENTILE_CONT / PERCENTILE_DISC
MotherDuck and uploaded Files (DuckDB)	No known limitations from this list
Actian (Actian Data Platform)	INTERSECT ALL / EXCEPT ALL; ordered aggregate windows; PERCENTILE_CONT / PERCENTILE_DISC; REGEXP_LIKE
Ingres	ROLLUP, CUBE, GROUPING SETS; INTERSECT ALL / EXCEPT ALL; ordered aggregate windows; PERCENTILE_CONT / PERCENTILE_DISC; REGEXP_LIKE
Informix	ROLLUP, CUBE, GROUPING SETS; INTERSECT ALL / EXCEPT ALL; ordered aggregate windows; PERCENTILE_CONT / PERCENTILE_DISC; REGEXP_LIKE
ZenDB	CTEs (WITH); window functions; OFFSET; FULL JOIN; INTERSECT / EXCEPT; ROLLUP, CUBE, GROUPING SETS; PERCENTILE_CONT / PERCENTILE_DISC; REGEXP_LIKE

"Ordered aggregate windows" are aggregate functions with ORDER BY inside OVER, such as a running total SUM(x) OVER (ORDER BY day). Ranking and offset windows (ROW_NUMBER, RANK, LAG, …) and aggregate windows without ORDER BY still work on these databases.

Some common functions are translated into each database's own spelling (see Common functions). Other function calls are passed to your database by name, so whether they work depends on your database.

For how to connect each database, see Connect a data source.

Reading compile errors

When AI Analyst's query is refused, it usually reads the error and corrects the query itself. You can see what happened by expanding Steps under the answer: a Run SemQL query step shows the query, the error and, where available, a Suggestion.

Error shown	What it means	Typical fix
Parse error	The query isn't valid SemQL, for example it uses {{ }} templating or an unknown cast type.	Use bare names and the portable form named in the suggestion.
Security error	The query uses a function on the blocked list or nests aggregates, such as SUM(SUM(x)).	Remove the function, or use a Measure or a CTE.
Semantic error	A name wasn't found, the query has no FROM, a Measure is used in WHERE or GROUP BY or wrapped in an aggregate, GROUP BY is incomplete, an output column name is repeated, a join would inflate totals (fan-out), the query mixes data sources, or the AI Analyst you chose doesn't have access to a Model or field.	Follow the suggestion. For access errors, grant the Model or field to the AI Analyst.
Unsupported	The query uses database-specific syntax (such as DISTINCT ON, ~ or TOP n), or a construct that isn't available on your database (see the table above).	Use the portable form named in the suggestion, or rewrite the query, for example with an inline subquery instead of a CTE on ZenDB.
Execution error	Your database rejected the SQL or the query timed out after 60 seconds.	Simplify the query or narrow it with filters.