Data Warehouse and Lakehouse Migration Playbook

Data warehouse and lakehouse migrations move the data reliably and lose the controls around it: masking policies, row-level filters, classification tags, network restrictions and the scoped service identities that limited who could read what. Organisations move analytical platforms to consolidate after a merger, leave an on-premises appliance, change cloud provider, adopt an open table format, or bring warehouse and data lake into one lakehouse. These platforms concentrate an organisation's most valuable data and are reached by a large population of non-human identities: ingestion jobs, transformation tools, BI dashboards, notebooks, reverse ETL syncs and, increasingly, AI agents. This playbook is for data platform, security and governance teams running such a move. It is vendor-neutral and covers what has to move besides the tables, how to translate access controls by intent, the identities involved, validation, parallel running, and the safe decommissioning of the old platform and its copies. First deployments are covered in the Data warehouse and lakehouse rollout playbook, and platform hardening in Securing the data warehouse and lakehouse itself.

Key takeaways

Migrate controls as first-class objects. Masking, row filters, tags, grants and network rules need their own inventory, translation and tests, just like tables.
Translate access by intent, not by syntax. Role models differ between platforms; rebuild from who should see what, then compare effective access on both sides.
Every pipeline identity gets a new credential. Service users for ingestion, transformation and BI are reissued with federation or key-based authentication, scoped per job, and the old ones are disabled on a dated plan.
Validate data and access together. Row counts and checksums prove the data arrived; effective-permission tests prove it did not arrive wide open.
Decommissioning is a data disposal project. Staging buckets, exports, snapshots and the old platform's storage all hold full copies until they are sanitised.

What a warehouse migration actually moves

Object	Migration approach	Watch for
Tables, schemas and partitions	Bulk copy plus incremental sync until cutover	Type changes (timestamps, decimals, character sets) that alter values
Views, stored procedures and user-defined functions	Rewrite and test; record which ones enforce security logic	Secure views used as an access control that become ordinary views
Dynamic masking and row-level policies	Rebuild by intent; test per role	Policies with no direct equivalent are silently dropped
Classification tags and lineage	Carry tags with the objects; re-run classification on the target	Tags lost in export, so tag-based policies match nothing
Roles, grants and ownership	Rebuild a least-privilege role model; map users and groups through the identity provider	"Temporarily" broad grants to make the first loads work
Service users and integrations	New credentials per job and tool	Password-only service users copied from the source
Network policies and private connectivity	Recreate allow lists and private endpoints before loading sensitive data	Target left open to the internet during the migration
Data sharing and external access	Re-establish each share with its recipient and expiry	Shares recreated without the original row or column limits
Encryption keys	Target data encrypted under keys you control where policy requires	Default keys used where customer-managed keys were mandated
Point-in-time recovery, backups and retention settings	Configure on the target before cutover	Shorter recovery windows than the source
Query history and access audit logs	Export and retain for the required period	Evidence lost when the source account closes

The table type matters too: moving from a proprietary warehouse format to open table formats on object storage changes where access is enforced. If engines can read the files directly, storage permissions become part of the access model and must be designed alongside the catalogue's grants. See the Data Lake Security Guide.

Migration approaches

Approach	How it works	Choose it when	Trade-off
Lift and shift	Copy schemas and data as they are, translate code mechanically	Hard deadline (contract or hardware end of life), simple security model	Carries over technical debt and over-broad grants
Re-platform by domain	Move one business domain at a time with its pipelines, consumers and policies	Most organisations; allows a parallel run per domain	Long mixed period with data in two places
Rebuild	Redesign models and pipelines on the target	Old platform is poorly governed or data model is being replaced	Slowest; highest risk of changing business numbers
Federated bridge	Target queries the source in place while data moves	Large datasets that cannot move quickly	Two enforcement points; query paths that bypass target policies

Whatever the approach, do not load regulated or highly sensitive data until the target's network restrictions, masking and row policies for that data are in place and tested.

