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).

Snowflake setup for Looker

Snowflake setup for Looker.

In the same documentation as above, you will find a guide to connect Looker to Snowflake.

Start Exploring

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.

Snowflake

[sql]

select monthname(starttime) as "month",
count(*) as "num trips"
from trips
group by 1
order by 2 desc
;

[/sql]

The above query results in the following:

Looker

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.

LookML example of an altered view - 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:

Looker Query results on Snowflake
Similar results, although the Month Name is calculated differently. This can be explained by the below generated query.
[sql]

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

[/sql]

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.

Cheers,

Daan Bakboord

DaAnalytics

DaAnalytics

Hi, ik ben Daan Bakboord. Ik ben een ondernemende Data & Analytics consultant. Mijn passie is het verzamelen, verwerken, opslaan en presenteren van data. Ik help organisaties deze interne en externe data zodanig in te zetten, dat ze in actie kunnen komen. Door het inzetten van betrouwbare data kunnen ondernemers hun ‘gut-feeling’ onderbouwen met feiten.

Leave a Reply

Do NOT follow this link or you will be banned from the site!