Direct Access Self-service Option

Setup

These instructions are only for customers that have chosen the Direct Access self-service option. Data sharing is a procedure that allows Snowflake to share Localytics data across Snowflake accounts without replicating data. In order to access your Localytics data, you will need to create a database from the Localytics data share as follows:

-- Import the shared database by creating a database from the share.                          
-- In the create statement, replace <source_account_name> with the name of your account;

use role accountadmin;

-- create role for general usage
CREATE ROLE READ_LOCALYTICS;

--  grant role to user or other main
GRANT ROLE READ_LOCALYTICS to role accountadmin;
GRANT ROLE READ_LOCALYTICS to user USERNAME;

show shares;

create database localytics from share localytics.<source_account_name>;

-- Grant privileges on the imported database.                                                  
grant imported privileges on database localytics to role READ_LOCALYTICS;

-- Query the views in the imported database.                                                   
use role READ_LOCALYTICS;

show views;

-- Test a query
select * from localytics.<schema>.localytics_applications;

Combining Localytics data with other sources

If you’ve chosen the Direct Access self-service option, you easily join Localytics data to other sources you upload to Snowflake to create integrated analytics views.

Joining two databases: Step-by-step

1) In Snowflake, it important to know that you can easily join across databases provided your role has read permission on both databases. Let’s say you use your LOCALYTICS_READ role to read Localytics data, and you also have a database called CRM using the public schema. You should run:

grant usage on database CRM to role localytics_read;
grant usage on schema CRM.public to role localytics_read;
grant select on all tables in schema CRM.public to role localytics_read;

Note that this does NOT give Localytics access to your data. Data shares are only in one direction. Likewise, this is why you cannot modify anything in your Localytics database.

2) In the worksheet, check that your role is set to localytics_read.

3) When writing SQL to combine data sources, you should use the fully qualified tables names:

SELECT count(1)
FROM CRM.public.customers c
JOIN localytics.SCHEMA.app_profiles p
ON sha1(c.customer_id) = p.hashed_customer_id
WHERE p.profiledb_id = 12345

Or, if you’re joining to a fact_ table:

SELECT count(1)
FROM CRM.public.customers c
JOIN localytics.SCHEMA.fact_sessions fs
ON c.customer_id = fs.customer_id
WHERE fs.app_id = 123
AND client_date = current_date()

The first example above assumes you have imported customer data into Snowflake with the same customer_id you set in the SDK for Profiles. You can use the sha1 function to the hashed_customer_id in app_profiles and org_profiles tables. We also store the raw, unhashed customer_id in these tables where attribute_key = '_ll.raw_customer_id'. In the second example, the fact_* tables store both hashed_customer_id and customer_id so there is no need to use the sha1 function.