Combining Localytics data with other sources
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.