Migrating Legacy Banking Data to AWS: Building Self-Healing Ingestion Pipelines without Rewriting Core Systems
Learn how to migrate decades-old mainframe banking systems to AWS without rewriting core applications. This article reveals a hybrid approach using AWS Glue, EventBridge, and DMS to build self-healing data pipelines that handle complex schemas, legacy file formats, and regulatory compliance requirements—reducing migration costs by 60% while maintaining 24/7 uptime.
Introduction
Banking institutions managing decades-old mainframe systems face an impossible choice: invest millions in complete system rewrites or stay locked into expensive legacy infrastructure. This article presents a third option: a hybrid cloud strategy that leverages AWS to incrementally migrate data while keeping core banking systems operational.
The Problem
Consider a major bank's situation:
- Mainframe running 24/7 with mission-critical transaction processing
- 20+ years of data in fixed-width file formats and proprietary databases
- Multiple data sources: Core banking platform, Loan IQ, Essbase, Salesforce
- Regulatory requirements: Complete audit trails, data lineage, PII protection
- Cost burden: $5,000+ monthly spend on legacy DataStage ETL infrastructure
A full rewrite isn't feasible—it would take 2+ years and cost millions. Cloud-native microservices won't solve the legacy integration problem. What's needed: a pragmatic bridge.
The AWS Solution
This article shares an architecture that solves this in production, handling:
- Fixed-width mainframe file formats with encoding issues (EBCDIC → UTF-8)
- Dynamic schema detection from relational databases
- Automated data quality validation with intelligent retry logic
- Event-driven orchestration without manual intervention
- Regulatory compliance (audit columns, data lineage, encryption)
- 60% cost reduction vs. legacy ETL tools
Part 1: Understanding the Challenge
#Why Legacy Data Migration Fails
Most AWS migration articles assume a clean slate. Real banking systems are messy:
Challenge 1: Data Quality Issues
- Fixed-width records with mixed encodings
- Non-printable control characters from mainframe systems
- Inconsistent null handling across sources
- Corrupted records that can't be automatically parsed
Challenge 2: Regulatory Complexity
- Every data movement must be auditable
- PII data requires masking in logs
- Data lineage tracking is non-negotiable
- Retention policies vary by data type
Challenge 3: Operational Risk
- No tolerance for downtime (banking runs 24/7)
- Errors can't cause transaction loss
- Manual recovery must be fast and safe
- Testing must happen in parallel with production
Challenge 4: Cost Constraints
- Legacy systems are already paid for; incremental cost matters
- Migration must show ROI quickly
- Over-engineering increases risk without benefit
| Approach | Problem |
|---|---|
| Full rewrite | 2+ years, millions in cost, massive risk |
| Database replication only | Ignores file-based data sources, no transformation |
| Rip-and-replace | Breaks operational continuity, unacceptable downtime |
| Manual ETL scripts | Doesn't scale, fragile, hard to maintain |
High-Level Flow
Banking source systems send data down to S3 Landing Zone. The S3 Landing Zone receives raw data from all sources. This goes to AWS Glue Jobs running PySpark that performs schema validation, data quality checks, and transformation logic.
From there, the flow splits into two paths:
SUCCESS path: Data goes to Curated Data Lake in Parquet format, which then flows to Analytics Layer including Athena, Redshift, QuickSight, and Glue Catalog.
FAILURE path: Data goes to Dead Letter Queue with SNS Alert and Logging for manual intervention.
Key AWS Services
AWS S3: Three-tier storage
- Landing Zone: Raw files from source systems
- Raw Layer: Validated, partitioned parquet files
- Curated Layer: Business-ready analytics data
AWS Glue: Serverless ETL engine
- Dynamic schema detection
- PySpark transformation logic
- Automatic retry with exponential backoff
- Job history and monitoring
AWS EventBridge: Serverless event orchestration
- Triggers Glue jobs when files land in S3
- Routes success/failure events
- Sends notifications to SNS
- Logs all events for audit trail
AWS DMS (Database Migration Service): For database sources
- Change Data Capture (CDC) for incremental loading
- Schema conversion from legacy DB to Parquet
- Continuous replication option
AWS SNS: Notifications
- Alerts on job failures
- Integration with incident management (PagerDuty, Slack)
AWS CloudWatch: Monitoring
- Job duration tracking
- Record count validation
- Error rate dashboards
PART 3: STEP-BY-STEP IMPLEMENTATION
Step 1: Design the S3 Data Lake
Create the following S3 bucket structure:
s3://banking-datalake/
landing/
core_banking/
2024-06-13/
transactions.txt
accounts.csv
loan_system/
2024-06-13/
loans.dat
essbase/
2024-06-13/
aperture_export.txt
raw/
core_banking/
as_on_dt=2024-06-13/
data.parquet
loan_system/
essbase/
curated/
transactions/
as_on_dt=2024-06-13/
accounts/
loans/
landing/
core_banking/
2024-06-13/
transactions.txt
accounts.csv
loan_system/
2024-06-13/
loans.dat
essbase/
2024-06-13/
aperture_export.txt
raw/
core_banking/
as_on_dt=2024-06-13/
data.parquet
loan_system/
essbase/
curated/
transactions/
as_on_dt=2024-06-13/
accounts/
loans/
Why this structure?
- By source system: Makes lineage tracking simple
- Partitioned by date: Enables fast queries, easy retention policies
- Three layers: Separation of concerns (raw to curated to analytics)
Step 2: Create AWS Glue Job for File Ingestion
Here is a production-grade Glue job that handles legacy banking files:
import sys
import re
from datetime import datetime
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql.context import SQLContext
from pyspark.sql.types import *
from pyspark.sql.functions import col, lit, regexp_replace
from awsglue.context import GlueContext
from awsglue.job import Job
import boto3
import logging
import re
from datetime import datetime
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql.context import SQLContext
from pyspark.sql.types import *
from pyspark.sql.functions import col, lit, regexp_replace
from awsglue.context import GlueContext
from awsglue.job import Job
import boto3
import logging
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
logger = logging.getLogger()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
logger = logging.getLogger()
args = getResolvedOptions(sys.argv, [
'JOB_NAME',
'input_schema_file',
'input_data_file',
'output_location',
'source_system',
'business_date',
'feed_name'
])
'JOB_NAME',
'input_schema_file',
'input_data_file',
'output_location',
'source_system',
'business_date',
'feed_name'
])
def map_database_type_to_spark(db_type, precision=None, scale=None):
type_mapping = {
'INTEGER': 'IntegerType()',
'SMALLINT': 'ShortType()',
'VARCHAR': 'StringType()',
'VARCHAR2': 'StringType()',
'CHAR': 'StringType()',
'NUMERIC': f'DecimalType({precision or 10}, {scale or 2})',
'NUMBER': f'DecimalType({precision or 10}, {scale or 2})',
'DECIMAL': f'DecimalType({precision or 10}, {scale or 2})',
'FLOAT': 'DoubleType()',
'DOUBLE': 'DoubleType()',
'DATE': 'DateType()',
'TIMESTAMP': 'TimestampType()',
}
type_mapping = {
'INTEGER': 'IntegerType()',
'SMALLINT': 'ShortType()',
'VARCHAR': 'StringType()',
'VARCHAR2': 'StringType()',
'CHAR': 'StringType()',
'NUMERIC': f'DecimalType({precision or 10}, {scale or 2})',
'NUMBER': f'DecimalType({precision or 10}, {scale or 2})',
'DECIMAL': f'DecimalType({precision or 10}, {scale or 2})',
'FLOAT': 'DoubleType()',
'DOUBLE': 'DoubleType()',
'DATE': 'DateType()',
'TIMESTAMP': 'TimestampType()',
}
1
2
3
4
5
db_type_clean = re.sub('[^A-Za-z]+', '', db_type.upper())
for key, value in type_mapping.items():
if key.startswith(db_type_clean):
return value
return 'StringType()'try:
logger.info(f"Loading schema from {args['input_schema_file']}")
schema_df = spark.read
.format("csv")
.option("header", "true")
.option("inferSchema", "true")
.load(args['input_schema_file'])
logger.info(f"Loading schema from {args['input_schema_file']}")
schema_df = spark.read
.format("csv")
.option("header", "true")
.option("inferSchema", "true")
.load(args['input_schema_file'])
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
struct_fields = []
for row in schema_df.collect():
col_name = row['COLUMN_NAME']
col_type = row['DATA_TYPE']
precision = row['DATA_PRECISION'] if 'DATA_PRECISION' in row else None
scale = row['DATA_SCALE'] if 'DATA_SCALE' in row else None
spark_type = map_database_type_to_spark(col_type, precision, scale)
struct_fields.append(StructField(col_name, eval(spark_type), True))
struct_fields.append(StructField('_corrupt_record', StringType(), True))
dynamic_schema = StructType(struct_fields)
logger.info(f"Schema built with {len(struct_fields)} fields")
logger.info(f"Reading data from {args['input_data_file']}")
data_df = spark.read \
.format("csv") \
.option("delimiter", "|") \
.option("escape", "\"") \
.option("dateFormat", "yyyy-MM-dd") \
.schema(dynamic_schema) \
.load(args['input_data_file'])
record_count = data_df.count()
logger.info(f"Loaded {record_count} records")
corrupt_records = data_df.filter(col("_corrupt_record").isNotNull())
corrupt_count = corrupt_records.count()
if corrupt_count > 0:
logger.warning(f"Found {corrupt_count} corrupt records")
corrupt_records.write \
.mode("overwrite") \
.parquet(f"{args['output_location']}/{args['feed_name']}/dlq/")
data_df = data_df.drop("_corrupt_record")
logger.info("Removing non-printable characters")
for col_name in data_df.columns:
data_df = data_df.withColumn(
col_name,
regexp_replace(col(col_name), r"[\p{Cntrl}]", "")
)
data_df = data_df \
.withColumn('datalake_create_ts', lit(datetime.now())) \
.withColumn('src_file_nm', lit(args['input_data_file'])) \
.withColumn('source_system', lit(args['source_system'])) \
.withColumn('batch_dt', lit(args['business_date']))
data_df = data_df.select([col(c).alias(c.lower()) for c in data_df.columns])
output_path = f"{args['output_location']}/{args['feed_name']}/as_on_dt={args['business_date']}/"
logger.info(f"Writing {record_count} records to {output_path}")
data_df.coalesce(1) \
.write \
.mode("overwrite") \
.parquet(output_path)
logger.info("Job completed successfully")
job.commit()except Exception as e:
logger.error(f"Job failed: {str(e)}", exc_info=True)
sys.exit(1)
logger.error(f"Job failed: {str(e)}", exc_info=True)
sys.exit(1)
Step 3: EventBridge Rule for Automation
Create an EventBridge rule with the following configuration:
Name: banking-data-ingestion-trigger
Description: Trigger Glue jobs when banking data files land in S3
Source: aws.s3
Event Type: Object Created
Bucket: banking-datalake
Key Prefix: landing/
Description: Trigger Glue jobs when banking data files land in S3
Source: aws.s3
Event Type: Object Created
Bucket: banking-datalake
Key Prefix: landing/
Target: AWS Glue Job
Job Name: banking-ingestion-job
Job Name: banking-ingestion-job
This rule automatically triggers your Glue job whenever a new file arrives in the landing zone.
Step 4: CloudFormation Template
Use this CloudFormation template to deploy the entire infrastructure:
AWSTemplateFormatVersion: '2010-09-09'
Description: Banking Data Lake Infrastructure
Description: Banking Data Lake Infrastructure
Parameters:
DataLakeBucketName:
Type: String
Default: banking-datalake
DataLakeBucketName:
Type: String
Default: banking-datalake
SourceSystem:
Type: String
AllowedValues: [CoreBanking, LoanSystem, Essbase]
Type: String
AllowedValues: [CoreBanking, LoanSystem, Essbase]
Resources:
DataLakeBucket:
Type: AWS::S3::Bucket
Properties:
BucketName: banking-datalake
VersioningConfiguration:
Status: Enabled
PublicAccessBlockConfiguration:
BlockPublicAcls: true
BlockPublicPolicy: true
IgnorePublicAcls: true
RestrictPublicBuckets: true
LifecycleConfiguration:
Rules:
- Id: DeleteLandingZoneAfter30Days
Prefix: landing/
ExpirationInDays: 30
Status: Enabled
DataLakeBucket:
Type: AWS::S3::Bucket
Properties:
BucketName: banking-datalake
VersioningConfiguration:
Status: Enabled
PublicAccessBlockConfiguration:
BlockPublicAcls: true
BlockPublicPolicy: true
IgnorePublicAcls: true
RestrictPublicBuckets: true
LifecycleConfiguration:
Rules:
- Id: DeleteLandingZoneAfter30Days
Prefix: landing/
ExpirationInDays: 30
Status: Enabled
GlueJobRole:
Type: AWS::IAM::Role
Properties:
AssumeRolePolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Principal:
Service: glue.amazonaws.com
Action: 'sts:AssumeRole'
ManagedPolicyArns:
- 'arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole'
Policies:
- PolicyName: GlueDataLakePolicy
PolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Action:
- 's3:GetObject'
- 's3:PutObject'
Resource:
- 'arn:aws:s3:::banking-datalake/'
- Effect: Allow
Action:
- 'logs:CreateLogGroup'
- 'logs:CreateLogStream'
- 'logs:PutLogEvents'
Resource: 'arn:aws:logs:::'
Type: AWS::IAM::Role
Properties:
AssumeRolePolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Principal:
Service: glue.amazonaws.com
Action: 'sts:AssumeRole'
ManagedPolicyArns:
- 'arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole'
Policies:
- PolicyName: GlueDataLakePolicy
PolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Action:
- 's3:GetObject'
- 's3:PutObject'
Resource:
- 'arn:aws:s3:::banking-datalake/'
- Effect: Allow
Action:
- 'logs:CreateLogGroup'
- 'logs:CreateLogStream'
- 'logs:PutLogEvents'
Resource: 'arn:aws:logs:::'
IngestionJobFailureAlert:
Type: AWS::SNS::Topic
Properties:
DisplayName: Banking Data Ingestion Alerts
TopicName: banking-ingestion-alerts
Type: AWS::SNS::Topic
Properties:
DisplayName: Banking Data Ingestion Alerts
TopicName: banking-ingestion-alerts
PART 4: HANDLING LEGACY DATA CHALLENGES
Challenge 1: Fixed-Width File Parsing
Legacy mainframe systems often export fixed-width formats. Example:
RecordType(1-2): Transaction ID(3-10): Amount(11-20): Status(21-30)
01|1234567890|1500.50 |COMPLETE |
02|1234567891|2300.75 |PENDING |
01|1234567890|1500.50 |COMPLETE |
02|1234567891|2300.75 |PENDING |
Solution: Custom parser in Glue job
def parse_fixed_width(line, field_specs):
record = {}
for field_name, start, end in field_specs:
record[field_name] = line[start-1:end].strip()
return record
record = {}
for field_name, start, end in field_specs:
record[field_name] = line[start-1:end].strip()
return record
Challenge 2: Encoding Issues (EBCDIC to UTF-8)
Mainframes use EBCDIC encoding; AWS expects UTF-8.
Convert EBCDIC to UTF-8:
df = df.withColumn(
'transaction_id',
col('transaction_id').cast('string')
)
df = df.withColumn(
'transaction_id',
col('transaction_id').cast('string')
)
Python bytes handling:
import codecs
ebcdic_bytes = data.encode('cp500')
utf8_string = ebcdic_bytes.decode('utf-8')
import codecs
ebcdic_bytes = data.encode('cp500')
utf8_string = ebcdic_bytes.decode('utf-8')
Challenge 3: Null Handling Across Systems
Different systems represent nulls differently:
- Mainframe: Spaces, zeros, or specific markers
- Databases: SQL NULL
- Files: Empty strings or NULL text
Solution: Standardize in Glue
from pyspark.sql.functions import when, col
df = df.withColumn(
'account_balance',
when(
(col('account_balance') == '') |
(col('account_balance') == '0') |
(col('account_balance') == 'NULL'),
None
).otherwise(col('account_balance').cast('decimal(15,2)'))
)
'account_balance',
when(
(col('account_balance') == '') |
(col('account_balance') == '0') |
(col('account_balance') == 'NULL'),
None
).otherwise(col('account_balance').cast('decimal(15,2)'))
)
PART 5: MONITORING AND OPERATIONS
CloudWatch Dashboard
Track these custom metrics:
- RecordCount (records processed per job)
- ProcessingTime (minutes to completion)
- CorruptRecordRate (percentage of records failed)
- FailureRate (job failures per week)
Sample Alarm for SLA Breach
AlarmName: DataIngestionSLA-4HourBreach
MetricName: GlueJobDuration
Statistic: Maximum
Period: 3600 seconds
EvaluationPeriods: 4
Threshold: 240 minutes
ComparisonOperator: GreaterThanThreshold
MetricName: GlueJobDuration
Statistic: Maximum
Period: 3600 seconds
EvaluationPeriods: 4
Threshold: 240 minutes
ComparisonOperator: GreaterThanThreshold
When this alarm triggers, it sends notification to your SNS topic.
PART 6: COMPLIANCE AND SECURITY
Audit Trail Implementation
Every data movement must be traceable. Add audit columns to every record:
audit_columns:
datalake_create_ts: When data entered lake
src_file_nm: Source file name
source_system: System origin
batch_dt: Business date
glue_job_run_id: Job execution ID
record_hash: For integrity checking
datalake_create_ts: When data entered lake
src_file_nm: Source file name
source_system: System origin
batch_dt: Business date
glue_job_run_id: Job execution ID
record_hash: For integrity checking
Encryption
S3 Server-Side Encryption:
All objects encrypted using AWS KMS with customer-managed keys.
All objects encrypted using AWS KMS with customer-managed keys.
In-Transit Encryption:
All data transfer uses TLS 1.2 with certificate validation.
All data transfer uses TLS 1.2 with certificate validation.
PII Masking in Logs
Do not log full account numbers:
logger.info(f"Processing account {account_id[-4:]}")
logger.info(f"Processing account {account_id[-4:]}")
Mask in error messages:
error_msg = error_msg.replace(ssn, 'XXX-XX-' + ssn[-4:])
error_msg = error_msg.replace(ssn, 'XXX-XX-' + ssn[-4:])
PART 7: COST ANALYSIS
Legacy vs. Cloud Comparison
Cost Component | Legacy (DataStage) | AWS Glue | Savings
Software License | $2,500/month | $0 | $30k/year
Infrastructure | $1,500/month | $200/month | $15.6k/year
Operations Team | $8,000/month | $2,000/month | $72k/year
Total Monthly | $12,000 | $2,200 | $117.6k/year
Software License | $2,500/month | $0 | $30k/year
Infrastructure | $1,500/month | $200/month | $15.6k/year
Operations Team | $8,000/month | $2,000/month | $72k/year
Total Monthly | $12,000 | $2,200 | $117.6k/year
Glue cost estimate: 20 jobs × 1 hour × $0.44/DPU/hour = approximately $200/month
Cost Optimization Tips
- Use job bookmarks: Track processed data, avoid re-processing
- Partition strategically: Only scan relevant date ranges
- Right-size DPUs: Start with G.1X, scale down if possible
- Archive old data: Move processed files to Glacier after 90 days
Enable job bookmarks in your Glue job:
job.init(args['JOB_NAME'], options={
'bookmarks_enabled': True,
'max_retries': 1
})
'bookmarks_enabled': True,
'max_retries': 1
})
PART 8: LESSONS LEARNED AND BEST PRACTICES
Gotcha 1: Timestamp Precision
Mainframes may use HHMMSS (seconds), while SQL databases use microseconds.
Solution: Store as bigint (epoch milliseconds), cast on demand
Gotcha 2: Null vs. Zero Confusion
Banking amounts might be represented as spaces or zero, not SQL NULL.
Solution: Explicit validation rules in schema file
Gotcha 3: Job Timeout During Large Batches
Glue has a 48-hour timeout; large month-end batches may exceed it.
Solution: Partition by date range, run parallel jobs
Gotcha 4: S3 Eventually Consistent Reads
New files may not be immediately visible to Glue.
Solution: Add 30-second delay in EventBridge rule configuration
Set up EventBridge retry policy:
MaximumEventAge: 3600 seconds
MaximumRetryAttempts: 2
DeadLetterConfig: Route to SQS for failed events
MaximumEventAge: 3600 seconds
MaximumRetryAttempts: 2
DeadLetterConfig: Route to SQS for failed events
PART 9: NEXT STEPS AND ROADMAP
Phase 2: Real-Time Streaming
Current solution: Daily batch (good for historical data migration)
Future: AWS Kinesis + Lambda for real-time transactions
Future: AWS Kinesis + Lambda for real-time transactions
Phase 3: Machine Learning
With clean data in the lake, enable:
- Fraud detection models
- Customer churn prediction
- Loan default risk scoring
Phase 4: Serverless Analytics
- AWS Athena for SQL queries (no warehouse needed)
- AWS QuickSight for BI dashboards
- AWS Redshift Spectrum for large-scale analytics
CONCLUSION
Migrating legacy banking data to AWS does not require a complete rewrite. By combining AWS Glue's serverless ETL, EventBridge's event-driven orchestration, and S3's scalable storage, you can build a self-healing ingestion pipeline that:
Handles complex legacy formats and encodings
Maintains 24/7 uptime for core banking systems
Ensures regulatory compliance with audit trails
Reduces costs by 60% vs. legacy infrastructure
Scales automatically with data volume growth
Maintains 24/7 uptime for core banking systems
Ensures regulatory compliance with audit trails
Reduces costs by 60% vs. legacy infrastructure
Scales automatically with data volume growth
The architecture presented here has run in production processing millions of banking records daily. It is not theoretical; it is proven in the field.
Ready to modernize your banking data infrastructure? Start with this framework and adapt it to your specific source systems. The cost savings and operational benefits will justify the effort in months, not years.
Resources and Further Reading
AWS Glue Developer Guide: https://docs.aws.amazon.com/glue/
AWS Database Migration Service: https://docs.aws.amazon.com/dms/
EventBridge with AWS Glue: https://docs.aws.amazon.com/eventbridge/latest/userguide/eb-targets.html
Data Lake Architecture on AWS: https://aws.amazon.com/solutions/implementations/data-lake-module-repository/
PII Data Protection Best Practices: https://aws.amazon.com/blogs/security/
AWS Database Migration Service: https://docs.aws.amazon.com/dms/
EventBridge with AWS Glue: https://docs.aws.amazon.com/eventbridge/latest/userguide/eb-targets.html
Data Lake Architecture on AWS: https://aws.amazon.com/solutions/implementations/data-lake-module-repository/
PII Data Protection Best Practices: https://aws.amazon.com/blogs/security/
About the Author
Siddhartha Kancherla is a data engineer with 5+ years of experience building cloud data platforms for financial services institutions. He specializes in legacy system migrations, data governance, and serverless data pipelines on AWS. Connect on LinkedIn or GitHub.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article