Suela Isaj is a Data and ML engineer at Churney. She holds a Joint PhD in Computer Science from Aalborg University and Université libre de Bruxelles in the field of spatial data extraction and entity resolution. Suela has previously worked as data engineer at Issuu, and data quality officer and risk system specialist at Raiffeisen Bank.

Suela Isaj

Data and ML Engineer

Suela

Jonas is the head of data engineering at Churney. He previously worked as data engineering team lead at LiveIntent. Jonas holds a PhD in Mathematical Physics from Aarhus University.

Jonas Dahlbæk

Head of Data Engineering

Jonas

We would need a role in AWS with the minimal permissions to access the data in Athena and export it to an S3 bucket. On a high level, we need access to:

The next steps explain the creation of the secure views for the data that you would like to share with Churney. If you would like Churney’s help to create the views of top of your tables and hash the private information, then follow 

Go to the query editor in Athena and choose the context, the data source and the database where your raw data lies.

 belongs to the schema with the tables you would like to share with Churney.

SELECT 
    t.table_catalog, 
    t.table_schema, 
    t.table_name, 
    c.column_name, 
    c.data_type
FROM information_schema.tables t 
INNER JOIN information_schema.columns c
ON t.table_catalog = c.table_catalog
    AND t.table_schema = c.table_schema
    AND t.table_name = c.table_name
WHERE t.table_schema = '<your_schema_name_here>'  
ORDER BY t.table_name;

Export the result by clicking on Download results as below,  and share the csv with Churney

We will come back to you with some create view scripts to automatically create the views and hash the data.

In the case of json fields, if there is private information in those fields like email, phone, last name, first name, etc, then we should skip the column from the view, and unpack the elements we need. The private information will also be included in the view for us, but we can modify the create view statement to unpack and hash the private field. In these cases, what we need from you is the name of the json column that has private information, and then the key that we should search for to unpack and hash. For the rest of the json fields that do not have any private user identifier, we can read them as they are.

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

. Basically, 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.

Churney wants to have access only to hashed sensitive data, and not to the raw data of the private information of the users. We will go over limiting our permissions in the next section, and in this section, we will guide you to create views on top of the raw data, which hash the private information.

2. Create hashed views on top of the raw data. Let’s suppose that the raw data lies in a database called 

 and the table with the user information is called 

, which contains private information like first_name, last_name, email, etc. In the code snippet below, we will create a view that hashes the private information, and leaves the rest of the columns as raw. Note that if your table (e.g. event tables) do not contain any private information, your view can be just a select from the table.

CREATE OR REPLACE VIEW churney.user_activity AS 
SELECT
    id,
    LOWER(TO_HEX("sha256"("to_utf8"("trim"(first_name))))) first_name,
    LOWER(TO_HEX("sha256"("to_utf8"("trim"(last_name))))) last_name,
    LOWER(TO_HEX("sha256"("to_utf8"("trim"(email))))) email,
    amount_spent,
    activity_at
FROM
    mydatabase.user_activity;

After you are done with creating all the views that you would like to give access to Churney, do a simple test by running a select query to make sure everything you have hashed looks correctly, e.g.

Choose a unique name for the bucket, and something self-explanatory, like 

choose the region of this bucket the same as your Athena region. 

, then the region of the bucket should be 

Make sure the rest of the setting are the default ones and they look like below

And the s3 bucket doesn’t have public access

to create a lifecycle rule that cleans old objects from the bucket, so Churney manages your storage carefully.

https://docs.aws.amazon.com/athena/latest/ug/workgroups-procedure.html

console.aws.amazon.com/athena/home?region={region-name}#/workgroups

https://us-east-1.console.aws.amazon.com/athena/home?region=us-east-1#/workgroups

Give the workgroup a unique name, we would suggest something simple like 

Expand the Additional Configurations section and fill them as below, where the default location of the query result of Churney would always be on our bucket, which is the bucket that was created in Step 1-3. In this example, we used 

Figure out the database parameters that you will need to provide. Churney will need the name of the table(s) in Athena to export from, the database, and the data source. You can find these parameters in Athena, for example, in the screenshot below, if the table that will contain the data is 

. Keep these parameters in mind because they will be needed for the steps below.

console.aws.amazon.com/iamv2/home?region={region-name}#/policies  

 is the name of the region where your Athena lie, for example if the region is 

https://us-east-1.console.aws.amazon.com/iamv2/home?region=us-east-1#/policies

Create a policy for Athena as below, where you need to replace all in bold: 

 with the region where Athena lies, for example 

 with the id of the Athena admin account. You will also need to provide the 

 which is the name you provided in Step 6 and 7

Create a policy for the S3 bucket where Churney will export the data. Replace 

  with the name of the bucket you chose in Step 1-3. Note that is the bucket where Churney will unload the hashed data from the views you created above.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucketMultipartUploads",
                "s3:ListBucket",
                "s3:DeleteObject",
                "s3:GetBucketLocation",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": [
                "arn:aws:s3:::<churney-bucket-name>/*",
                "arn:aws:s3:::<churney-bucket-name>"
            ]
        }
    ]


