That’s it really, the GROUPING SET operator simply performs and collates many GROUP BY in one fell swoop. In fact, you could just as well implement a GROUPING SET yourself by concatenating many GROUP BY query results together via some UNION operators. This is in fact the concatenation of three smaller tables: SELECT week, industry, SUM ( claims ) / SUM ( premiums ) AS loss_ratio FROM accounts WHERE country = '🇫🇷' GROUP BY GROUPING SETS ( ( week ), ( industry ), ( week, industry ) ) week Assuming we’ve collected these figures at a company level, this might result in such a tidy dataset: week We do this by comparing premiums, which is the money we receive from the people we cover, with claims, which are the healthcare expenses we reimburse. What GROUPING SETS doesĪs a health insurance company, it is key for us is to keep track of our margin. Before delving into said pattern, let us start by dwelling on what this operator does. This has unlocked quite a powerful pattern for us. We recently discovered that Snowflake has a GROUPING SETS operator. One of them is to prepare data in such a way that it can be consumed in a dashboard with minimal effort. We’re addressing this via various initiatives.
#METABASE SNOWFLAKE CODE#
We’ve agreed that the less SQL code is in Metabase, the better. Generally speaking, we wish to change our relationship with Metabase. Moreover, when we make a change to our prepared data, it’s burdensome to propagate the changes into our dashboards. There is also business logic that is duplicated in many places across Metabase. One of the issues we’re facing is that we have a lot of business logic that is stored in Metabase, rather than being versioned in our analytics codebase. This gives us the liberty to do whatever we want between the data warehouse and the visualisation. Metabase allows querying the data warehouse with SQL. Recently, we’ve been having a lot of discussion around our setup. Our data analysis is done on top of our prepared data. We transform this into prepared data via an in-house tool that resembles dbt. This includes dumps of our production database, third-party data, and health data from other actors in the health ecosystem. We load data into our warehouse with Airflow. Our data warehouse used to be PostgreSQL, and have since switched to Snowflake for performance reasons. At Alan, we do almost all our data analysis in SQL.
0 kommentar(er)
