Appearance
SQL block
A SQL block on a doc page lets you edit and run SQL, view run results, view column statistics, and create charts. Add one from a doc page by typing / and selecting SQL block.
Key features
- Editing and running SQL
- Autocomplete, highlighting, and formatting
- Table search and reference insertion via
/ - References to other SQL blocks (SQL chains) and expanding/overriding parameters
- Adding a chart
- Adding column stats
- Code snippets
- Referencing saved queries, and creating saved queries with Save SQL (see Query)
- Launching Explorer from a SQL run result
- Column header operations on results
- Display modes
- Cache
References
In the SQL editor, type / to reference the following.
| Reference target | Insert example | Description |
|---|---|---|
| Another SQL block | /sql/ | Expands the referenced SQL as a subquery. |
| Table | /table/ | Lets you select a table from the catalog. |
| Parameter | /param/ | Embeds a parameter's value. |
| Saved query | /query/ | Embeds the SQL at the time of insertion. When the source is updated, select Update embedded query to apply the change. See Query for details. |
| Inline table | /inline-table/ | Expands an inline table in the notebook at run time. |
You can also see reference relationships in the notebook's dependency diagram. Open Show dependencies from a SQL block's menu.
Referencing SQL blocks
When you reference another SQL block, the referenced SQL is expanded as a subquery at run time. This lets you factor out and reuse common extraction logic, or build up a query while checking intermediate results block by block.
- You can reference SQL blocks on the same page, as well as SQL blocks on other doc pages within the same notebook.
- References to other pages are shown as
Page name.SQL block name. - Circular references result in an error.
Inserting
- In the SQL editor, type
/sql/, or choose Reference another SQL block's SQL from the/suggestions. - Select the SQL block you want to reference.
Expansion settings
Hovering over a reference chip shows a popup where Settings lets you change the following.
| Item | Description |
|---|---|
| Don't wrap in parentheses | Off by default. When off, the expanded result is wrapped in ( ). Turning it on omits the wrapping. |
| Add alias at the end | Only selectable when Don't wrap in parentheses is off. Appends AS and the SQL block name at the end of the expanded result. |
Expanding and overriding parameters
If the referenced SQL block contains a parameter reference, the value used for expansion is as follows.
| Parameter used by the reference target | Value used for expansion |
|---|---|
| Notebook-wide | The value held by the referencing source (the page containing this SQL block). |
| Page-specific | If the referencing page has no definition with the same name, the value from the reference target's page. |
If you want to decouple the parameter dependency on the referencing page, use Override internal parameter references in the reference chip's popup.
| Method | Description |
|---|---|
| Override with another parameter | Replaces the value with another parameter of the same type. |
| Override with a fixed value | Specifies a fixed value. |
| Don't override | Expands according to the rules above. |
You can override the same way when referencing from a grid page's SQL run result or chart. See Overriding parameters for details.
Adding a chart
Once SQL runs successfully, select Chart in the run result header to open the chart wizard. Configure dimensions, metrics, and so on to add a chart directly below the run result.
- See Chart for details on chart types and settings.
- You can also add a chart from Explorer.
- An added chart can also be placed on a grid page using Add to grid page.
- You can also insert a Block chart via
/to reference the run result of a SQL block on the same page (see Doc page).
Adding column stats
Select Column stats in the run result header to add the distribution and summary statistics of a result column. Selecting a target column issues a separate SQL query and adds a column stats block.
The items shown vary depending on the data type.
| Item | Description |
|---|---|
| Data type | The column's data type. |
| Number of NULL values | The count and percentage of NULLs. |
| Unique count* | The number of unique values. |
| Max / Min / Average | Shown for numeric, time, and similar types. |
| Percentiles* | The 25th, 50th, and 75th percentiles. |
| Character length* | The distribution of string lengths. |
| Frequency* | The most frequently occurring values. |
| Histogram* | The distribution for numeric, time, and similar types. |
Values marked with * are calculated using an approximation function.
Available connections are BigQuery, Snowflake, and Databricks. Not available for Redshift.
If the SQL content or parameters change, you need to re-run the stats.
Select a cell in the result table to show its column name, data type, and complete value in the preview pane. Selecting multiple cells, a row, or a column shows statistics or row data for that selection.
Column header operations on results
Select a column header in the result table to access the following operations. Sorting and filtering apply only to the displayed rows and don't re-run SQL. You can combine different types of filters on the same column. These operations aren't synced to other users.
| Operation | Description |
|---|---|
| Show column statistics | Selects all cells in the column and shows the selection statistics in the preview pane: Number of nulls and Unique count, plus Sum value / Average value / Minimum value / Maximum value for numeric columns. |
| Sort | Sorts the displayed rows by Asc or Desc. |
| Filter by value / Exclude by value | Select candidate values, then select Apply to narrow down the displayed rows. |
| Filter by condition | Narrows down rows with a condition such as Contains, Equals, Does not equal, Between, or Greater than. |
| Filter NULL | Narrows down rows with IS NULL or IS NOT NULL. |
| Pin to left / Pin to right | Pins the column to either edge. Pinned columns stay visible while scrolling horizontally. |
| Column width | Use Fit to values or Reset to initial width for the selected column. Use Fit all columns to values or Reset all columns to initial widths for every column. |
You can reorder columns by dragging their headers.
Display mode
The icon at the top left of a SQL block lets you switch the display mode. This switch isn't synced to other users. You can change the default setting from the SQL block menu ⋯ (horizontal three-dot icon) > Default display.
| Mode | Description |
|---|---|
| Show all | Shows SQL, the run result, and the chart. |
| Show result only | Shows the run result and the chart. |
| Hide all | Hides SQL, the run result, and the chart. |
Save SQL
Use Save SQL in the SQL block's top-right menu to register the block's content as a saved query.
- References to other SQL blocks or saved queries are saved fully expanded.
- Parameters are saved with their value at that point substituted in.
Parameter embedding and references to other queries aren't available on the saved query side.
Code snippets
Use Manage code snippets in the SQL editor to create and edit snippets for autocomplete. Code snippet autocomplete is enabled by default. You can disable it by turning off Enable code snippet autocomplete in SQL editor preferences.
| Item | Description |
|---|---|
| Personal snippet / Shared snippet | Choose whether it's personal (per user) or shared across the workspace. |
| Autocomplete key | Typing this key lets you call up the snippet from autocomplete suggestions. |
| Code snippet to insert | The text inserted when the suggestion is selected. You can specify the cursor position after insertion with $$. |
| Display name / description | Optional. |
Cache
A SQL block's run result is cached for display purposes. This reuses the past successful result of the same SQL; the job itself is retained for 24 hours by default.
- Up to 1,000 rows of run result are retained for display.
- When editing a notebook, running a new job is skipped by default if the cache is within 24 hours, and the cache is used instead.
- To run without using the cache, use a SQL block's Run with latest data, or the notebook's Run all with latest data.
The handling differs by access path, as follows.
| Path | Display cache | Overriding the reuse period |
|---|---|---|
| Editing a notebook | 24 hours by default. Can be bypassed with Run with latest data / Run all with latest data. | None. |
| Viewing a report | Same as above. | Report settings' Cache duration (24 hours by default; 0 disables it; maximum 24 hours, in 10-minute increments). |
| Signed embed | Same as above. | cache_max_age at token issuance (86400 seconds by default and at maximum; 0 disables it). See Signed embed for details. |
Warehouse-side caching (such as the DWH's native cache for identical SQL) can't be controlled from the connection settings.
Constraints
Column suggestions and similar are available only when you use a table reference or a reference to another SQL block. They aren't suggested for string table IDs or saved query references.