Create a policy for AWS Glue to access the hashed views. Replace 

 with the id of the Athena admin account. Replace 

 with the named of the database with the hashed views, so in this example, that would be 

Create a policy for AWS Glue to be able to read data from the hashed views. For this, we need access to the tables from which you created hashed views. Replace 

 with the name of the database as described in Step 10, and 

 with the names of the raw tables you created hashed views for. In our example, we had only 1 table 

Note that by listing all the tables, we limit access only to those tables that you would like to share with Churney.

Create a policy for the S3 bucket where Athena queries tha data from. This is the location of the physical table data that Athena refers to. To find the bucket of the data, you can click on the three dots in the table you want to find the s3 bucket for, for example in the screenshot below the table is 

. in Location, highlighted in blue, you can see the s3 bucket. In this example, that is s3://one-time-athena-experiment/activity, so 

Note that we are adding a condition to limit the access to S3 only through Athena! This means that Churney cannot read the contents of your S3 bucket directly unless we access it through Athena.

It can happen that your tables originate from different S3 buckets, so make sure to check the physical S3 location of each table that you will share with Churney through views. Let’s suppose you have two different buckets, then the above code would look like:

 is the region where Athena is and click on Create role. For the purpose of the example, the name of the role is 

Choose a custom trust policy and add the trust policy as below, Churney will give you the ids needed for this step, so what you should fill in is 

 The way we will use this trust policy is all described in this link and follows the best practices 

https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-idp_oidc.html

On the next step, you will be required to add permissions, remember to add all permissions we created on Step 11, 12, 13, 14, and 15

And the role should finally look like this, where you can see the policies and if you click on Trust relationships, you should be able to see the trust relationship

After you are done with the above steps, you will need to send these details to Churney:

1 .The arn of the role you created in Step 17. You can find that in this link 

console.aws.amazon.com/iamv2/home#/roles/details/<role-name>?section=permissions

 is the name of the region where Athena lies and 

 is the name of the role you created in Step 17. For our example, the link would be 

https://us-east-1.console.aws.amazon.com/iamv2/home#/roles/details/churney-role?section=permissions

 and the ARN is the one marked in red below

2 .The details of the Athena data (the hashed views), which would be 

3 . The name of the S3 bucket where Churney will unload the data, this is the bucket you created in Step 1

4 . The name of workgroup you created above in Step 6. 

Here you can read our guide for connecting your Athena data warehouse with Churney. 

Connecting Athena to Churney

Athena Connector

In this guide, we will go over the steps for setting up permissions for Churney to access your Redshift cluster, using hashed views and minimal permissions. On a high level, Churney will need:

A Churney user for the Redshift cluster with read permissions only on the secure views

An s3 bucket for Churney to unload the data

Access permissions to connect to your Redshift cluster through a static IP

For this task, you can either ask Churney to help you create the hashed views, or decide to create them yourself. If you would like Churney’s help, follow up the procedure of 

Open the query editor and set up the working environment, choose the correct cluster and database above:

SELECT 
   t.table_catalog, t.table_schema, t.table_name, c.column_name, c.data_type
FROM 
   information_schema.tables t inner join information_schema.columns c
ON t.table_catalog = c.table_catalog
   AND t.table_schema = c.table_schema
   AND t.table_name = c.table_name
WHERE t.table_schema = '<your_schema_name_here>'  
  ORDER BY t.table_name;

Export the result, either as json or csv and share it with Churney

Consider this setting, under database dev there is a schema named private with your raw data, containing sensitive data. We will need to create a new schema for Churney with secure views that read from private

Make sure the sensitive data is hashed correctly and the non-sensitive data is on plain text. 

(in bold above), because Redshift does not support unloading time data in the parquet format

If the table doesn’t contain any private data, then the view can list the plan column names.

When you are done with creating all the views, it is time to create the Churney user

3. Create a Churney user and grant access to the data. In the running example, you would replace 

The below permissions mean that we are giving the user Churney access to select data from the view, but since the view refers to the raw data in private, we need to grant usage for private as well. Note that Churney can ONLY select from the view, and not from the raw data. The below permissions are detailed in the AWS documentation 

https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html

CREATE USER churney password disable; 

GRANT USAGE ON SCHEMA <your-schema> TO churney; 

GRANT USAGE ON SCHEMA <churney-schema> TO churney; 

GRANT SELECT ON ALL TABLES IN SCHEMA <churney-schema> TO churney; 

Allow access for the Churney IP to your redshift cluster 

Churney will give you a static ip, that you will use in the below step. For the purpose of this example, we will use 12.123.123.123

console.aws.amazon.com/vpc/home?region=<region-name>#SecurityGroups

 is the region of your cluster to create a VPC security group and click on 

5. Create a VPC security group in the VPC of your Redshift cluster and add an inbound rule for the the Churney static ip 12.123.123.123

Choose Type = Redshift and put the Churney static ip (in our example 12.123.123.123) in the box where the arrow is below. You will see the ip is added when it is added where the red box is.

We will add this security group to the Redshift cluster. For that, go to your Redshift cluster and click on Properties

And finally add the new security group. Make sure you 

