Connecting Snowflake to Churney

Our data sharing procedure follows the best practise suggested by Snowflake https://docs.snowflake.com/en/user-guide/data-sharing-secure-views#step-3-validate-tables-and-secure-view. First, we will create secure views, and then a reader account and a share to be able to share it with Churney.

For context, Facebook has a guide on how to hash contact information for their conversion api: https://developers.facebook.com/docs/marketing-api/conversions-api/parameters/customer-information-parameters. Google has a similar guide for enhanced conversions using their ads api https://developers.google.com/google-ads/api/docs/conversions/enhance-conversions. In our case, we want to hash columns that contain data which would allow one to determine the identity of a user: First name, last name, birth day, street address, phone number, email address, ip address.

If you would like Churney's help with the secure views, follow Generating secure views, otherwise, jump to An example of a secure view

Open a worksheet and run the following query, where <your_database_name> and  <your_dataset_schema_name> is the name of the database and the name of the dataset of the tables that you wish to share securely with Churney 

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM <your_database_name>.INFORMATION_SCHEMA.COLUMNS 

where table_schema = <your_schema_name>

Download the output as csv and share the csv file with Churney. We will come back to you with a text file with the secure view scripts that you can run to create the secure views.

Open a worksheet and run the following command, where <your_database_name is the name of the database where the data is lying.

use role accountadmin;
create or replace schema <your_database_name>.CHURNEY;

Let's assume the raw table has the following ddl

create or replace table mytestdb.private.sensitive_data (
   name string,
   email string,
   phone_number string,
   birthday date,
   date date,
   time time(9),
   bid_price float,
   ask_price float,
   bid_size int,
   ask_size int
)
   cluster by (date);

Then the secure view script will be the following:

create or replace secure view mytestdb.public.paid_sensitive_data as
   select
       sha2(lower(trim(name))) name,
       sha2(lower(trim(email))) email,
       sha2(ltrim(trim(regexp_replace(phone_number, '[ +()-]', '')), '0')) phone_number,
       sha2(to_char(birthday, 'YYYYMMDD')) as birthday
       date,
       time,
       bid_price,
       ask_price,
       bid_size,
       ask_size
   from mytestdb.private.sensitive_data;

Following the above example, you can create secure views for all the tables that you would like to share with Churmey

First create a Reader Account following https://docs.snowflake.com/en/user-guide/data-sharing-reader-create.html. 

Below, we will be using the UI to create the reader account. If you are using the Classic Console, jump to Snowflake with reader account - Classic Console

If you are using Snowsight, follow Snowflake with reader account - Snowsight

1. Make sure you are logged as AccountAdmin, you can check it as below, and switch the role accordingly

2. Go under Data -> Private Sharing and click on Reader Accounts

3. Fill out the Account Name, User Name and Password fields in the form

NOTE that Username and Password might be automatically filled from your current account. CHANGE them to the username and password you will create for Churney.

4. You will see the new account in the reader account. If you hover your mouse over Locator URL, you can copy it. You will need to share with Churney the Region, Locator URL, Locator, also the Username and Password of the above step.

5. Click on Shared by your Account and click on Share -> Create a Direct Share

6. Click on Select Data and choose the views you want to share with Churney

7. Select the share you just created and click on Add Consumers. Add the Churney account you created in Step 3

8. That is all! Share with Churney all the information in Step 4 and Churney will take care of the next steps.

1. In the Reader Accounts pane, click Create Reader Account

2. Fill out the Account Name, User Name and Password fields in the form

NOTE that Username and Password might be automatically filled from your current account. CHANGE them to the username and password you will create for Churney.

3. After clicking Create Account, you will be presented with the Region, Account URL and Locator.

4. Click the Shares menu button, switch to Outbound and click Create

5. Click Next: Add Consumers in the Review the Secure Share, Preview Tables & Validate Secure Views menu.

6. Add the recently created Reader Account as a consumer

7. Send us the Account URL along with login information. We can take care of any additional setup required in the Reader Account