Snowflake

Connect Snowflake to bring any data stored in your warehouse into Lifesight, from paid and organic performance to KPIs and contextual factors.

Snowflake is the cloud data warehouse where many teams keep the data that does not live in an ad platform: revenue and orders, organic and CRM activity, pricing, promotions, and spend from channels with no connector of their own.

Connecting Snowflake lets Lifesight read those tables on a schedule, so nobody has to export and upload files.

The steps at a glance

There are seven steps, in two parts.

Part 1: Set up the Snowflake credential. Done once. It needs someone who manages Snowflake, so most marketers hand this part to their IT or data team.

StepWhat happensWho does it
Step 1: Create the two key filesTwo key files are created. Lifesight uses them to sign in instead of a password.Your IT or data team, or you
Step 2: Create the user in SnowflakeA script creates a read-only Snowflake user for Lifesight.Your IT or data team, or you
Step 3: Save the private keyThe private key is stored safely and passed to whoever does Part 2.Your IT or data team, or you

Part 2: Connect Snowflake in Lifesight. Done for each table. No Snowflake knowledge needed.

StepWhat happensWho does it
Step 4: Name and connectYou name the integration and add the credential from Part 1.You
Step 5: Select a tableYou pick the table to bring in.You
Step 6: Set up the dataYou tell Lifesight how to read the table.You
Step 7: Schedule and syncYou choose how often the data refreshes.You

Also on this page: What Lifesight brings in, After connecting and Troubleshooting.


What Lifesight brings in

What it readsOnly the tables and materialized views you choose.
How manyOne table per integration. Add an integration for each table and reuse the same credential.
What it can doRead only. Lifesight never writes to, changes or deletes anything in Snowflake.
Kinds of dataPaid Marketing, KPI, Organic or Contextual. You choose the kind for each table in step 6.

Before you start

You needDetails
A Snowflake adminSomeone who can run a script as ACCOUNTADMIN. This can be you or your data or IT team.
Your account identifierThe part of your Snowflake URL before .snowflakecomputing.com. In acme-xy12345.snowflakecomputing.com, it is acme-xy12345.
A warehouse nameAn X-Small warehouse is enough.
The database, schema and table namesWhat you want Lifesight to read.

Part 1: Set up the Snowflake credential

Lifesight signs in to Snowflake as its own read-only user, with a key pair instead of a password. You do this part once.

👍

You do not have to do this yourself

Send this part to whoever looks after Snowflake at your company. It takes about ten minutes. They send you back the private key and the names you need in step 4.

Step 1: Create the two key files

  1. Open a terminal:
    • Mac: press Cmd + Space, type Terminal and press Enter.
    • Windows: open Git Bash, or ask your IT team to run this step.
  2. Paste these two lines and press Enter:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Result: two new files in your home folder.

FileWhat it isUsed in
rsa_key.pubThe public key. Not secret.Step 2
rsa_key.p8The private key. Keep it secret.Steps 3 and 4
📘

What a key file looks like

Both are plain text. The private key looks the same as this one, but is longer and says PRIVATE KEY.

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAu7Tq9xw2LkP4vGm1Zc8H
... a few more lines like this ...
8QIDAQAB
-----END PUBLIC KEY-----

Step 2: Create the user in Snowflake

  1. Print the public key as one line, the form Snowflake needs. Run this in the terminal, then copy the output:
grep -v "PUBLIC KEY" rsa_key.pub | tr -d '\n'
  1. In Snowflake, click + and open a new SQL Worksheet.
  2. Paste the script below into the worksheet.
  3. Replace each placeholder:
PlaceholderReplace with
<WAREHOUSE_NAME>Your warehouse
<DATABASE_NAME> and <SCHEMA_NAME>Where your data is
<TABLE_NAME>The table to share
<PUBLIC_KEY>The line you copied in action 1, inside the quotes
  1. Choose the read access in section 6 of the script:
OptionWhat Lifesight can readChoose it when
A (recommended)Only the tables you name, one line eachYou want to share as little as possible. This is all Lifesight needs.
BEvery table in the schema, including ones created laterNew tables appear regularly
  1. Run the whole script as ACCOUNTADMIN.
-- ============================================================================
-- LIFESIGHT: SNOWFLAKE SETUP
-- ============================================================================
-- Creates a read-only service user that Lifesight signs in as. Sign-in is by
-- key pair only: no password is created or stored.
--
-- BEFORE YOU RUN
--   1. Replace every <PLACEHOLDER> with your own value.
--   2. Run the whole script in a worksheet as ACCOUNTADMIN.
--   3. It is safe to run again. It never removes existing grants.
-- ============================================================================

