Looker and Snowflake Quickstart

Snowflake for Developers GuidesLooker and Snowflake Quickstart

Looker and Snowflake Quickstart

Build

Ali Khosro

Overview

This guide will walk you through the process of connecting Looker to Snowflake and building a simple dashboard. We will cover two authentication methods: Key-Pair for service accounts and OAuth for individual user authentication.

What You Will Learn

How to load sample data into Snowflake.
How to configure both Key-Pair and OAuth connections between Looker and Snowflake.
How to create a Looker project and model from a Snowflake schema.
How to build a simple dashboard in Looker.
How to version control your Looker project with Git.

Prerequisites

A Snowflake account with ACCOUNTADMIN privileges. If you don't have one, you can sign up for a free trial.
A Looker instance.
A GitHub account.

What You Will Build

A connection between your Looker instance and your Snowflake account.
A Looker dashboard visualizing Citibike trip data.
A Git repository for your Looker project.

1. Loading Data into Snowflake

First, we will load public Citibike trip data from an S3 bucket into your Snowflake account.

-- Set the context for your session
USE ROLE SYSADMIN;

-- Create a warehouse, database, and schema
CREATE WAREHOUSE IF NOT EXISTS poc_wh;
USE WAREHOUSE poc_wh;
CREATE DATABASE IF NOT EXISTS citibike;
CREATE SCHEMA IF NOT EXISTS citibike.poc;
USE DATABASE citibike;
USE SCHEMA poc;

-- Create a table for the trip data
CREATE OR REPLACE TABLE trips (
  ride_id STRING,
  rideable_type STRING,
  started_at TIMESTAMP,
  ended_at TIMESTAMP,
  start_station_name STRING,
  start_station_id STRING,
  end_station_name STRING,
  end_station_id STRING,
  start_lat FLOAT,
  start_lng FLOAT,
  end_lat FLOAT,
  end_lng FLOAT,
  member_casual STRING
);

-- Create a file format for the CSV data
CREATE OR REPLACE FILE FORMAT csv_format
  TYPE = 'CSV'
  FIELD_DELIMETER = ','
  SKIP_HEADER = 1
  NULL_IF = ('NULL', '""')
  EMPTY_FIELD_AS_NULL = TRUE
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

-- Create a stage to access the public S3 bucket
CREATE OR REPLACE STAGE citibike_trips
  URL = 's3://tripdata/';

-- Load data from the S3 bucket into the trips table
-- This pattern loads all zipped CSV files for the year 2024
COPY INTO trips
  FROM @citibike_trips
    PATTERN = '.*2024.*-citibike-tripdata.csv.zip'  
    FILE_FORMAT = (FORMAT_NAME = 'csv_format')
    ON_ERROR = 'CONTINUE';

-- Verify the data has been loaded
SELECT COUNT(*) FROM trips;

2. Configuring Key-Pair Authentication

This method is ideal for service accounts and automated processes. We'll generate a key pair and assign the public key to a dedicated Looker service user in Snowflake.

2.A Create a Key Pair

Generate the keys in your terminal: The following commands create a 2048-bit unencrypted private key (rsa_key.p8) and a public key (rsa_key.pub).

# Create a directory to store your keys
mkdir -p ~/secret_keys && cd ~/secret_keys

# Generate the private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

# Generate the public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Copy the public key to your clipboard: The public key must be pasted into Snowflake as a single line of text.

# macOS
cat rsa_key.pub | grep -v '^-' | tr -d 'n' | pbcopy

# Linux (requires xclip)
cat rsa_key.pub | grep -v '^-' | tr -d 'n' | xclip -sel clip

2.B Create Snowflake Service Account and Permissions

Create a dedicated user and role for Looker and assign the public key.

USE ROLE ACCOUNTADMIN;

-- Create a role for Looker
CREATE ROLE IF NOT EXISTS looker_role;

