Building custom SQL queries in Metabase for advanced users

If you’ve outgrown Metabase’s drag-and-drop query builder but still want fast, flexible dashboards, it’s time to write your own SQL. This guide is for folks who know SQL basics, but want to get serious about building custom queries in Metabase. We’ll cover the workflow, gotchas, and some tricks Metabase doesn’t advertise.

Skip the fluff—here’s how to get real work done.

1. When (and Why) to Use Custom SQL in Metabase

Metabase’s visual tools are great for quick charts. But the second you need:

  • Subqueries or window functions
  • Custom date logic
  • Complex joins
  • CTEs (Common Table Expressions)
  • Variables (think: dynamic filters)

...the point-and-click interface falls over. SQL is the only way to get there. Don’t bother wrangling workarounds—just open SQL and type what you mean.

Pro tip: If you’re redoing the same “group by week, then filter top N” voodoo every time, save the logic as a SQL question. Build dashboards from there.

2. Getting Set Up: The SQL Editor in Metabase

To run custom SQL:

  1. Click Ask a question.
  2. Choose Native query (that’s Metabase-speak for “SQL”).
  3. Pick your database.
  4. You’ll see a blank editor. Start typing SQL.

That’s it. The editor is basic—no autocomplete, minimal syntax highlighting, and no query history. If you’re used to DataGrip or DBeaver, this will feel barebones.

Real talk: Write gnarly queries elsewhere (your favorite SQL IDE), then paste into Metabase. Saves headaches and typos.

3. Working with Variables and Filters

Metabase lets you add {{variables}} to your SQL for dynamic filtering. This is handy for dashboards or sharing questions with less technical teammates.

How to add a variable:

sql SELECT * FROM orders WHERE created_at >= {{start_date}} AND created_at < {{end_date}}

Metabase will prompt you to pick these dates in the UI.

Types of variables: - Field filters: Tied to actual table columns. Lets Metabase build a dropdown or date picker. - Text/Number variables: Simple text or number inputs.

What to watch out for: - Variables can’t change table or column names—just values. - You can’t use variables for everything; no dynamic pivoting or schema changes. - SQL injection is handled, but don’t get clever with concatenation.

Pro tip: If you need advanced filtering (like regex or custom IN lists), you can use SQL’s ILIKE or IN logic inside your variable blocks, but keep it readable for future-you.

4. Using CTEs, Window Functions, and Subqueries

Metabase passes your SQL directly to your database. If your warehouse supports it, so does Metabase. That means:

  • Use CTEs (WITH ... AS) for cleaner, more readable queries.
  • Window functions work just fine.
  • Subqueries? No problem.

Example: Rolling 7-day average with a variable date filter

sql WITH filtered AS ( SELECT * FROM events WHERE event_date >= {{start_date}} AND event_date < {{end_date}} ) SELECT event_date, COUNT() AS daily_events, AVG(COUNT()) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg FROM filtered GROUP BY event_date ORDER BY event_date

What doesn’t work:
- Recursive CTEs are hit-and-miss (depends on your DB). - Some warehouse-specific syntax (BigQuery’s scripting, Postgres’ procedural stuff) won’t run.

Advice: If you’re debugging, run the query in your DB’s native console first. Metabase errors are often vague.

5. Saving, Sharing, and Visualizing Custom SQL Results

Once your query is working:

  1. Click Save. Name it clearly—future you (and your team) will thank you.
  2. You can add saved SQL questions to dashboards, set up alerts, or use them as starting points for new questions (just “Duplicate”).
  3. Visualization options are mostly the same as the visual builder, except some features (like drill-through) are limited.

What to keep in mind: - If your query returns lots of columns or odd types, some chart options won’t appear. - You can’t “edit in GUI” after going SQL—your question is now code.

Pro tip: Build small, composable SQL questions. If you try to make one mega-query that powers a dashboard, you’ll regret it the first time you need to change something.

6. Parameterizing for Dashboards

If you want dashboard-level filters to control your SQL, you need to use Field Filters. Here’s the trick:

sql SELECT * FROM users WHERE {{user_signup_date}}

Then, in Metabase, you link the variable to a column (e.g., users.signup_date). This lets dashboards filter across multiple questions.

It’s finicky:

  • Use the “Field Filter” type in variable settings.
  • Only works if the filter is in a WHERE clause.
  • Metabase tries to be smart, but sometimes guesses wrong—double-check the filter mapping.

Pro tip: Keep your SQL clean and avoid clever hacks here. If the filter mapping is too weird, Metabase will just ignore it.

7. Managing Performance and Security

SQL in Metabase is powerful, but it’s easy to shoot yourself in the foot.

Performance: - Don’t select * unless you mean it. - Always limit rows in dev/test (LIMIT 100). - Watch for N+1 queries or massive joins—Metabase won’t warn you before running a runaway query.

Security: - Users with SQL access can see any data their database permissions allow. Metabase’s data sandboxing doesn’t apply to raw SQL. - Don’t store credentials or secrets in your queries (sounds obvious, but you’d be surprised). - If you use variables, Metabase parameterizes safely. Still, don’t let random users input free text into queries if you can help it.

8. Common Pitfalls and Annoyances

Here’s where you’ll bang your head:

  • Query timeouts: Metabase defaults to a short timeout. If your query is slow, it’ll just error out.
  • Limited debugging: Error messages are vague. Test elsewhere first.
  • No stored procedures: You can’t run multi-statement procedures or scripts.
  • No reusing SQL fragments: There’s no “import” or “include”—copy/paste is your only friend.
  • Schema changes: If the underlying table changes, your SQL will break. No warning.

Stuff to ignore:
- Don’t bother with Metabase’s “Custom Expressions” if you already know SQL. They’re limited and don’t save you much time.

9. Advanced Tricks (and When to Stop)

  • Use SQL for heavy lifting, but let Metabase handle the chart: Do all data shaping in SQL, return tidy columns, then switch to Metabase’s visualization for the last step.
  • Chaining: You can use a saved SQL question as a “virtual table” in another SQL question (using the Saved Questions table reference), but this can get messy. Test performance.
  • Exporting: You can export results to CSV or Excel, but there’s a row limit (usually 1M rows, but check your instance).

When to stop: If your SQL is more than 200 lines, split it. If you’re writing complex ETL, do it upstream (in dbt or scripts), not in Metabase.

Keep it Simple and Iterate

Building custom SQL queries in Metabase unlocks a ton of power, but don’t get carried away. Start small, test as you go, and keep your queries readable. When things break (and they will), you’ll thank yourself for not being too clever.

If you’re stuck, remember: Metabase is just a thin layer over your data. The real magic—and the real mess—is in the SQL you write. So keep it simple, iterate, and don’t trust the hype. Just build what you need.