.tql Manual

Motivation

The core primitive for knowledge management in TextQL is the git-backed ontology repo. Business definitions, SQL templates, notes, examples, dashboard snippets, spreadsheet references, and operating rules should live in the same versioned file tree and cross-reference one another naturally.

.tql is the SQL-native semantic layer file format for that repository. It replaces application-stored ontology objects, relations, and metrics with reviewable files. Those files define governed metrics, dimensions, filters, joins, query-backed objects, reusable functions, and runtime-aware access logic while still compiling to warehouse-native SQL.

Why files and folders?

• Frontier AI is strongest when working with files, folders, search, terminal tools, diffs, and review workflows.
• Files let Ana choose the right exploration strategy: lexical search, semantic search, structural search, import following, local previews, and temporary scratch edits.
• Any context file can sit near the model it supports: prose explanations, generated SQL examples, source statistics, dashboard components, style references, spreadsheet exports, and presentation templates.
• Git gives teams diffs, approvals, history, rollback, auditability, and a familiar workflow for improving Ana's behavior over time.
• TextQL RBAC, SCIM, and SSO can control who can view, propose, approve, and use ontology repo content.

Locality of behavior

A major design goal is locality of behavior: the behavior of a semantic unit should be as obvious as possible by reading that unit and the nearby files it imports. .tql intentionally keeps the declaration of a metric, dimension, filter, or join close to the SQL it affects.

This is the main contrast with highly dispersed semantic systems. A query should not require a reader to mentally stitch together distant UI state, hidden application objects, injected prompts, and warehouse SQL. A .tql file should show:

• what parameters callers can provide,
• what metric labels mean,
• what dimension labels select,
• which joins are trusted,
• which filters are valid,
• which runtime roles or client attributes are required, and
• what SQL is ultimately assembled.

.tql still supports reuse through imports and helper files. The point is not to inline every implementation detail into one huge file. The point is to make the invocation and declaration of behavior visible where the behavior matters, and to keep shared logic in small, nearby modules with obvious names.

No query compiler

.tql is not centered around a black-box query compiler. It is centered around authored SQL fragments, sanitized interpolation, lambdas, and helper patterns for common BI workflows. That makes the generated SQL inspectable, keeps behavior local, and avoids a large hidden translation layer between the model and the warehouse.

This matters for AI. Ana can read the same source that produces the SQL. She can see the metric formula, the join, the filter registry, and the runtime guard in the file rather than reverse-engineering behavior from compiler output or opaque semantic-model state.

If a .tql entry does not support exactly what Ana needs, it is still valuable. Ana can read the entry as a reference for trusted metric definitions, joins, table aliases, filter keys, and access rules, then manually write a one-off SQL query using that context. This is a major advantage over semantic modeling languages that only expose a compiler: .tql is both executable and directly useful as AI-readable source material.

Why not only text-to-SQL?

Freeform text-to-SQL is useful for discovery, but it is not enough for governed analytics. Teams need stable definitions for revenue, active customer, ARR, churn, valid status, trusted joins, tenant scoping, and role-aware access. .tql gives Ana a governed surface she can rely on, while preserving an escape hatch for direct SQL when no model covers the question.

Why not only a rigid semantic UI?

Real warehouses contain imperfect schemas, legacy tables, bridge joins, role-playing dimensions, bespoke access rules, and business-specific calculations. .tql keeps a SQL-shaped escape hatch so teams can model reality without forcing every definition through a narrow UI schema or requiring TextQL to build complex multi-dialect semantic compilation for every warehouse edge case.

Mental Model

A .tql file is a typed program that renders SQL.

• Authored SQL fragments are trusted. They come from ontology files that are reviewed and governed.
• Parameter values are untrusted data. They can become bind values or escaped inline literals, but they cannot become SQL structure by themselves.
• Sets choose authored structure. A caller can request "revenue", but only the authored matchSet arm decides what SQL "revenue" means.
• Filters are compiled from a registry. A caller can request an allowed key and operator, but only authored filterables decide which SQL expression is filtered.
• Runtime context is explicit. Role names and client attributes are available under _tql so access-aware behavior lives in versioned code.

Ana can take three different actions on .tql entries:

• inspect: parse the file and return parameter names, types, defaults, and docs from -- comments in the params block.
• render: render SQL for a supplied JSON parameter object.
• execute: render SQL and run it against a connector.