-- Grant privileges to the role
GRANT USAGE ON DATABASE citibike TO ROLE looker_role;
GRANT USAGE ON SCHEMA citibike.poc TO ROLE looker_role;
GRANT USAGE ON WAREHOUSE poc_wh TO ROLE looker_role;
GRANT SELECT ON ALL TABLES IN SCHEMA citibike.poc TO ROLE looker_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA citibike.poc TO ROLE looker_role;

-- Create the service user for Looker
CREATE USER IF NOT EXISTS looker_service_account
  DEFAULT_ROLE = 'looker_role'
  DEFAULT_WAREHOUSE = 'poc_wh'
  MUST_CHANGE_PASSWORD = FALSE;

-- Assign the public key to the user
ALTER USER looker_service_account
  SET RSA_PUBLIC_KEY = 'your_public_key_string';

-- Grant the role to the service user
GRANT ROLE looker_role TO USER looker_service_account;

3. Configuring OAuth Authentication

OAuth allows each Looker user to authenticate with their own Snowflake credentials.

Note on OAuth Limitations

Persistent Derived Tables (PDTs): Not supported with Snowflake OAuth.

Token Expiration: Users may need to re-authenticate periodically.

Role Limitations: The connection uses the user's default role in Snowflake.

Create Security Integration in Snowflake

Run the following SQL. Replace <your_looker_hostname> with your Looker instance's hostname.

USE ROLE ACCOUNTADMIN;
CREATE SECURITY INTEGRATION IF NOT EXISTS looker_oauth
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = LOOKER
  OAUTH_REDIRECT_URI = 'https://<your_looker_hostname>/external_oauth/redirect';

Retrieve the Client ID and Secret. You will need these for the Looker connection.

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('looker_oauth');

Note on Snowflake Network Policies

If you encounter a connection error, you may need to whitelist Looker's IP addresses.

-- Use the IP from the error message or Looker documentation
CREATE OR REPLACE NETWORK POLICY looker_ip_policy
  ALLOWED_IP_LIST = ('<looker_ip_address>');

ALTER ACCOUNT SET NETWORK_POLICY = looker_ip_policy;

4. Configuring the Looker Connections

With Snowflake configured, we can now create the connections in Looker.

Navigate to Admin > Database > Connections.
Click Add Connection.

Connection A: Key-Pair Authentication

Name: snowflake_citibike_keypair
Dialect: Snowflake
Host: <your_snowflake_account>.snowflakecomputing.com
Database: CITIBIKE
Schema: POC
Username: looker_service_account
Auth Method: Key Pair
Private Key: Paste the content of your rsa_key.p8 file.
Warehouse: poc_wh

Connection B: OAuth Authentication

Name: snowflake_citibike_oauth
Dialect: Snowflake
Host: <your_snowflake_account>.snowflakecomputing.com
Database: CITIBIKE
Schema: POC
Authentication: OAuth
OAuth Client ID: Paste the OAUTH_CLIENT_ID from the previous step.
OAuth Client Secret: Paste the OAUTH_CLIENT_SECRET.
Warehouse: poc_wh

Click Test These Settings, and if successful, click Add Connection.

5. Creating a Looker Project and Dashboard

Create a project

Now we will create a Looker project.

Navigate to Develop > LookML Projects.
Click New LookML Project.
- Project Name: citibike_quickstart
- Starting Point: Generate Model from Database.
- Connection: Select snowflake_citibike_keypair.
- Schema: POC
Click Create Project.

Looker will automatically generate a model and view file from your database schema.

Create a Dashboard

From the Looker homepage, click + New > Dashboard.
Name it "Citibike Trip Overview" and click Create Dashboard.
Click Add Tile, and choose the Trips Explore.
In the Explore interface:
- Under Dimensions, click Start Station Name.
- Under Measures, click Count.
- Click Run.
Select the Bar chart visualization and click Save.

Conclusion and Resources

Congratulations! You have successfully connected Looker to Snowflake, created a LookML project, built a dashboard, and set up version control.

What You Learned

How to load data into Snowflake.
How to configure Key-Pair and OAuth authentication for Looker.
How to create a Looker project from a database schema.
How to build a Looker dashboard.
How to set up Git for a Looker project.

Related Resources

Updated 2025-10-25

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances