Metabase SQL: A Practical Guide to Native Queries, Variables, and Filters

Metabase SQL lets you write native queries directly instead of dragging through the visual query builder. If you know SQL, you get full control over your analysis: you can write complex joins, aggregate data however you need, and reference saved questions as tables. This guide walks you through the native query editor, SQL variables, field filters, and the exact syntax Metabase accepts.

Why You Might Need SQL in Metabase

The Metabase query builder is powerful, but it hits a wall fast. If you need a complex WHERE clause, a multi-step CTE, or a self-join, the visual interface forces you to break the logic into pieces. SQL is more direct: write what you mean, send it to the database, get results. The tradeoff is that SQL requires you to know your schema and database syntax. But if you're evaluating options, SQL in Metabase gives you flexibility without leaving the tool.

When should you use SQL instead of the query builder? Use SQL when your query involves more than two joins or complex filtering logic, when you need window functions or CTEs that the builder can't express, or when you're building something you'll reuse and want to version-control it. Use the query builder when you're exploring data and don't know your schema yet, when your team has non-technical users who need to write their own queries, or when the question is simple enough to drag together in seconds.

For a 10-50 person company, the pattern is this: most teams start with the query builder, then switch to SQL for monthly reports, cohort analysis, and anything that touches multiple tables. Your analysts spend 80% of their time on the query builder and 20% on SQL. As you scale, that ratio flips.

Getting Started: Writing Your First SQL Query

Open Metabase and click + New in the top nav. Select SQL query. You'll see the native query editor: a text box for SQL on the left, a table showing your databases on the right. Select your database from the dropdown at the top of the editor.

Type a simple query to start:

SELECT * FROM users LIMIT 10

Press Ctrl+Enter (or Cmd+Enter on Mac) to run it. Your results appear below the editor. If the query errors, you'll see the database error message: read it carefully. Most errors are typos in table names or missing WHERE clauses.

Once you've confirmed your tables exist, try a basic aggregation:

SELECT 
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS user_count
FROM users
GROUP BY 1
ORDER BY 1 DESC

This groups users by signup month. Metabase visualizes it as a line chart automatically. If you want a different chart type, click Visualization at the bottom and choose from bar, table, number, scatter, line, or pivot table.

A few things to know about the editor: Metabase sends your SQL to the database as-is, so you're not learning Metabase SQL, you're learning your database's SQL. PostgreSQL and MySQL have different date functions. BigQuery doesn't use DATE_TRUNC. Always check your database docs. Metabase can't run DDL (CREATE TABLE, DROP, ALTER), transactions, or multi-statement queries. If you try, you'll get an error.

Mastering SQL Variables: Text, Numbers, and Dates

A variable is a placeholder in your SQL that becomes a filter widget in Metabase. Write {{variable_name}} and Metabase auto-generates an input field above your results. This is how you turn a static query into a reusable question.

Start simple:

SELECT * FROM users
WHERE country = {{selected_country}}

When you save this, Metabase asks you to define selected_country as a variable. Choose 'Text' or 'String'. Now when you run the query, you get an input box at the top. Type 'USA' and the query filters. That's it.

Variables work with any SQL type:

SELECT * FROM orders
WHERE created_at >= {{start_date}}
  AND total > {{minimum_amount}}

Define start_date as 'Date' and minimum_amount as 'Number'. Metabase renders a date picker and a number input. Users can change both.

Common mistake: forgetting to quote text variables. Write WHERE country = {{country}}, not WHERE country = '{{country}}'. Metabase doesn't auto-quote. If you want quotes, add them: WHERE country = '{{country}}'.

Another mistake: NULL handling. If a user leaves a variable blank, Metabase sends it as NULL. Write WHERE age > {{min_age}} OR {{min_age}} IS NULL if you want to make a variable optional (though the optional filter syntax with [[...]] is cleaner). Variables are saved with the question. If you change a variable's type or name, you have to re-save.

Building Smart Filters with Field Filters

Field filters are a special kind of variable that auto-discover your schema. Instead of writing {{country}}, you use [[field_name]] and Metabase infers the table, column, and data type. This is faster and less error-prone.

Write:

SELECT * FROM orders
WHERE [[orders.created_at]] > '2024-01-01'