If an entry does not support what Ana wants directly, she can use it as a reference for manually writing a query. The file still tells her which business definitions, joins, filters, and runtime constraints are trusted.

Short Language Spec

File forms

.tql files have two common shapes.

Plain SQL template

Use this when the file is basically SQL with direct parameters. In a plain body, interpolation is limited to parameter names.

params {
  -- Country code. Examples: "US", "CA", "BR"
  country: String = "US"

  -- Inclusive lower bound in ISO 8601 timestamp format.
  created_after: Timestamp?
}

SELECT *
FROM visits
WHERE country = ${country}
  AND created_at >= ${created_after}

Expression body

Use this when you need branching, reusable fragments, imports, helper functions, semantic views, or conditional joins. The expression body must evaluate to a SqlFragment, usually with sql"..." or sql'' ... ''.

params {
  include_region: Bool = false
}

let
  select_expr = if include_region then sql"region, COUNT(*)" else sql"COUNT(*)"
  group_clause = if include_region then sql"GROUP BY region" else sql""
in sql''
  SELECT ${select_expr}
  FROM orders
  ${group_clause}
''

Imports

Project templates support imports between .tql files. Relative imports resolve from the importing file. Absolute imports resolve from the project root.

import t from "../relations/transactions.tql"
import dims from "../dimensions/standard.tql"
import { eq } from "../filters/compare.tql"

Whole-record imports bind the exported record to a local name. Destructured imports bind named fields from the exported record.

Params block

Parameters are declared in a params { ... } block. Use one declaration per line and no commas.

params {
  -- Metrics to include.
  metrics: Set<"revenue" | "order_count"> = []

  -- Optional dimensions.
  dimensions: Set<"customer" | "month"> = []

  -- Filters to apply. Allowed keys: customer_name (equals, like), ordered_at (gte, lte, between).
  filters: List<FilterInput> = []

  -- Optional region override.
  region: String?
}

.tql type	JSON shape	Notes
Int	integer	Non-integer numbers are rejected.
Float	number	Integers are accepted.
String	string	Used for literal values, not SQL identifiers.
Bool	boolean	Use with if or boolean operators.
Date	string	Document the expected format. .tql does not deeply parse dates.
Timestamp	string	Document timezone expectations.
Set<"...">	array of strings	Values are deduped and validated against the allowed labels.
List<T>	array	Elements are validated recursively.
FilterInput	object	Usually used as List<FilterInput>.

? marks a nullable parameter. Omitted nullable params resolve to null. Non-nullable params without defaults are required. Defaults are supported for scalar literals and empty lists or sets.

Pure expressions in Set signatures

Set type signatures can use pure expressions, not only literal unions. This is a key technique for multi-file ontology repo layouts: object modules can export metric and dimension key lists, and query entrypoints can compose those lists into their public parameter types.

-- objects/orders.tql
let
  metric_keys = ["revenue", "order_count"]
  dimension_keys = ["Orders.customer", "Orders.month"]
in { metric_keys, dimension_keys }

-- queries/orders.tql
import orders from "../objects/orders.tql"

params {
  metrics: Set<orders.metric_keys> = []
  dimensions: Set<orders.dimension_keys ++ ["Orders.region"]> = []
}

Expressions

• Literals: numbers, strings, booleans, null, lists, and records.
• Bindings: let ... in ..., evaluated in order. Duplicate binding names are rejected.
• Records: { expr = sql"b.name", join = sql"JOIN buyers b ON ..." }.
• Record shorthand: { expr, join }.
• Field access: dims.buyer.expr.
• Conditionals: if condition then value else value. Only the selected branch is evaluated.
• Lambdas: \col val -> sql"${col} = ${val}". Function application is whitespace-separated.
• Set matching: matchSet dimensions { "buyer" -> ... }.

Operators

Category	Operators
Boolean	||, &&, not
Equality	==, !=
Comparison	>, <, >=, <=
Concatenation	++ for two SqlFragment values or two lists.

Precedence, from highest to lowest: field access and function application, not, ++, comparisons, equality, &&, ||. Use parentheses when mixing boolean operators.

SQL fragments and interpolation

Use sql"..." for short fragments and sql'' ... '' for multiline SQL.