Translating access controls by intent

Extract effective access: for each sensitive table and column, list which roles, users and service identities can read it, including through views, shares and inherited grants. This is the baseline, not the grant scripts.
Write the intent: one line per policy, for example "analysts in the UK team see customer rows for UK only, with email masked except for the support role".
Implement on the target: use the target's native mechanism for each intent. Where there is no equivalent, use a secure view or a separate curated dataset, and record the gap.
Test per role: run the same queries as each test persona on both platforms and diff the results, including row counts and masked values.
Review with data owners: data owners sign off effective access for their domain before cutover.

The OWASP Database Security Cheat Sheet's baseline still applies on any analytical platform: least privilege for every account, separate accounts and databases for development, test and production, TLS 1.2 or later for client connections, and encrypted, access-controlled backups. Classification should drive the policy set; see the Data Classification Guide.

Where identity fits

Most queries against a warehouse come from non-human identities, and a migration is where their credentials get copied around in configuration files and migration scripts.

Inventory callers from logs: use the source's login and query history for at least 90 days (NHI Mgmt Group recommendation) to list every service user, the tool behind it and the objects it touches.
Reissue, do not copy: create new service identities on the target with key-based or federated authentication, one per job or tool, never shared. See the Workload Identity Federation Guide and the Service Account Security Guide.
Scope the migration identity: the job that reads everything from the source and writes everything to the target is the most powerful identity in the programme. Grant it per wave, restrict it by network, log every use and remove it at the end.
Humans through SSO with MFA: no local passwords on the target, and network restrictions for administrative access.
Disable old credentials on a dated plan: keep a register of every source service user with its owner and disable date.

The 2024 campaign against customer data warehouse accounts, analysed by Mandiant as UNC5537, used credentials stolen by infostealers, some dating to 2020, against accounts without MFA or network allow lists; Mandiant and the platform provider notified about 165 potentially exposed organisations. Old credentials left valid during a long migration are exactly that risk. See the Snowflake breach analysis.

Validating data and access

Validation has two halves that are usually run by different teams. Run them together, per domain, and keep the evidence for audit.

Check	How	Catches
Row counts per table and partition	Count on both platforms after each sync	Missing or duplicated loads
Column aggregates and checksums	Sums, distinct counts, min and max, or hashes of normalised rows	Type conversion and truncation errors
Business metrics	Key reports run on both platforms and compared by owners	Logic changes in rewritten views and procedures
Effective access per persona	Same queries as each test role; diff rows returned and masked values	Dropped masking and row policies, over-broad grants
Tag coverage	Compare classified columns on source and target	Tags lost in export, policies that match nothing
Network exposure	Attempt connections from outside allowed networks	Allow lists missing on the target
Recovery	Restore a table to a point in time on the target	Recovery settings shorter than the source

Run validation queries inside the platforms rather than exporting data to compare it elsewhere, and use a dedicated, read-only validation identity. See the Data lineage, data quality and data contracts as security controls guide for making these checks permanent.

The phased playbook

Inventory: datasets with classification and owner, pipelines, consumers, service identities, access policies, shares, network rules, keys and retention settings.
Target security baseline: network restrictions, SSO and MFA, role model, masking and row policy framework, customer-managed keys if required, audit logging to the SIEM, recovery settings. Complete before any sensitive data lands.
Pilot a domain: one domain end to end, including pipelines, BI consumers, access tests per persona, a restore test and a rollback rehearsal.
Load and sync by wave: bulk load, then incremental sync. Lowest-sensitivity domains first; regulated data last.
Validate: row counts, column-level checksums or aggregates, sample comparisons, and the effective-access diff for every sensitive object.
Parallel run: consumers read from the target while the source stays authoritative, for at least one reporting cycle (month end is a good test).
Cut over: repoint writers, then readers, per domain. Make the source read-only for that domain.
Decommission: disable old credentials, export audit logs, delete staging copies and exports, and sanitise or certify deletion of the old platform's storage.

