SQL Snippets

Overview

SQL snippets are reusable SQL fragments that can be referenced in queries across many reports, filters, and SQL alerts, while being maintained in a single central definition. When the snippet text changes, every query that references it automatically uses the new version.

A snippet is defined by a name (e.g. revenue) and a replacement text (the SQL fragment). To use a snippet in a query, enclose the name in square brackets: SELECT [revenue].

There are two categories of SQL snippets:

• Built-in Snippets — provided by Cluvio with pre-defined behavior.
• Custom Snippets — freely defined name and replacement text, with optional parameters.

Built-in Snippets

Built-in snippets let a SQL query access contextual information about the currently logged-in user or the dashboard displaying the report.

Snippet	Value
[CLUVIO_USER_EMAIL]	E-mail address of the logged-in user, or NULL for sharing links and scheduled e-mails.
[CLUVIO_USER_FIRSTNAME]	First name of the logged-in user, or NULL for sharing links and scheduled e-mails.
[CLUVIO_USER_LASTNAME]	Last name of the logged-in user, or NULL for sharing links and scheduled e-mails.
[CLUVIO_USER_ROLE]	'admin', 'analyst' or 'viewer' for logged-in users, or NULL for sharing links and scheduled e-mails.
[CLUVIO_DASHBOARD_NAME]	Name of the dashboard on which the report or dynamic filter is displayed.
[CLUVIO_DASHBOARD_ID]	ID of the dashboard on which the report or dynamic filter is displayed.

Example query using built-in snippets:

SELECT [CLUVIO_USER_EMAIL], [CLUVIO_USER_FIRSTNAME], [CLUVIO_USER_LASTNAME], [CLUVIO_USER_ROLE],
       [CLUVIO_DASHBOARD_NAME], [CLUVIO_DASHBOARD_ID]

Executed in a dashboard context as:

SELECT 'user@example.com', 'John', 'Doe', 'viewer',
       'Sample Dashboard', '69wx-3kz9-3opj'

Custom Snippets

Custom snippets allow you to extract any SQL fragment into a named, reusable unit.

Basic example

Snippet name: with_us_airports

Snippet text:

WITH us_airports AS (
  SELECT * FROM airports WHERE country='United States'
)

Report query:

[with_us_airports]
SELECT * FROM us_airports WHERE elevation > 1000

Executed as:

WITH us_airports AS (
  SELECT * FROM airports WHERE country='United States'
)
SELECT * FROM us_airports WHERE elevation > 1000

This makes the WITH clause reusable across many reports. Changing the snippet text (e.g. to add a country filter) immediately affects every report that uses it, without requiring individual edits.

Snippets inside Snippets

A snippet's replacement text can itself reference other snippets. The constraints are:

• The maximum chain length is 10 levels deep (A → B → C → … → J).
• Cycles are not allowed (A → B → A would cause an infinite loop and is rejected).

Nesting multiplies rather than adds, so the query that finally runs can be much larger than any of the snippets that produced it — and it is measured against the size limit after that expansion, not before. See Limits.

Parameterized Snippets

A snippet can accept parameters, making it behave like a function. Specify parameter names (comma-separated) in the Params field of the snippet dialog, then use them inside the snippet text enclosed in square brackets.

Call a parameterized snippet by passing arguments in brackets:

SELECT [in_eur(100, 'USD')]

Parameters that are declared but not passed when the snippet is called resolve to a SQL NULL value.

Limits

One figure covers all of it: 1,048,576 UTF-8 characters, which is 1 MB of ASCII text. It is a count of characters rather than bytes, and it applies to

• a snippet's text,
• a query as you write it — the same limit a report's SQL has,
• and the query after every snippet has been substituted.

The third is the one worth knowing about, because it is reached by combination rather than by any single snippet being large: a snippet referencing three snippets that each reference three more expands to nine copies of the innermost ones, and every further level of nesting multiplies again, so snippets that are individually well within the limit can exceed it together.

When that happens the query is refused rather than run. Saving it in the report editor gives the reason in full, naming the snippet being expanded at the moment the limit was reached — which is where to look, rather than at the query in front of you. On a dashboard the report shows only Query error, since the reason is of no use to a viewer and dashboards are also served over sharing links.