sql''
  SELECT ${select_expr}
  FROM orders o
  ${where_clause}
''

Inside sql, ${expr} evaluates an expression and lowers it into SQL. Scalar values become bind values or escaped inline values. null becomes NULL. Lists of scalar values become SQL tuples. SqlFragment values splice in directly.

Never quote interpolations yourself.

-- Correct
WHERE country = ${country}

-- Wrong: .tql rejects interpolations inside SQL string literals.
WHERE country = '${country}'

Nullable params are not omitted automatically. If created_after is null, created_at >= ${created_after} renders a NULL comparison. Use branching for optional predicates.

let
  where_clause =
    if created_after == null
    then sql""
    else sql"WHERE created_at >= ${created_after}"
in sql"SELECT * FROM visits ${where_clause}"

List interpolation already adds parentheses. Write WHERE id IN ${ids}, not WHERE id IN (${ids}).

Builtins

Builtin	Purpose
concatSep	Concatenates a list of SqlFragment values with an authored string literal separator. Empty fragments are skipped.
wrap	Adds an authored prefix and suffix only when the fragment is non-empty.
isEmpty	Returns true for empty fragments, strings, sets, lists, and null.
map	Applies a function to each item in a list.
any	Returns true if any item in a boolean list is true.
contains	Checks membership in a set or list.
matchSet	Maps a set param to authored expressions in authored arm order.
filter	Keeps list items where a predicate returns true.
dedupeByKey	Deduplicates record lists by their key field, preserving the first definition.
filterWhere	Compiles FilterInput values against an authored filter registry.
error	Stops rendering with an explicit message.

FilterInput

At the JSON boundary, filters are objects with key, op or operator, and usually value or values.

{
  "key": "customer_name",
  "operator": "like",
  "value": "Acme"
}

Supported canonical operators include equals, not_equals, gt, gte, lt, lte, like, not_like, starts_with, not_starts_with, ends_with, not_ends_with, between, in, not_in, is_null, and is_not_null. Aliases such as eq, equal, ilike, greater_than_or_equal, and less_than_or_equal are normalized. Each filterable still controls which operators are allowed for its key.

Runtime context

Chat-scoped runtime values are available under _tql without declaring params:

• _tql.roleset_by_rolename: set of effective chat role names.
• _tql.role_names: list of effective chat role names.
• _tql.client_attributes_json: client attribute object from the chat/API key context.
• _tql.client_attributes: alias of _tql.client_attributes_json.

let
  tenant_id = _tql.client_attributes_json.tenant_id
  has_access = any (map (\role -> contains _tql.roleset_by_rolename role) ["tenant_admin", "tenant_viewer"])
  scoped_tenant_id = if has_access then tenant_id else error "Query requires tenant_admin or tenant_viewer"
in sql"SELECT * FROM accounts WHERE tenant_id = ${scoped_tenant_id}"

Features

Typed query interfaces

A .tql file exposes a small typed API to callers. Required params, nullable params, defaults, set labels, expression-backed set signatures, and filter shapes can be inspected before execution.

Semantic-view query surfaces

The standard reusable pattern is a view with metrics, dimensions, and filters. Callers get a compact surface. Authors keep SQL structure, join logic, grouping behavior, and filter rules in code.

Safe parameter interpolation

Caller values do not become SQL structure directly. They render as bind values or inline-escaped values. Authored fragments are the only way to introduce SQL structure.

Transparent SQL assembly

.tql uses sanitized string interpolation and lambdas instead of hiding logic behind a query compiler. Authors can represent imperfect warehouse reality directly, including hand-tuned SQL, custom formulas, conditional joins, role-playing dimensions, warehouse-specific functions, and messy legacy conventions that are hard to encode in conventional semantic layers.

Governed arbitrary filtering

filterWhere allows flexible caller filters without raw SQL from the caller. The author controls filter keys, SQL expressions, and allowed operators.

Modular reuse

Imports allow table backing definitions, relation modules, dimensions, measures, filters, key lists, and query templates to live in separate files. This keeps definitions small without hiding behavior in application state. Because Set signatures can reference pure expressions such as obj_order.metric_keys or obj_order.dimension_keys ++ obj_customer.dimension_keys, multi-file modules can define the allowed query surface once and reuse it in entrypoints.

Access-aware modeling

