Snowflake

Snowflake is the core data platform hosting all storage and compute for the roche-data pipeline. It houses the full medallion architecture: physical Bronze tables (append-only), validated Silver views, business-ready Gold views, and the Semantic Layer for Cortex Analyst. Quality gates are embedded as view predicates — no separate DQ engine needed.

Connection Details

Property	Value
Account	`roche_dia.eu-central-1`
URL	`https://roche_dia.eu-central-1.snowflakecomputing.com/`
Auth method (CI/CD)	External OAuth (PingFederate `client_credentials` grant via WAM)
Auth method (local dev)	External Browser SSO (PingFederate)
Network	Cloud (direct — no VPN required)
Access tasks	A05, A06
GitHub issue	#23
ServiceNow request	RITM6279879
Service account	S1DEINGI (RXPMODE1)

Environment Variables

Variable	Source	Description
`SNOWFLAKE_ACCOUNT`	Vault `common/snowflake`	Account identifier (`roche_dia.eu-central-1`)
`SNOWFLAKE_URL`	Vault `{env}/snowflake`	Full account URL
`SNOWFLAKE_DATABASE`	Vault `{env}/snowflake`	Target database (`RDT_MODEL`)
`SNOWFLAKE_WAREHOUSE`	Vault `{env}/snowflake`	Compute warehouse (`RDT_MODEL_WH_DEV` / `_TEST` / `_PROD`)
`SNOWFLAKE_SCHEMA_PREFIX`	Vault `{env}/snowflake`	Schema prefix (`DEV` / `TEST` / `PROD`)
`SNOWFLAKE_ROLE`	Vault `{env}/snowflake`	Execution role (`RDT_MODEL_DEV_DEPLOYER` / `_TEST_` / `_PROD_`)
`SNOWFLAKE_USER`	Vault `{env}/snowflake`	Service account (`S1DEINGI@EMEA.ROCHE.COM`)
`SNOWFLAKE_USER_PWD`	Vault `{env}/snowflake`	User password (for ROPC flow)
`SNOWFLAKE_AUTH_METHOD`	Vault `{env}/snowflake`	Auth method (`oauth`)
`SNOWFLAKE_OAUTH_TOKEN_URL`	Vault `{env}/snowflake`	WAM token endpoint (`https://wam.roche.com/as/token.oauth2`)
`SNOWFLAKE_OAUTH_SCOPE`	Vault `{env}/snowflake`	OAuth scope (`session:role:PUBLIC`)
`SNOWFLAKE_OAUTH_CLIENT_ID`	Vault `{env}/snowflake`	PingFederate client ID
`SNOWFLAKE_OAUTH_CLIENT_SECRET`	Vault `{env}/snowflake`	PingFederate client secret
`SNOWFLAKE_WAM_USERNAME`	Vault `{env}/snowflake`	WAM username (default: `S1DEINGI`)

Database Architecture

The project uses a split-database strategy:

Database	Purpose	Schemas
`MDM_DEV`	Bronze layer (Iceberg tables)	`RAW_DV_MDM`
`BUSINESS_MDM_DV`	Silver/Gold views	`BUSINESS_DV_MDM`, `DP_MDM`

Schema Mapping

Layer	Schema	Content
Bronze	`{PREFIX}_BRONZE` or `RAW_DV_MDM`	Iceberg append-only tables (open format, S3-backed)
Silver	`{PREFIX}_SILVER` or `BUSINESS_DV_MDM`	Validated views (G2 predicates)
Gold	`{PREFIX}_GOLD` or `DP_MDM`	Business-ready views (G3+G4 predicates)
Semantic	`{PREFIX}_SEMANTIC`	Semantic view definitions for Cortex Analyst

CLI Modules

Module	Usage
`rdt-model-store`	Generates DDL, dbt models, and deploys across all layers
`rdt-model-snowflake-horizon`	Registers data products in Snowflake Horizon for cross-account discovery
`rdt-model-profile`	Discovers upstream tables for onboarding entities without RTiS representation

Access Verification

Script: scripts/access/check-snowflake.sh

Required tools: snowsql, curl, jq

Required env vars: SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_USER_PWD, SNOWFLAKE_OAUTH_CLIENT_ID, SNOWFLAKE_OAUTH_CLIENT_SECRET, SNOWFLAKE_ROLE

Checks performed:

OAuth token acquisition via WAM (ROPC grant with role scoping)
Snowflake connection verification (SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_WAREHOUSE())
Database visibility: MDM_DEV, MDM_TEST, MDM_PROD
Schema access in MDM_DEV: RAW_DV_MDM, BUSINESS_DV_MDM, DP_MDM
DDL permissions (CREATE ICEBERG TABLE, CREATE VIEW on MDM_DEV.RAW_DV_MDM)

Authentication

CI/CD — External OAuth (PingFederate)

Token acquisition via WAM Resource Owner Password Credentials (ROPC) grant:

TOKEN=$(curl -s -X POST "https://wam.roche.com/as/token.oauth2" \
  -d "grant_type=password" \
  -d "client_id=$SNOWFLAKE_OAUTH_CLIENT_ID" \
  -d "client_secret=$SNOWFLAKE_OAUTH_CLIENT_SECRET" \
  -d "username=$SNOWFLAKE_WAM_USERNAME" \
  -d "password=$SNOWFLAKE_USER_PWD" \
  -d "scope=session:role:PUBLIC" \
  | jq -r '.access_token')

Local Development — External Browser SSO

For interactive development, use External Browser authentication (STREITS user):

snowsql --accountname roche_dia.eu-central-1 \
  --authenticator externalbrowser \
  --username YOUR_EMAIL@roche.com

Key-pair Authentication (planned for CI/CD)

RSA key-pair auth (S1DEINGI) is the planned long-term approach for CI/CD. PAT (Programmatic Access Tokens) are not approved at Roche.

Generated Artifacts

Artifact	Output path	Layer
Bronze DDL	`snowflake/ddl/{entity}.sql`	Bronze
Bronze COPY INTO	`snowflake/copy_into/{entity}.sql`	Bronze
dbt Bronze model	`dbt/models/bronze/{entity}.sql` + `.yml`	Bronze
dbt Silver view	`dbt/models/silver/{entity}_silver.sql` + `.yml`	Silver
dbt Gold view	`dbt/models/gold/{entity}_gold.sql` + `.yml`	Gold
Semantic view	`dbt/models/semantic/{entity}.semantic.yml`	Semantic

Current Status

Authentication: OAuth token acquisition confirmed working (2026-04-29)
Database provisioning: Pending (RITM6279879 — requested 2026-04-24)
Workaround: All modules generate artifacts in --dry-run mode without Snowflake access
Blocking: Database/schema/role provisioning before DDL can be deployed