Metabase converts [[orders.created_at]] into a date filter widget. Users see a calendar and can pick ranges. The syntax [[table.column]] tells Metabase exactly where to apply the filter.

Field filters are smart about data types. If the column is a date, you get a date picker with presets like 'Last 30 days' or 'This quarter'. If it's a number, you get number sliders. If it's categorical (like country or product_id), you get a dropdown or multi-select.

The advantage: you don't write the WHERE clause condition. Metabase writes it for you based on the column type. This means fewer syntax errors and faster query building.

Limitation: field filters only work on direct database columns, not on computed fields. If you have a CTE, you can't use [[]] on its output. For those cases, fall back to {{variable}} and write the WHERE clause yourself.

Example with multiple field filters:

SELECT 
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
WHERE [[orders.customer_id]] = 5
  AND [[orders.created_at]] > '2023-01-01'
GROUP BY 1

Here, customer_id gets a text/number input and created_at gets a date filter. Both are optional by default.

Making Filters Optional and Conditional

By default, Metabase requires a variable to have a value. If you run the query without filling it, you get an error. Use double brackets [[...]] to make a filter optional.

SELECT * FROM orders
WHERE true
  [[AND customer_id = {{customer_id}}]]
  [[AND region = {{region}}]]

The WHERE true part looks weird, but it's the trick: you start with a condition that's always true, then add optional conditions. If the user skips both filters, the query runs as-is. If they fill customer_id, that filter applies. The brackets [[...]] tell Metabase 'only include this if the variable is not null'.

Another approach for optional conditions:

SELECT * FROM orders
WHERE ({{start_date}} IS NULL OR created_at >= {{start_date}})
  AND ({{end_date}} IS NULL OR created_at <= {{end_date}})

If the user leaves start_date blank, the first condition becomes (NULL IS NULL) which is true, and the date filter doesn't apply. This works but is verbose.

The double-bracket syntax is cleaner and is Metabase-specific. Use it when you have optional field filters. Use the NULL check when you're building conditional logic in text variables. One gotcha: optional filters can hide logic errors. A user might think they're filtering, but the query is actually returning all rows because they forgot to fill a field.

Saving, Organizing, and Sharing SQL with Snippets

Once you've written a query, click Save to add it to your Metabase instance. You can organize questions into collections and share them with your team. But if you write the same WHERE clause or JOIN over and over, use SQL snippets instead.

A snippet is a chunk of SQL you can reuse across multiple queries. Click Snippet in the left sidebar of the SQL editor. Create a snippet for a common table:

-- customers_with_lifetime_value.sql
SELECT 
  id,
  email,
  SUM(orders.amount) AS ltv
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY 1, 2

Now in any other query, type {{ using snippets.customers_with_lifetime_value }} to insert it. You can also embed variables in snippets, so you don't repeat logic. When you're hosting Metabase on Opsily's managed platform, snippets sync across your team instantly, so everyone uses the same building blocks.

Snippets are powerful for teams. Instead of copying SQL into Slack, everyone uses the same source of truth. If you fix a bug in the snippet, all queries using it get the fix automatically. When you're ready to set up a shared Metabase instance, Opsily's hosted Metabase platform makes team collaboration simple.