.tql can branch on role names and client attributes through _tql. This lets teams model tenant scoping, regional scoping, role-based visibility, and personalization in versioned files.

Versioned governance

Because .tql lives in the ontology repo, semantic changes can be proposed, diffed, reviewed, approved, reverted, and audited like software changes.

Ana-readable context

Ana can read a .tql file and learn the business terms, valid dimensions, trusted joins, metric definitions, filter grammar, runtime access rules, and final SQL shape. This makes .tql both executable code and high-signal context.

Current limitations

• Date and Timestamp params are strings at validation time. Document format expectations in comments.
• Plain SQL bodies only support param-name interpolation. Use expression-style .tql for computed fragments.
• Structured source assembly with compiler-managed join aliases is a design direction, not the current authoring surface. Today, joins are authored as SQL fragments.

Patterns

1. Plain template query

Use when the user-facing API is a few direct values and the SQL shape is fixed.

params {
  customer_id: Int
  start_date: Date?
}

SELECT *
FROM orders
WHERE customer_id = ${customer_id}
  AND (${start_date} IS NULL OR ordered_at >= ${start_date})

Example input

{
  "customer_id": 42,
  "start_date": "2026-01-01"
}

Rendered SQL

SELECT *
FROM orders
WHERE customer_id = 42
  AND ('2026-01-01' IS NULL OR ordered_at >= '2026-01-01')

2. Semantic view

Use when callers should select governed metrics, dimensions, and filters.

params {
  metrics: Set<"revenue" | "order_count"> = []
  dimensions: Set<"customer" | "month"> = []
  filters: List<FilterInput> = []
}

let
  metric_frags = matchSet metrics {
    "revenue" -> sql"SUM(o.revenue) AS revenue"
    "order_count" -> sql"COUNT(DISTINCT o.id) AS order_count"
  }
  dim_entries = matchSet dimensions {
    "customer" -> { expr = sql"c.name", join = sql"JOIN customers c ON o.customer_id = c.id" }
    "month" -> { expr = sql"DATE_TRUNC('month', o.ordered_at)", join = sql"" }
  }
  filterables = [
    { key = "customer_name", expr = sql"c.name", ops = ["equals", "like"] }
  , { key = "ordered_at", expr = sql"o.ordered_at", ops = ["gte", "lte", "between"] }
  ]
  joins = concatSep " " (map (\d -> d.join) dim_entries)
  select_dims = concatSep ", " (map (\d -> d.expr) dim_entries)
  select_metrics = concatSep ", " metric_frags
  select_expr = if isEmpty select_dims && isEmpty select_metrics then sql"*"
    else if isEmpty select_dims then select_metrics
    else if isEmpty select_metrics then select_dims
    else sql"${select_dims}, ${select_metrics}"
  where_clause = wrap "WHERE " "" (filterWhere filterables filters)
  group_clause = wrap "GROUP BY " "" select_dims
in sql''
  SELECT ${select_expr}
  FROM orders o
  ${joins}
  ${where_clause}
  ${group_clause}
''

Example input

{
  "metrics": ["revenue", "order_count"],
  "dimensions": ["customer", "month"],
  "filters": [
    { "key": "customer_name", "operator": "like", "value": "Acme" },
    { "key": "ordered_at", "operator": "gte", "value": "2026-01-01" }
  ]
}

Rendered SQL

SELECT c.name, DATE_TRUNC('month', o.ordered_at), SUM(o.revenue) AS revenue, COUNT(DISTINCT o.id) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.name ILIKE '%Acme%' AND o.ordered_at >= '2026-01-01'
GROUP BY c.name, DATE_TRUNC('month', o.ordered_at)

3. File reuse with imports

Use imports when several views share backing table names, helper predicates, measures, or dimension definitions. Keep files small and named after their role.

-- relations/orders.tql
let
  source = sql"analytics.orders o"
  id = sql"o.id"
  customer_id = sql"o.customer_id"
  revenue = sql"o.revenue"
  ordered_at = sql"o.ordered_at"
in { source, id, customer_id, revenue, ordered_at }

-- filters/compare.tql
let
  eq = \col val -> sql"${col} = ${val}"
  gte = \col val -> sql"${col} >= ${val}"
in { eq, gte }

-- queries/revenue_by_customer.tql
import o from "../relations/orders.tql"
import { eq } from "../filters/compare.tql"

