Databricks Lakehouse Migration on AWS
Plan and build a BNM-compliant Databricks Lakehouse on AWS with Medallion pipelines.
Introduction
30 Second Summary
Financial institutions sit on massive data estates spread across on-premises databases and partial cloud deployments. When regulators like Bank Negara Malaysia require strict controls over where data lives and who holds the encryption keys, migrating to the cloud becomes equal parts technical implementation and compliance planning.
In this project, you will build a complete cloud migration decision package and a working Databricks Lakehouse proof-of-concept on AWS. You will cover regulatory compliance documentation, infrastructure provisioning with Terraform, and a data pipeline that proves your architecture works.
What You'll Build
A complete Cloud Migration Decision Package paired with a Technical Proof of Concept that takes you from regulatory planning through to a deployed Databricks Lakehouse environment on AWS.
By the end of this project, you'll have:
- A BNM RMiT-compliant risk assessment framework and scored platform evaluation matrix with 3-year TCO projections, ready for board and CISO review.
- A production-grade Databricks workspace deployed on AWS via Terraform with PrivateLink, customer-managed VPC, and Unity Catalog governance.
- A working Medallion Architecture pipeline (Bronze, Silver, Gold) ingesting sample data from S3 with Auto Loader and Lakeflow Spark Declarative Pipelines.
- Secret Mission: Build a data quality monitoring framework with continuous compliance evidence for BNM audit readiness.
Are there any prerequisites?
This project assumes familiarity with database concepts (SQL Server, Oracle), basic AWS knowledge (VPC, IAM, S3), and an understanding of financial services data governance requirements. Basic Terraform knowledge is helpful but not strictly required.
You will need an AWS account with an existing VPC, the Terraform CLI installed, and a Databricks account (the 14-day free trial works). A spreadsheet tool and document editor are also needed for the planning deliverables.
Build the Platform Evaluation Matrix
Before committing to a platform for your 25TB migration, you need an objective, evidence-based comparison.
In this step, you will build a weighted scoring matrix comparing four platform options: Databricks on AWS, Snowflake, Amazon Redshift, and Google BigQuery. The matrix produces a single defensible recommendation backed by financial projections that your board and BNM can review.
In this step, get ready to:
- Define four weighted evaluation criteria reflecting your regulatory and business priorities.
- Score each platform 1-5 with evidence and calculate weighted totals.
- Build a 3-year TCO model covering storage, compute, transfer, licensing, operations, and migration costs.
Define evaluation criteria and weights
Your scoring criteria need to reflect the specific priorities of a Malaysian financial institution migrating 25TB of mixed SQL Server and Oracle data. BNM compliance and cost are weighted higher because they are non-negotiable constraints.
- Open your spreadsheet tool and create a new spreadsheet named Platform_Evaluation_Matrix.
- In the first row, add these column headers:
| Criterion | Weight | Databricks on AWS | Snowflake | Amazon Redshift | Google BigQuery |
|-----------|--------|-------------------|-----------|-----------------|-----------------|- Add the following four criteria rows with their weights:
Performance at 25TB Scale | 20%
Ease of Use (BI + Data Science) | 20%
BNM RMiT Compliance Readiness | 30%
Total Cost of Ownership (3 years) | 30%Why these specific weights?
BNM RMiT compliance (30%) and TCO (30%) together account for 60% of the score. This reflects reality for a regulated financial institution. A platform that fails compliance is disqualified regardless of performance. A platform that is too expensive will never get board approval.
Performance (20%) and Ease of Use (20%) matter for day-to-day operations. Your workload is a mix of BI queries and ML training, so the platform must serve both analyst and data scientist personas effectively.
- Below the criteria table, add a scoring legend explaining the 1-5 scale:
Scoring Legend:
1 = Does not meet requirement
2 = Partially meets with significant gaps
3 = Meets basic requirement
4 = Exceeds requirement with minor gaps
5 = Fully exceeds requirement with no gaps- Save your spreadsheet.
Populate platform scores and calculate 3-year TCO
Now you will score each platform against the four criteria using verified pricing and compliance evidence. You will also build a TCO breakdown that factors in the full cost of operating each platform over three years.
- Create a new sheet tab named Scores and populate it with the following structure. Score each platform 1-5 on each criterion:
PLATFORM SCORING MATRIX
Criterion: Performance at 25TB Scale (Weight: 20%)
| Platform | Raw Score | Evidence | Weighted Score |
|--------------------|-----------|-------------------------------------------------------|----------------|
| Databricks on AWS | 5 | Photon engine, Delta Lake optimized reads, spot GPUs | 1.00 |
| Snowflake | 4 | Strong warm-cache BI, limited ML compute flexibility | 0.80 |
| Amazon Redshift | 4 | RA3 nodes scale well, limited ML integration | 0.80 |
| Google BigQuery | 3 | Cross-cloud adds latency, Omni limited to 60GB/query | 0.60 |
Criterion: Ease of Use - BI + Data Science (Weight: 20%)
| Platform | Raw Score | Evidence | Weighted Score |
|--------------------|-----------|-------------------------------------------------------|----------------|
| Databricks on AWS | 5 | SQL Warehouses for BI, notebooks for ML, unified UI | 1.00 |
| Snowflake | 5 | Excellent SQL UX, Snowpark for ML | 1.00 |
| Amazon Redshift | 3 | SQL-focused, ML requires SageMaker integration | 0.60 |
| Google BigQuery | 4 | Good SQL UX, BigQuery ML built-in | 0.80 |
Criterion: BNM RMiT Compliance Readiness (Weight: 30%)
| Platform | Raw Score | Evidence | Weighted Score |
|--------------------|-----------|-------------------------------------------------------|----------------|
| Databricks on AWS | 5 | Customer VPC, customer-owned S3, BYOK, PrivateLink | 1.50 |
| Snowflake | 4 | Business Critical has Tri-Secret Secure, private link | 1.20 |
| Amazon Redshift | 4 | Runs in customer VPC, KMS encryption, native AWS | 1.20 |
| Google BigQuery | 2 | Second cloud provider complicates RMiT governance | 0.60 |
Criterion: Total Cost of Ownership - 3 Years (Weight: 30%)
| Platform | Raw Score | Evidence | Weighted Score |
|--------------------|-----------|-------------------------------------------------------|----------------|
| Databricks on AWS | 4 | Competitive for combined BI+ML, S3 storage is cheap | 1.20 |
| Snowflake | 3 | $4.00/credit Business Critical, expensive at scale | 0.90 |
| Amazon Redshift | 4 | Reservations reduce cost, but ML needs SageMaker | 1.20 |
| Google BigQuery | 2 | $0.09/GiB egress from AWS, cross-cloud overhead | 0.60 |How is the weighted score calculated?
The weighted score for each cell is: Raw Score (out of 5) multiplied by the Weight (as a decimal). For example, a raw score of 5 on a 20% criterion gives 5 x 0.20 = 1.00. The maximum possible weighted total across all four criteria is 5.00.
- At the bottom of the Scores sheet, add a totals row with the formula for each platform:
WEIGHTED TOTALS
| Platform | Total Weighted Score |
|--------------------|---------------------|
| Databricks on AWS | 4.70 |
| Snowflake | 3.90 |
| Amazon Redshift | 3.80 |
| Google BigQuery | 2.60 |Why does Databricks score highest?
Databricks on AWS scores highest because it uniquely satisfies both the compliance requirement (customer-managed VPC, customer-owned S3, BYOK encryption via PrivateLink) and the combined BI plus ML workload requirement in a single governed platform. Snowflake is strong for BI but requires a separate ML platform, adding governance complexity under BNM RMiT.
- Create a third sheet tab named TCO_Model for your 3-year financial projection. Structure it as follows:
3-YEAR TCO MODEL (All figures in USD)
Assumptions:
- Raw data volume: 25TB
- Compressed storage (columnar): ~6TB (assuming 4:1 compression)
- Workload mix: 60% BI queries, 40% ML training
- Migration period: 6 months dual-running
- Region: AP-Southeast-1 (Singapore)
=== DATABRICKS ON AWS ===
Storage (S3 Standard, ~6TB compressed):
Year 1: 6,144 GB x $0.025/GB x 12 months = $1,843
Year 2: 6,144 GB x $0.025/GB x 12 months = $1,843
Year 3: 6,144 GB x $0.025/GB x 12 months = $1,843
Compute - SQL Warehouse (BI, starting at $0.22/DBU):
Year 1: ~50,000 DBU/month x 12 x $0.22 = $132,000
Year 2: ~50,000 DBU/month x 12 x $0.22 = $132,000
Year 3: ~50,000 DBU/month x 12 x $0.22 = $132,000
Compute - Jobs (ML/ETL, ~$0.15/DBU):
Year 1: ~30,000 DBU/month x 12 x $0.15 = $54,000
Year 2: ~30,000 DBU/month x 12 x $0.15 = $54,000
Year 3: ~30,000 DBU/month x 12 x $0.15 = $54,000
AWS Infrastructure (VPC, PrivateLink, KMS):
Year 1: ~$2,000/month x 12 = $24,000
Year 2: ~$2,000/month x 12 = $24,000
Year 3: ~$2,000/month x 12 = $24,000
Data Transfer (one-time migration + ongoing):
Year 1: 25TB ingest + ongoing = $5,000
Year 2: ongoing = $1,200
Year 3: ongoing = $1,200
Operational Overhead (1 FTE platform admin):
Year 1-3: $120,000/year = $360,000
Migration (DMS, consulting, dual-running):
Year 1: one-time = $150,000
Databricks 3-Year Total: ~$1,153,000
=== SNOWFLAKE (Business Critical) ===
Storage (~6TB, billed monthly per TB compressed):
3-Year Total: ~$8,600
Compute (Business Critical at $4.00/credit):
BI Warehouses: ~40,000 credits/month x 36 x $4.00 = $5,760,000
ML (Snowpark): ~15,000 credits/month x 36 x $4.00 = $2,160,000
Data Transfer:
Year 1: ingest from AWS = $2,500
Operational Overhead (1.5 FTE - Snowflake + separate ML platform):
3-Year Total: = $540,000
Migration:
Year 1: = $120,000
Snowflake 3-Year Total: ~$8,591,000
=== AMAZON REDSHIFT (Serverless) ===
Compute (Serverless at $0.375/RPU-hour, min 4 RPU):
BI: ~$1.50/hour x 10 hrs/day x 365 x 3 = $16,425
Burst: additional capacity = $30,000
Managed Storage (RMS at $0.024/GB):
6TB x 1024 x $0.024 x 36 months = $5,308
SageMaker (for ML workloads, separate service):
3-Year Total: = $450,000
Operational Overhead (1.5 FTE):
3-Year Total: = $540,000
Migration:
Year 1: = $80,000
Redshift 3-Year Total: ~$1,122,000
=== GOOGLE BIGQUERY ===
Cross-Cloud Transfer (25TB from AWS at $0.09/GiB):
One-time: 25,600 GiB x $0.09 = $2,304
Compute (on-demand, first 1 TiB/month free):
3-Year Total (estimated): = $180,000
Storage:
3-Year Total: = $5,400
Operational Overhead (2 FTE - managing two cloud providers under BNM):
3-Year Total: = $720,000
Migration + Governance overhead:
Year 1: = $200,000
BigQuery 3-Year Total: ~$1,108,000What do these numbers tell us?
The raw cost figures show BigQuery and Redshift are close to Databricks. However, the Redshift option requires a separate SageMaker investment for ML workloads, and BigQuery requires governing a second cloud provider under BNM RMiT. When you factor in the compliance scoring (30% weight), Databricks on AWS emerges as the optimal choice for your specific scenario of combined BI plus ML under BNM regulation.
The Snowflake estimate appears high because Business Critical edition pricing at $4.00 per credit applies to every compute operation. With committed-use discounts, Snowflake costs would be lower in practice. Adjust these estimates based on your vendor negotiations.
Adjust estimates for your environment
These TCO figures use list prices as a baseline. Databricks DBU rates, Snowflake credit prices, and Redshift reservations all offer volume discounts under contract. Replace list prices with your negotiated rates before presenting to the board. The relative rankings typically hold even after discounts, because Databricks and Redshift discount similarly (up to 37% with committed use).
- Save your spreadsheet with all three sheet tabs: the criteria, scores, and TCO model.
- Review your weighted totals. Databricks on AWS should show the highest score (4.70 out of 5.00), confirming it as the recommended platform.
Your evaluation matrix is complete. You now have a defensible, evidence-backed platform recommendation. Next up, you will draft the BNM RMiT Risk Assessment Framework that accompanies this recommendation to satisfy regulatory consultation requirements.
Draft the BNM RMiT Risk Assessment Framework
Your Platform Evaluation Matrix gave you the data-driven case for Databricks on AWS. Now you need the regulatory case.
Bank Negara Malaysia's RMiT framework requires a formal risk assessment before any financial institution adopts public cloud for critical systems. Without this document, your migration cannot proceed to BNM consultation.
In this step, get ready to:
- Structure the Risk Assessment document per RMiT Appendix 7/10 with all 5 mandatory areas.
- Map specific RMiT requirements to Databricks/AWS controls in a controls mapping table.
- Draft the CISO/Board confirmation structure per RMiT Appendix 8.
Structure the Risk Assessment per RMiT Appendix 7/10
RMiT §15.1 requires a comprehensive risk assessment formatted per Appendix 7 before consulting BNM. This document must address the risks outlined in paragraph 10.49 and Appendix 10.
- Switch back to your document editor.
- Create a new document and save it as bnm_rmit_risk_assessment.docx inside your /docs folder.
- Add the document title: BNM RMiT Risk Assessment Report — Cloud Data Migration to Databricks Lakehouse on AWS.
- Add a metadata header with: Date, Prepared By (CISO/Head of Technology Risk), Document Version, and Classification (Confidential).
Now create the 5 mandatory risk assessment sections. Add each as a top-level heading in your document with the content described below.
Add Section 1 with the heading and content below:
# 1. Cloud Governance Framework
## 1.1 Internal Policies
- Cloud adoption policy aligned with enterprise risk appetite
- Data classification policy (Critical/Sensitive/General tiers)
- Acceptable use policy for cloud workloads
- Change management procedures for cloud infrastructure
## 1.2 Roles and Accountability
- CISO: Independent oversight of cloud technology risk, reporting
directly to the board-designated technology committee
- Head of Technology Risk: Day-to-day cloud risk management,
KRI monitoring, escalation procedures
- Board accountability: Quarterly review of cloud risk posture,
annual approval of cloud adoption roadmap per RMiT §15.5
## 1.3 Risk Appetite Statement
- Technology risk appetite approved by the board
- Specific tolerances for cloud-related risks (availability,
data breach, vendor lock-in)
- Escalation triggers and notification thresholdsWhy does governance come first?
RMiT places governance at the foundation of all technology risk management. BNM expects the board to actively lead the institution's technology risk posture, approve risk appetite, and designate a CISO with independence from day-to-day operations.
Without an established governance structure, the remaining sections of your risk assessment have no accountability framework to reference.
- Add Section 2 for CSP Due Diligence:
# 2. Cloud Service Provider Due Diligence
## 2.1 Provider Certifications
- Databricks: SOC 1/2/3, PCI-DSS, ISO 27001, ISO 27017,
ISO 27018, ISO 22301, ISO 27701, HITRUST, FedRAMP
- AWS: SOC 1/2/3, PCI-DSS, ISO 27001, CSA STAR,
MTCS (Singapore), published Malaysia RMiT workbook
## 2.2 Audit Rights
- Contractual right to audit cloud provider operations
- Access to SOC 2 Type II reports (quarterly)
- Right to conduct or commission penetration testing
on dedicated resources
## 2.3 Incident Reporting
- Databricks: Security incident notification within 72 hours
- AWS: Real-time health dashboard, Personal Health Dashboard
for targeted event notifications
- Contractual obligation to report breaches affecting
customer data within BNM notification timeframes
## 2.4 Service Level Agreements
- Databricks workspace availability SLA
- AWS S3: 99.99% availability, 99.999999999% durability
- VPC and networking: availability guarantees for
PrivateLink endpoints
- Escalation procedures for SLA breachesWhat certifications matter for BNM?
BNM expects robust due diligence on cloud service providers covering security, work capability, financial soundness, and prior compliance. Databricks holds SOC 1/2/3, PCI-DSS, ISO 27001, ISO 27017, ISO 27018, ISO 22301, ISO 27701, HITRUST, and FedRAMP certifications.
AWS publishes a dedicated RMiT workbook and User Guide for Financial Institutions in Malaysia, which maps AWS controls directly to RMiT requirements.
- Add Section 3 for Data Security and Privacy:
# 3. Data Security and Privacy
## 3.1 Encryption at Rest
- All data stored in customer-owned S3 buckets
- Encrypted using AWS KMS with Customer Managed Keys (CMK)
- Databricks BYOK (Bring Your Own Key) for workspace storage
- Key rotation policy: annual rotation minimum
## 3.2 Encryption in Transit
- TLS 1.2+ for all data in transit
- AWS PrivateLink ensures data never traverses public internet
- Mutual TLS for inter-service communication where supported
## 3.3 Key Management Ownership
- Financial institution retains full ownership and control
of all cryptographic keys (per RMiT §10.51)
- AWS KMS keys stored in institution's AWS account
- Key access policies restricted to named IAM roles
- Key deletion requires multi-party approval
## 3.4 Data Residency
- All data stored in AP-Southeast-1 (Singapore)
- No cross-border data transfer without explicit approval
- Unity Catalog metastore deployed in AP-Southeast-1
- Country risk assessment documented per RMiT requirementsWhy is key management ownership critical?
RMiT §10.51 explicitly requires financial institutions to retain ownership, control, and management of customer data and cryptographic keys when using cloud services. This means the institution holds the keys in its own AWS account, not the cloud provider.
- Add Section 4 for Business Continuity and Exit Strategy:
# 4. Business Continuity and Exit Strategy
## 4.1 Failover Design
- Multi-AZ deployment within AP-Southeast-1
- S3 cross-region replication to secondary region
(if approved by BNM for DR purposes)
- Recovery Time Objective (RTO): 4 hours for critical systems
- Recovery Point Objective (RPO): 1 hour maximum data loss
## 4.2 Data Portability
- Delta Lake open format: Parquet files on customer-owned S3
- No proprietary lock-in for data storage layer
- Data readable by any Parquet-compatible engine
(Spark, Trino, Athena, DuckDB)
- Unity Catalog metadata exportable
## 4.3 Exit Procedures
- Documented exit plan developed during planning phase
- 90-day exit window to migrate to alternative platform
- Data extraction procedures tested quarterly
- Contractual guarantees for data return and deletion
upon contract termination
## 4.4 Dual-Running Strategy
- Legacy and Databricks systems run in parallel during
each migration phase
- Rollback criteria defined with measurable thresholds
- No legacy decommission until validation period completeWhy does Delta Lake satisfy the exit strategy requirement?
BNM requires a documented exit strategy to ensure the institution can move away from a cloud provider without losing data. Delta Lake stores data as open-format Parquet files on customer-owned S3. This means the data remains accessible even without Databricks.
- Add Section 5 for Cybersecurity Operations:
# 5. Cybersecurity Operations
## 5.1 Monitoring
- Unity Catalog audit logs for all data access events
- AWS CloudTrail for API-level activity logging
- AWS GuardDuty for threat detection
- Network flow logs for VPC traffic analysis
- Log retention: minimum 3 years per RMiT requirements
## 5.2 Incident Response Plan
- Documented Cyber Incident Response Plan (CIRP)
- Annual cyber drill with board and senior management
participation
- Incident classification aligned with BNM notification
thresholds
- 24/7 Security Operations Centre (SOC) coverage
## 5.3 Supply Chain Security
- AWS PrivateLink: no public internet data traversal
- All compute runs within customer-managed VPC
- Network segmentation between workload types
(BI vs ML vs ETL)
- Zero-trust architecture with deny-by-default posture- Save your document.
- Review the document structure. You should see 5 top-level sections, each with 3-4 subsections covering governance, due diligence, data security, business continuity, and cybersecurity.
Not sure what to put in each section?
The content above provides a template structure. In a real engagement, you would fill each bullet with specific evidence from your institution's policies, contracts, and technical configurations. For this project, use the template content as your baseline and customize as needed for your context.
Help me understand which RMiT sections apply to my specific cloud adoption scenario.
Map controls and draft CISO/Board confirmation
BNM needs to see a clear mapping between their requirements and your specific technical controls. This proves you have addressed each regulatory concern with a concrete implementation.
- Add a new section at the end of your document with the heading 6. Controls Mapping Table.
- Create a table with three columns: RMiT Requirement, Databricks/AWS Control, and Evidence/Verification.
- Populate the table with the following mappings:
# 6. Controls Mapping Table
| RMiT Requirement | Databricks/AWS Control | Evidence/Verification |
|------------------|----------------------|----------------------|
| §10.51: Data ownership | Customer-managed VPC, customer-owned S3 storage, Unity Catalog on institution's infrastructure | VPC configuration, S3 bucket policies, Terraform state |
| §10.51: Key management | AWS KMS with CMK, Databricks BYOK configuration | KMS key policy, BYOK workspace settings |
| Appendix 10(a): Cybersecurity | Unity Catalog audit logs, CloudTrail, GuardDuty, SOC integration | Log export configuration, GuardDuty findings dashboard |
| Appendix 10(b): Supply chain | AWS PrivateLink (no public internet traversal), VPC endpoints for workspace and relay | Network architecture diagram, VPC endpoint configuration |
| Appendix 10(c): Responsibility segregation | Shared responsibility model documented, customer VPC isolation, Databricks control plane separated from data plane | Architecture diagram, security group rules |
| MFA requirement | AWS IAM Identity Center with MFA, Databricks SSO integration | IAM Identity Center MFA policy, SSO configuration |What does each mapping prove to BNM?
Each row demonstrates that a specific regulatory requirement has a concrete technical control in place. The Evidence column tells auditors exactly where to verify the control is operational.
- §10.51 data ownership: Data lives in your S3 buckets, not the provider's. You control access.
- §10.51 key management: Encryption keys are in your AWS KMS account. The provider cannot decrypt your data.
- Appendix 10(b) supply chain: PrivateLink means no data crosses the public internet. This eliminates an entire category of network-level supply chain risk.
- MFA requirement: RMiT mandates MFA that is resistant to interception or manipulation. IAM Identity Center with hardware token or authenticator app satisfies this.
Now add the CISO/Board confirmation structure. RMiT §15.1(b) requires a formal confirmation per Appendix 8 that your institution is ready to adopt public cloud for critical systems.
- Add a new section with the heading 7. CISO/Board Confirmation of Readiness (per Appendix 8).
- Add the following confirmation structure to your document:
# 7. CISO/Board Confirmation of Readiness (per Appendix 8)
## 7.1 Confirmation Statement
I, [CISO Name / Board Committee Chair], confirm that:
1. A comprehensive risk assessment has been conducted per
paragraph 10.49 and Appendix 10, addressing all identified
risks associated with the proposed cloud adoption.
2. The institution's Technology Risk Management Framework
has been enhanced to manage cloud-specific risks, including
data security, business continuity, and vendor management.
3. Appropriate mitigating controls are in place as documented
in the Controls Mapping Table (Section 6 of this report).
4. The institution is prepared to undergo a third-party
pre-implementation review covering Appendix 10 and
Part A of Appendix 9.
5. The board-designated committee (per paragraph 8.4) has
reviewed and endorsed this risk assessment.
## 7.2 Signatories
- CISO: _________________________ Date: ___________
- Head of Technology Risk: ________ Date: ___________
- Board Committee Chair: __________ Date: ___________
## 7.3 Supporting Documentation
- Platform Evaluation Matrix (attached)
- Target Architecture Diagram (to be completed in Step 3)
- 3-Year TCO Model (attached)
- Controls Mapping Table (Section 6 above)What is the Appendix 8 confirmation?
RMiT §15.1(b) requires a confirmation by the CISO, senior management officer, or the chairman of the board-designated committee that the institution is ready to adopt public cloud for critical systems. This is a formal sign-off, not just an email approval.
The confirmation states that risks have been assessed, controls are in place, and the institution is prepared for the third-party pre-implementation review required by §15.1(c).
- Save bnm_rmit_risk_assessment.docx.
- Review the full document. You should now have 7 sections total: 5 risk assessment areas, 1 controls mapping table, and 1 CISO/Board confirmation.
Unsure about mapping controls to your specific environment?
The controls mapping table uses the architecture you will build in Step 3 and deploy in Step 4. If specific controls feel abstract right now, that is expected. You will update this document with concrete evidence (Terraform state files, VPC endpoint IDs, KMS key ARNs) after completing the infrastructure deployment.
Help me map my specific Databricks and AWS controls to BNM RMiT requirements.
✔️ Awesome, I've got everything!
Great. Double check you have saved your document with all 7 sections.
ⓧ I'd like to double check the full document
Your completed document should have this overall structure:
BNM RMiT Risk Assessment Report — Cloud Data Migration to
Databricks Lakehouse on AWS
Date: [Date]
Prepared By: [CISO / Head of Technology Risk]
Version: 1.0
Classification: Confidential
# 1. Cloud Governance Framework
## 1.1 Internal Policies
## 1.2 Roles and Accountability
## 1.3 Risk Appetite Statement
# 2. Cloud Service Provider Due Diligence
## 2.1 Provider Certifications
## 2.2 Audit Rights
## 2.3 Incident Reporting
## 2.4 Service Level Agreements
# 3. Data Security and Privacy
## 3.1 Encryption at Rest
## 3.2 Encryption in Transit
## 3.3 Key Management Ownership
## 3.4 Data Residency
# 4. Business Continuity and Exit Strategy
## 4.1 Failover Design
## 4.2 Data Portability
## 4.3 Exit Procedures
## 4.4 Dual-Running Strategy
# 5. Cybersecurity Operations
## 5.1 Monitoring
## 5.2 Incident Response Plan
## 5.3 Supply Chain Security
# 6. Controls Mapping Table
| RMiT Requirement | Databricks/AWS Control | Evidence |
|------------------|----------------------|----------|
| §10.51: Data ownership | Customer VPC, S3 | ... |
| §10.51: Key management | AWS KMS CMK, BYOK | ... |
| Appendix 10(a): Cybersecurity | Audit logs, CloudTrail, GuardDuty | ... |
| Appendix 10(b): Supply chain | PrivateLink, VPC endpoints | ... |
| Appendix 10(c): Responsibility | Shared model, VPC isolation | ... |
| MFA requirement | IAM Identity Center + MFA, SSO | ... |
# 7. CISO/Board Confirmation of Readiness (per Appendix 8)
## 7.1 Confirmation Statement
## 7.2 Signatories
## 7.3 Supporting DocumentationYour risk assessment framework is now complete. This document, combined with your Platform Evaluation Matrix from Step 1, forms the core of your BNM consultation package. Next, you will design the target architecture that these controls reference.
Design the Target Architecture
Your risk assessment and platform evaluation are complete. You now have a clear recommendation for Databricks on AWS, backed by BNM compliance evidence and a 3-year TCO model.
Before provisioning any infrastructure, you need a target-state architecture diagram that shows stakeholders exactly how data flows through the system. This diagram serves two purposes: it gives your board and CISO a visual they can review alongside the risk assessment, and it becomes the blueprint for the Terraform deployment in the next step.
In this step, get ready to:
- Design the network and security architecture in Draw.io with PrivateLink, customer-managed VPC, and encryption controls.
- Map your 25TB data estate to the Medallion Architecture and define the compute layers.
Design the network and security architecture
Your architecture diagram needs to show how data moves from on-premises systems into the Databricks Lakehouse without ever traversing the public internet. This is a core requirement for BNM RMiT compliance.
- Switch back to Draw.io in your browser.
- Select File in the top menu bar, then click New.
- Name your diagram databricks_lakehouse_architecture and select Blank Diagram, then click Create.
- Enable the AWS shape library by clicking More Shapes at the bottom of the left panel.
- Scroll down to the Networking section, check the box next to the AWS19 library, then click Apply.
Why enable the AWS19 library?
Draw.io includes dedicated AWS shape libraries sorted by year. The AWS19 library provides categorized sub-libraries for VPC, S3, IAM, and networking components. These give your diagram the official AWS icons that stakeholders and auditors expect to see.
Now build the diagram layer by layer. Start with the outermost boundary and work inward.
- Drag a large rectangle onto the canvas and label it Customer-Managed VPC (ap-southeast-1). This represents your VPC in the Singapore region.
- Inside the VPC rectangle, create two private subnet zones. Label one Private Subnet - Databricks Workspace and the other Private Subnet - VPC Endpoints.
- Add two VPC Endpoint shapes inside the endpoint subnet. Label them Workspace VPC Endpoint (Port 443) and Relay VPC Endpoint (Port 6666).
- Draw connectors from both VPC endpoints to a box outside the VPC labeled Databricks Control Plane (AWS PrivateLink).
- Add an S3 bucket icon inside the VPC and label it S3 - Delta Lake Storage (KMS CMK Encrypted).
- Add a Unity Catalog Metastore component connected to the workspace subnet. Label the region as AP-Southeast-1.
- On the left side of the diagram, add a box labeled On-Premises Data Centre. Draw a connector into the VPC labeled AWS Direct Connect / VPN.
- Add an S3 - Staging / Landing Zone bucket between the on-premises connector and the Delta Lake storage. This is where DMS drops incoming data.
- Add two separate compute zones inside the workspace subnet. Label them BI Workloads (SQL Warehouse) and ML Workloads (Job Clusters) to show network segmentation.
Why two ports for PrivateLink?
Databricks on AWS uses two separate PrivateLink connections. Port 443 handles inbound user traffic to the workspace UI and API. Port 6666 is the secure cluster relay that connects the compute plane (in your VPC) back to the Databricks control plane.
Setting public_access_enabled = false in Terraform later ensures all traffic stays on these private connections. No data traverses the public internet, which directly satisfies the BNM RMiT data ownership and supply chain requirements.
What is a customer-managed VPC?
By default, Databricks creates and manages its own VPC for your compute resources. A customer-managed VPC flips this model. You create and own the VPC, subnets, and security groups. Databricks deploys compute nodes into your VPC using a cross-account IAM role.
This gives you full control over network routing, firewall rules, and connectivity. For BNM RMiT §10.51 compliance, customer-managed VPC ensures you retain ownership and control of the infrastructure hosting your data.
- Save your diagram by selecting File then Save. Save to your /diagrams folder as databricks_lakehouse_architecture.drawio.
- Review your diagram. You should see the VPC boundary containing private subnets, two VPC endpoints with port labels, S3 storage with KMS encryption noted, the Unity Catalog metastore, on-premises connectivity via Direct Connect/VPN, and the BI/ML network segmentation.
Diagram looking cluttered?
Use the AWS Groups shapes to create clear visual boundaries around each zone. Right-click any shape and select To Back to push boundary rectangles behind the components inside them.
If shapes overlap, select a group and use the Arrange tab in the format panel on the right to reposition and resize.
How can I improve the layout of my AWS architecture diagram in Draw.io?
Design data layers and compute architecture
With the network diagram in place, you need to map how your 25TB SQL Server and Oracle data estate will be organized inside the Lakehouse. The Medallion Architecture is a data design pattern that progressively improves data quality through three layers: Bronze, Silver, and Gold.
- Add a new page to your diagram by clicking the + icon at the bottom of the canvas. Label this page Medallion Architecture.
Create four horizontal swim lanes on this page, one for each data layer. Add the following components and labels to each lane.
- Create the first lane and label it Bronze (Raw). Inside it, add:
- Create the second lane and label it Silver (Validated). Inside it, add:
- Create the third lane and label it Gold (Enriched). Inside it, add:
- Create the fourth lane and label it Feature Store. Inside it, add:
What is the Medallion Architecture?
The Medallion Architecture is a data design pattern that organizes data into progressive quality layers. Data quality improves as it flows from Bronze to Silver to Gold.
Bronze preserves raw data fidelity and serves as the single source of truth for reprocessing and auditing. Silver performs cleansing, deduplication, and validation. Gold contains business-ready aggregates optimized for analytics and dashboards.
For BNM compliance, this pattern provides clear data lineage from source to consumption. Auditors can trace any Gold-layer metric back through Silver transformations to the original Bronze ingestion record.
Now add the compute architecture definitions to the same diagram page.
- Below the swim lanes, create a section labeled Compute Architecture.
- Add a box labeled BI Compute: Databricks SQL Warehouse (Serverless or Pro). Draw a connector from Gold to this box.
- Add a box labeled ML Compute: Job Clusters (Spot Instances + GPU). Draw a connector from Feature Store to this box.
- Add a box labeled ETL: Lakeflow Spark Declarative Pipelines. Draw connectors showing this orchestrates Bronze-to-Silver and Silver-to-Gold transformations.
Why Lakeflow Spark Declarative Pipelines for ETL?
Lakeflow Spark Declarative Pipelines (formerly Delta Live Tables) provide declarative, production-grade pipelines with built-in lineage, observability, and schema evolution. You define what your data should look like, and the framework handles incremental processing, retries, and data quality enforcement.
Combined with Auto Loader for ingestion, this gives you exactly-once processing guarantees via RocksDB checkpointing. For your BNM audit trail, every record's journey from S3 landing zone through to Gold layer is automatically tracked.
- Save the updated diagram by selecting File then Save.
- Review both pages of your diagram. Page 1 should show the network and security architecture. Page 2 should show the Medallion Architecture layers with compute definitions.
Not sure how to structure the swim lanes?
Use simple rectangles with different background colours for each layer. Bronze can be a dark grey, Silver a lighter grey, and Gold a warm yellow or amber. This colour coding makes the progressive refinement immediately visible.
Help me lay out a Medallion Architecture swim-lane diagram in Draw.io with Bronze, Silver, Gold, and Feature Store layers.
Your architecture diagram is now complete. It documents both the security controls that satisfy BNM requirements and the data flow pattern your Terraform deployment will implement. In the next step, you will turn this blueprint into real infrastructure by provisioning the Databricks workspace with PrivateLink and Unity Catalog using Terraform.
Provision Infrastructure with Terraform
In the previous steps, you designed the architecture and documented the regulatory compliance framework. Now it is time to turn those diagrams into running infrastructure.
In this step, you will use Terraform to provision a production-grade Databricks workspace on AWS with PrivateLink, customer-managed encryption, and Unity Catalog governance. Every resource maps directly to a control in your BNM RMiT risk assessment.
In this step, get ready to:
- Configure Terraform providers and create foundational AWS resources (KMS, S3, IAM, subnets).
- Deploy a Databricks workspace with PrivateLink for VPC-only access.
- Set up Unity Catalog with a metastore, storage credentials, catalogs, and cluster policies.
Set up Terraform providers and AWS prerequisites
Before creating the workspace, Terraform needs to know how to authenticate with both AWS and the Databricks account API. You also need foundational resources: an encrypted S3 bucket for workspace storage, a cross-account IAM role, and a dedicated subnet for PrivateLink.
- Switch back to your terminal and navigate to the /terraform folder.
- Set the required environment variables for Terraform authentication by running:
export DATABRICKS_ACCOUNT_ID=your-databricks-account-id
export DATABRICKS_CLIENT_ID=your-service-principal-client-id
export DATABRICKS_CLIENT_SECRET=your-service-principal-client-secret
export AWS_ACCESS_KEY_ID=your-aws-access-key
export AWS_SECRET_ACCESS_KEY=your-aws-secret-key
export AWS_REGION=ap-southeast-1Where do I find these values?
Your Databricks Account ID is in the top-right dropdown of accounts.cloud.databricks.com. The Client ID and Secret come from the service principal you created in your Databricks account console under Settings > Service principals.
Your AWS credentials come from the IAM user or role you configured with the AWS CLI in the previous step.
- Create the main.tf file in your /terraform folder. Start with the provider configuration and foundational AWS resources by pasting this code:
# ---------------------------------------------------------
# Terraform Providers and Variables
# ---------------------------------------------------------
terraform {
required_providers {
databricks = {
source = "databricks/databricks"
}
aws = {
source = "hashicorp/aws"
}
}
}
# Account-level provider for workspace provisioning
provider "databricks" {
alias = "mws"
host = "https://accounts.cloud.databricks.com"
}
# AWS provider targeting Singapore region
provider "aws" {
region = "ap-southeast-1"
}
# Configuration variables
variable "databricks_account_id" {
description = "Databricks account ID from the account console"
type = string
}
variable "vpc_id" {
description = "Existing VPC ID for Databricks deployment"
type = string
}
variable "vpc_cidr" {
description = "VPC CIDR block for security group rules"
type = string
default = "10.0.0.0/16"
}
# ---------------------------------------------------------
# AWS KMS - Customer-Managed Key for S3 Encryption
# ---------------------------------------------------------
resource "aws_kms_key" "databricks_cmk" {
description = "CMK for Databricks workspace S3 encryption"
deletion_window_in_days = 7
enable_key_rotation = true
tags = {
Purpose = "databricks-workspace-encryption"
}
}
resource "aws_kms_alias" "databricks_cmk_alias" {
name = "alias/databricks-workspace-cmk"
target_key_id = aws_kms_key.databricks_cmk.key_id
}
# ---------------------------------------------------------
# S3 Root Bucket for Databricks Workspace Storage
# ---------------------------------------------------------
resource "aws_s3_bucket" "databricks_root" {
bucket = "databricks-workspace-root-${var.databricks_account_id}"
tags = {
Purpose = "databricks-workspace-root-storage"
}
}
resource "aws_s3_bucket_versioning" "databricks_root" {
bucket = aws_s3_bucket.databricks_root.id
versioning_configuration {
status = "Enabled"
}
}
resource "aws_s3_bucket_server_side_encryption_configuration" "databricks_root" {
bucket = aws_s3_bucket.databricks_root.id
rule {
apply_server_side_encryption_by_default {
sse_algorithm = "aws:kms"
kms_master_key_id = aws_kms_key.databricks_cmk.arn
}
}
}
# ---------------------------------------------------------
# Cross-Account IAM Role for Databricks EC2 Orchestration
# ---------------------------------------------------------
resource "aws_iam_role" "databricks_cross_account" {
name = "databricks-cross-account-role"
assume_role_policy = jsonencode({
Version = "2012-10-17"
Statement = [{
Effect = "Allow"
Principal = {
AWS = "arn:aws:iam::414351767826:root"
}
Action = "sts:AssumeRole"
Condition = {
StringEquals = {
"sts:ExternalId" = var.databricks_account_id
}
}
}]
})
}
resource "aws_iam_role_policy" "databricks_cross_account" {
name = "databricks-cross-account-policy"
role = aws_iam_role.databricks_cross_account.id
policy = jsonencode({
Version = "2012-10-17"
Statement = [
{
Effect = "Allow"
Action = ["ec2:*", "s3:*"]
Resource = "*"
}
]
})
}
# ---------------------------------------------------------
# PrivateLink Subnet (minimum /25 CIDR)
# ---------------------------------------------------------
resource "aws_subnet" "privatelink" {
vpc_id = var.vpc_id
cidr_block = "10.0.128.0/25"
tags = {
Name = "databricks-privatelink-subnet"
Purpose = "VPC endpoints for Databricks PrivateLink"
}
}What does this code do?
- The databricks provider with alias = "mws" connects to the Databricks account API at https://accounts.cloud.databricks.com for workspace provisioning.
- The KMS key with automatic rotation satisfies RMiT §10.51 (customer ownership of cryptographic keys).
- The S3 bucket uses the CMK for server-side encryption, storing workspace data (DBFS root).
- The cross-account IAM role allows Databricks to launch and manage EC2 instances in your VPC.
- The PrivateLink subnet uses a /25 CIDR block, which is the minimum size required by Databricks for VPC endpoints.
- Save main.tf.
Seeing provider authentication errors?
Confirm that your DATABRICKS_ACCOUNT_ID, DATABRICKS_CLIENT_ID, and DATABRICKS_CLIENT_SECRET environment variables are exported in the same terminal session where you run Terraform.
help me troubleshoot Terraform provider authentication with Databricks MWS
Deploy Databricks workspace with PrivateLink
With the foundational resources in place, you can now create the PrivateLink connectivity layer and deploy the workspace itself. This ensures no data traverses the public internet, which directly satisfies your BNM compliance requirement for private connectivity.
- Add the following code below the PrivateLink subnet resource in main.tf:
# ---------------------------------------------------------
# Security Group for PrivateLink VPC Endpoints
# ---------------------------------------------------------
resource "aws_security_group" "privatelink" {
vpc_id = var.vpc_id
name = "databricks-privatelink-sg"
description = "Security group for Databricks PrivateLink endpoints"
# Workspace API access
ingress {
from_port = 443
to_port = 443
protocol = "tcp"
cidr_blocks = [var.vpc_cidr]
description = "HTTPS for workspace API"
}
# Secure cluster relay
ingress {
from_port = 6666
to_port = 6666
protocol = "tcp"
cidr_blocks = [var.vpc_cidr]
description = "Secure cluster connectivity relay"
}
tags = {
Name = "databricks-privatelink-sg"
}
}
# ---------------------------------------------------------
# VPC Endpoints for Databricks PrivateLink
# ---------------------------------------------------------
# Workspace endpoint (port 443)
resource "aws_vpc_endpoint" "workspace" {
vpc_id = var.vpc_id
service_name = var.workspace_vpce_service
vpc_endpoint_type = "Interface"
subnet_ids = [aws_subnet.privatelink.id]
security_group_ids = [aws_security_group.privatelink.id]
private_dns_enabled = true
tags = {
Name = "databricks-workspace-vpce"
}
}
# Secure cluster relay endpoint (port 6666)
resource "aws_vpc_endpoint" "relay" {
vpc_id = var.vpc_id
service_name = var.relay_vpce_service
vpc_endpoint_type = "Interface"
subnet_ids = [aws_subnet.privatelink.id]
security_group_ids = [aws_security_group.privatelink.id]
private_dns_enabled = true
tags = {
Name = "databricks-relay-vpce"
}
}
variable "workspace_vpce_service" {
description = "Region-specific VPCE service name for Databricks workspace"
type = string
}
variable "relay_vpce_service" {
description = "Region-specific VPCE service name for Databricks relay"
type = string
}
# ---------------------------------------------------------
# Databricks MWS Resources - Private Access and Workspace
# ---------------------------------------------------------
# Private access settings (VPC-only, no public internet)
resource "databricks_mws_private_access_settings" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
private_access_settings_name = "lakehouse-private-access"
region = "ap-southeast-1"
public_access_enabled = false
}
# Register root S3 bucket with Databricks
resource "databricks_mws_storage_configurations" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
storage_configuration_name = "lakehouse-root-storage"
bucket_name = aws_s3_bucket.databricks_root.bucket
}
# Register cross-account IAM role with Databricks
resource "databricks_mws_credentials" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
credentials_name = "lakehouse-credentials"
role_arn = aws_iam_role.databricks_cross_account.arn
}
# Network configuration with PrivateLink endpoints
resource "databricks_mws_networks" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
network_name = "lakehouse-network"
vpc_id = var.vpc_id
subnet_ids = [aws_subnet.privatelink.id]
security_group_ids = [aws_security_group.privatelink.id]
vpc_endpoints {
dataplane_relay = [databricks_mws_vpc_endpoint.relay.vpc_endpoint_id]
rest_api = [databricks_mws_vpc_endpoint.workspace.vpc_endpoint_id]
}
depends_on = [aws_vpc_endpoint.workspace, aws_vpc_endpoint.relay]
}
# Register VPC endpoints with Databricks
resource "databricks_mws_vpc_endpoint" "workspace" {
provider = databricks.mws
account_id = var.databricks_account_id
aws_vpc_endpoint_id = aws_vpc_endpoint.workspace.id
vpc_endpoint_name = "workspace-vpce"
region = "ap-southeast-1"
}
resource "databricks_mws_vpc_endpoint" "relay" {
provider = databricks.mws
account_id = var.databricks_account_id
aws_vpc_endpoint_id = aws_vpc_endpoint.relay.id
vpc_endpoint_name = "relay-vpce"
region = "ap-southeast-1"
}
# Deploy workspace at Enterprise tier with PrivateLink
resource "databricks_mws_workspaces" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
workspace_name = "lakehouse-prod"
aws_region = "ap-southeast-1"
credentials_id = databricks_mws_credentials.this.credentials_id
storage_configuration_id = databricks_mws_storage_configurations.this.storage_configuration_id
network_id = databricks_mws_networks.this.network_id
private_access_settings_id = databricks_mws_private_access_settings.this.private_access_settings_id
pricing_tier = "ENTERPRISE"
depends_on = [databricks_mws_networks.this]
}What does this code do?
- The security group allows inbound TCP on ports 443 (workspace API) and 6666 (secure cluster relay) from your VPC CIDR only.
- Two VPC endpoints create private network paths to Databricks services, eliminating public internet exposure.
- Setting public_access_enabled = false means the workspace is only reachable via VPC endpoints. This is the key BNM compliance control.
- The pricing_tier = "ENTERPRISE" is required for PrivateLink support on Databricks.
- The databricks_mws_networks resource links your VPC, subnets, and security groups into a network configuration that Databricks uses to launch compute in your account.
- Save main.tf.
Why PrivateLink instead of public access?
BNM RMiT Appendix 10(b) requires that data does not traverse the public internet. PrivateLink creates a direct network path between your VPC and the Databricks control plane. This eliminates the attack surface of public endpoints while keeping all traffic within the AWS backbone.
The alternative (public access with IP allowlists) still routes through the internet and creates a larger compliance documentation burden during BNM audits.
Workspace provisioning failed?
If you see a credentials validation error, the cross-account IAM role may not have propagated yet. Add a time_sleep resource with a 10-second delay that depends on the IAM role, then make the workspace depend on that sleep resource.
If VPC endpoint registration fails, confirm that your VPC has DNS Hostnames and DNS Resolution enabled. These are required for PrivateLink.
help me debug my Databricks workspace provisioning failure in Terraform
Deploy Unity Catalog metastore
Unity Catalog provides centralized governance across all data assets. It enforces fine-grained access control, tracks data lineage, and produces audit logs.
For BNM compliance, Unity Catalog is the mechanism that demonstrates you control who accesses what data and when.
- Add the following Unity Catalog configuration below the workspace resource in main.tf:
# ---------------------------------------------------------
# Workspace-Level Provider (after workspace creation)
# ---------------------------------------------------------
provider "databricks" {
alias = "workspace"
host = databricks_mws_workspaces.this.workspace_url
}
# ---------------------------------------------------------
# Unity Catalog Metastore
# ---------------------------------------------------------
# S3 bucket for Unity Catalog managed storage
resource "aws_s3_bucket" "unity_catalog" {
bucket = "unity-catalog-metastore-${var.databricks_account_id}"
tags = {
Purpose = "unity-catalog-metastore-storage"
}
}
# Unity Catalog metastore for AP-Southeast-1
resource "databricks_metastore" "this" {
provider = databricks.mws
name = "lakehouse-metastore-apse1"
storage_root = "s3://${aws_s3_bucket.unity_catalog.bucket}/metastore"
region = "ap-southeast-1"
force_destroy = true
}
# ---------------------------------------------------------
# Storage Credentials (IAM Role for Unity Catalog S3 Access)
# ---------------------------------------------------------
resource "aws_iam_role" "unity_catalog" {
name = "unity-catalog-storage-role"
assume_role_policy = jsonencode({
Version = "2012-10-17"
Statement = [{
Effect = "Allow"
Principal = {
AWS = "arn:aws:iam::414351767826:root"
}
Action = "sts:AssumeRole"
Condition = {
StringEquals = {
"sts:ExternalId" = var.databricks_account_id
}
}
}]
})
}
resource "aws_iam_role_policy" "unity_catalog_s3" {
name = "unity-catalog-s3-access"
role = aws_iam_role.unity_catalog.id
policy = jsonencode({
Version = "2012-10-17"
Statement = [
{
Effect = "Allow"
Action = [
"s3:GetObject",
"s3:PutObject",
"s3:DeleteObject",
"s3:ListBucket",
"s3:GetBucketLocation"
]
Resource = [
aws_s3_bucket.unity_catalog.arn,
"${aws_s3_bucket.unity_catalog.arn}/*"
]
}
]
})
}
resource "databricks_storage_credential" "unity" {
provider = databricks.workspace
name = "unity-catalog-credential"
aws_iam_role {
role_arn = aws_iam_role.unity_catalog.arn
}
depends_on = [databricks_metastore_assignment.this]
}
# ---------------------------------------------------------
# External Locations for Data Buckets
# ---------------------------------------------------------
resource "databricks_external_location" "raw_data" {
provider = databricks.workspace
name = "raw-data-location"
url = "s3://${aws_s3_bucket.unity_catalog.bucket}/raw/"
credential_name = databricks_storage_credential.unity.name
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_external_location" "curated_data" {
provider = databricks.workspace
name = "curated-data-location"
url = "s3://${aws_s3_bucket.unity_catalog.bucket}/curated/"
credential_name = databricks_storage_credential.unity.name
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_external_location" "analytics_data" {
provider = databricks.workspace
name = "analytics-data-location"
url = "s3://${aws_s3_bucket.unity_catalog.bucket}/analytics/"
credential_name = databricks_storage_credential.unity.name
depends_on = [databricks_metastore_assignment.this]
}
# ---------------------------------------------------------
# Assign Metastore to Workspace
# ---------------------------------------------------------
resource "databricks_metastore_assignment" "this" {
provider = databricks.workspace
metastore_id = databricks_metastore.this.id
workspace_id = databricks_mws_workspaces.this.workspace_id
}
# ---------------------------------------------------------
# Catalogs for Medallion Architecture Layers
# ---------------------------------------------------------
resource "databricks_catalog" "raw" {
provider = databricks.workspace
name = "raw"
comment = "Bronze layer - raw ingested data"
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_catalog" "curated" {
provider = databricks.workspace
name = "curated"
comment = "Silver layer - cleansed and validated data"
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_catalog" "analytics" {
provider = databricks.workspace
name = "analytics"
comment = "Gold layer - business-ready aggregates"
depends_on = [databricks_metastore_assignment.this]
}
# ---------------------------------------------------------
# Cluster Policies for BI and ML Workloads
# ---------------------------------------------------------
# Shared access mode for BI analysts (row/column security enforced)
resource "databricks_cluster_policy" "bi_shared" {
provider = databricks.workspace
name = "BI Analytics - Shared Access"
definition = jsonencode({
"data_security_mode" = {
type = "fixed"
value = "USER_ISOLATION"
}
})
}
# Single User access mode for ML workloads (full library support)
resource "databricks_cluster_policy" "ml_single_user" {
provider = databricks.workspace
name = "ML Training - Single User"
definition = jsonencode({
"data_security_mode" = {
type = "fixed"
value = "SINGLE_USER"
}
})
}What does this code do?
- The databricks_metastore resource creates the top-level Unity Catalog container for the AP-Southeast-1 region. Only one metastore exists per region.
- The storage credential uses an IAM role to grant Unity Catalog secure access to S3 without embedding keys.
- External locations map S3 paths to credential-controlled access points. Each Medallion layer gets its own location.
- Three catalogs (raw, curated, analytics) correspond to Bronze, Silver, and Gold Medallion layers.
- Cluster policies enforce access modes. Shared (USER_ISOLATION) gives BI analysts row/column security. Single User gives ML engineers full library access with per-user isolation.
- Save main.tf.
- Initialize and apply your Terraform configuration by running:
terraform init
terraform plan
terraform applyWhat should I expect during apply?
The terraform apply will prompt you to confirm. Type yes to proceed. The workspace creation typically takes 5-10 minutes as Databricks provisions the control plane resources and validates VPC endpoint connectivity.
You will see resources being created in order. If any resource fails, Terraform will stop and report the error. The most common issues are IAM role trust policy misconfigurations and VPC endpoint service name typos.
- After the apply completes, verify the workspace is only accessible via VPC endpoint by confirming that the workspace URL resolves only from within your VPC.
Unity Catalog metastore creation failed?
If the metastore fails to create, confirm that no other metastore already exists in the AP-Southeast-1 region for your account. Only one metastore is allowed per region.
If the storage credential fails, verify that the IAM role trust policy references the correct Databricks AWS account ID and that your external ID matches your Databricks account ID.
help me debug Unity Catalog metastore creation in Terraform
✔️ Awesome, I've got everything!
Great. Double check you have saved main.tf and that terraform apply completed without errors.
ⓧ I'd like to double check the full code
Here is the complete main.tf file with all resources:
# ---------------------------------------------------------
# Terraform Providers and Variables
# ---------------------------------------------------------
terraform {
required_providers {
databricks = {
source = "databricks/databricks"
}
aws = {
source = "hashicorp/aws"
}
}
}
# Account-level provider for workspace provisioning
provider "databricks" {
alias = "mws"
host = "https://accounts.cloud.databricks.com"
}
# AWS provider targeting Singapore region
provider "aws" {
region = "ap-southeast-1"
}
# Configuration variables
variable "databricks_account_id" {
description = "Databricks account ID from the account console"
type = string
}
variable "vpc_id" {
description = "Existing VPC ID for Databricks deployment"
type = string
}
variable "vpc_cidr" {
description = "VPC CIDR block for security group rules"
type = string
default = "10.0.0.0/16"
}
# ---------------------------------------------------------
# AWS KMS - Customer-Managed Key for S3 Encryption
# ---------------------------------------------------------
resource "aws_kms_key" "databricks_cmk" {
description = "CMK for Databricks workspace S3 encryption"
deletion_window_in_days = 7
enable_key_rotation = true
tags = {
Purpose = "databricks-workspace-encryption"
}
}
resource "aws_kms_alias" "databricks_cmk_alias" {
name = "alias/databricks-workspace-cmk"
target_key_id = aws_kms_key.databricks_cmk.key_id
}
# ---------------------------------------------------------
# S3 Root Bucket for Databricks Workspace Storage
# ---------------------------------------------------------
resource "aws_s3_bucket" "databricks_root" {
bucket = "databricks-workspace-root-${var.databricks_account_id}"
tags = {
Purpose = "databricks-workspace-root-storage"
}
}
resource "aws_s3_bucket_versioning" "databricks_root" {
bucket = aws_s3_bucket.databricks_root.id
versioning_configuration {
status = "Enabled"
}
}
resource "aws_s3_bucket_server_side_encryption_configuration" "databricks_root" {
bucket = aws_s3_bucket.databricks_root.id
rule {
apply_server_side_encryption_by_default {
sse_algorithm = "aws:kms"
kms_master_key_id = aws_kms_key.databricks_cmk.arn
}
}
}
# ---------------------------------------------------------
# Cross-Account IAM Role for Databricks EC2 Orchestration
# ---------------------------------------------------------
resource "aws_iam_role" "databricks_cross_account" {
name = "databricks-cross-account-role"
assume_role_policy = jsonencode({
Version = "2012-10-17"
Statement = [{
Effect = "Allow"
Principal = {
AWS = "arn:aws:iam::414351767826:root"
}
Action = "sts:AssumeRole"
Condition = {
StringEquals = {
"sts:ExternalId" = var.databricks_account_id
}
}
}]
})
}
resource "aws_iam_role_policy" "databricks_cross_account" {
name = "databricks-cross-account-policy"
role = aws_iam_role.databricks_cross_account.id
policy = jsonencode({
Version = "2012-10-17"
Statement = [
{
Effect = "Allow"
Action = ["ec2:*", "s3:*"]
Resource = "*"
}
]
})
}
# ---------------------------------------------------------
# PrivateLink Subnet (minimum /25 CIDR)
# ---------------------------------------------------------
resource "aws_subnet" "privatelink" {
vpc_id = var.vpc_id
cidr_block = "10.0.128.0/25"
tags = {
Name = "databricks-privatelink-subnet"
Purpose = "VPC endpoints for Databricks PrivateLink"
}
}
# ---------------------------------------------------------
# Security Group for PrivateLink VPC Endpoints
# ---------------------------------------------------------
resource "aws_security_group" "privatelink" {
vpc_id = var.vpc_id
name = "databricks-privatelink-sg"
description = "Security group for Databricks PrivateLink endpoints"
# Workspace API access
ingress {
from_port = 443
to_port = 443
protocol = "tcp"
cidr_blocks = [var.vpc_cidr]
description = "HTTPS for workspace API"
}
# Secure cluster relay
ingress {
from_port = 6666
to_port = 6666
protocol = "tcp"
cidr_blocks = [var.vpc_cidr]
description = "Secure cluster connectivity relay"
}
tags = {
Name = "databricks-privatelink-sg"
}
}
# ---------------------------------------------------------
# VPC Endpoints for Databricks PrivateLink
# ---------------------------------------------------------
# Workspace endpoint (port 443)
resource "aws_vpc_endpoint" "workspace" {
vpc_id = var.vpc_id
service_name = var.workspace_vpce_service
vpc_endpoint_type = "Interface"
subnet_ids = [aws_subnet.privatelink.id]
security_group_ids = [aws_security_group.privatelink.id]
private_dns_enabled = true
tags = {
Name = "databricks-workspace-vpce"
}
}
# Secure cluster relay endpoint (port 6666)
resource "aws_vpc_endpoint" "relay" {
vpc_id = var.vpc_id
service_name = var.relay_vpce_service
vpc_endpoint_type = "Interface"
subnet_ids = [aws_subnet.privatelink.id]
security_group_ids = [aws_security_group.privatelink.id]
private_dns_enabled = true
tags = {
Name = "databricks-relay-vpce"
}
}
variable "workspace_vpce_service" {
description = "Region-specific VPCE service name for Databricks workspace"
type = string
}
variable "relay_vpce_service" {
description = "Region-specific VPCE service name for Databricks relay"
type = string
}
# ---------------------------------------------------------
# Databricks MWS Resources - Private Access and Workspace
# ---------------------------------------------------------
# Private access settings (VPC-only, no public internet)
resource "databricks_mws_private_access_settings" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
private_access_settings_name = "lakehouse-private-access"
region = "ap-southeast-1"
public_access_enabled = false
}
# Register root S3 bucket with Databricks
resource "databricks_mws_storage_configurations" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
storage_configuration_name = "lakehouse-root-storage"
bucket_name = aws_s3_bucket.databricks_root.bucket
}
# Register cross-account IAM role with Databricks
resource "databricks_mws_credentials" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
credentials_name = "lakehouse-credentials"
role_arn = aws_iam_role.databricks_cross_account.arn
}
# Network configuration with PrivateLink endpoints
resource "databricks_mws_networks" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
network_name = "lakehouse-network"
vpc_id = var.vpc_id
subnet_ids = [aws_subnet.privatelink.id]
security_group_ids = [aws_security_group.privatelink.id]
vpc_endpoints {
dataplane_relay = [databricks_mws_vpc_endpoint.relay.vpc_endpoint_id]
rest_api = [databricks_mws_vpc_endpoint.workspace.vpc_endpoint_id]
}
depends_on = [aws_vpc_endpoint.workspace, aws_vpc_endpoint.relay]
}
# Register VPC endpoints with Databricks
resource "databricks_mws_vpc_endpoint" "workspace" {
provider = databricks.mws
account_id = var.databricks_account_id
aws_vpc_endpoint_id = aws_vpc_endpoint.workspace.id
vpc_endpoint_name = "workspace-vpce"
region = "ap-southeast-1"
}
resource "databricks_mws_vpc_endpoint" "relay" {
provider = databricks.mws
account_id = var.databricks_account_id
aws_vpc_endpoint_id = aws_vpc_endpoint.relay.id
vpc_endpoint_name = "relay-vpce"
region = "ap-southeast-1"
}
# Deploy workspace at Enterprise tier with PrivateLink
resource "databricks_mws_workspaces" "this" {
provider = databricks.mws
account_id = var.databricks_account_id
workspace_name = "lakehouse-prod"
aws_region = "ap-southeast-1"
credentials_id = databricks_mws_credentials.this.credentials_id
storage_configuration_id = databricks_mws_storage_configurations.this.storage_configuration_id
network_id = databricks_mws_networks.this.network_id
private_access_settings_id = databricks_mws_private_access_settings.this.private_access_settings_id
pricing_tier = "ENTERPRISE"
depends_on = [databricks_mws_networks.this]
}
# ---------------------------------------------------------
# Workspace-Level Provider (after workspace creation)
# ---------------------------------------------------------
provider "databricks" {
alias = "workspace"
host = databricks_mws_workspaces.this.workspace_url
}
# ---------------------------------------------------------
# Unity Catalog Metastore
# ---------------------------------------------------------
# S3 bucket for Unity Catalog managed storage
resource "aws_s3_bucket" "unity_catalog" {
bucket = "unity-catalog-metastore-${var.databricks_account_id}"
tags = {
Purpose = "unity-catalog-metastore-storage"
}
}
# Unity Catalog metastore for AP-Southeast-1
resource "databricks_metastore" "this" {
provider = databricks.mws
name = "lakehouse-metastore-apse1"
storage_root = "s3://${aws_s3_bucket.unity_catalog.bucket}/metastore"
region = "ap-southeast-1"
force_destroy = true
}
# ---------------------------------------------------------
# Storage Credentials (IAM Role for Unity Catalog S3 Access)
# ---------------------------------------------------------
resource "aws_iam_role" "unity_catalog" {
name = "unity-catalog-storage-role"
assume_role_policy = jsonencode({
Version = "2012-10-17"
Statement = [{
Effect = "Allow"
Principal = {
AWS = "arn:aws:iam::414351767826:root"
}
Action = "sts:AssumeRole"
Condition = {
StringEquals = {
"sts:ExternalId" = var.databricks_account_id
}
}
}]
})
}
resource "aws_iam_role_policy" "unity_catalog_s3" {
name = "unity-catalog-s3-access"
role = aws_iam_role.unity_catalog.id
policy = jsonencode({
Version = "2012-10-17"
Statement = [
{
Effect = "Allow"
Action = [
"s3:GetObject",
"s3:PutObject",
"s3:DeleteObject",
"s3:ListBucket",
"s3:GetBucketLocation"
]
Resource = [
aws_s3_bucket.unity_catalog.arn,
"${aws_s3_bucket.unity_catalog.arn}/*"
]
}
]
})
}
resource "databricks_storage_credential" "unity" {
provider = databricks.workspace
name = "unity-catalog-credential"
aws_iam_role {
role_arn = aws_iam_role.unity_catalog.arn
}
depends_on = [databricks_metastore_assignment.this]
}
# ---------------------------------------------------------
# External Locations for Data Buckets
# ---------------------------------------------------------
resource "databricks_external_location" "raw_data" {
provider = databricks.workspace
name = "raw-data-location"
url = "s3://${aws_s3_bucket.unity_catalog.bucket}/raw/"
credential_name = databricks_storage_credential.unity.name
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_external_location" "curated_data" {
provider = databricks.workspace
name = "curated-data-location"
url = "s3://${aws_s3_bucket.unity_catalog.bucket}/curated/"
credential_name = databricks_storage_credential.unity.name
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_external_location" "analytics_data" {
provider = databricks.workspace
name = "analytics-data-location"
url = "s3://${aws_s3_bucket.unity_catalog.bucket}/analytics/"
credential_name = databricks_storage_credential.unity.name
depends_on = [databricks_metastore_assignment.this]
}
# ---------------------------------------------------------
# Assign Metastore to Workspace
# ---------------------------------------------------------
resource "databricks_metastore_assignment" "this" {
provider = databricks.workspace
metastore_id = databricks_metastore.this.id
workspace_id = databricks_mws_workspaces.this.workspace_id
}
# ---------------------------------------------------------
# Catalogs for Medallion Architecture Layers
# ---------------------------------------------------------
resource "databricks_catalog" "raw" {
provider = databricks.workspace
name = "raw"
comment = "Bronze layer - raw ingested data"
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_catalog" "curated" {
provider = databricks.workspace
name = "curated"
comment = "Silver layer - cleansed and validated data"
depends_on = [databricks_metastore_assignment.this]
}
resource "databricks_catalog" "analytics" {
provider = databricks.workspace
name = "analytics"
comment = "Gold layer - business-ready aggregates"
depends_on = [databricks_metastore_assignment.this]
}
# ---------------------------------------------------------
# Cluster Policies for BI and ML Workloads
# ---------------------------------------------------------
# Shared access mode for BI analysts (row/column security enforced)
resource "databricks_cluster_policy" "bi_shared" {
provider = databricks.workspace
name = "BI Analytics - Shared Access"
definition = jsonencode({
"data_security_mode" = {
type = "fixed"
value = "USER_ISOLATION"
}
})
}
# Single User access mode for ML workloads (full library support)
resource "databricks_cluster_policy" "ml_single_user" {
provider = databricks.workspace
name = "ML Training - Single User"
definition = jsonencode({
"data_security_mode" = {
type = "fixed"
value = "SINGLE_USER"
}
})
}Your infrastructure is now live. You have a Databricks workspace that is only accessible via PrivateLink, governed by Unity Catalog with three catalogs mapped to your Medallion Architecture. Next up, you will build the data pipelines that flow through these layers.
Build the Medallion Architecture Pipeline
Your Databricks workspace is deployed with PrivateLink and Unity Catalog is governing three catalogs. Now it is time to prove the architecture works by building a real data pipeline that flows from raw ingestion through to BI-ready tables.
The Medallion Architecture organizes data into three quality layers: Bronze (raw), Silver (validated), and Gold (business-ready). Each layer adds progressively more structure and quality, creating an auditable lineage from source to dashboard.
In this step, get ready to:
- Ingest sample data into the Bronze layer using Auto Loader.
- Build Silver layer transformations with deduplication, quality constraints, and PII masking.
- Create Gold layer aggregates and validate end-to-end pipeline flow.
Set up Bronze layer ingestion with Auto Loader
The Bronze layer captures raw data exactly as it arrives, preserving source fidelity. Auto Loader incrementally processes new files from S3 without reprocessing old ones.
- Upload sample CSV files (representative of your SQL Server/Oracle tables) to your S3 landing zone bucket by running:
# Create sample customer data
cat > /tmp/customers_001.csv << 'EOF'
customer_id,name,email,phone_number,reg_date
1,John Smith,john.smith@example.com,+60123456789,2023-01-15
2,Sarah Lee,sarah.lee@example.com,+60198765432,2023-03-22
3,Ahmad Razak,ahmad.razak@example.com,+60176543210,2023-05-10
4,Lisa Wong,lisa.wong@example.com,+60145678901,2023-07-01
5,David Tan,david.tan@example.com,+60112345678,2023-09-18
EOF
# Create sample transaction data
cat > /tmp/transactions_001.csv << 'EOF'
transaction_id,customer_id,amount,txn_date,category,status
1001,1,2500.00,2024-01-05 10:30:00,deposit,completed
1002,2,150.75,2024-01-05 11:15:00,withdrawal,completed
1003,1,3200.00,2024-01-06 09:00:00,transfer,completed
1004,3,890.50,2024-01-06 14:20:00,deposit,completed
1005,4,1200.00,2024-01-07 08:45:00,withdrawal,pending
1006,5,4500.00,2024-01-07 16:30:00,deposit,completed
1007,2,670.25,2024-01-08 10:00:00,transfer,completed
1008,3,,2024-01-08 11:30:00,deposit,failed
EOF
# Upload to S3 landing zone
aws s3 cp /tmp/customers_001.csv s3://[[LANDING_ZONE_BUCKET="your-landing-zone-bucket-name"]]/customers/
aws s3 cp /tmp/transactions_001.csv s3://[[LANDING_ZONE_BUCKET="your-landing-zone-bucket-name"]]/transactions/What does this data represent?
These CSV files simulate what AWS DMS would output from your SQL Server and Oracle databases. The customer file has PII fields (email, phone) that you will mask in the Silver layer.
The transaction file includes a null amount on row 1008 to demonstrate how data quality constraints catch invalid records.
- Confirm the files landed by running:
aws s3 ls s3://[[LANDING_ZONE_BUCKET="your-landing-zone-bucket-name"]]/customers/
aws s3 ls s3://[[LANDING_ZONE_BUCKET="your-landing-zone-bucket-name"]]/transactions/You should see customers_001.csv and transactions_001.csv listed with their file sizes.
- In your Databricks workspace, create a new notebook called bronze_pipeline.py in a pipeline folder.
- Paste the following Bronze layer pipeline code:
from pyspark import pipelines as dp
from pyspark.sql.functions import current_timestamp, input_file_name
# Bronze layer: append-only raw ingestion from S3 landing zone
# Uses Auto Loader (cloudFiles) for incremental file processing
@dp.table(
name="bronze_customers",
comment="Raw customer data ingested from S3 landing zone - append only",
table_properties={"quality": "bronze"}
)
def bronze_customers():
# Stream new CSV files as they arrive in the customers path
return (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "csv")
.option("mergeSchema", "true")
.option("header", "true")
.option("cloudFiles.schemaLocation", "/mnt/schema/bronze_customers")
.load("s3://your-landing-zone-bucket/customers/")
.withColumn("_ingested_at", current_timestamp())
.withColumn("_source_file", input_file_name())
)
@dp.table(
name="bronze_transactions",
comment="Raw transaction data ingested from S3 landing zone - append only",
table_properties={"quality": "bronze"}
)
def bronze_transactions():
# Stream new CSV files as they arrive in the transactions path
return (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "csv")
.option("mergeSchema", "true")
.option("header", "true")
.option("cloudFiles.schemaLocation", "/mnt/schema/bronze_transactions")
.load("s3://your-landing-zone-bucket/transactions/")
.withColumn("_ingested_at", current_timestamp())
.withColumn("_source_file", input_file_name())
)What does this code do?
Each function decorated with @dp.table creates a streaming table in your raw catalog. The cloudFiles format tells Auto Loader to watch the S3 path and process only new files incrementally.
The mergeSchema option allows the pipeline to handle schema changes (new columns added over time) without breaking. The _ingested_at and _source_file metadata columns create an audit trail showing when each record arrived and which file it came from.
- Save the notebook.
- Configure a pipeline in the Databricks workspace pointing to this notebook with the target catalog set to raw.
Pipeline not finding your files?
Verify the S3 path in your pipeline matches the exact bucket and prefix where you uploaded the files. Ensure the external location in Unity Catalog grants READ FILES on the landing zone path.
help me debug why Auto Loader cannot read files from my S3 landing zone
Build Silver layer transformations
The Silver layer is where data becomes trustworthy. You will deduplicate records, enforce quality constraints, mask PII, and standardize schemas.
- Create a new notebook called silver_pipeline.py in the pipeline folder and paste the following code:
from pyspark import pipelines as dp
from pyspark.sql.functions import col, row_number, trim, lower, when
from pyspark.sql.window import Window
# Silver layer: validated, cleansed, deduplicated data
# Reads from Bronze streaming tables in raw catalog
@dp.table(
name="silver_customers",
comment="Cleansed and deduplicated customer data",
table_properties={"quality": "silver"}
)
@dp.expect_or_drop("valid_customer_id", "customer_id IS NOT NULL")
@dp.expect_or_drop("valid_email", "email IS NOT NULL")
def silver_customers():
# Read from Bronze streaming table
bronze_df = spark.readStream.table("raw.default.bronze_customers")
# Deduplicate using ROW_NUMBER window function
window_spec = Window.partitionBy("customer_id").orderBy(col("_ingested_at").desc())
deduplicated = (
bronze_df
.withColumn("row_num", row_number().over(window_spec))
.filter(col("row_num") == 1)
.drop("row_num")
)
# Schema standardization: type casting, renaming, null handling
return (
deduplicated
.withColumn("customer_id", col("customer_id").cast("long"))
.withColumn("email", lower(trim(col("email"))))
.withColumn("full_name", trim(col("name")))
.withColumn("phone", col("phone_number"))
.withColumn("registration_date", col("reg_date").cast("date"))
.drop("name", "phone_number", "reg_date", "_source_file", "_ingested_at")
)
@dp.table(
name="silver_transactions",
comment="Validated and cleansed transaction data",
table_properties={"quality": "silver"}
)
@dp.expect_or_drop("valid_transaction_id", "transaction_id IS NOT NULL")
@dp.expect_or_drop("valid_amount", "amount > 0")
@dp.expect_or_drop("valid_customer_ref", "customer_id IS NOT NULL")
def silver_transactions():
# Read from Bronze streaming table
bronze_df = spark.readStream.table("raw.default.bronze_transactions")
# Deduplicate using ROW_NUMBER window function
window_spec = Window.partitionBy("transaction_id").orderBy(col("_ingested_at").desc())
deduplicated = (
bronze_df
.withColumn("row_num", row_number().over(window_spec))
.filter(col("row_num") == 1)
.drop("row_num")
)
# Schema standardization: type casting, renaming, null handling
return (
deduplicated
.withColumn("transaction_id", col("transaction_id").cast("long"))
.withColumn("customer_id", col("customer_id").cast("long"))
.withColumn("amount", col("amount").cast("decimal(18,2)"))
.withColumn("transaction_date", col("txn_date").cast("timestamp"))
.withColumn("category", trim(col("category")))
.withColumn("status", when(col("status").isNull(), "unknown").otherwise(col("status")))
.drop("txn_date", "_source_file", "_ingested_at")
)What does this code do?
The @dp.expect_or_drop decorators enforce data quality constraints. Records that fail (like the null-amount transaction in your sample data) are dropped before reaching the target table. The dropped count is logged in pipeline metrics for auditing.
The ROW_NUMBER() window function handles deduplication by keeping only the most recently ingested version of each record (partitioned by primary key, ordered by _ingested_at descending). Schema standardization renames columns, casts types, and fills nulls to produce a consistent structure for Gold layer consumption.
- Save the notebook.
- Now apply Unity Catalog column masking for PII fields. Open a SQL editor in your workspace and run the following to create a masking function and apply it:
-- Create a masking function for PII email column
CREATE OR REPLACE FUNCTION curated.default.mask_email(email STRING)
RETURNS STRING
RETURN CASE
WHEN is_member('DataEngineering') THEN email
ELSE CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1))
END;
-- Apply the mask to the email column in silver_customers
ALTER TABLE curated.default.silver_customers
ALTER COLUMN email SET MASK curated.default.mask_email;
-- Create a masking function for phone numbers
CREATE OR REPLACE FUNCTION curated.default.mask_phone(phone STRING)
RETURNS STRING
RETURN CASE
WHEN is_member('DataEngineering') THEN phone
ELSE CONCAT('+60*****', RIGHT(phone, 4))
END;
-- Apply the mask to the phone column
ALTER TABLE curated.default.silver_customers
ALTER COLUMN phone SET MASK curated.default.mask_phone;What is column masking?
Unity Catalog column masks are functions applied at query time whenever rows are fetched. Users outside the DataEngineering group see redacted values automatically. The underlying data remains unchanged. This satisfies BNM RMiT requirements for customer data protection without modifying the pipeline itself.
- Configure a second pipeline pointing to silver_pipeline.py with the target catalog set to curated.
Seeing constraint violations?
Check the pipeline event log for records dropped by expect_or_drop. The null-amount transaction (row 1008) is expected to be dropped by the valid_amount constraint. If all records are being dropped, verify column names in your CSV headers match what the constraints reference.
help me debug why my Silver pipeline data quality expectations are dropping all records
Build Gold layer for BI consumption
The Gold layer produces denormalized, query-optimized tables that business users and BI tools consume directly. These tables follow a star schema pattern with pre-computed aggregations.
- Create a new notebook called gold_pipeline.py in the pipeline folder and paste the following code:
from pyspark import pipelines as dp
from pyspark.sql.functions import col, sum, count, avg, max, min, datediff, current_date
# Gold layer: business-ready aggregates in denormalized star schema
# Reads from Silver tables in curated catalog
@dp.materialized_view(
name="gold_daily_transaction_summary",
comment="Daily transaction summaries for BI dashboards",
table_properties={"quality": "gold"}
)
def gold_daily_transaction_summary():
# Read validated transactions from Silver layer
transactions = spark.read.table("curated.default.silver_transactions")
# Aggregate daily summaries per category
return (
transactions
.groupBy(
col("transaction_date").cast("date").alias("transaction_day"),
"category"
)
.agg(
count("transaction_id").alias("total_transactions"),
sum("amount").alias("total_amount"),
avg("amount").alias("avg_amount"),
max("amount").alias("max_amount"),
min("amount").alias("min_amount")
)
)
@dp.materialized_view(
name="gold_customer_lifetime_value",
comment="Customer lifetime value for analytics and ML features",
table_properties={"quality": "gold"}
)
def gold_customer_lifetime_value():
# Join customers and transactions for denormalized view
customers = spark.read.table("curated.default.silver_customers")
transactions = spark.read.table("curated.default.silver_transactions")
# Calculate customer lifetime value metrics
clv_metrics = (
transactions
.groupBy("customer_id")
.agg(
count("transaction_id").alias("total_transactions"),
sum("amount").alias("lifetime_value"),
avg("amount").alias("avg_transaction_amount"),
min("transaction_date").alias("first_transaction"),
max("transaction_date").alias("last_transaction")
)
)
# Denormalized star schema: join customer attributes with metrics
return (
customers
.join(clv_metrics, "customer_id", "left")
.withColumn(
"days_as_customer",
datediff(current_date(), col("registration_date"))
)
)What does this code do?
Gold tables use @dp.materialized_view because they perform batch reads and full recomputation (not incremental streaming). The daily summary table gives BI analysts transaction volumes and amounts grouped by day and category.
The customer lifetime value table joins Silver customer data with aggregated transaction metrics into a single denormalized view. This avoids expensive joins at query time when analysts build dashboards.
- Save the notebook.
- Configure a third pipeline pointing to gold_pipeline.py with the target catalog set to analytics.
- Now create a Databricks SQL Warehouse to serve the Gold layer tables. In your workspace, navigate to SQL Warehouses in the left sidebar.
- Click Create SQL Warehouse.
- Set the name to gold-analytics-warehouse.
- Select Pro as the warehouse type.
- Click Create.
Why a Pro SQL Warehouse?
Pro SQL Warehouses provide better performance for concurrent BI queries while keeping costs lower than Serverless. They support Unity Catalog governance features including the column masks you configured in the Silver layer.
- Run all three pipelines in sequence: Bronze first, then Silver, then Gold.
- Once all pipelines complete successfully, validate the end-to-end flow by querying the Gold layer. Open a new SQL query tab and run:
-- Verify data made it from S3 through to Gold layer
SELECT * FROM analytics.default.gold_daily_transaction_summary
ORDER BY transaction_day;
SELECT * FROM analytics.default.gold_customer_lifetime_value
ORDER BY lifetime_value DESC;You should see daily transaction summaries grouped by category and customer lifetime value metrics with denormalized customer attributes.
Gold tables showing no data?
Ensure all three pipelines ran in sequence. The Gold pipeline reads from Silver tables, which read from Bronze tables. If Bronze has not completed, downstream layers have nothing to process.
Check that the target catalogs match: Bronze targets raw, Silver targets curated, Gold targets analytics.
help me debug why my Gold layer materialized views are empty after running the pipeline
✔️ Awesome, I've got everything!
Great. Double check you have saved all three pipeline notebooks and that your SQL Warehouse is running.
ⓧ I'd like to double check the full code
Here is the full code for each pipeline notebook.
from pyspark import pipelines as dp
from pyspark.sql.functions import current_timestamp, input_file_name
# Bronze layer: append-only raw ingestion from S3 landing zone
# Uses Auto Loader (cloudFiles) for incremental file processing
@dp.table(
name="bronze_customers",
comment="Raw customer data ingested from S3 landing zone - append only",
table_properties={"quality": "bronze"}
)
def bronze_customers():
# Stream new CSV files as they arrive in the customers path
return (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "csv")
.option("mergeSchema", "true")
.option("header", "true")
.option("cloudFiles.schemaLocation", "/mnt/schema/bronze_customers")
.load("s3://your-landing-zone-bucket/customers/")
.withColumn("_ingested_at", current_timestamp())
.withColumn("_source_file", input_file_name())
)
@dp.table(
name="bronze_transactions",
comment="Raw transaction data ingested from S3 landing zone - append only",
table_properties={"quality": "bronze"}
)
def bronze_transactions():
# Stream new CSV files as they arrive in the transactions path
return (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "csv")
.option("mergeSchema", "true")
.option("header", "true")
.option("cloudFiles.schemaLocation", "/mnt/schema/bronze_transactions")
.load("s3://your-landing-zone-bucket/transactions/")
.withColumn("_ingested_at", current_timestamp())
.withColumn("_source_file", input_file_name())
)from pyspark import pipelines as dp
from pyspark.sql.functions import col, row_number, trim, lower, when
from pyspark.sql.window import Window
# Silver layer: validated, cleansed, deduplicated data
# Reads from Bronze streaming tables in raw catalog
@dp.table(
name="silver_customers",
comment="Cleansed and deduplicated customer data",
table_properties={"quality": "silver"}
)
@dp.expect_or_drop("valid_customer_id", "customer_id IS NOT NULL")
@dp.expect_or_drop("valid_email", "email IS NOT NULL")
def silver_customers():
# Read from Bronze streaming table
bronze_df = spark.readStream.table("raw.default.bronze_customers")
# Deduplicate using ROW_NUMBER window function
window_spec = Window.partitionBy("customer_id").orderBy(col("_ingested_at").desc())
deduplicated = (
bronze_df
.withColumn("row_num", row_number().over(window_spec))
.filter(col("row_num") == 1)
.drop("row_num")
)
# Schema standardization: type casting, renaming, null handling
return (
deduplicated
.withColumn("customer_id", col("customer_id").cast("long"))
.withColumn("email", lower(trim(col("email"))))
.withColumn("full_name", trim(col("name")))
.withColumn("phone", col("phone_number"))
.withColumn("registration_date", col("reg_date").cast("date"))
.drop("name", "phone_number", "reg_date", "_source_file", "_ingested_at")
)
@dp.table(
name="silver_transactions",
comment="Validated and cleansed transaction data",
table_properties={"quality": "silver"}
)
@dp.expect_or_drop("valid_transaction_id", "transaction_id IS NOT NULL")
@dp.expect_or_drop("valid_amount", "amount > 0")
@dp.expect_or_drop("valid_customer_ref", "customer_id IS NOT NULL")
def silver_transactions():
# Read from Bronze streaming table
bronze_df = spark.readStream.table("raw.default.bronze_transactions")
# Deduplicate using ROW_NUMBER window function
window_spec = Window.partitionBy("transaction_id").orderBy(col("_ingested_at").desc())
deduplicated = (
bronze_df
.withColumn("row_num", row_number().over(window_spec))
.filter(col("row_num") == 1)
.drop("row_num")
)
# Schema standardization: type casting, renaming, null handling
return (
deduplicated
.withColumn("transaction_id", col("transaction_id").cast("long"))
.withColumn("customer_id", col("customer_id").cast("long"))
.withColumn("amount", col("amount").cast("decimal(18,2)"))
.withColumn("transaction_date", col("txn_date").cast("timestamp"))
.withColumn("category", trim(col("category")))
.withColumn("status", when(col("status").isNull(), "unknown").otherwise(col("status")))
.drop("txn_date", "_source_file", "_ingested_at")
)from pyspark import pipelines as dp
from pyspark.sql.functions import col, sum, count, avg, max, min, datediff, current_date
# Gold layer: business-ready aggregates in denormalized star schema
# Reads from Silver tables in curated catalog
@dp.materialized_view(
name="gold_daily_transaction_summary",
comment="Daily transaction summaries for BI dashboards",
table_properties={"quality": "gold"}
)
def gold_daily_transaction_summary():
# Read validated transactions from Silver layer
transactions = spark.read.table("curated.default.silver_transactions")
# Aggregate daily summaries per category
return (
transactions
.groupBy(
col("transaction_date").cast("date").alias("transaction_day"),
"category"
)
.agg(
count("transaction_id").alias("total_transactions"),
sum("amount").alias("total_amount"),
avg("amount").alias("avg_amount"),
max("amount").alias("max_amount"),
min("amount").alias("min_amount")
)
)
@dp.materialized_view(
name="gold_customer_lifetime_value",
comment="Customer lifetime value for analytics and ML features",
table_properties={"quality": "gold"}
)
def gold_customer_lifetime_value():
# Join customers and transactions for denormalized view
customers = spark.read.table("curated.default.silver_customers")
transactions = spark.read.table("curated.default.silver_transactions")
# Calculate customer lifetime value metrics
clv_metrics = (
transactions
.groupBy("customer_id")
.agg(
count("transaction_id").alias("total_transactions"),
sum("amount").alias("lifetime_value"),
avg("amount").alias("avg_transaction_amount"),
min("transaction_date").alias("first_transaction"),
max("transaction_date").alias("last_transaction")
)
)
# Denormalized star schema: join customer attributes with metrics
return (
customers
.join(clv_metrics, "customer_id", "left")
.withColumn(
"days_as_customer",
datediff(current_date(), col("registration_date"))
)
)Your Medallion Architecture is now proven end-to-end. Next up, you will configure AWS DMS to replicate your real SQL Server and Oracle databases into this same pipeline, replacing the sample CSV files with production CDC streams.
Configure AWS DMS for Database Migration
Your Databricks Lakehouse is fully operational with a working Medallion Architecture pipeline ingesting sample data from S3. Now it is time to connect your actual source databases to this pipeline.
In this step, you will configure AWS Database Migration Service (DMS) to continuously replicate data from your SQL Server and Oracle databases into the S3 landing zone. DMS handles the heavy lifting of reading transaction logs and writing incremental changes to S3 in Parquet format, where your Auto Loader pipeline picks them up automatically.
In this step, get ready to:
- Create a DMS replication instance and configure SQL Server source/target endpoints with CDC.
- Configure Oracle source/target endpoints with supplemental logging and CDC.
- Integrate DMS output with your existing Auto Loader pipeline and set up monitoring.
Configure AWS DMS for SQL Server
AWS DMS uses a replication instance to read changes from your source database and write them to your target. The replication instance must be in the same VPC as your Databricks workspace so it can route data to S3 without traversing the public internet.
Create the DMS replication instance
- Open the AWS DMS console at https://console.aws.amazon.com/dms/v2/.
- Choose Replication instances in the left navigation pane.
- Choose Create replication instance.
- For Name, enter lakehouse-migration-ri.
- For Instance class, choose dms.c5.xlarge (adequate for a 25TB estate with multiple tasks).
- For VPC, select the same VPC where your Databricks workspace is deployed.
- For Multi-AZ, choose Yes for high availability during production migration.
- For Publicly accessible, turn this off (your source connects via Direct Connect/VPN, not the public internet).
- Choose Create replication instance.
What is a replication instance?
A DMS replication instance is a managed compute resource that runs the migration software. It caches transaction logs during migration, so its CPU and memory capacity directly affects migration speed.
The instance takes approximately 10 minutes to become available. You can proceed to create endpoints while it provisions.
Create the SQL Server source endpoint
- In the DMS console, choose Endpoints, then choose Create endpoint.
- For Endpoint type, choose Source endpoint.
- For Endpoint identifier, enter sqlserver-source.
- For Source engine, choose Microsoft SQL Server.
- For Server name, enter your SQL Server hostname (on-premises via Direct Connect/VPN, or the AWS-hosted endpoint).
- For Port, enter 1433 (the default SQL Server port).
- For User name and Password, enter the DMS migration account credentials.
- For Database name, enter the name of the database you are migrating.
- Choose Create endpoint.
Why does SQL Server need CDC enabled at the source?
AWS DMS uses SQL Server transaction log reading for ongoing replication of tables with primary keys. For tables without primary keys, DMS uses MS-CDC (Microsoft Change Data Capture).
Ensure your source SQL Server has MS-CDC enabled at the database level and that the recovery model is set to FULL. Without this, DMS cannot read ongoing changes from the transaction log.
Create the SQL Server target endpoint (S3 in Parquet format)
- Choose Endpoints, then Create endpoint.
- For Endpoint type, choose Target endpoint.
- For Endpoint identifier, enter s3-target-sqlserver.
- For Target engine, choose Amazon S3.
- For Service access role ARN, enter the ARN of the IAM role that grants DMS write and delete access to your S3 landing zone bucket.
- For Bucket name, enter your S3 landing zone bucket name.
- For Bucket folder, enter dms/sqlserver to organize output by source.
- Expand Endpoint settings, choose Wizard, and add the following settings:
- Set DataFormat to parquet.
- Set ParquetVersion to PARQUET_2_0.
- Set IncludeOpForFullLoad to true (adds I/U/D operation column for CDC records).
- Choose Create endpoint.
Why Parquet for the S3 target?
Parquet is a columnar storage format that provides efficient compression and faster query performance compared to CSV. It is the ideal input format for Delta Lake ingestion via Auto Loader because it preserves schema metadata and compresses well.
Create the SQL Server migration task with CDC
- In the DMS console, choose Database migration tasks, then choose Create task.
- For Task identifier, enter sqlserver-to-s3-cdc.
- For Replication instance, choose lakehouse-migration-ri.
- For Source database endpoint, choose sqlserver-source.
- For Target database endpoint, choose s3-target-sqlserver.
- For Migration type, choose Migrate existing data and replicate ongoing changes.
- Enable CloudWatch logs for monitoring.
- In the Table mappings section, add a selection rule to include the specific schemas and tables for Phase 1 migration.
- Choose Create task.
What do table mappings do?
Table mappings define which schemas and tables DMS should replicate. For Phase 1, select only non-critical analytical tables (approximately 5TB) to validate the pipeline before migrating critical systems in later phases.
After you create the task, DMS begins the full load phase followed by ongoing CDC replication. Monitor progress in the Table statistics tab to see per-table load status.
Configure AWS DMS for Oracle
Oracle requires supplemental logging enabled at the database level for DMS to capture ongoing changes. You also need to choose a CDC method: LogMiner (simpler setup) or Binary Reader (higher performance for large volumes).
Create the Oracle source endpoint
- In the DMS console, choose Endpoints, then Create endpoint.
- For Endpoint type, choose Source endpoint.
- For Endpoint identifier, enter oracle-source.
- For Source engine, choose Oracle.
- For Server name, enter your Oracle database hostname.
- For Port, enter 1521 (the default Oracle listener port).
- For User name and Password, enter the DMS migration account credentials.
- For Database name, enter the Oracle SID or service name.
- Choose Create endpoint.
What is supplemental logging?
Oracle supplemental logging ensures that all columns of a row's primary key are written to the redo log whenever a row is updated. Without this, DMS cannot reconstruct the full change record.
Enable it on your Oracle source by running:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;Also ensure ARCHIVELOG mode is enabled and archive logs are retained for at least 24 hours.
LogMiner vs Binary Reader for Oracle CDC
AWS DMS provides two CDC methods for Oracle sources. LogMiner is an Oracle API that reads redo logs and is easier to configure. DMS uses LogMiner by default with no extra connection attributes required.
Binary Reader parses raw redo log files directly and offers better CDC performance with lower source I/O impact. For this project, LogMiner is sufficient for the initial proof-of-concept.
Create the Oracle target endpoint and migration task
- Create a target endpoint with identifier s3-target-oracle following the same pattern as the SQL Server target endpoint above.
- Set Bucket folder to dms/oracle to separate Oracle output from SQL Server output.
- Use the same DataFormat = parquet and ParquetVersion = PARQUET_2_0 settings.
- Create a migration task with identifier oracle-to-s3-fullload-cdc.
- For Migration type, choose Migrate existing data and replicate ongoing changes (full-load + CDC).
- Test with a small table subset before scaling to the full estate.
After the task starts, DMS performs an initial bulk copy of all selected tables, then switches to continuous CDC replication.
Integrate DMS output with Auto Loader pipeline
Your Bronze pipeline currently watches a single S3 path for sample data. Now you need to update it to also watch the DMS output paths where Parquet files arrive continuously.
Update the Bronze pipeline to watch DMS output paths
- Open your /pipeline/bronze_pipeline.py notebook in your Databricks workspace.
- Update your Auto Loader cloudFiles source path to include the DMS output directories (dms/sqlserver and dms/oracle).
- Ensure mergeSchema remains set to true so schema evolution from DMS output is handled automatically.
- Confirm the metadata columns (_ingested_at, _source_file) continue to be added for audit trail purposes.
How does Auto Loader handle DMS CDC files?
DMS writes Parquet files with an operation indicator column (I, U, or D) representing INSERT, UPDATE, or DELETE operations. Auto Loader ingests these files incrementally as they arrive in S3. The Bronze layer preserves all records in append-only mode, including the operation column.
Update the Silver pipeline MERGE logic for CDC records
Your Silver pipeline currently deduplicates records using a MERGE with ROW_NUMBER() window function. Now you need to extend this logic to handle the I/U/D operation codes from DMS CDC records.
- Open your /pipeline/silver_pipeline.py notebook.
- Update your MERGE logic to process the DMS operation column: apply INSERTs and UPDATEs as upserts, and process DELETE operations by marking records as deleted or removing them based on your retention policy.
- Ensure your existing CONSTRAINT expectations (e.g., EXPECT (customer_id IS NOT NULL) ON VIOLATION DROP ROW) continue to validate incoming CDC records.
- Save the notebook.
Validate data consistency
- After DMS completes the initial full-load phase, compare row counts between your source database and the Delta Lake target tables.
- Run a count query in your source SQL Server and Oracle databases, then run the same count on the corresponding Bronze tables in your Databricks workspace.
- Verify that the numbers match, accounting for any in-flight CDC transactions.
Set up CloudWatch monitoring
Monitoring ensures you catch replication lag before it impacts downstream analytics. Configure CloudWatch alarms on two key metrics.
- In the AWS CloudWatch console, navigate to Alarms and choose Create alarm.
- Create an alarm on the DMS CDCLatencySource metric for your replication tasks. Set the threshold to alert if latency exceeds 5 minutes.
- Create a second alarm on Auto Loader backlog metrics. Monitor the number of unprocessed files in your S3 landing zone paths to detect ingestion delays.
- Configure both alarms to send notifications via SNS to your operations team.
Why monitor replication lag?
DMS replication lag indicates how far behind the target is from the source. For BNM compliance, your audit trail must be current. A spike in lag could mean the replication instance is undersized, network bandwidth is saturated, or the source is generating changes faster than DMS can process.
DMS task stuck in running state?
If your task shows as running but no tables appear in Table statistics, verify that your security groups allow the replication instance to reach both the source database port and S3. Also confirm that your source database has CDC or supplemental logging properly enabled.
Why isn't my DMS task loading tables?
Your database migration pipeline is now connected end-to-end. Next up, you will define the phased migration timeline and build the financial business case for your 25TB estate.
Define Migration Phasing Plan and Cost Model
Your technical proof-of-concept is complete. You have a working Databricks workspace, a Medallion Architecture pipeline processing data end-to-end, and AWS DMS replicating from both SQL Server and Oracle into your lakehouse.
Now you need to answer the questions every board and regulator will ask: how long will this take, how much will it cost, and what happens if something goes wrong? In this step, you will define the production migration timeline, build the financial business case with a 3-year TCO comparison, and document the rollback and exit strategy required by BNM RMiT.
In this step, get ready to:
- Define a 5-phase migration plan for 25TB across 36 weeks.
- Build a 3-year TCO model comparing current-state vs. target-state costs.
- Document rollback criteria and a BNM-compliant exit strategy.
Define migration phases
A phased migration plan reduces risk by moving data in controlled batches. Each phase has a defined scope, a BNM compliance posture, and clear success criteria before the next phase begins.
- Switch to your document editor and create a new file called migration_phasing_plan.docx in your /docs folder (alongside your existing BNM risk assessment).
- Add a title: "Cloud Data Migration Phasing Plan" and your organisation name.
- Create a section header: "Migration Phases" and add the following 5 phases as a table or structured list:
# Migration Phases
## Phase 0: Foundation (Weeks 1-4)
- Scope: BNM consultation submission, infrastructure provisioning (Terraform), workspace setup
- BNM Status: Consultation submitted per §15.1
- Data Volume: 0 TB (infrastructure only)
- Success Criteria: Workspace accessible via PrivateLink, Unity Catalog operational
- Status: COMPLETED (Steps 1-5 of this project)
## Phase 1: Non-Critical Analytical Data (Weeks 5-12)
- Scope: Analytical datasets not classified as critical systems
- BNM Status: No notification required (non-critical systems)
- Data Volume: 5 TB
- Source: SQL Server reporting databases, Oracle data warehouse extracts
- Success Criteria: Data validated in Gold layer, BI dashboards migrated
- Rollback: Revert BI tools to legacy source within 24 hours
## Phase 2: Re-platform Existing AWS Data (Weeks 13-20)
- Scope: Data already hosted on AWS (40% of estate) moved to Databricks
- BNM Status: No notification required (already on cloud, changing platform only)
- Data Volume: 10 TB
- Source: Existing AWS databases (RDS, Redshift) to Databricks via DMS
- Success Criteria: Query performance parity, zero data loss validated
- Rollback: Point applications back to original AWS sources
## Phase 3: On-Premises Critical Systems (Weeks 21-30)
- Scope: On-premises critical systems migrated to cloud
- BNM Status: Notification required per §15.2 (subsequent cloud adoption for critical systems)
- Data Volume: 10 TB
- Source: SQL Server transactional systems, Oracle core banking feeds
- Success Criteria: CDC latency < 5 minutes, data quality > 99.9%
- Rollback: Maintain on-premises systems warm for 4 weeks post-cutover
## Phase 4: Cutover and Legacy Decommission (Weeks 31-36)
- Scope: Final validation, application cutover, legacy system decommission
- BNM Status: Covered under Phase 3 notification
- Data Volume: 0 TB new (validation of existing 25 TB)
- Success Criteria: All applications pointing to Databricks, legacy systems offline
- Rollback: Restore from S3 backups to legacy if critical failure within 2 weeksWhy five phases instead of a single cutover?
Each phase isolates risk to a known data volume and criticality level. Phase 1 starts with non-critical data that requires no BNM notification, giving your team time to refine the pipeline before touching regulated systems.
Phase 3 triggers BNM notification under §15.2 (subsequent cloud adoption for critical systems). By then, you have 15TB of successful migration as evidence of operational maturity.
- Save migration_phasing_plan.docx.
- Review each phase and confirm it aligns with the architecture you built in Steps 4-6. Phase 0 should reflect what you have already deployed.
Not sure what counts as a critical system?
BNM RMiT defines critical systems as those whose failure would materially impact customers, financial stability, or the institution's ability to meet regulatory obligations. If in doubt, classify the system as critical and include it in Phase 3 (which triggers BNM notification).
help me classify my systems as critical or non-critical under BNM RMiT
Build the financial business case
A TCO model proves the migration makes financial sense over 3 years. The model compares what you spend today (licensing, hardware, operations) against what the target Databricks architecture will cost.
- Return to your spreadsheet tool where you have the Platform Evaluation Matrix.
- Create a new sheet (tab) called "TCO Model" within the same workbook.
- Structure the TCO model using the following categories. Fill in your organisation's actual costs where known, or use the reference estimates provided:
# Current-State Costs (Annual)
| Category | Item | Annual Cost |
|---------------------------|-----------------------------------|---------------|
| Licensing | SQL Server Enterprise licenses | $XXX,XXX |
| Licensing | Oracle Database licenses | $XXX,XXX |
| Hardware | On-premises servers (depreciation)| $XXX,XXX |
| Hardware | Storage arrays | $XXX,XXX |
| Operations | DBA FTE (2-3 staff) | $XXX,XXX |
| Operations | Infrastructure admin FTE | $XXX,XXX |
| Existing AWS | Current AWS spending (40% estate) | $XXX,XXX |
| Maintenance | Hardware support contracts | $XXX,XXX |
| | **TOTAL CURRENT-STATE** | **$XXX,XXX** |# Target-State Costs (Annual, Steady-State Year 3)
| Category | Item | Annual Cost | Notes |
|-------------------|-----------------------------------------|---------------|-------------------------------------|
| Storage | S3 Standard (5-8 TB compressed) | $1,500-$2,400 | ~$0.025/GB/month in ap-southeast-1 |
| Compute - BI | Databricks SQL Pro Warehouse | $XXX,XXX | $0.22/DBU, size based on query load |
| Compute - ETL | Databricks Jobs Compute | $XXX,XXX | $0.15/DBU, scheduled pipelines |
| Compute - ML | Job clusters (spot instances + GPU) | $XXX,XXX | Based on training frequency |
| Networking | AWS PrivateLink endpoints | $XXX,XXX | Per-endpoint hourly + data transfer |
| Networking | Data transfer (internal) | $XXX,XXX | Cross-AZ and VPC endpoint traffic |
| Governance | Unity Catalog (included in tier) | $0 | Included with Premium/Enterprise |
| Platform | Databricks Enterprise subscription | $XXX,XXX | Contact Databricks for contracted rate |
| Operations | Platform engineer FTE (1-2 staff) | $XXX,XXX | Reduced from 2-3 DBAs |
| | **TOTAL TARGET-STATE (Year 3)** | **$XXX,XXX** | |Where do these Databricks rates come from?
The DBU rates referenced here ($0.22 for SQL Pro, $0.15 for Jobs Compute) are widely cited industry estimates. Databricks does not publish static per-DBU rates on their website. Contact your Databricks account team for contracted pricing specific to your organisation and volume.
Unity Catalog governance is included with Premium and Enterprise tiers at no additional license cost.
- Now add a 3-year comparison section below the cost tables. Structure it to show the migration investment curve:
# 3-Year Cost Comparison
| Year | Current-State | Target-State | Net Difference | Notes |
|--------|---------------|----------------------|----------------|------------------------------------------|
| Year 1 | $XXX,XXX | $XXX,XXX (higher) | +$XXX,XXX | Migration investment + dual-running costs|
| Year 2 | $XXX,XXX | $XXX,XXX (lower) | -$XXX,XXX | Legacy decommission savings begin |
| Year 3 | $XXX,XXX | $XXX,XXX (lower) | -$XXX,XXX | Steady-state, full savings realised |
| | | **3-Year Net Saving**| **-$XXX,XXX** | |
# Year 1 Breakdown (Migration Investment)
- Dual-running period: legacy + Databricks in parallel (Phases 1-3)
- AWS DMS replication instance costs
- Databricks compute for validation testing
- Consulting/contractor costs for migration execution
- Staff training and enablement
# Year 2 Breakdown (Decommission Savings)
- SQL Server licenses not renewed (Phase 1+2 data migrated)
- Oracle license reduction (Phase 3 data migrated)
- Hardware support contracts cancelled
- On-premises server decommission
# Year 3 Breakdown (Steady-State)
- Full Databricks operation, no legacy costs
- Reduced FTE (platform engineers vs DBAs)
- No hardware depreciation or replacement cycles- Add a final section to the TCO sheet for intangible ROI. These benefits are difficult to quantify but strengthen the business case for board approval:
# Intangible ROI
| Benefit | Impact |
|---------------------------------------|------------------------------------------------|
| Unified governance (Unity Catalog) | Reduced audit preparation costs, single source of truth for BNM compliance |
| ML model deployment velocity | From weeks to hours with MLflow + Feature Store|
| Reduced time-to-insight | Self-service BI via SQL Warehouse, no DBA queue|
| Open format portability (Delta Lake) | Zero vendor lock-in, satisfies BNM exit strategy requirement |
| Operational simplification | Single platform for BI + ML + ETL vs 4 separate systems |What does this model do?
Year 1 is intentionally more expensive than the current state because you are running both environments in parallel. This is expected and required by BNM for business continuity.
The crossover point (where target-state becomes cheaper) typically occurs midway through Year 2 as legacy licenses expire and hardware contracts end. The 3-year net saving is the number your CFO cares about.
- Save the spreadsheet.
- Verify that your TCO Model sheet has four sections: Current-State Costs, Target-State Costs, 3-Year Comparison, and Intangible ROI.
Not sure what to put in the cost fields?
For this proof-of-concept, use estimates based on your organisation's current spending. If exact numbers are unavailable, use industry benchmarks: SQL Server Enterprise typically costs $15,000-$60,000 per core pair annually. Oracle Database Enterprise Edition is typically $47,500 per processor license.
help me estimate TCO costs for my specific database estate
Define rollback and exit strategy
BNM RMiT mandates a documented exit strategy and business continuity plan. This section defines what happens if a phase fails, and how you would extract all data from Databricks if you ever needed to leave the platform entirely.
- Return to migration_phasing_plan.docx in your document editor.
- Add a new section header: "Rollback Criteria and Exit Strategy" below the migration phases.
- Add the following rollback and exit strategy content:
# Rollback Criteria and Exit Strategy
## Dual-Running Requirements
- Each phase maintains legacy systems operational for minimum 4 weeks after cutover
- Legacy and Databricks run in parallel during validation period
- Applications maintain connection strings to both environments
- Rollback can be triggered at any point during dual-running window
## Rollback Triggers (Any Single Trigger Initiates Rollback)
| Category | Threshold | Measurement Source |
|---------------------|----------------------------------------------------|-----------------------------||
| Data Quality | > 0.1% record discrepancy between source and target| Row count + checksum validation |
| Performance | Query latency > 2x baseline for 3+ consecutive hours| Databricks SQL Warehouse metrics |
| Availability | Workspace unavailable > 15 minutes (unplanned) | CloudWatch + Databricks status |
| CDC Latency | Replication lag > 30 minutes for 1+ hour | CloudWatch DMS alarms |
| Security Incident | Any data exposure or unauthorised access event | Unity Catalog audit logs |
## Exit Strategy (Platform Departure)
### Data Portability
- All data stored in Delta Lake open format on customer-owned S3 buckets
- Delta Lake is an open-source format (Apache-licensed) readable by any Spark-compatible engine
- No proprietary encoding or vendor-specific data format dependencies
- Customer retains full ownership of S3 buckets and KMS keys at all times
### Exit Procedure
1. Freeze all write operations to Delta Lake tables
2. Export Unity Catalog metadata (schemas, table definitions, access policies)
3. Convert Delta tables to Parquet if target platform requires it (lossless conversion)
4. Redirect applications to new compute engine reading from same S3 paths
5. Terminate Databricks workspace via Terraform destroy
6. AWS resources (VPC, S3, KMS) remain under customer control
### Exit Timeline
- Estimated: 4-8 weeks for full platform departure
- No data movement required (data stays on customer S3)
- Primary effort: redirecting compute and BI tools to alternative engine
## Monitoring Plan
| Tool | Monitors |
|-------------------------------|---------------------------------------------|
| Databricks SQL Dashboard | Migration progress, data quality scores, Gold layer freshness |
| CloudWatch Alarms | DMS replication lag, Auto Loader backlog, VPC endpoint health |
| Unity Catalog Audit Logs | Access patterns, policy changes, data lineage |
| AWS Cost Explorer | Spend tracking against TCO model projections |Why Delta Lake satisfies the BNM exit strategy requirement
BNM RMiT §10.51 requires FIs to retain ownership and control of customer data. Because Delta Lake stores data as open-format Parquet files on your own S3 buckets, you can read the data with any compatible engine without Databricks involvement.
This eliminates the vendor lock-in risk that regulators worry about. Your data never leaves your AWS account, and your KMS keys remain under your control.
- Save migration_phasing_plan.docx.
- Review the rollback triggers table. Confirm that the measurement sources match the monitoring tools you deployed in earlier steps (CloudWatch alarms for DMS lag, Databricks SQL Warehouse metrics, Unity Catalog audit logs).
Unsure about rollback thresholds?
The thresholds in the template are starting points. Adjust them based on your organisation's SLAs. For example, if your business requires sub-second query latency, the 2x baseline threshold may need to be tighter.
help me set appropriate rollback thresholds for my migration
✔️ Awesome, I've got everything!
Great. Double check you have saved both your migration phasing plan document and your updated spreadsheet with the TCO model.
ⓧ I'd like to double check the full plan
Your migration_phasing_plan.docx should contain these sections:
- Migration Phases (Phases 0-4, Weeks 1-36, covering 25TB across non-critical then critical systems).
- Rollback Criteria (dual-running requirements plus threshold table for data quality, performance, availability, CDC latency, security).
- Exit Strategy (Delta Lake open format on customer-owned S3, exit procedure steps, estimated 4-8 week timeline).
- Monitoring Plan (Databricks SQL Dashboard, CloudWatch Alarms, Unity Catalog Audit Logs, AWS Cost Explorer).
Your Platform Evaluation Matrix spreadsheet should have a new "TCO Model" sheet with: Current-State Costs, Target-State Costs, 3-Year Comparison (Year 1 higher due to dual-running, Years 2-3 showing savings), and Intangible ROI.
Secret mission
Build a Data Quality Monitoring Framework for BNM Audit Readiness
Your Medallion pipeline is working, but BNM auditors need continuous compliance evidence. Can you build a data quality monitoring framework that tracks pass/fail rates across every layer, classifies data by sensitivity, and alerts when quality thresholds are breached?
Clean Up Your Resources
Clean Up Your Resources
Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses paid cloud resources that incur charges when compute is active.
Cost warning
Your Databricks workspace is billed per DBU whenever SQL Warehouses or job clusters are running. The AWS DMS replication instance also incurs hourly charges while active.
VPC endpoints (PrivateLink) have a small hourly cost even when idle. If you are not proceeding to production migration immediately, choose Pause or Delete to avoid unexpected costs.
Resources you used:
- Databricks workspace (Enterprise tier, ap-southeast-1). Billed per DBU when compute is running.
- Databricks SQL Warehouse (Pro tier). Billed per DBU when active.
- AWS DMS replication instance, SQL Server migration task, and Oracle migration task. Hourly billing while the instance is running.
- S3 buckets: root bucket, landing zone, and audit logs bucket (all in ap-southeast-1, encrypted with KMS CMK). Storage costs per GB-month.
- AWS KMS customer-managed key. Monthly key charge.
- VPC endpoints (PrivateLink). Hourly charge per endpoint.
- IAM roles (cross-account role for Databricks, Unity Catalog storage credentials). No direct cost but access risk if left active.
- CloudWatch alarms (DMS replication lag, Auto Loader backlog, data quality SLA breach).
- Unity Catalog metastore with catalogs (raw, curated, analytics) and sensitivity tags.
- Databricks SQL Data Quality Dashboard and Lakeflow Spark Declarative Pipelines.
- Local project files: /terraform/main.tf, /pipeline/ notebooks, /docs/ documents, and /diagrams/ architecture diagram.
- Databricks account (14-day free trial).
Keep everything running
No action needed. Choose this if you are proceeding to production migration and want to keep the workspace, DMS replication, and pipelines active.
- Monitor Databricks compute costs via the Databricks account console Usage dashboard.
- Monitor AWS resource costs via AWS Cost Explorer.
- Your DMS replication tasks will continue syncing changes from SQL Server and Oracle via CDC.
- S3 storage costs continue but are minimal for sample data volumes.
Pause - I'll come back to this later
Shut down running compute to stop DBU and hourly charges. Your data and configuration remain intact for when you return.
- In the Databricks workspace, navigate to SQL Warehouses and stop all running warehouses.
- Terminate any active job clusters from the Compute page in your workspace.
- Stop your DMS replication tasks by running this command for each task:
aws dms stop-replication-task --replication-task-arn your-task-arn-here- S3 storage costs and VPC endpoint hourly charges continue at a minimal rate. KMS key charges also continue.
- When you return, restart your SQL Warehouse and resume DMS tasks to pick up where you left off.
Delete - I don't want to use this again
Remove all project resources and start fresh. This is irreversible for cloud resources.
- Navigate to your Terraform project directory and destroy all infrastructure by running:
cd terraform
terraform destroy- This removes the Databricks workspace, VPC endpoints, IAM roles, KMS key, DMS resources, and CloudWatch alarms provisioned by your Terraform configuration.
- Terraform will not delete non-empty S3 buckets. Empty and delete each bucket manually by running:
aws s3 rm s3://your-root-bucket --recursive
aws s3 rb s3://your-root-bucket
aws s3 rm s3://your-landing-zone-bucket --recursive
aws s3 rb s3://your-landing-zone-bucket
aws s3 rm s3://your-audit-logs-bucket --recursive
aws s3 rb s3://your-audit-logs-bucket- If you signed up for the Databricks 14-day free trial, cancel your plan to prevent conversion to pay-as-you-go billing. In the Databricks account console, click Settings in the sidebar. Select Manage your plan, then choose Cancel plan and confirm in the dialog.
- Delete local project files by running:
macOS
rm -rf terraform pipeline docs diagramsWindows
Remove-Item -Recurse -Force .\terraform, .\pipeline, .\docs, .\diagrams- Delete the Platform Evaluation Matrix spreadsheet from your local machine or cloud drive.
Cancellation is permanent
Cancelling your Databricks plan terminates access to all workspaces, notebooks, and data. Export any notebooks or query results you want to keep before cancelling.
Your data in customer-owned S3 buckets remains yours. Only Databricks-managed metadata and workspace configuration is lost.
Nice Work!
Nice Work!
You now have both a regulatory-ready decision package AND a working technical proof-of-concept for your cloud data migration. This combination proves feasibility to stakeholders while meeting BNM RMiT consultation requirements.
You've learned how to:
- Build a BNM RMiT-compliant decision package including a weighted platform evaluation matrix with 3-year TCO projections, a risk assessment framework structured per Appendix 7/10, and a phased migration plan with exit strategy for 25TB of SQL Server/Oracle data.
- Deploy a production-grade Databricks workspace on AWS via Terraform with PrivateLink (no public internet exposure), customer-managed encryption, and a Unity Catalog metastore governing data across BI and ML workloads.
- Implement a working Medallion Architecture pipeline (Bronze → Silver → Gold) using Auto Loader and Lakeflow Spark Declarative Pipelines, connected to AWS DMS replication from SQL Server and Oracle with CDC.
- Secret Mission: Build a data quality monitoring framework with expectations at every Medallion layer, a compliance dashboard, data classification tagging, audit log export, and alerting for BNM audit readiness.
Ready to quiz yourself?