-- SECTION 1: Create the service user
USE ROLE USERADMIN;

CREATE USER IF NOT EXISTS LIFESIGHT
  LOGIN_NAME   = 'LIFESIGHT'
  DISPLAY_NAME = 'Lifesight Integration'
  COMMENT      = 'Read-only service user for the Lifesight integration';

-- SECTION 2: Key-pair sign-in, user type and default warehouse
-- These run every time, so they also apply if the user already existed.
ALTER USER LIFESIGHT UNSET PASSWORD;
ALTER USER LIFESIGHT SET TYPE = SERVICE;
ALTER USER LIFESIGHT SET
  DEFAULT_WAREHOUSE = <WAREHOUSE_NAME>
  RSA_PUBLIC_KEY    = '<PUBLIC_KEY>';
-- ^ The public key as one line, without the BEGIN/END lines or line breaks.

-- SECTION 3: Create the read-only role and give it to the user
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS LIFESIGHT_READER;
GRANT ROLE LIFESIGHT_READER TO USER LIFESIGHT;
ALTER USER LIFESIGHT SET DEFAULT_ROLE = LIFESIGHT_READER;

-- SECTION 4: Let the role use the warehouse
GRANT USAGE ON WAREHOUSE <WAREHOUSE_NAME> TO ROLE LIFESIGHT_READER;

-- SECTION 5: Let the role see the database and schema (this gives no data access)
-- Repeat the SCHEMA line for each extra schema.
GRANT USAGE ON DATABASE <DATABASE_NAME>               TO ROLE LIFESIGHT_READER;
GRANT USAGE ON SCHEMA   <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE LIFESIGHT_READER;

-- SECTION 6: Give read access to your data. Choose ONE option.
-- Lifesight only needs SELECT on the tables you want to sync.

-- OPTION A (recommended): only the tables you list. One line per table.
GRANT SELECT ON TABLE <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME> TO ROLE LIFESIGHT_READER;
-- GRANT SELECT ON TABLE <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME_2> TO ROLE LIFESIGHT_READER;
-- GRANT SELECT ON MATERIALIZED VIEW <DATABASE_NAME>.<SCHEMA_NAME>.<VIEW_NAME> TO ROLE LIFESIGHT_READER;

-- OPTION B (broader): every table in the schema, including ones created later.
-- To use it, delete the Option A line above and remove the leading "-- " below.
-- GRANT SELECT ON ALL    TABLES IN SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE LIFESIGHT_READER;
-- GRANT SELECT ON FUTURE TABLES IN SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE LIFESIGHT_READER;

-- SECTION 7: Check the result
DESC USER LIFESIGHT;
SHOW GRANTS TO ROLE LIFESIGHT_READER;
  1. Check the output of the last two lines:
LineIt should show
DESC USER LIFESIGHTTYPE is SERVICE, RSA_PUBLIC_KEY_FP has a value, HAS_PASSWORD is false
SHOW GRANTS TO ROLE LIFESIGHT_READERUSAGE on the warehouse, database and schema, and SELECT on each table you granted. Nothing else.

Result: the user LIFESIGHT exists with the role LIFESIGHT_READER, and can only read what you granted.

📘

Adding more later

To add a database, schema or table, add the matching lines and run the script again. It is safe to run more than once.

🚧

Does your Snowflake account restrict sign-in by IP?

If your account uses a network policy, also run this, or the connection test in step 4 will fail. Lifesight's IP address is 34.46.164.161.

CREATE NETWORK POLICY LIFESIGHT_NETWORK_POLICY
  ALLOWED_IP_LIST = ('34.46.164.161');

ALTER USER LIFESIGHT SET NETWORK_POLICY = 'LIFESIGHT_NETWORK_POLICY';

Step 3: Save the private key

  1. Copy everything in rsa_key.p8, including the BEGIN and END lines:
    • Mac: run pbcopy < rsa_key.p8 in the terminal. The key is now on your clipboard.
    • Windows: open the file in Notepad, select all and copy.
  2. Save it in your company password manager, such as 1Password or Bitwarden.
  3. Delete rsa_key.p8 from your computer.

Result: the private key is stored safely and ready to paste in step 4.

🚧

Treat the private key like a password

Do not send it by email or chat. Share it through the password manager. Lifesight never shows the key again, so keep your copy. If it is lost or shared by mistake, repeat steps 1 to 3 to create a new one.

Before you go on to Part 2, check you have:

WhatExample
Private keySaved in your password manager
Account identifieracme-xy12345
WarehouseLIFESIGHT_WH
UserLIFESIGHT
RoleLIFESIGHT_READER
Databases and schemasANALYTICS with MARKETING