7. Create an s3 bucket in the region of your Redshift cluster that we can unload data into, so go to 

https://s3.console.aws.amazon.com/s3/buckets?region=<region-name>

 is the same as your Redshift cluster region. Create a bucket with a self-explanatory name, like 

8. Add a lifecycle rule to the bucket, to delete the content after 7 days. Go go to the bucket you created above and click on 

Make sure you have the below selections: 

IAM Policies to allow for Churney to extract data from Redshift 

9. Create IAM policy ChurneyRedshiftS3 to allow Churney to access the S3 bucket you created in Step 7. Churney should have access to unload the data in this bucket. Replace 

 with the name of the bucket you chose in Step 7, in our example, that is 

To find where you create policies, go to 

console.aws.amazon.com/iamv2/home#/policies

 is the region of your Redshift cluster and click on Create Policy

10. Create an IAM policy to allow Churney to unload data from Redshift. We will limit these permissions to the user you created before and to the database with the hashed views that you created for Churney.

 where region-name is the region of your Redshift cluster and click on Create Policy

11. Create a role for Churney that will have the two policies created above and a trust relationship. Go to 

 is the region where Redshift is and click on Create role. For the purpose of the example, the name of the role is churney-redshift-role

Choose Custom Trust Policy and put the trust policy as in the code snippet below. The way we will use this trust policy is all described in this link and follows the best practices 

 you will put two ids that you will get from Churney. These are the service accounts used from us to transfer data to our google cloud.

Attach the policies you created in Step 9 and Step 10

Here you can read our guide for connecting your Redshift data warehouse with Churney. 

Connecting Redshift to Churney

Redshift Connector

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

. 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 

Open a worksheet and run the following query, where 

 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 

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

Snowflake data sharing with reader account

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

Snowflake with reader account - Snowsight

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 

 and choose the views you want to share with Churney

7. Select the share you just created and click on 

. 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.

Snowflake with reader account - classic console

Review the Secure Share, Preview Tables & Validate Secure Views

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

Here you can read our guide for connecting your Snowflake data warehouse with Churney. 

Connecting Snowflake to Churney

Snowflake Connector

We follow the Google standard for sharing data in a secure way, described in 

https://cloud.google.com/bigquery/docs/share-access-views

To create a new dataset with the views, go on your project where the raw data is and click on the three dots to create a new dataset, for example, if your project is called 

To create the views, we would need to create queries to hash the PII columns.

. Basically, 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 etc.

For each potentially relevant dataset, we would ask you to run the following query:

SELECT
  table_schema, table_name, ddl
FROM
  <dataset_name>.INFORMATION_SCHEMA.TABLES;

If the raw data contains email, phone, birthday or other identifiers, the columns need to be excluded and hashed in the view. 

CREATE OR REPLACE <your_project>.churney.sensitive_data_view as ( 
select 
TO_HEX(SHA256(LOWER(TRIM(email)))) as email,
TO_HEX(SHA256(LOWER(TRIM(name)))) as name,
TO_HEX(SHA256(LTRIM(regexp_replace(phone, '[()-]', ''), '0'))) as phone,
TO_HEX(SHA256(FORMAT_DATE('%Y%m%d',birthday))) as birthday,
* EXCEPT(email, name, phone, birthday)
from <your_project>.raw_data.sensitive
)

if you want to test it how the script looks with some data:

with sensitive as
(
  select 1 as id,
  "abc" as name,
  "abc@abc.com" as email,
  current_date() as birthday,
  '+4511111111' as phone
)
select 
TO_HEX(SHA256(LOWER(TRIM(email)))) as email,
TO_HEX(SHA256(LOWER(TRIM(name)))) as name,
TO_HEX(SHA256(LTRIM(regexp_replace(phone, '[()-]', ''), '0'))) as phone,
TO_HEX(SHA256(FORMAT_DATE('%Y%m%d',birthday))) as birthday,
* EXCEPT(email, name, phone, birthday)
from sensitive

Churney will need permissions to read the hashed views. We base our guide on the best practices published on Google documentation 

Churney will give you a service account and below you can find which permissions to assign to it:

1. Assign BigQuery User role to Churney service account 

https://cloud.google.com/bigquery/docs/share-access-views#assign_a_project-level_role_to_your_data_analysts

For this, you need to go to the IAM page of the project where you created the views for Churney and add 

BigQuery User to the service account. Note that this does not give Churney permission to access the data under your project.

2. Give Churney service account permission to access the dataset with the views created previously 

https://cloud.google.com/bigquery/docs/share-access-views#assign_access_controls_to_the_dataset_containing_the_view

This requires that you go to the dataset and click on the three dots on the side to select 

 and add the Churney service account with the permission 

Authorize the view to access the source data. This means that Churney, only through the view, would be able to read the data. 

https://cloud.google.com/bigquery/docs/share-access-views#authorize_the_view_to_access_the_source_dataset

For this step, go on the raw data dataset (in this example, that is called synthetic), and click on the dataset itself (not the 3 dots) and choose 

Then finally, type the name of the authorized view that you created above

Here you can read our guide for connecting your BigQuery data warehouse with Churney. 

Documentation