params {
  customer_id: Int?
}

let
  where_clause = if customer_id != null then sql"WHERE ${eq o.customer_id customer_id}" else sql""
in sql''
  SELECT o.customer_id, SUM(${o.revenue}) AS revenue
  FROM ${o.source}
  ${where_clause}
  GROUP BY o.customer_id
''

Example input

{
  "customer_id": 42
}

Rendered SQL

SELECT o.customer_id, SUM(o.revenue) AS revenue
FROM analytics.orders o
WHERE o.customer_id = 42
GROUP BY o.customer_id

4. Fact and dimension star schema pattern

The fact and dimension pattern separates reusable object modules from query entrypoints. Object modules export backing tables, source helpers, metric keys, dimension keys, filter keys, semantic key checks, joins, metrics, dimensions, and filterables. Query files import the relevant object modules and compose them into a fact-centered view.

This pattern is important because Set param signatures can be pure expressions. A query can expose Set<obj_order.metric_keys> or Set<obj_order.dimension_keys ++ obj_customer.dimension_keys>, so the allowed query surface is assembled from imported files rather than copied into every entrypoint.

This is also the pattern used by the legacy ontology auto-migration, but it is a useful hand-authored pattern whenever a domain has clear fact tables and dimension tables.

Object modules usually follow this shape:

-- objects/order.tql
let
  backing = sql"warehouse.orders"
  source = \alias -> sql"${backing} ${alias}"

  metric_keys = ["Order.revenue", "Order.order_count"]
  dimension_keys = ["Order.status", "Order.month"]
  filter_keys = ["Order.status", "Order.ordered_at"]
  semantic_keys = dimension_keys ++ filter_keys

  needs = \dimensions filter_keys ->
    any (map (\k -> contains dimensions k || any (map (\fk -> fk == k) filter_keys)) semantic_keys)

  join = \alias parent_alias parent_key key ->
    sql"JOIN ${source alias} ON ${parent_alias}.${parent_key} = ${alias}.${key}"

  join_if = \needed alias parent_alias parent_key key ->
    if needed then join alias parent_alias parent_key key else sql""

  metrics = \alias selected -> matchSet selected {
    "Order.revenue" -> sql"SUM(${alias}.net_revenue) AS revenue"
    "Order.order_count" -> sql"COUNT(DISTINCT ${alias}.id) AS order_count"
  }

  dimensions = \alias -> [
    { key = "Order.status", label = "status", expr = sql"${alias}.status" }
  , { key = "Order.month", label = "month", expr = sql"DATE_TRUNC('month', ${alias}.ordered_at)" }
  ]

  filterables = \alias -> [
    { key = "Order.status", label = "status", expr = sql"${alias}.status", ops = ["equals", "not_equals", "in"] }
  , { key = "Order.ordered_at", label = "ordered_at", expr = sql"${alias}.ordered_at", ops = ["gte", "lte", "between"] }
  ]
in { backing, source, metric_keys, dimension_keys, filter_keys, semantic_keys, needs, join, join_if, metrics, dimensions, filterables }

-- objects/customer.tql
let
  backing = sql"warehouse.customers"
  source = \alias -> sql"${backing} ${alias}"

  metric_keys = []
  dimension_keys = ["Customer.name", "Customer.region"]
  filter_keys = ["Customer.name", "Customer.region"]
  semantic_keys = dimension_keys ++ filter_keys

  needs = \dimensions filter_keys ->
    any (map (\k -> contains dimensions k || any (map (\fk -> fk == k) filter_keys)) semantic_keys)

  join = \alias parent_alias parent_key key ->
    sql"JOIN ${source alias} ON ${parent_alias}.${parent_key} = ${alias}.${key}"

  join_if = \needed alias parent_alias parent_key key ->
    if needed then join alias parent_alias parent_key key else sql""

  metrics = \alias selected -> []
  dimensions = \alias -> [
    { key = "Customer.name", label = "customer_name", expr = sql"${alias}.name" }
  , { key = "Customer.region", label = "customer_region", expr = sql"${alias}.region" }
  ]
  filterables = \alias -> [
    { key = "Customer.name", label = "customer_name", expr = sql"${alias}.name", ops = ["equals", "like"] }
  , { key = "Customer.region", label = "customer_region", expr = sql"${alias}.region", ops = ["equals", "in"] }
  ]
