Skip to Content

SQL and Python

SQL guidelines

Rules for SQL that works in the metric builder: aliases, quotes, client filters and filter variables.

Rules

SQL editor toolbar with the Format query button highlighted next to Run query.
Format query tidies the SQL before you run it.
  • Write one SELECT or WITH query. Databrain refuses statements that change data, such as INSERT, UPDATE, DELETE, CREATE, DROP or GRANT.
  • Give every column, table, subquery and join an alias with AS.
  • Put identifiers and aliases in double quotes: "schema_name"."table_name" AS "alias_name".
  • Write SQL for your warehouse’s dialect. Date functions, quotes and limits differ between warehouses.

Client filter

Customer-facing metrics must filter by client.

TenancyWriteDatabrain puts in
TableWHERE "client_id" = 'client_id_variable'The client ID from the guest token
DatabaseFROM {{DATABASE_NAME}}.schema.tableThe client’s database name

A numeric client column takes a bare number. For a token with access to all clients, the filter becomes IS NOT NULL.

Filter variables

Let a metric filter or a dashboard filter change the query:

  1. In the metric filter settings, choose Variable Filter.
  2. Enter the Variable Value, for example region. It shows as {{region}}.
  3. Use it in the SQL: WHERE "region" = '{{region}}'.

Wrap an optional part in [[ ]]. Databrain drops the part when its variable is empty:

SELECT "o"."status" AS "status", COUNT(*) AS "orders"
FROM "public"."orders" AS "o"
WHERE "o"."client_id" = 'client_id_variable'
[[ AND "o"."region" = '{{region}}' ]]
GROUP BY "o"."status"

The editor suggests variables as you type, under Metric Filter Variables and Dashboard Filter Variables.

Microsoft SQL Server and Fabric

  • Use a subquery instead of WITH.
  • Use ORDER BY only with TOP or OFFSET ... FETCH.

On this page