Best practices regarding code organization

Hey folks,

Recently, while making a few changes in a dashboard that’s embedded in our web application, I’ve started to face a lot of code repetition. So, I was wondering what are a few best practices regarding code organization that I could explore in order to improve the quality of the code.

I’m using Common Table Expressions (CTEs) to prepare the data before doing the aggregations, in order to better organize the code. Since the dashboard is showing customer data, I’m making sure that there’s the Company ID being filtered inside the WHERE clause in the CTE of every question.

But I found myself defining CTEs and applying the company filter to every question on the Dashboard, creating a lot of repeated code. An option that I was trying to explore was regarding Using an existing question as a sub-query, but I haven’t found out if we are able to add hidden filters in them.

I know that a common approach in the Metabase community is to build views into the database, and then use these views to populate the dashboards, but I was looking to avoid dealing with other customer’s data as soon as possible, in order to improve performance.

So, I’m open to suggestions. Is it possible to avoid repeating myself in every question? I was wondering if it’s possible to “prepare” a dataset with a SQL query (thus passing the parameters into this query) and then use that filtered query as a reference to every other aggregation in the Dashboard.

Thanks in advance.

Hi @hsberlesi
It sounds like you’re doing some multi-tenant setup, which is what Sandboxing in the Enterprise Edition can be used for as well.
For reference, this issue is about being able to parse variables to a nested query:
https://github.com/metabase/metabase/issues/6449 - upvote by clicking :+1: on the first post

Same issue here, CTEs or parts thereof containing WHERE 1=1 AND {{VARIABLE}} being repeated over many questions in embedded dashboards.

Could SQL Snippets work in such a situation?

@mesquest It doesn’t support parsing the variable, just like for other nested queries.