Looker into Snowflake
Recently I attended a “Zero to Snowflake in 90 minutes” training session. This session supports us as partners to provide a similar session for our clients. In the course of the session you had to query a table from the Snowflake UI. It’s common to use a BI Platform to query data. In an earlier blog I showed how easy it was to query Snowflake using Oracle Analytics. This time I will have a Looker into Snowflake.
Last may I joined Looker Join in Amsterdam. In a related blogpost I briefly describe what Looker is all about. I won’t repeat that here.
Setting up Snowflake
The documentation is pretty thorough about the steps to perform. It even contains a script to setup the necessary objects in Snowflake. As a result (not exactly as in the documentation), the setup is in place to query Snowflake from Looker. In this case we are going to query the Citibike-database (Trips-table).
In the same documentation as above, you will find a guide to connect Looker to Snowflake.
The purpose of Looker is to explore data. To make data explorable in Looker you need two things; a model and a view. The central part of Looker is an Agile Modeling Layer; LookML, where you can add custom logic. LookML reduces complexity and supports Data Governance to become everybody’s responsibility.
- A model defines which tables to use and how you could join these together (in case of multiple views). The information about the model, its Explores, and its joins is found in the model.
- The view contains information about the view (table) and its dimensions and its measures. It defines how one can access or calculate information from each table (or a combination of tables).
- An Explore is typically defined within a model. It is the starting point for a query.
Find an example below.
More details about LookML and how it’s organised can be found here in these LookML Terms and Concepts.
Query Snowflake and Looker
So now it’s time to see how you can both query Snowflake and Looker and still get the same result. Although it might be a slightly different query (syntax). Let’s start with the Snowflake-query.
select monthname(starttime) as "month", count(*) as "num trips" from trips group by 1 order by 2 desc ;
The above query results in the following:
As you can see in the query above, the “month” and the “num trips” are no columns in the trips-table. Therefore these columns won’t be out of the box available in the LookML-view. Fortunately we can customise the view.
There is a Dimension Group based on the starttime in the trips-table. By default there are already a few timeframes. In this case, I added the month_name. Now we can create the query in Looker and see what query it generates.
A similar result is generated in Looker:
Similar results, although the Month Name is calculated differently. This can be explained by the below generated query.
SELECT EXTRACT(MONTH FROM trips."STARTTIME" )::integer AS "trips.starttime_month_num", DECODE(EXTRACT('month', trips."STARTTIME" ), 1, 'January', 2, 'February', 3, 'March', 4, 'April', 5, 'May', 6, 'June', 7, 'July', 8, 'August', 9, 'September', 10, 'October', 11, 'November', 12, 'December') AS "trips.starttime_month_name", COUNT(*) AS "trips.count" FROM PUBLIC.TRIPS AS trips GROUP BY 1,2 ORDER BY 3 DESC LIMIT 500
To sum this up. The above showed how well Snowflake and Looker work together. As from this point onwards you can start visualizing Snowflake data in Looker.
Thanks for reading.