Saved questions are different. A saved question is a complete query with filters and visualization. You can reference another saved question inside a new query using {{#question_id}} or {{#question_name}}, which we'll cover next. Use questions for finished analysis you want to share. Use snippets for building blocks.

Referencing Models and Saved Questions in SQL

Metabase lets you use the results of one query as a table in another. If you've already built a 'Customer Lifetime Value' question, write:

SELECT * FROM {{#1-customer-lifetime-value}}

The syntax {{#id-name}} references a saved question by its ID and name. Metabase treats it like a table: you can filter it, aggregate it, join it to other tables. This is powerful for modular analysis.

Example:

SELECT 
  customer_id,
  ltv,
  ROW_NUMBER() OVER (ORDER BY ltv DESC) AS rank
FROM {{#1-customer-lifetime-value}}
WHERE ltv > 1000
ORDER BY rank LIMIT 100

The advantage: you've defined lifetime value once, now you use it everywhere. If the definition changes, all dependent queries update automatically. This is version control for analysis. For teams building embedded analytics into their products, this modular approach keeps your analysis organized and maintainable.

Find the ID by opening the saved question, clicking the info icon, and copying the URL. The ID is in the URL.

Models are Metabase's concept for a 'curated table'. A model is a saved question marked as a data source. You can reference models the same way. The difference: models appear in the schema browser, so non-SQL users can use them in the visual query builder too.

Limitation: you can't reference a question that uses {{variables}}. The referenced question has to be self-contained. Work around this by saving two versions: one parameterized for exploration, one static for reuse.

Best Practices, Optimization, and Troubleshooting

Database-specific syntax is the main gotcha. Metabase SQL is really your database's SQL. PostgreSQL, MySQL, BigQuery, and Snowflake have different date functions, string functions, and window function syntax. Always check your database documentation.

For example, date truncation:

• PostgreSQL: DATE_TRUNC('month', created_at)
• MySQL: DATE_FORMAT(created_at, '%Y-%m-01')
• BigQuery: DATE_TRUNC(created_at, MONTH)

When your query errors, read the database error message. It tells you exactly what's wrong.

Performance tips: avoid SELECT *. Name the columns you need. This reduces data transfer and makes Metabase faster. Use CTEs (WITH clauses) to break complex logic into steps. It's easier to debug and often faster. Filter early: push WHERE clauses to sub-queries. Don't pull all rows and filter in Metabase. Use LIMIT during development. Don't run a full table scan while you're testing.

Example with CTE:

WITH recent_orders AS (
  SELECT * FROM orders
  WHERE created_at >= NOW() - INTERVAL 30 DAY
),
by_customer AS (
  SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
  FROM recent_orders
  GROUP BY 1
)
SELECT * FROM by_customer
WHERE total_spent > 100

This is more readable than nesting sub-queries and often runs faster because the database can optimize each CTE independently.

Common errors and fixes: 'Table or column not found' means a typo in table or column name. Check the schema browser. 'Invalid operator' means you used a database function that doesn't exist. Check the docs. 'Syntax error near' means a missing comma or quotes. Copy the error line and reread it.

Metabase Metabot is a new feature that can help with SQL generation. You can describe what you want in English and it generates SQL for you. It's not perfect, but for simple queries it saves time. Use it as a starting point, then review the SQL before running it.

Limitations Metabase enforces: you can't run CREATE TABLE, INSERT, UPDATE, DROP, or any DDL. You can't run multiple statements separated by semicolon. You can't create transactions. These restrictions exist for safety.

Frequently Asked Questions

Can I reference saved questions in a SQL query?

Yes. Use {{#question_id-name}} syntax. Metabase treats the saved question's results as a table. You can filter it, aggregate it, and join it just like a database table. The referenced question must be self-contained (no variables).

How do I make a filter optional in Metabase SQL?

Use double brackets: [[AND customer_id = {{customer_id}}]]. Metabase only includes the filter if the variable is not null. Alternatively, use a NULL check: ({{variable}} IS NULL OR column = {{variable}}).

What's the difference between {{variable}} and [[field_filter]]?

{{variable}} is a text placeholder you fill in manually. [[field_filter]] auto-discovers your schema and generates the appropriate filter widget (date picker, dropdown, etc.). Field filters are smarter and less error-prone, but only work on direct database columns.

Can I use CTEs in Metabase SQL?

Yes. Write WITH clauses as you normally would. Metabase sends the entire query to your database as-is. CTEs work in PostgreSQL, MySQL, BigQuery, Snowflake, and most modern databases.

Why doesn't my complex join work in Metabase?

Check your join syntax against your database's documentation. Metabase doesn't validate joins; the database does. If you get a syntax error, the join might be written wrong. Also check that table aliases are consistent.

How do I improve query speed in Metabase?

Filter early (push WHERE clauses to sub-queries), avoid SELECT *, use LIMIT while testing, and check if you have database indexes on join columns. If a query is slow, run it directly in your database's query tool to see if it's Metabase or the database.

Does Metabase support multi-statement SQL?

No. You can't run multiple statements separated by semicolons. Write one query per native question. If you need multiple steps, use CTEs or split into separate questions and reference them.

The Bottom Line

Metabase SQL is your database's SQL with a few extra features (variables, field filters, snippets, and question references). Master the native query editor by starting simple, then add filters and variables as you build. SQL gives you flexibility that the visual query builder can't match, but it requires you to know your schema and database syntax. Get started with Metabase on Opsily's managed hosting today.