# Snowflake Migration: Plan of Action ## Overview We are applying the [Snowflake Migration Runbook](https://www.notion.so/Snowflake-Account-Migration-sme-dataplatform-34897177520f81f99100d3be458fc3e4) to migrate our services across the boad by `May 11, 2026`. The data team has identified the following users and accounts [shown here](https://docs.google.com/spreadsheets/d/1mIyPB7kLZXMpGg3YZ3lMxRw4gQ3M2DqaeudhBLLhkuM/edit?gid=1415792079#gid=1415792079). The abacus team reviewed these documents and performed search to find all snowflake usages across all abacus' service inventory. Here are the key findings: 1. The `OWS_ACCOUNTING` is decomissioned and not referenced by the current Abacus infrastructure. 2. The [ows-abacus-legacy-sync](https://github.com/theorchard/ows-abacus-legacy-sync) references snowflake connectors but no longe uses it. 3. The [lambda-abacus](https://github.com/theorchard/lambda-abacus/) requires multiple changes at the repo level, and through the [terraform-infra](https://github.com/theorchard/terraform-infra) module. 4. The [ows-royalties-workflows](https://github.com/theorchard/ows-royalties-workflows) requires multiple changes at the repo level, and through the [terraform-infra](https://github.com/theorchard/terraform-infra) module. 5. There are miscellanous uses for shared data utilities that also require updates at the terraform or repo level for [python-neo4j-cypher-scheduler](https://github.com/theorchard/python-neo4j-cypher-scheduler) and [python-rds-utils](https://github.com/theorchard/python-rds-utils). We could use the help of the data team with these two repos. Here is the timeline communicated by the Data Team: 1. `Mon 5/11` Migrate connection strings 2. `Wed 4/13` Begin running parallel two snowflake accounts in parallel, synced. Address any issues. 3. `Mon 5/18` Turn off `Orchard` snowflake account if everything looks good. Migration is complete. ### ⚠️ Risks Identified For Accounting Accounting Run Risk - The May Accounting run begins on `5/12`. The run is approved on `5/14`, the balances are closed on `5/22` and payments sent out on `5/27`. During this time stakeholders work closely with Accounting systems (Abacus primarily), Snowflake reports, and Looker Reports. With the migration happening during the run there's the compounding risk of something going out of sync between these tools. For risk mitigation, the following must be airtight: EPIC: [LINK](https://theorchard.atlassian.net/browse/ACC-10331) ### Abacus Team - [ ] QA, UAT, and PROD PRs are raised by `5/7` - [ ] E2E automation suite has run successfully by `5/8` - [ ] The Abacus Dry-Run in QA has succeeded by `5/8` - [ ] Abacus has been successfully migrated in all environments by `5/11` - [ ] Pipelines are green and Monitors healthy ### Data Team - [ ] The new snowflake account is working correctly and reports are generated normally on the new account ### BI Team - [ ] The BI team has also completed their migration of the Looker reports by `5/11` ** Note that this is a tight timeline for the ABACUS team. If our testing doesn't succeed on `5/8` that gives us no window to course correct before the run starts. *If we encounter this blocker, we will communicate promptly by 5/8‼️*. ** Note that the coordination with the BI team is happening directly between The BI team and the Data team. ## Thursday 5/7 ### Prep changes - [x] Block automatic prod deployment for [lambda-abacus](https://github.com/theorchard/lambda-abacus/) — [ACC-10332](https://theorchard.atlassian.net/browse/ACC-10332) - https://github.com/theorchard/lambda-abacus/pull/1505 - [x] Block automatic prod deployment for [ows-royalties-workflows](https://github.com/theorchard/ows-royalties-workflows) — [ACC-10333](https://theorchard.atlassian.net/browse/ACC-10333) - https://github.com/theorchard/ows-royalties-workflows/pull/709 ### QA Changes - [ ] ⛔️ Lambda Abacus: Repo changes - example PR: https://github.com/theorchard/lambda-abacus/pull/1492/changes — [ACC-10334](https://theorchard.atlassian.net/browse/ACC-10334) - [ ] adjustment_file_prepare: https://github.com/theorchard/lambda-abacus/pull/1493 - [ ] adjustment_file_validation: https://github.com/theorchard/lambda-abacus/pull/1494 - [ ] adjustment_json_validation: https://github.com/theorchard/lambda-abacus/pull/1495 - [ ] commit_mechanicals: https://github.com/theorchard/lambda-abacus/pull/1496 - [ ] commit_royalties: https://github.com/theorchard/lambda-abacus/pull/1497 - [ ] correction_stage: https://github.com/theorchard/lambda-abacus/pull/1498 - [ ] earnings_transfer: https://github.com/theorchard/lambda-abacus/pull/1499 - [ ] generate_flowthrough_adjutments: https://github.com/theorchard/lambda-abacus/pull/1500 - [ ] ledger_accounting_run_balance: https://github.com/theorchard/lambda-abacus/pull/1501 - [ ] reserves_take: https://github.com/theorchard/lambda-abacus/pull/1502 - [ ] snapshot_contracts: https://github.com/theorchard/lambda-abacus/pull/1503 - [ ] Jenkinsfile: https://github.com/theorchard/lambda-abacus/pull/1504 - [x] Lambda Abacus: Terraform PR Raised and Deployed - https://github.com/theorchard/terraform-infra/pull/33855 — [ACC-10335](https://theorchard.atlassian.net/browse/ACC-10335) - [x] Workflows: Docker build - example PR: https://github.com/theorchard/ows-royalties-workflows/pull/708/changes — [ACC-10336](https://theorchard.atlassian.net/browse/ACC-10336) - [x] Workflows: Terraform PR Raised and Deployed — [ACC-10337](https://theorchard.atlassian.net/browse/ACC-10337) [NO SNOWFLAKE VAR FOUND FOR THIS] ### UAT Changes - [ ] ⛔️ Lambda Abacus: Repo changes - example PR: https://github.com/theorchard/lambda-abacus/pull/1492/changes — [ACC-10338](https://theorchard.atlassian.net/browse/ACC-10338) - [ ] ⛔️ Lambda Abacus: Terraform PR Raised but `Not Deployed` — [ACC-10339](https://theorchard.atlassian.net/browse/ACC-10339) - https://github.com/theorchard/terraform-infra/pull/33858 - [ ] Workflows: Docker build - example PR: https://github.com/theorchard/ows-royalties-workflows/pull/708/changes — [ACC-10340](https://theorchard.atlassian.net/browse/ACC-10340) - [ ] Workflows: Terraform PR Raised but `Not Deployed` — [ACC-10341](https://theorchard.atlassian.net/browse/ACC-10341) ### PROD Changes - [ ] ⛔️ Lambda Abacus: Repo changes - example PR: https://github.com/theorchard/lambda-abacus/pull/1492/changes — [ACC-10342](https://theorchard.atlassian.net/browse/ACC-10342) - [ ] Lambda Abacus: Terraform PR Raised but `Not Deployed` — [ACC-10343](https://theorchard.atlassian.net/browse/ACC-10343) - [ ] Workflows: Docker build - example PR: https://github.com/theorchard/ows-royalties-workflows/pull/708/changes — [ACC-10344](https://theorchard.atlassian.net/browse/ACC-10344) - [ ] Workflows: Terraform PR Raised but `Not Deployed` — [ACC-10345](https://theorchard.atlassian.net/browse/ACC-10345) ### Miscellanous Utils - [ ] [python-neo4j-cypher-scheduler](https://github.com/theorchard/python-neo4j-cypher-scheduler) updated, ideally by the Data team - [ ] [python-rds-utils](https://github.com/theorchard/python-rds-utils) updated, ideally by the Data team ## Friday 5/8 - [ ] QA End to End test has completed successfully by AM EDT - [ ] QA Accounting run has completed successfuly by EOB EDT - [ ] UAT changes have been deployed by EOB EDT - [ ] UAT has been smoke tested by EOB EDT ## Monday 5/11 - [ ] PROD changes have been deployed by AM EDT - [ ] Healthy pipelines and monitors are in place by EOB EDT ** Note: if the team feels confident, we will deploy on Friday 5/8 # Snowflake Migration: Part II: Final Destination **Change:** Snowflake account string `sme-dataplatform` → **https://delphi.us-east-1.snowflakecomputing.com/** (Delphi merge complete). --- ## 1. terraform-infra — Abacus account string Update `snowflake_account` / `lambda_snowflake_account` / `SNOWFLAKE_ACCOUNT` defaults. > Note: the `ows-accounting` entries below are historical (completed during this migration). `ows-accounting` has since been decommissioned under [ACC-10631](https://theorchard.atlassian.net/browse/ACC-10631) — see the [decommission tracking doc](../technical-projects/ows-accounting-decommission/README.md). ### PROD - [x] `prod/lambda-abacus/variables.tf:68` - [x] `prod/abacus-managed-airflow/variables.tf:121` - [x] `prod/ows-reporting/variables.tf:32` - [x] `prod/ows-accounting/main.tf:132` - [x] `prod/dbt-accounting/main.tf:123` - [x] `prod/ows-payment/variables.tf` - [x] `prod/documents/close-balance/variables.tf` - [x] `prod/documents/knr-payments/variables.tf` - [x] `prod/documents/load-from-s3/variables.tf` - [x] `prod/documents/update-knr-vat-details/variables.tf` - [x] `royaltyshare/prod/fivetran/main.tf:55` (full host: `sme-dataplatform.snowflakecomputing.com`) ### QA - [x] `qa/lambda-abacus/variables.tf:56` - [x] `qa/abacus-managed-airflow/variables.tf:101` - [x] `qa/ows-reporting/variables.tf:32` - [x] `qa/ows-accounting/main.tf:132` - [x] `qa/dbt-accounting/main.tf:94` - [x] `qa/ows-payment/variables.tf` - [x] `qa/documents/*/variables.tf` - [ ] `qa/ows-coda/variables.tf:65,99,151` https://github.com/theorchard/terraform-infra/pull/35699 - [x] `accounting/qa/lambda-abacus/variables.tf:22` - [x] `accounting/qa/lambda-documents/knr-payments/variables.tf:50` ### UAT - [x] `accounting/uat/abacus-airflow/variables.tf:76` - [x] `accounting/uat/lambda-abacus/variables.tf:65` - [x] `accounting/uat/ows-payment/variables.tf:56` - [x] `accounting/uat/documents/close-balance/variables.tf` - [x] `accounting/uat/documents/knr-payments/variables.tf` - [x] `accounting/uat/documents/load-from-s3/variables.tf` - [x] `accounting/uat/documents/update-knr-vat-details/variables.tf` ### Do NOT touch (DevOps/Data-owned, checkov-guarded) - [x] `prod/snowflake/orchard/warehouses/main.tf` & `variables.tf` - [x] `prod/snowflake/orchard/external-stages/royaltyshare-storage-integration/main.tf` --- ## 2. Repo-level changes - [ ] **lambda-abacus** — https://github.com/theorchard/lambda-abacus/pull/1581/changes - [x] **ows-royalties-workflows** — Docker build / connection config - [x] **ows-reporting** — Snowflake connection config - [x] **python-rds-utils** — `lambda/snowflake_refresh/config.py:18` still defaults to `'orchard'` ⚠️ - [x] **python-neo4j-cypher-scheduler** — `terraform-infra/prod/neo4j-cypher-scheduler/assert-snowflake-sync/main.tf` --- ## 3. Secret / non-code updates (not visible to code search) - [?] **AWS Secrets Manager — `SNOWFLAKE_CONNECTION_URI`** backing MWAA `prod-abacus-airflow` (and QA/UAT). Update the URI value directly — this is the Airflow trap. - [x] **Secret-Manager URLs** for `PROD_OWS_ROYALTIES_WORKFLOW` and `QA_OWS_ACCOUNTING` - [x] **python-rds-utils** secrets under prefix `shared/rds-refresh/` - [ ] Re-check **all adjustment lambdas** — these missed a URL last round and paged prod mid-run --- ## 4. Deploy sequence - [ ] Deploy QA → run E2E + accounting dry-run - [ ] Deploy UAT → smoke test - [ ] Deploy PROD (outside live accounting run window) - [ ] Pipelines green + monitors healthy --- ## 5. Post-deploy verification Run against Snowflake: ```sql SELECT DISTINCT USER_NAME, REPORTED_CLIENT_TYPE FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY WHERE EVENT_TIMESTAMP >= DATEADD(day,-7,CURRENT_TIMESTAMP()) AND IS_SUCCESS='YES' AND REPORTED_CLIENT_TYPE!='SNOWFLAKE_UI' AND (USER_NAME LIKE 'QA%' OR USER_NAME LIKE 'PROD%') AND NVL(CONNECTION,'DIRECT_ACCOUNT_URL') <> 'DATAPLATFORM'; -- update sentinel ``` Confirm these users are off the old URL: - [ ] `PROD_OWS_ROYALTIES_WORKFLOW` - [ ] `QA_OWS_ROYALTIES_WORKFLOW` - [ ] `QA_OWS_ACCOUNTING` - [ ] `QA_CODA_SVC_USER` - [ ] `QA_CODA_SEARCH_SVC_USER` - [ ] DBT users ---