Introduction
So you’re using Metabase. First, allow me to congratulate you to this choice, b/c who needs Tableau and Power BI anyway? In Metabase you can write native SQL queries and visualize them in the same run (it’s Open Source, hence free to use)¹.
Now, typically you should have a ready-to-use-by-clicking area where all your reporting tables lay and can be queried by your BI tool of choice. You know, just a few clicks and that chart is done. 🖯
This is no different with Metabase but sometimes even the mightiest tools meet their limits and a manually written SQL query is the better way. But once you wrote that query, it’s static.
Let’s say, a colleague comes up to you and asks if she can get the numbers of 2014 year’s sales for territory 1 and its share of the total sales. Unfortunately, Metabase cannot handle this by its built-in logic, but thanks to its native SQL-capability, you can use it as an IDE. Now the query may look like this:
SELECT
SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END) AS sales_territory_1
, SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END) / SUM(salesamount) AS ratio
FROM dbo.fact_internet_sales
WHERE EXTRACT(YEAR FROM orderdate::DATE) = 2014Nice. So what if the same question is asked for another time period?
Well, that’s super easy, barely an inconvenience.
Adjust the WHERE-clause and you’re done. But maybe your co-worker wants to inspect the data some more and comes to you for every single change in the result set. So you change the code over and over again, which can add up really fast.²
With variables you enable your co-workers to do their own research in depth and do filtering in your native query without them needing to know anything about SQL.
Oh and by the way, the database I am using is the AdventureWorksDW2019 sample database with PostgreSQL. Interested in how I shifted that data from MS SQL Server to Postgres? Take a look here. 👈
The concept of variables in Metabase
The official documentation can be found here. But you weren’t here if you were reading the docs, right? 😉 So let me explain.
Variables are simple placeholders that insert strings into your SQL-code. Depending on the data type you’re choosing for your filter (Text, Number or Date) the input field will accept a string, number or lets you pick a date from the GUI. Whatever you’re choosing, it will be inserted into your code where your variable sits.
Step 1: Initiation
Variables are initiated by curly braces: {{}}. Enter some name within and Metabase will do two things: First, it will create a filter widget above and second, it will open a context menu on the right. Here you can choose the data type³, give a proper name that’s displayed in the filter widget and if this variable is required.
Now, let’s rewrite that WHERE clause from the SQL-code above with a variable:
WHERE EXTRACT(YEAR FROM orderdate::DATE) = {{var_businessyear}}Pretty straight forward, right?
I enabled the “required”-switch to force a default value. Otherwise the SQL code will fail, b/c there would be nothing behind that equal sign.
But now, we can enter any number and will eventually get the results if there’s a match (which will be between values 2010 and 2014 here).
Step 2: Optionality
Alright, now that we know how to add a variable, let’s go a step further and add some square brackets: [[]]. What are these doing?
Everything wrapped inside those brackets will be invisible to the source code. As soon as a variable is entered, the expression will be visible for the machine and evaluated in the query. Keep in mind that those only work when there is a variable inside. Otherwise Metabase will throw an error. This also means that you cannot write native SQL with those brackets as easy, which will cause problems with the SQL Server Syntax.⁴
Okay, let’s make the business year optional and the code will look like this:
SELECT
SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END)
AS sales_territory_1
, SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END)
/ SUM(salesamount) AS ratio
FROM dbo.fact_internet_sales
WHERE 1 = 1
[[AND EXTRACT(YEAR FROM orderdate::DATE) = {{var_businessyear}}]]When using optional variables you should start the filter block with WHERE 1 = 1. Since it’s a valid WHERE-expression it won’t throw an error and it introduces the WHERE-clause in your query. With this you can easily add more filters by adding [[AND …]].
The effect on our example query is minor — at least we can deactivate the “required”-button without breaking our code. But the true strengths of optionality come into play with multiple filters.
Step 3: Multiple filters
Like in any WHERE-clause you can combine a whole bunch of operators. In larger queries you may end up hoarding AND’s and OR’s like consumers do with TV’s on Black Friday. However, to enable the end user to do the same thing and filter for more than one field, you just have to wrap every statement into square brackets that begins with the operator.
So in order to make multiple filters work, we have to rewrite our query from above. This ensures that no result is excluded before filtering with a variable. Take a look at this:
WITH sales AS (
SELECT
salesterritorykey
, EXTRACT(YEAR FROM orderdate::DATE) AS businessyear
, SUM(salesamount) AS salesamount
FROM dbo.fact_internet_sales
WHERE 1 = 1
[[AND EXTRACT(YEAR FROM orderdate::DATE) = {{var_businessyear}}]]
[[AND salesterritorykey = {{var_territory}}]]
[[AND orderdate::DATE >= {{var_orderdate_min}}]]
[[AND orderdate::DATE <= {{var_orderdate_max}}]]
GROUP BY 1,2
)SELECT
businessyear
, salesterritorykey
, salesamount
, salesamount / SUM(salesamount) OVER(PARTITION BY businessyear) AS total_sales
FROM sales
ORDER BY 1,2
I created a CTE to preprocess the data a little which makes it easier to compute our numbers. The Window Function (OVER(…)) helps us to get the total amount for each business year and calculate the ratio for every territory in that year.
And here’s the end result:
Be aware of the end user
You know, nowadays we are used to correct search results even if we misspell the search keywords. And thanks to Google we don’t even need to finish our search text due to auto-fill. So typos or unfinished text must be considered when building a query with variables.
I think it’s best practice if you do some text manipulation with SQL functions that catch those cases. A combination of
- case-insensitive matching (ILIKE)
- string-concatenation with the use of the wildcard operator (|| ‘%’),
- removing leading and trailing spaces (TRIM)
should be fine for the start. Depending on the user you may add more functions for string manipulation and pattern matching.
Actually, I’ll show you in the next paragraph how all that can work in this setting.
Creating a text filter that accepts multiple keywords
Finally, we got anything covered to get real fancy with those variables. Building a filter that accepts multiple keywords is our final little project here (separated by commas in this example):
SELECT
EXTRACT(YEAR FROM s.orderdate::DATE) AS businessyear
, t.salesterritoryregion AS region
, SUM(s.salesamount) AS sales
FROM dbo.fact_internet_sales s
LEFT JOIN dbo.dim_sales_territory t
ON s.salesterritorykey = t.salesterritorykey
WHERE 1 = 1
[[AND EXTRACT(YEAR FROM s.orderdate::DATE) = {{var_businessyear}}]]
[[AND (t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',1)) || '%', '^\%$', '')
OR t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',2)) || '%', '^\%$', '')
OR t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',3)) || '%', '^\%$', '')
OR t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',4)) || '%', '^\%$', '')
)]]
GROUP BY 1,2
ORDER BY 1,2I think this goes with advanced text manipulation in SQL. But no worries, it’s way more easy than you might think and we got most of those functions covered in the paragraph before. So what are we doing here?
If you enter this string “ g, Can”, your result set will contain the regions Germany and Canada. Let’s inspect the first part of our nested functions:
ILIKE ... TRIM(SPLIT_PART({{var_region}}, ',',1)) || '%'First, the function SPLIT_PART splits the string at the comma and returns the first part of it (or the second, or the third, or the fourth). This will be “ g”. Next, TRIM removes leading and trailing spaces. This returns “g”. The next evaluation step is “|| ’%’ ”. This adds the SQL-wildcard character % to our string. We finally get “g%”. And now, with ILIKE leading our expression, Postgres will search for anything that starts with “g”, but case-insensitive.
REGEXP_REPLACE(..., '^\%$', '')Now, what is this? It’s an additional layer of error-catching. The Regex-function replaces the text with an empty string, if it only contains “%”, i.e. does not contain any text. This can and will happen if you allow for more keywords than the end user actually enters. In this example the user can filter up to 4 comma-separated words, but if only 1 word is entered, the remaining 3 spaces will be empty.
There’s one drawback with this approach: The code is still static.
You need to set the amount of searchable keywords manually. We can search for 4 regions (i.e. enter 4 keywords), but there are actually 11. If you want to enable the user to throw at least 11 keywords at the filter, you must add them manually:
OR t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',5)) || '%', '^\%$', '')
OR t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',6)) || '%', '^\%$', '')...OR t.salesterritoryregion ILIKE REGEXP_REPLACE(TRIM(SPLIT_PART({{var_region}}, ',',11)) || '%', '^\%$', '')
Bonus: Creating an On/Off-Switch with Variables (sort of)
There isn’t just one way of doing it but it basically boils down to a field that can take two forms (0 or 1, “Yes” or “No”, “Ernie” or “Bert”) and that this one will be evaluated with the input you’re given by your filter. So any string that’s entered into that filter will be inserted into the WHERE-clause and must be SQL-readable. Eventually it must be converted in a way that makes sense in an ON-OFF-Situation. The reason we’re going this way is the limitation of data types with variables. Metabase does not (yet) offer a binary type that adds a clickable switch-widget (a swidget, so to speak 😝).
Here’s my interpretation. See the explanation below.
WITH sales_predefined AS (
SELECT
orderdatekey
, EXTRACT(YEAR FROM orderdate::DATE) AS "Order Year"
, salesamount
, CASE
WHEN EXTRACT(YEAR FROM orderdate::DATE) = (SELECT MAX(EXTRACT(YEAR FROM orderdate::DATE)) FROM adventureworksdw2019.dbo.fact_internet_sales) THEN 'Yes'
END AS is_latest_year
FROM adventureworksdw2019.dbo.fact_internet_sales
)SELECT
"Order Year"
, SUM(salesamount) AS "Total Sales"
FROM sales_predefined
WHERE 1 = 1
[[AND is_latest_year = REPLACE({{var_latest_year}}, {{var_latest_year}}, 'Yes')]]
GROUP BY 1
ORDER BY 1
Since we’re not altering any table and it’s more comfy than defining a View, we’re beginning this query with a CTE. This one is creating a new field that contains “Yes” if the order date’s year is equal to the maximum order date’s year (which is 2014), else NULL (we do not need to explicitly specify that in our CASE statement). This field will be relevant for our On/Off-Switch.
Next up we’re querying this CTE by summing the sales amount and grouping them by the order year. Nothing special so far.
The real magic happens inside the box brackets. We already learned that those define an optional filter-clause with variables.
Now, anything that’s entered in that filter will be transformed into “Yes” and our result set will contain the total sales for the year 2014.
Summary
In today’s post I introduced you to variables in a native SQL query in Metabase. They allow for dynamic filtering in static SQL queries by inserting any text (like an f-string in Python 3). We started with an easy use case and added a layer of complexity at each step we took. You can even go a step further and leverage the whole concept by inserting variables not only in your WHERE-clause but also in SELECT and ORDER BY (those were not covered in this post, but if you’re interested in this, let me know!). It’s just text, so there are plenty of possibilities.
However, do not forget the end user! Keep in mind that some inputs may return errors in context of your query. Be aware of possible typos or unfinished words. Catch those cases with additional layers of SQL-commands, especially string manipulation and pattern matching.
Well then, that’s it. I hope you enjoyed the read and if you like to learn more about Metabase or some cool SQL tricks, start following me (I drop a post once in a while). 👍
[1] It may surprise you, but I am not affiliated with Metabase or the programmers behind it. I really just love the capabilities it offers and find it astonishing that so many companies particularly here in Germany still use expensive and absolutely non-intuitive tools like Tableau (“yikes!”) or Power BI (“at least it’s not Tableau”).
[2] The dialogue may go like this: “Okay, now show me business year 2011. And now 2010. … Okay, now I want to see the orderings in the period from July 1st to December 31st in 2012. … Now the same thing for the year before. And now all the same with sales territory 2.” — You see where this is going?
[3] The Field Filter references a field from another table. But that’s too much to explain for this post.
[4] In case you’re not familiar with SQL Server — a typical query in SQL Server Management Studio (SSMS) looks like this:
SELECT * FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]You should get rid of those brackets when copying the code into the Metabase editor.