A consequence worth noting: because a snippet's text may itself run to the full limit, a query referencing a snippet that long can contain nothing but the reference — any other text would take it past the limit once the snippet is substituted. In practice a snippet anywhere near that size is a sign the SQL wants restructuring rather than a bigger limit.

Filter values are a separate matter and do not count towards the limit: they are substituted when the query runs, after it has been checked, so the statement that reaches your database can exceed the limit without the query being refused.

Managing Snippets

All custom snippets are managed from the SQL Snippets overview page, which shows the snippet name, description, and how many reports, filters, or alerts use each snippet. Use the search box to find a snippet by name, description, or text, click the Name header to sort, and page through longer lists with the pagination controls.

Creating and Editing

Select Add SQL Snippet to create a new snippet. Every snippet requires:

• Name — used to reference the snippet in queries as [name]. Names may only contain letters, digits, and the characters +, *, _, - (spaces and most special characters are not allowed).
• Text — the SQL fragment to substitute when the snippet is referenced (see Limits for the maximum size).

The optional Params field (comma-separated parameter names) turns the snippet into a parameterized snippet. The optional Description is shown in tooltips in the report editor and in the overview list.

The Datasource selection only affects code completion in the SQL editor — it has no bearing on where the snippet can be used at runtime.

tip

Snippets can also be created and edited from within the report editor, without leaving the query you are writing — from the Snippets Sidebar, or by extracting a piece of the query you already have (see Creating Snippets).

Actions

From the actions menu on any snippet you can:

• Edit — modify the snippet's name, text, params, description, or datasource.
• Duplicate… — create a copy with all settings pre-filled. The new name is generated automatically by appending a numeric suffix (e.g. revenue_1).
• Find Usages — search for all reports, filters, and alerts that reference this snippet. Results open in the global search.
• Delete — permanently remove the snippet.

Deleting a Snippet

Deleting a snippet that is still referenced by reports, filters, or alerts will cause those queries to fail at runtime. Use Find Usages first to check whether the snippet is in use.

Report Editor Integration

Snippets Sidebar

The SQL Snippets icon in the report editor's left icon bar opens a panel listing the organization's snippets. Each entry shows the snippet's name with its parameters and its description, and the search box narrows the list by name or snippet text. The + button in the panel header creates a new snippet.

The list leaves out snippets that do not apply to the report's datasource: with a real datasource selected, the sample snippets seeded into a new organization are hidden, and with the sample datasource selected, only those are shown.

Selecting an entry expands it, showing:

• The snippet text, so you can check what will be substituted.
• Used by — the number of reports, filters, and alerts that reference the snippet. Select it to see them. They open in a new browser tab, so the report you are editing keeps its unsaved changes.
• Usage — the reference to write in a query, parameters included.
• Add to query — inserts that reference at the cursor. For a parameterized snippet the declared parameter names are inserted with it and left selected, ready to be typed over with the arguments.

The actions menu on an expanded entry holds the same actions as the overview page's Actions menu:

• Edit Snippet — open the snippet dialog for this snippet.
• Duplicate… — open the create dialog prefilled from this snippet, named with the same numeric suffix a duplicate gets on the overview page (revenue → revenue_1). Select Save to create the copy, or Cancel to leave things as they were.
• Find Usages — the same usages the Used by count links to.
• Delete… — remove the snippet, after a confirmation.

Creating, editing, or deleting a snippet here also updates the SQL editor's auto-completion immediately, so a snippet can be used in the query as soon as it is saved.

Auto-Completion

In the report SQL editor, snippet names appear in the auto-complete suggestions. Select a suggestion to insert the snippet reference.

Snippet Tooltip

Hovering over a snippet reference in the SQL editor shows a tooltip containing the snippet text and description, so you can verify what will be substituted without leaving the editor.

Creating Snippets

To extract part of an existing query into a snippet, select the SQL text you want to extract and choose Create Snippet from the editor's expanded Run menu.

Inlining Snippets

To replace a snippet reference with its literal text — for example, as a starting point for customization — select the snippet name (with or without the surrounding brackets) and choose Inline Snippet from the editor's expanded Run menu.