Compiling Sql At Runtime

Engineering Oct 1, 2021

In a previous post, I already mentioned that we are heavily relying on Metabase for many use-cases, not only analytics but automation as well. In other words, Metabase is a very good SQL IDE for team to compose and track their business logic presentation in SQL. But this approach cause a new headache for us: running all queries via metabase is very bad, especially for heavy tasks, because it is anti-performance in many ways.

My team used to work around by getting the raw queries from metabase database and run it directly to our data warehouse, but it is quite troublesome for many reason: we have to know which parameters to be replaced with proper values, and even worse that the generated queries can be broken because there are some snippet code in the raw SQL that are unable to handle.

Fortunately, when we digged deeper into this problem, we found metabase author are very foreseeing to handle our problem so smartly with just an api, hereby giving us the compiled sql for any metabase card. As a result, our BA/DA team can keep working on metabase and our DE team can get the compiled sql queries to run directly onto database in a much faster and safer way. All stakeholders are happy on the solution. :D