Skip to content
M↓ View as Markdown ↗

Semantic SQL (SemQL) overview

SemQL (Semantic SQL) is the query language Actian AI Analyst uses to query your data. It looks like standard SQL, but instead of physical tables and columns it names the business concepts in your semantic layer: Models, Dimensions, Measures and Filters. AI Analyst compiles each SemQL query into the SQL dialect of your database and runs it there.

This page explains the ideas behind SemQL. For the full language reference, see the SemQL section.

The problem with raw SQL

Say you want to know: "How many users does each active organization have?"

In raw SQL, you would write something like this:

SELECT
  o.organization_name,
  COUNT(DISTINCT u.user_id) AS user_count
FROM analytics_marts.dim_app_organizations o
JOIN analytics_marts.dim_app_users u
  ON o.organization_id = u.organization_id
WHERE o.current_member_count > 0
GROUP BY o.organization_name

To write this correctly, you need to know:

  • Exact table names and schemas (analytics_marts.dim_app_organizations)
  • Exact column names (organization_name, not org_name or name)
  • How the tables relate (which columns to join on)
  • When to use COUNT and when to use COUNT(DISTINCT)
  • SQL aggregation rules

And if a teammate writes the same query tomorrow, they might count users differently. They might forget the current_member_count > 0 condition, or use COUNT(*) instead of COUNT(DISTINCT user_id). Now your organization has two different "user counts".

The SemQL approach

With SemQL, the same question becomes:

SELECT name, user_count
FROM organizations[active_only]
  • organizations is a Model. SemQL looks up the table it maps to.
  • name is a Dimension of that Model.
  • user_count is a Measure: the correct way to count users is defined once in the semantic layer, so every query uses the same logic.
  • [active_only] applies a predefined Filter to the Model.

You reference names directly. There is no special templating syntax: SemQL rejects {{ }} brackets and asks you to use bare names instead. Because the query aggregates (it uses a Measure), SemQL groups the result by name automatically. You can also write GROUP BY name yourself.

Why this matters for AI Analyst

When you ask "Show me revenue by region for last quarter", AI Analyst has to turn your question into a query. With raw SQL, it would have to work out:

  • How your database is structured
  • How to calculate "revenue" (which columns, which exclusions)
  • What "last quarter" means in your data
  • How to join the tables

With SemQL, AI Analyst references total_revenue and region. Your semantic layer already defines:

  • total_revenue, for example as SUM(amount - refunds)
  • region, a Dimension of the customers Model
  • The relationship between sales and customers, on customer_id

Because AI Analyst uses the Measures and Filters your team has defined instead of reinventing them, you get:

  • Consistent metrics: every query uses the same "revenue" calculation.
  • Less guesswork: AI Analyst doesn't have to infer schemas or business rules.
  • Governance: queries only see the Models and fields you allow, and default Filters always apply.

Core concepts

Models

A Model is a business entity, such as organizations, users, sales or products. Each Model maps to a table in your database, or to a custom SQL query.

FROM organizations

See Models.

Dimensions

Dimensions are the attributes you slice and filter by: names, dates, categories, IDs. You use them in SELECT, WHERE, GROUP BY and ORDER BY.

SELECT name, email, created_at FROM users

A Dimension can be a plain column or an expression. For example, full_name might be defined as CONCAT(first_name, ' ', last_name). Queries just reference full_name.

See Dimensions.

Measures

Measures are predefined, already-aggregated calculations: the metrics your business cares about.

user_count       -- for example COUNT(DISTINCT user_id)
total_revenue    -- for example SUM(amount - refunds)
average_rating   -- for example AVG(rating)

Because the logic lives in one place, everyone gets the same answer. If you later decide user counts should exclude internal test users, you update the Measure definition once, and every query that runs after that uses the new logic. Results that were already returned are not recalculated.

Measures are already aggregated, so you never wrap them in another aggregate. SemQL rejects SUM(total_revenue). See Measures and Dimensions, Measures & Filters.

Filters

Filters are named, reusable row conditions on a Model. You apply them in square brackets after the Model name, in FROM or JOIN:

FROM organizations[active_only]
FROM users[enterprise_customers]
FROM sales[last_quarter, high_value]   -- both filters apply

Filters let a query use a business concept (active_only) without knowing the condition behind it. If the definition of "active" changes, you update the Filter once.

A Filter is written in SemQL over the Model's own Dimensions. It can't use Measures or aggregate functions.

Filters that apply to every query

When you create or edit a Filter in Studio (Models → select a Model → Filters), you can turn on Apply to every query. When it is on, the Filter is applied to every query on that Model. AI Analyst can see the Filter and cannot turn it off.

For example, if your transactions Model has a Filter for status = 'completed' with Apply to every query turned on, every query on transactions only sees completed transactions.

Filters restrict rows, not columns

A Filter controls which rows a query can see, not which columns are exposed. It is not a way to hide sensitive fields such as personal data: a Dimension built on a sensitive column can still be queried. To keep sensitive data out of a Model, exclude, mask or hash it in the Model's underlying source, for example in a database view or in the SELECT list of a custom SQL Model.

See Pre-defined Filters.

Built-in guardrails

Guardrail What it means
Read-only queries SemQL accepts only SELECT queries, so a SemQL query can't contain statements that change data. It also blocks a fixed list of known dangerous functions, for example pg_sleep and dblink. SemQL alone doesn't guarantee read-only access, because some connectors can't enforce it. See the tip below.
AI Analyst access A query can only use the Models, Dimensions and Measures granted to the AI Analyst you chose for the conversation. Anything else is refused with an "Access denied" error. See Models in the AI Analyst configuration.
One data source per query All Models in a query, including its joins, CTEs and subqueries, must come from the same data connection. A query that mixes data sources is refused, and the error names each data source with the Models it used.
Default Filters Filters with Apply to every query turned on always apply.
Workspace boundary A query only sees the Models in the active Workspace.

Use a read-only database user

Connect AI Analyst with a database user that has read-only privileges. Some connectors can't enforce read-only access themselves, and Studio shows a warning for these when you set up the connection.

When the database changes

Queries reference semantic names, not physical columns. If a column is renamed or its logic changes, you update the Dimension or Measure expression once, and queries that use that Dimension or Measure keep working.

Dimension and Measure expressions are written in your database's own SQL dialect.

How a query runs

Every time AI Analyst runs a SemQL query, it:

  1. Parses the query. Only read queries (SELECT) are accepted.
  2. Checks safety. A fixed list of known dangerous functions (for example pg_sleep and dblink) and nested aggregates are blocked.
  3. Resolves names. Models, Dimensions, Measures and Filters are looked up in the current semantic layer of the active Workspace, and Filters are expanded.
  4. Validates the query. For example, Measures can't be re-aggregated or used in WHERE, and types must match.
  5. Emits SQL in your database's dialect.
  6. Checks access. Every Model and field the query uses must be granted to the AI Analyst you chose for the conversation.
  7. Runs the SQL on your database.

Compilation runs on every query against the current semantic layer, so changes to Models, Measures or Filters apply to the next query. For details, including per-database limitations, see Compilation & Dialects.

Where you see SemQL

  • In an Artifact: open an Artifact and select the Calculation tab to see the SemQL query and the SQL it compiled to. Use Copy SemQL or Copy SQL to copy either one.
  • In a conversation: expand Steps under an answer. A Run SemQL query step shows the query AI Analyst wrote.
  • In Studio: Filter conditions are written in SemQL.

Learn more