in { backing, source, metric_keys, dimension_keys, filter_keys, semantic_keys, needs, join, join_if, metrics, dimensions, filterables }

Query entrypoints compose object modules into one callable surface:

-- queries/order.tql
import obj_order from "../objects/order.tql"
import obj_customer from "../objects/customer.tql"

params {
  metrics: Set<obj_order.metric_keys> = []
  dimensions: Set<obj_order.dimension_keys ++ obj_customer.dimension_keys> = []
  filters: List<FilterInput> = []
}

let
  order_alias = sql"order_object"
  customer_alias = sql"customer"

  metric_frags = obj_order.metrics order_alias metrics
  all_dim_entries = dedupeByKey (obj_order.dimensions order_alias ++ obj_customer.dimensions customer_alias)
  dim_entries = filter (\d -> contains dimensions d.key) all_dim_entries

  filter_keys = map (\f -> f.key) filters
  needs_customer = obj_customer.needs dimensions filter_keys
  join_customer = obj_customer.join_if needs_customer customer_alias order_alias sql"customer_id" sql"id"

  filterables = dedupeByKey (obj_order.filterables order_alias ++ obj_customer.filterables customer_alias)
  select_dims = concatSep ", " (map (\d -> d.expr) dim_entries)
  select_metrics = concatSep ", " metric_frags
  select_expr = if isEmpty select_dims && isEmpty select_metrics then sql"*"
    else if isEmpty select_dims then select_metrics
    else if isEmpty select_metrics then select_dims
    else sql"${select_dims}, ${select_metrics}"
  group_clause = wrap "GROUP BY " "" select_dims
  where_clause = wrap "WHERE " "" (filterWhere filterables filters)
in sql''
  SELECT ${select_expr}
  FROM ${obj_order.source order_alias}
  ${join_customer}
  ${where_clause}
  ${group_clause}
''

The important details are:

• Object modules define semantic keys once.
• Query params compose imported key lists with pure expressions inside Set type signatures.
• needs makes conditional joins local to the object that owns the semantic keys.
• join_if keeps query entrypoints concise.
• dedupeByKey allows several objects to contribute dimensions and filterables without duplicate keys.
• filter turns all imported dimension definitions into only the requested dimensions.

Example input

{
  "metrics": ["Order.revenue"],
  "dimensions": ["Customer.region", "Order.month"],
  "filters": [
    { "key": "Customer.region", "operator": "in", "values": ["East", "West"] }
  ]
}

Rendered SQL

SELECT customer.region, DATE_TRUNC('month', order_object.ordered_at), SUM(order_object.net_revenue) AS revenue
FROM warehouse.orders order_object
JOIN warehouse.customers customer ON order_object.customer_id = customer.id
WHERE customer.region IN ('East', 'West')
GROUP BY customer.region, DATE_TRUNC('month', order_object.ordered_at)

5. Fail-closed row-level scoping

Use error when an access condition is not met. Do not silently render unscoped SQL.

let
  tenant_id = _tql.client_attributes_json.tenant_id
  has_access = any (map (\role -> contains _tql.roleset_by_rolename role) ["tenant_admin", "tenant_viewer"])
  scoped_tenant_id = if has_access then tenant_id else error "Query requires tenant_admin or tenant_viewer"
in sql''
  SELECT *
  FROM accounts
  WHERE tenant_id = ${scoped_tenant_id}
''

Example runtime context

{
  "roleset_by_rolename": ["tenant_viewer"],
  "client_attributes_json": { "tenant_id": "tenant-123" }
}

Rendered SQL

SELECT *
FROM accounts
WHERE tenant_id = 'tenant-123'

Intentional error

{
  "roleset_by_rolename": [],
  "client_attributes_json": { "tenant_id": "tenant-123" }
}

Expected error:
Query requires tenant_admin or tenant_viewer

Best Practices

Modeling

• Prefer a plain SQL template when a few direct params are enough.
• Prefer a semantic view when users need reusable metrics, dimensions, and filters.
• Use the same labels that business users use, even if the warehouse column names differ.
• Alias every computed metric with AS stable_metric_name.
• Keep metric definitions at the grain where they are valid. Do not join facts at different grains unless the query explicitly protects against duplication.
• Make bridge joins and many-to-many joins obvious in comments and names.
• For date dimensions, expose useful grains such as day, week, month, or year as separate dimension labels.

