Skip to content

Snowflake

Connect to run queries

Snowsight

Go to app.snowflake.com/rebtel/prod, log in with Google and create a worksheet.

Client application (DBeaver, DataGrip, ...)

Setting Value
Host rebtel.eu-west-1.snowflakecomputing.com
Port 443
Database DW4_PROD
Schema DBT
Warehouse END_USER_ADHOC_WH
Username your Google account, e.g. john.doe@rebtel.com
Role READ_ONLY
Authenticator externalbrowser

Python notebook

Install snowflake-connector-python, pandas and sqlalchemy, create a connection with your account details, then query into a pandas DataFrame.

Where to find what

  • Query only DW4_PROD.DBT. Other databases and schemas are data team internal and can change without notice
  • Tables and views are interchangeable for querying
  • Fact* hold transactional information and metrics, Dim* hold the dimensions to slice them by
  • Int* and Stg* are intermediate and stage models, not for end users
  • Unprefixed models are datasets built for specific business questions, e.g. BundleExpiryReminder
select *
from DW4_PROD.DBT."FactCalling"
limit 100;

dbt development connection

dbt Cloud IDE and CLI

Go to Account Settings > Your Profile > Credentials > Data Team's DBT Project. Under Development credentials, choose Username and password. This requires MFA with the Duo app.

dbt Core

In profiles.yml set either:

  • authenticator: externalbrowser (SSO, recommended, no password or MFA needed)
  • authenticator: username_password_mfa

See the dbt docs.

Create a user

Emails are firstname.lastname@rebtel.com. Use the stored procedure, which parses first and last name from the email:

call dw4_stage.s3.create_snowflake_user('email_goes_here');

It runs:

create user firstname_lastname
    login_name = 'firstname.lastname@rebtel.com',
    display_name = 'Firstname Lastname',
    email = 'firstname.lastname@rebtel.com',
    first_name = 'Firstname',
    last_name = 'Lastname',
    type = person;

grant role READ_ONLY to user firstname_lastname;

alter user firstname_lastname
set default_warehouse = 'END_USER_ADHOC_WH',
    default_role = 'READ_ONLY',
    default_namespace = 'DW4_PROD.DBT';

Create a Service User

Use for POCs or production access. Authenticate with RSA key pairs, created separately (Snowflake docs). Follow the data governance rules.

use role accountadmin;
create role USERNAME_ROLE;
create user USERNAME type = service;

use role sysadmin;
create warehouse USERNAME_WH
    with warehouse_size = 'X-Small'
    auto_suspend = 60
    auto_resume = true;

use role accountadmin;
grant usage on warehouse USERNAME_WH to role USERNAME_ROLE;
grant role USERNAME_ROLE to user USERNAME;
alter user USERNAME set default_role = 'USERNAME_ROLE';
alter user USERNAME set default_warehouse = 'USERNAME_WH';

grant usage on database DW4_PROD to role USERNAME_ROLE;
grant usage on schema dw4_prod.dbt to role USERNAME_ROLE;
grant select on all tables in schema dw4_prod.dbt to role USERNAME_ROLE;
grant select on future tables in schema dw4_prod.dbt to role USERNAME_ROLE;
grant select on all views in schema dw4_prod.dbt to role USERNAME_ROLE;
grant select on future views in schema dw4_prod.dbt to role USERNAME_ROLE;

alter user USERNAME set rsa_public_key = '<public key without BEGIN/END tags>';

Generate the keys in bash:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Verify the fingerprint from desc user USERNAME; (RSA_PUBLIC_KEY_FP) matches:

openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

Request access to PII data

PII access must be requested and approved before use, in any tool (Looker, Count, Snowsight, DBeaver, notebooks).

  1. Submit the PII Access Request Form: why, which fields, which tools, how long, and agreement to the usage guidelines
  2. Your manager reviews it
  3. The Compliance committee reviews it (Legal for high-risk requests)
  4. Access is granted, or you get a reason and alternatives

Rules:

  • Query PII only through approved tools, only for the approved purpose, only the minimum needed
  • Never copy PII to personal devices, Drive or email, and never share externally without approval
  • Access is temporary (30 days by default). Permanent access needs strong justification and periodic review
  • Access is logged, reviewed quarterly, and violations can lead to revocation

For the data team

Once approved:

grant role READ_ONLY_PII to user user_name;
alter user user_name set default_role = 'READ_ONLY_PII';

If the user needs it in Looker, reset the persisted delegated authorization so they log in again via OAuth:

show delegated authorizations by user user_name;
alter user user_name remove delegated authorization of role READ_ONLY from security integration LOOKER;

Ad-hoc file upload

Temporary uploads for one-off checks. Anything for production goes through the data team.

  1. Open Snowsight with the READ_ONLY role, go to DW4_STAGE.TMP_STATIC, then Create > Table > From File
  2. Name the table clearly and append _YOURNAME, e.g. nigeria_phone_number_quentin
  3. .xlsx is not supported, convert to CSV. Keep default options and check the columns and types
  4. Click Load

The data team is notified. The table is deleted 7 days after its last change.