Write custom SQL

When you explore your data platform using the SQL editor, you can preview SQL queries and create workbooks, data models, and datasets directly from SQL.

This document describes how to do the following:

• Create a workbook from SQL
• Create a SQL workbook element
• Reference existing Sigma workbook elements
• Use shortcuts, including:
  • Toggle within SQL-based workbook data elements to an inline SQL editor with side-by-side display.
  • Auto-format your SQL query.
  • Use find and replace within your SQL query.

Requirements

• To use this feature, you must be assigned an account type with the Write SQL permission enabled.
• To run custom SQL, you must be granted Can use access for an entire connection. See Manage access to data and connections.
• The SQL editor only appears if you have connection-level access to at least one connection in your organization.

Create a workbook from SQL

To create a workbook from a SQL query, do the following:

1. Open Sigma Home.

2. In the navigation panel, click Create New, then select Write SQL to open the SQL editor.

   

3. In the side panel, click Select a Connection and select the connection you want to query.

4. In the query editor, enter your SQL query. Sigma provides autocomplete suggestions to guide you.

   

   💡

   You can reference workbook controls in your SQL by wrapping the control ID in curly brackets. See Reference workbook controls.

5. To run your SQL query, click Run, or use the keyboard shortcuts ⌘ + return on a Mac or ctrl + enter on a PC.

6. To save your unpublished workbook (exploration) as a workbook, click Save As.

💡

To convert your SQL query to a data model, select More to open the element menu, then select Advanced options > Create data model.

Create a SQL workbook element

To add a SQL element to an existing workbook and supplement existing analysis, do the following:

1. Open a workbook for editing.

2. In the add element bar, select the type of element you want to add, then choose a specific element type: Data > Table, Data > Pivot Table or Chart and any supported chart type.

3. In the Select source modal, choose the SQL option at the bottom.

   

4. Select the connection you want to query.

   An element appears in your workbook, prompting you to enter a SELECT statement to query the connection.

5. In the query editor, enter your custom SQL. Sigma provides autocomplete suggestions to guide you.

   

   💡

   You can reference workbook controls in your SQL by wrapping the control ID in curly brackets. See Reference workbook controls.

Reference existing Sigma workbook elements

🚩

This documentation describes a public beta feature and is under construction. This page should not be considered part of our published documentation until this notice, and the corresponding Beta flag on the feature in the Sigma service, are removed. As with any beta feature, the feature discussed below is subject to quick, iterative changes. The latest experience in the Sigma service might differ from the contents of this document.

Beta features are subject to the Beta features disclaimer.

To reference other data elements in your workbook in your SQL query, including other custom SQL elements, use the sigma_element() syntax.

For example, to query a data element in your workbook titled Fiscal Year Forecast and filter based on the values of the Revenue column in the element, write a SQL statement like the following:

SELECT * FROM sigma_element('Fiscal Year Forecast')
   WHERE "Revenue" > 100000

Syntax and usage notes

Use the following syntax to reference a data element in the same workbook:

sigma_element('Element title')

When using this syntax, consider the following usage notes:

• You must use the exact element title in your SQL statement.
• The element title that you reference must be a string.
• Column names must be identifiers. For example, if your column name contains spaces and you run SQL with Snowflake, double quote the column names when referencing them in your SQL statement. If you run SQL with BigQuery, wrap column names with backticks (`).

Limitations

• You cannot use this syntax in a data model.
• Using this syntax with AzureSQL connections is not yet supported.

Shortcuts

Some shortcuts exist to make working with SQL in Sigma easier.

Toggle SQL editor in the element menu

When a workbook is published, the SQL element displays only the results. When you edit or explore the workbook, you can toggle the SQL editor to show the SQL query that generates the results.

Click Toggle SQL Editor to switch back and forth between the data element and the SQL query editor.

Maximize the SQL element

To maximize the SQL element in your workbook canvas and make it easier to write a long complex SQL query, press the space bar or click Maximize element to expand the element to full screen mode.

Format SQL

To quickly format long blocks of SQL text, click  Format SQL.

Find and replace

To search within your SQL query, and optionally replace instances of the searched term, click Search SQL or use the keyboard shortcut ⌘ + F.

Access custom SQL from the lineage view

View and edit custom SQL from the lineage view of a workbook or data model. Copy the full query to your clipboard or click Edit SQL to go to the inline SQL editor for the element.

For more details about working with lineage, see View workbook and data model data lineage.

View the query history

When you use the SQL editor, you can access the history of the queries recently run against the current connection.

To access this history, click the DB tab in the side panel, then select the History tab.

To see the query history for all elements in a specific workbook or data model, see Examine workbook queries.

View table previews

When writing your query, you can use the side panel to explore tables in your data platform. You can navigate to a specific table and select Preview to open a preview of the columns and rows in the table, helping you decide whether to add that table to your existing SQL query.

For a given table, select More and choose Place name in SQL or Place select statement in SQL to easily reference a new table in your SQL query.

Examples

For more advanced custom SQL cases, refer to these examples.

Change output based on a user attribute

If you have user attributes defined in your organization, you can reference an attribute to limit the results returned from a SQL query based on the value of the user attribute for the current user.

For example, for the example Plugs Electronics data, return results only with the store region that the current user can access based on the store_region user attribute assigned to them:

SELECT
  *
FROM
  EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
WHERE 
  {{system::CurrentUserAttributeText::store_region}} = STORE_REGION

As another example, return results filtered by the customer_name that the current user has access to view, based on the value of the organization_name user attribute assigned to them:

SELECT 
  * 
FROM 
  test.orders 
WHERE 
  customer_name = {{system::CurrentUserAttributeText::organization_name}}

For more details about the function syntax, see CurrentUserAttributeText. For more details about user attributes and assignment, see User Attributes.

Return output for a specific date range control

For an example returning rows only when a date column matches the value specified in a date range control, refer to the example SQL in Reference workbook control values in SQL statements.

Return rows depending on the value of a multi-select control

For an example returning rows depending on the value of a multi-select list control, refer to the example SQL in Reference workbook control values in SQL statements.

Return rows based on a schema text selector

If you have a single-select or text control called schema-param that has a valid selection (a schema in your connected CDW), and the STATIONS table exists in the specified schema, the following example SQL returns all columns from the STATIONS column in the specified schema:

SELECT *
FROM {{#raw schema-param}}.STATIONS

🚩

If you use the #raw configuration value, row-level security can be bypassed, creating a security vulnerability.

When using this parameter to swap table, schema, or database names, the column names referenced in the SQL must be identical.