Rollback: while the source remains authoritative and writable, rollback is repointing consumers. After writers move, rollback requires syncing target changes back, so keep a reverse sync or a clear freeze window for each domain until sign-off. Never delete source data or disable source recovery before the domain is signed off.

Small vs large organisations: a small team with one warehouse and a handful of pipelines can often lift and shift with a one-month parallel run, provided access is rebuilt properly. Large estates need domain waves, automated access diffing, a data owner sign-off process and a credential revocation register.

Staging copies and decommissioning

Staging buckets: bulk exports to object storage are full copies of production data. Encrypt them, restrict them to the migration identity, block public access, set lifecycle deletion, and log access.
Workstation and notebook extracts: forbid ad hoc local extracts for validation; compare in place on the platforms.
Old platform storage: for cloud services, obtain written confirmation of deletion; for appliances and on-premises storage, sanitise media following NIST SP 800-88 Rev. 2 (final, September 2025).
Retained evidence: keep access audit logs and any data under legal hold, with a documented owner and expiry. See the Data Retention and Secure Deletion Guide.

Detection and measurement

Metric or signal	Why it matters	Target (NHI Mgmt Group recommendation)
Sensitive objects with matching effective access on both platforms	Proves controls migrated	100% before cutover of each domain
Data validation pass rate (counts and checksums)	Proves data integrity	100%, every difference explained
Sensitive columns without masking or tags on the target	Finds dropped policies	Zero
Service identities using passwords only	Credential theft exposure	Zero on the target
Source logins after domain cutover, by identity	Forgotten consumers or misuse of old credentials	Falling to zero; alert on any after disable date
Large result sets or exports by the migration identity outside approved runs	Misuse of the most powerful identity	Zero; alert on any

Stream login and query history from both platforms to the SIEM throughout. Valid cloud account abuse (MITRE ATT&CK T1078.004, Cloud Accounts) followed by bulk reads from cloud data stores (T1530, Data from Cloud Storage) is the pattern to hunt for, especially from unfamiliar networks.

Common failure patterns

Masking left behind: columns masked on the source arrive in clear on the target because the policy had no direct equivalent.
Grant-all to get started: a broad role created to unblock the first loads becomes the permanent model.
Password service users copied: old credentials, possibly already exposed, are reused on the new platform.
Open network during migration: the target accepts connections from anywhere until "after go-live".
Forgotten staging exports: full copies of production data remain in buckets months after the project ends.
Source never truly closed: the old platform stays reachable with valid credentials and no monitoring.

Framework mapping

Activity	NIST SP 800-53 Rev 5	NIST CSF 2.0
Data inventory and classification	CM-8	ID.AM-07
Access policy translation and least privilege	AC-6, AC-5	PR.AA-05
Encryption in transit and at rest, keys	SC-8, SC-28, SC-12	PR.DS-01, PR.DS-02
Migration change control	CM-3	PR.PS-01
Backups and recovery on the target	CP-9	PR.DS-11
Access logging and monitoring	AU-2, AU-6, SI-4	DE.CM-09
Disposal of staging copies and old storage	MP-6, SI-12	PR.DS-01

Practitioner checklist

Inventory datasets, owners, classifications, policies, shares, pipelines and service identities.
Complete the target security baseline before any sensitive data lands.
Write each access policy as an intent and test it per persona on both platforms.
Carry classification tags with the data and re-run classification on the target.
Reissue every service identity with key-based or federated authentication, one per job.
Scope, network-restrict and log the migration identity, and remove it at the end.
Validate data with counts and checksums, and access with effective-permission diffs.
Run in parallel through at least one reporting cycle before cutover.
Delete staging exports and sanitise or certify deletion of old storage.
Disable old credentials on a dated register and alert on any later use.

Data warehouse and lakehouse migration playbook: moving platforms without losing control