Locality and reuse

• Keep behavior near the query surface it affects.
• Extract shared logic only when it removes real duplication or prevents inconsistent definitions.
• Put shared backing table definitions in schema.tql or _defs/*.tql so table renames are local.
• Keep imported modules small: relations, dimensions, measures, filters, and query entrypoints are easier to navigate than a single large utility file.
• Prefer obvious import names: orders, customers, dims, filters, revenue.

Parameters and docs

• Document every user-facing param with -- comments immediately above the declaration.
• For date and timestamp params, document accepted formats and timezone assumptions.
• For filters, document allowed keys and operators because inspect exposes those comments to users and Ana.
• Use nullable params only when null has clear meaning.
• Use Set<"..."> when callers select from a known set of SQL structures.

Safety and correctness

• Never quote interpolations inside SQL string literals.
• Never accept raw SQL as a string param to splice into a query.
• Use filterWhere instead of hand-rolled string concatenation for arbitrary filters.
• Use if and wrap for optional clauses.
• Use error to reject unsupported parameter combinations or missing runtime access context.
• Render generated SQL before execution when changing a .tql file.

Ana usage

• Ana should prefer a relevant .tql file over raw SQL when the file covers the user's question.
• Ana should inspect params before rendering or executing unfamiliar .tql.
• Ana should use direct SQL for exploration when no .tql file fits, then propose a .tql improvement if the analysis pattern becomes reusable.
• Ana should keep proposed .tql patches minimal, reviewable, and colocated with related files.

Small Example

This example shows a tiny ontology repo area with table reuse, semantic dimensions, reusable filter helpers, and a query entrypoint.

Directory layout

ontology/commerce/
  schema.tql
  relations/orders.tql
  dimensions/customer.tql
  filters/compare.tql
  queries/revenue_by_customer.tql
  notes/revenue-definition.md

schema.tql

let
  orders = { backing = sql"analytics.orders" }
  customers = { backing = sql"analytics.customers" }
in { orders, customers }

relations/orders.tql

import schema from "../schema.tql"

let
  source = sql"${schema.orders.backing} o"
  id = sql"o.id"
  customer_id = sql"o.customer_id"
  revenue = sql"o.net_revenue"
  ordered_at = sql"o.ordered_at"
in { source, id, customer_id, revenue, ordered_at }

dimensions/customer.tql

import schema from "../schema.tql"
import orders from "../relations/orders.tql"

let
  join = sql"JOIN ${schema.customers.backing} c ON ${orders.customer_id} = c.id"
  name = { expr = sql"c.name", join }
  region = { expr = sql"c.region", join }
in { name, region }

filters/compare.tql

let
  eq = \col val -> sql"${col} = ${val}"
  gte = \col val -> sql"${col} >= ${val}"
  lte = \col val -> sql"${col} <= ${val}"
in { eq, gte, lte }

queries/revenue_by_customer.tql

import orders from "../relations/orders.tql"
import customer from "../dimensions/customer.tql"

params {
  -- Metrics to return. Use [] with dimensions to list dimension values without aggregation.
  metrics: Set<"revenue" | "order_count"> = []

  -- Dimensions to group by. "customer" uses customer name; "region" uses customer region.
  dimensions: Set<"customer" | "region" | "order_month"> = []

  -- Allowed filters: customer_name (equals, like), region (equals, in), ordered_at (gte, lte, between).
  filters: List<FilterInput> = []

  -- Required partition year. Example: 2026.
  year: Int?
}

let
  scoped_year = if year == null then error "year is required because orders are partitioned by year" else year
  metric_frags = matchSet metrics {
    "revenue" -> sql"SUM(${orders.revenue}) AS revenue"
    "order_count" -> sql"COUNT(DISTINCT ${orders.id}) AS order_count"
  }
  dim_entries = matchSet dimensions {
    "customer" -> customer.name
    "region" -> customer.region
    "order_month" -> { expr = sql"DATE_TRUNC('month', ${orders.ordered_at})", join = sql"" }
  }
  filterables = [
    { key = "customer_name", expr = customer.name.expr, ops = ["equals", "like"] }
  , { key = "region", expr = customer.region.expr, ops = ["equals", "in"] }
  , { key = "ordered_at", expr = orders.ordered_at, ops = ["gte", "lte", "between"] }
  ]
  filter_keys = map (\f -> f.key) filters
  needs_customer = any (map (\k -> contains dimensions k || any (map (\fk -> fk == k) filter_keys)) ["customer", "region", "customer_name"])
  customer_join = if needs_customer then customer.name.join else sql""
  partition_clause = sql"EXTRACT(year FROM ${orders.ordered_at}) = ${scoped_year}"
  select_dims = concatSep ", " (map (\d -> d.expr) dim_entries)
  select_metrics = concatSep ", " metric_frags
  select_expr = if isEmpty select_dims && isEmpty select_metrics then sql"*"
    else if isEmpty select_dims then select_metrics
    else if isEmpty select_metrics then select_dims
    else sql"${select_dims}, ${select_metrics}"
  extra_filters = filterWhere filterables filters
  predicates = if isEmpty extra_filters then partition_clause else sql"${partition_clause} AND ${extra_filters}"
  where_clause = wrap "WHERE " "" predicates
  group_clause = wrap "GROUP BY " "" select_dims
in sql''
  SELECT ${select_expr}
  FROM ${orders.source}
  ${customer_join}
  ${where_clause}
  ${group_clause}
''

Example render params

{
  "metrics": ["revenue", "order_count"],
  "dimensions": ["customer", "order_month"],
  "year": 2026,
  "filters": [
    { "key": "customer_name", "operator": "like", "value": "Acme" },
    { "key": "ordered_at", "operator": "between", "values": ["2026-01-01", "2026-03-31"] }
  ]
}

Example rendered SQL

SELECT c.name, DATE_TRUNC('month', o.ordered_at), SUM(o.net_revenue) AS revenue, COUNT(DISTINCT o.id) AS order_count
FROM analytics.orders o
JOIN analytics.customers c ON o.customer_id = c.id
WHERE EXTRACT(year FROM o.ordered_at) = 2026 AND c.name ILIKE '%Acme%' AND o.ordered_at BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY c.name, DATE_TRUNC('month', o.ordered_at)

Intentional business logic error

The error builtin is useful when a request is syntactically valid but violates a business or warehouse rule. In this example, year is required because the table is partitioned by year.

{
  "metrics": ["revenue"],
  "dimensions": ["customer"],
  "filters": []
}

Expected error:
year is required because orders are partitioned by year

Why this example works

• The public query surface is small: metrics, dimensions, and filters.
• Warehouse table names live in schema.tql.
• The customer join is declared near customer dimensions.
• The query only emits the customer join when a dimension or filter needs it.
• Filter keys and operators are governed by filterables.
• Ana can inspect the query params, read related files, render SQL, and explain the business behavior.

Guidance For Ana

When answering analytics questions, use .tql as the first-class semantic surface.

1. Search for relevant .tql files by business term, metric name, table name, and filter key.
2. Prefer an existing .tql file when its params can express the user's request.
3. Inspect before use so parameter docs, defaults, and filter contracts are visible.
4. Render before execute when using a .tql file for the first time or after editing it.
5. Use raw SQL for discovery when no .tql file fits, but do not treat raw SQL discoveries as governed definitions.
6. When a raw SQL pattern is repeated or important, propose a small .tql file or a small edit to an existing one.
7. Do not invent metric definitions when a .tql file defines the metric. Use the authored definition or explain why the .tql file is insufficient.
8. Respect runtime scoping. If a .tql file uses _tql, preserve its fail-closed behavior.

Checklist

• Params use params { ... }, one declaration per line, no commas.
• Param docs use -- comments immediately above declarations.
• String defaults use double quotes.
• Equality tests use == and !=, not bare =.
• Optional clauses use if, isEmpty, and wrap.
• Interpolations are not wrapped in SQL quotes.
• List interpolations are not wrapped in extra parentheses.
• Metrics have stable AS ... aliases.
• Filter params document allowed keys and operators.
• Filter behavior uses filterWhere instead of caller-provided SQL.
• Conditional joins include dimensions and filter keys that require each join.
• Runtime access checks fail closed with error.
• Shared table backing definitions live in a local shared file.
• The file has been inspected and rendered before approval.