Part 2: Connect Snowflake in Lifesight

You do this part for each table you want to bring in. The credential from Part 1 is reused every time.

Step 4: Name and connect

  1. Go to Data > Integrations and click Add Integration.
  2. Open the Data Warehouses tab and click Connect on the Snowflake tile.
The Data Warehouses tab with the Snowflake tile
  1. Enter an Integration Name, for example Paid media daily.
The Name & Connect step of the Snowflake setup
  1. Click Add credential and fill in the form:
FieldWhat to enter
Credential nameAny name that helps you recognise this sign-in, for example Acme analytics
Account identifierYour account identifier. Not the full URL.
WarehouseYour warehouse name
Role (optional)LIFESIGHT_READER
UsernameLIFESIGHT
Passphrase (optional)Leave empty
Private keyPaste the private key from your password manager
Databases and schemasEvery database and schema you granted. Click Add database for more.
The Add Snowflake credential dialog
  1. Click Test and save.
  2. Choose the Database and Schema for this integration.
  3. Click Verify and continue.

Result: the credential is saved and you move to the table list. If the test fails, see Troubleshooting.

📘

Next time, skip the form

The credential is saved. For your next table, pick it from the list in action 4.

Step 5: Select a table

  1. Find the table you want. Use the search box in a large schema.
  2. Select it. Its columns and their types show on the right.
  3. Click Continue.
The Select a table step, with a table chosen and its columns listed on the right
📘

Cannot pick a table?

A table that is already connected cannot be picked again, so the same data is never counted twice. If your table is not in the list, see Troubleshooting.

Step 6: Set up the data

  1. Answer the questions about your table:
QuestionWhat to choose
Which column has the date?The column that says which day, week or month each row belongs to
GranularityDaily, Weekly or Monthly
How is your data laid out?Stacked rows: every row is one record, and channels appear as values in a column
What kind of data is this?Paid Marketing, KPI, Contextual, Organic or Other. Other keeps the table for reference only.
Columns to import (optional)All columns, or only the ones you need
The Data setup step, with the date column, granularity and layout choices
  1. Check the Preview to make sure it is the right table.
The preview of the first rows of the table on the Data setup step
  1. Click Continue.

Step 7: Schedule and sync

  1. Choose how often Lifesight refreshes the data:
FrequencyWhat you set
DailyThe time of day
WeeklyThe day of the week and the time
MonthlyThe day of the month and the time
  1. Choose how much to read each time:
OptionWhat it doesUse it for
Full reload (recommended)Reads the whole table and replaces what was loaded beforeMost tables
Incremental by date columnReads only new rows, plus a few days back for late changesVery large tables with a date or timestamp column
  1. Click Connect.

Result: the integration is connected. The first sync starts straight away, then repeats on your schedule.


After connecting

NextWhat to doGuide
Map the fieldsOpen Data > Data Transformation, find your Snowflake source and match its columns to Lifesight fields. Nothing is mapped until you do this.Data Transformation, Map a field from a source
Fill in what the table does not sayIf the whole table belongs to one channel or one country, set that once.Set a fixed value for a source, Map a source to a channel
Bring in another tableRepeat steps 4 to 7 and pick the same credential. You do not need another Snowflake user.Part 2

Troubleshooting

Signing in

What you seeWhat to do
Snowflake rejected the sign-inCheck the username, and that the private key belongs to the public key set on that user.
Snowflake has no account with that nameEnter only the account identifier, not the full URL.
"JWT token is invalid"The public key on the Snowflake user is missing or has line breaks. Run section 2 of the script again with the key as one line.
The private key is not acceptedThe key must start with -----BEGIN PRIVATE KEY-----. If it starts with -----BEGIN RSA PRIVATE KEY-----, repeat step 1.
The test fails only from LifesightYour account probably has a network policy. Allow Lifesight's IP address 34.46.164.161, as shown in step 2.

Role and warehouse

What you seeWhat to do
The user cannot use the roleGrant the role to the user, or leave Role empty to use the user's default role.
The warehouse cannot be usedCheck the warehouse name, and that the role has USAGE on it.
"No active warehouse"Use the same warehouse name in the script and in Lifesight.

Databases, schemas and tables

What you seeWhat to do
Lifesight cannot read a database or schemaCheck the spelling, and that the role has USAGE on both the database and the schema.
My table is not listedCheck that the role has SELECT on it, that it is a table or materialized view, and that it is not already connected.
A table I added later does not appearAdd a GRANT SELECT line for it and run the script again, or use Option B.

Still stuck? Contact Lifesight support and include the message you see.


Next up

Connect your:

  • Google Sheets
  • CSV Import
  • Google Ads

Did this page help you?