Menu Close

SQL Snippets: reuse and share SQL code

Jul 22, 2020 by The Metabase Team

SQL Snippets are one of our most anticipated features among our power users. If you’re just tuning in, here’s why: you can now save SQL code as a snippet, and then you or other SQL authors can refer to that snippet in different SQL queries. If you ever need to update that code, you can edit the snippet, and those changes will propagate to all questions that use that snippet. Any user with SQL editor permissions for at least one database can use, create, and edit snippets.

Highlight SQL code and save it as a snippet.
Highlight SQL code and save it as a snippet.

Snippets are a simple but powerful feature, so let’s unpack them. We’ll also cover an upcoming Enterprise feature: Snippet Folders. These folders and their permissions will help teams keep their snippets organized, and they’ll be shipping with our upcoming 1.36 release of Metabase Enterprise Edition.

First, a simple example

Take this short join statement:

orders AS o
LEFT JOIN products AS p
ON o.product_id = p.id

You can highlight that code, name the snippet “Orders and products”, add a helpful description, and summon that snippet in any SQL query using the snippet: tag, like so:

SELECT *
FROM {{snippet: Orders and products}}

That’s all it takes to create and use a snippet. Now let’s dig in to why they’re so useful.

Why use SQL Snippets?

There are three main use cases for SQL snippets:

  • Standardization. How does your organization define a popular product? Is it by number of units sold? Or by reviews with an average rating greater than 4? You can define those qualifications for a popular product and codify them in a SQL snippet, {{snippet: popular products}}, and have that code populate in every question that uses that snippet. If down the line this definition needs to change, simply update the snippet’s SQL, and the change will propagate to all questions that use that snippet. Similar to how segments (a named filter or set of filters) and metrics (a named computation) can standardize analytics in your organization, SQL snippets offer another way to ensure correctness and consistency across teams.
  • Sloth (a.k.a. efficiency). Find yourself copying and pasting SQL code often? Don’t want to bother remembering which foreign keys map to which tables? Write that complicated join once, save it as a snippet, and summon the snippet as needed.
  • Education. Snippets can level up folks who are new to SQL (or even experienced analysts) by exposing them to your organization’s “canonical SQL,” or to more efficient or more complex queries. Reading, copying, and building upon quality code is one of the best ways to develop skills. It can also save your organization time: people can copy a snippet’s code, modify it to obtain different results, and save that code as a new snippet for others to use.

SQL snippets in action

Snippet sidebar

Metabase’s SQL editor has a sidebar for viewing, creating, and editing snippets, which you can access by mousing over to the right side of the editor and clicking on the snippet icon (three uneven horizontal lines).

Access the Snippets sidebar by clicking on the snippet icon to the right of the SQL editor.
Access the Snippets sidebar by clicking on the snippet icon to the right of the SQL editor.

Once you create snippets, you can insert them in the editor using the snippet tag: {{snippet: Orders and products}}, or by selecting them from the snippet sidebar:

Previewing and inserting a snippet from the Snippets sidebar.
Previewing and inserting a snippet from the Snippets sidebar.

Clicking on a snippet’s name in the sidebar will insert it at your cursor’s current location in the query.

Editing snippets

Updating the SQL code of a snippet will update every query that uses that snippet, so make sure to test your code before saving it to avoid breaking people’s questions. Of course, if you make a mistake and cause chaos, you can simply correct the code, save the snippet again, and restore order to the universe.

Note that if you use aliases in your snippet (e.g., orders AS o), you’ll need to use those aliases outside of the snippet to refer to that data in your query.

Archiving snippets

You can archive a snippet via the Snippet Edit modal.
You can archive a snippet via the Snippet Edit modal.

Snippets are indestructible; you cannot delete them. You can, however, archive (and unarchive) snippets. Archiving a snippet will 1) keep your Snippets sidebar tidy, as archived snippets do not populate the sidebar, and 2) prevent the archived snippet from appearing in the typeahead results in the SQL editor. I.e., the archived snippet won’t show up as an autocomplete option when you type {{snippet: in the SQL editor.

Archiving a snippet does not affect questions that use that snippet, so you can safely archive a snippet without breaking anyone’s questions.

Snippet folders in the Enterprise Edition

The upcoming Enterprise Edition of Metabase, 1.36, will include snippet folders and permissions to help keep large numbers of snippets organized.

Snippet folders and permissions

Similar to a file system, admins can add snippets to folders, and place folders within folders.

Saving a snippet to a folder.
Saving a snippet to a folder.

Additionally, admins can add permissions to those folders, which control who can view or edit snippets in those folders:

Changing a folder's permissions.
Changing a folder's permissions.

Permissions are granted to groups for each folder, with three permission levels:

  • Edit access. View, edit, archive/unarchive snippets.
  • View access. View and run snippets.
  • No access. Cannot view snippets in the sidebar and autocomplete menus. People without access can, however, run queries that include these snippets.

Archiving and unarchiving snippets has no affect on permissions, though you’ll need edit access to the folder to archive and unarchive its snippets.

Use folders to keep snippets organized and standardized among and between teams, as well as to keep sensitive snippets visible only to the appropriate groups.

Go deeper

For even more on SQL snippets, check out our detailed documentation: