Write custom SQL

When you explore your data warehouse using the SQL Runner, you can preview SQL queries and  create workbooks and datasets directly from SQL. 

In this article, we demonstrate how you can create a new workbook or dataset. You can also create custom SQL-based workbook data elements within an existing workbook. Both flows use the SQL Runner. You are also able to reference your existing Sigma datasets and workbook elements within your SQL. 

Contents

• Requirements
• Limitations and warnings
• Create a workbook element or dataset
  • Reference existing Sigma datasets
  • Reference existing Sigma workbooks
  • Reference parameters
  • Reference multi-select list parameters
• Format SQL
• Query history
• Examples

Requirements

• To use this feature, you must be a Creator or be assigned a custom account type with permission to write custom SQL.
• To run custom SQL, you must have the Can use data permission for the entire connection; see Data permissions. 
• The SQL Runner will only appear if you have connection level access to at least one connection in your organization.
• To reference existing Sigma datasets and workbook elements within your SQL, you must have Write Access configured on your connection.

Limitations and warnings

• When you change the parameter value, ensure that the tables use the same schema. 
• When you use #raw parameter value, you create a security vulnerability by enabling a possible bypass of row level security in the worksheet.

Create a workbook element or dataset

1. Go to your Sigma Home page.
2. In the navigation panel, click Create New, then select Write SQL to open the SQL runner.
   
3. In the side panel, click the Select a Connection dropdown field and select the connection you want to query.
   
4. In the query editor, enter your custom SQL. Sigma automatically provides autocomplete suggestions to guide you.
   
   

   Note: You can reference Sigma parameters in your SQL by wrapping the parameter's name in curly brackets: {{my_parameter_name}}

   If the parameter's value is being output with single quotation marks, you can remove these quotation marks by prepending the special keyword "#raw" before its name:
   {{#raw my_parameter_name}}
   If the parameter is from a "Date range" control, you can extract its start and end date:
   {{my_parameter_name}}:start and {{my_parameter_name}}:end
   

5. To run your SQL query, click the blue Run button.
   Keyboard shortcuts: CTRL-Enter on a PC or CMD-Enter on a Mac.
6. To explore your query in a workbook, click the Explore button the top right corner of the page. 
   OR
   To create a dataset, click the dropdown icon next to Explore. Then click Create Dataset.

Reference existing Sigma datasets

You can reference your existing Sigma dataset in your SQL by using its fully qualified name as a View inside the warehouse. This will be a combination of its Location and its Dataset view name.

SELECT * FROM <location>.<dataset_warehouse_view_name>

You can find these particulars within the dataset's information panel here:

Example: 

SELECT * FROM SIGMASUPPORT.WRITE_BACK.VIEW_MY_SIGMA_DATASET

To reference individual columns from your Sigma dataset in your SQL, wrap the column name in double quotes.

Example: 

SELECT "Customer Id" FROM SIGMASUPPORT.MATERIALIZATION.VIEW_NEW_DATASET_FROM_SQL_A4438843B7604999A324C84265EE695E

Reference existing Sigma workbook elements

You can reference your existing Sigma workbook element in your SQL by creating a Warehouse View of your element and using the path of the View as the query's source table in SQL Runner. A Warehouse View transforms your Published workbook element into a View inside the warehouse. The path of the Warehouse View comprises of its Location and Workbook Warehouse View name.

You can find these particulars in the View warehouse view info window on the top right corner of the element tile:

Example:

select * from SAMPLE_CONNECTION_PRODUCTION.ORG_S_DEEFB10CD27D4AAE9D717DB0F0826FC7.NEW_LINKED_INPUT_TABLE

Reference parameters

To reference specific parameters in SQL, use the name of the parameter in doubled curly brackets, ({{ }}). For example, {{my_parameter_name}}.

If the parameter's value is being output with single quotation marks, you can remove these quotation marks by prepending the special keyword "#raw" before its name:
{{#raw my_parameter_name}}
If the parameter is from a "Date range" control, you can extract its start and end date:
{{my_parameter_name}}:start and {{my_parameter_name}}:end

Reference multi-select list parameters

Multi-select List controls pass the multi-selected data as an array. 

For more information see the community articles:

Multi-select Parameters in Workbooks (hand-written SQL source) (Community)

Multi-select Parameters in Workbooks (Calculated Fields) (Community)

Format SQL

Use the format SQL icon button to quickly format long blocks of SQL text.

Query history

When you are using the SQL Runner, you will have access to a history of the queries you've recently run against your current connection. To access this history, click the ‘History’ tab in the left hand panel.

View multiple tables

When writing your query, you can still use the left side menu to explore data tables. You can switch between the Table Preview and SQL results, letting you preview what columns you may want to use when joining data tables.‍

Examples

SELECT * FROM test.{{#raw system::CurrentUserAttributeText::schema_name}}.orders

• The above will switch schemas based on the user if there is a schema_name user attribute. See more about this use-case here.

SELECT * FROM table
WHERE
CASE WHEN date({{Date-Range-Parameter}}:start) IS NULL
THEN DATE <= date({{Date-Range-Parameter}}:end)
WHEN date({{Date-Range-Parameter}}:end) IS NULL
THEN DATE >= date({{Date-Range-Parameter}}:start)
ELSE DATE BETWEEN date({{Date-Range-Parameter}}:start) AND date({{Date-Range-Parameter}}:end)
END

• The above will return all rows where the [DATE] column meets the specifications of the Date-Range-Parameter. Please note that the exact syntax may change depending on how your particular warehouse interprets the parameter, that is:

  • In Snowflake, the parameter is a VARIANT, so you should use the following when extracting the start date of the date range parameter in custom SQL:
    to_timestamp({{Date-Range-Parameter}}:start)

  • In BigQuery or Databricks, the parameter is a STRUCT, so you should use
    {{Date-Range-Parameter}}.start when extracting the start date from the parameter in custom SQL.

  • In Redshift, the parameter is a SUPER, so you should use
    select date_range.start start from (select {{Date-Range-Parameter}} date_range) to extract the parameter start date.

  • In Postgres, the parameter is a JSONB, so you should use
    ({{Date-Range-Parameter}}->>‘start’)::timestamptz when extracting the start date from the date range parameter in custom SQL.

SELECT * 
FROM EXAMPLES.BIKES.STATIONS 
WHERE 
CASE WHEN 
LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{City}},',') ) = 0
THEN True 
ELSE CITY in {{City}} END

• Assuming there's a multi-select parameter called City, and that this is run on a Snowflake connection, this query will return all rows where the [CITY] column value is listed in the City parameter selection (or, when no City parameter selection is specified, it will return all rows).

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

• Assuming there's a single-select/text parameter called schema-param (with a valid selection), and the STATIONS table is present in the specified schema, this will return all columns from the STATIONS table in the specified schema.

Related resources

• Keyboard shortcuts in Sigma