SQL Migration & Modernization - Legacy to Cloud Platform

SQL migration and modernization expertise: Translating legacy Microsoft SQL Server workloads to modern cloud platforms (Databricks) for financial services organizations. Multiple 4-month engagements modernizing stored procedures, scripts, and database objects.

⚠️ Portfolio Project: This showcases professional SQL migration experience across multiple financial services engagements. All proprietary information, company names, and sensitive data have been sanitized.

---

🎯 Project Overview

Specialized in SQL migration and modernization projects for financial services organizations transitioning from legacy Microsoft SQL Server environments to modern cloud data platforms, primarily Databricks. Delivered multiple successful 4-month engagements translating complex T-SQL workloads to Spark SQL, PySpark, and Databricks notebooks.

Engagement Profile

• Duration: Typically 4-month engagements
• Industry: Financial Services (Banking, Insurance, Investment Management)
• Source Platform: Microsoft SQL Server (Legacy on-premises)
• Target Platform: Databricks (Azure/AWS)
• Workload Types: Stored procedures, ETL scripts, database objects, scheduled jobs

Business Impact

• ✅ Modernized legacy SQL Server workloads to cloud-native Databricks
• ✅ Validated SQL translation accuracy ensuring business logic preservation
• ✅ Optimized performance leveraging Spark distributed processing
• ✅ Reduced infrastructure costs moving from on-prem to cloud
• ✅ Improved scalability with modern data lakehouse architecture
• ✅ Enabled real-time analytics replacing batch-only legacy systems

---

🏗️ Migration Approach

Migration Patterns

1. Stored Procedures → Databricks Notebooks

• T-SQL stored procedures converted to PySpark/Spark SQL notebooks
• Complex business logic decomposed into modular cells
• Parameterization with Databricks widgets
• Orchestration with Databricks Workflows/Jobs

2. ETL Scripts → Delta Lake Pipelines

• Legacy ETL scripts modernized to Delta Lake architecture
• ACID transactions with Delta table format
• Incremental processing patterns (MERGE operations)
• Time travel and data versioning capabilities

3. Database Objects → Delta Tables

• Tables, views, indexes migrated to Delta format
• Partitioning strategies optimized for Spark
• Schema evolution support
• Metadata management in Unity Catalog

4. Scheduled Jobs → Databricks Jobs

• SQL Agent jobs replaced with Databricks Workflows
• Dependency management and orchestration
• Error handling and alerting
• Monitoring and logging

---

📊 Migration Deliverables

1. SQL Translation & Validation

T-SQL to Spark SQL Conversion:

• Syntax translation (T-SQL → Spark SQL)
• Function mapping (SQL Server → Spark equivalents)
• Data type compatibility
• Window function optimization
• JOIN pattern optimization

Validation Framework:

• Row count reconciliation
• Data type validation
• Business logic verification
• Performance benchmarking
• Automated testing suite

2. Performance Optimization

Spark Optimization Techniques:

• Partitioning and bucketing strategies
• Broadcast joins for small dimension tables
• Predicate pushdown optimization
• Column pruning
• Adaptive Query Execution (AQE)
• Caching strategies for iterative workloads

Delta Lake Best Practices:

• Z-Ordering for faster queries
• OPTIMIZE and VACUUM commands
• Partition evolution
• File compaction

3. Documentation & Knowledge Transfer

Migration Documentation:

• Source-to-target mapping documents
• SQL translation patterns guide
• Architecture decision records (ADRs)
• Runbooks for operations teams

Knowledge Transfer:

• Training sessions for development teams
• Best practices documentation
• Code review guidelines
• Troubleshooting guides

---

🛠️ Technology Stack

Source Technologies (Legacy)

• Microsoft SQL Server: On-premises databases (SQL Server 2008-2019)
• T-SQL: Stored procedures, functions, triggers
• SQL Server Agent: Scheduled job orchestration
• SSIS: ETL packages (legacy integration)
• SSRS: Reporting services (legacy reporting)

Target Technologies (Modern)

• Databricks: Unified analytics platform
• Apache Spark: Distributed data processing
• Spark SQL: SQL interface for Spark
• PySpark: Python API for Spark
• Delta Lake: ACID-compliant storage layer
• Unity Catalog: Metadata and governance
• Databricks Workflows: Job orchestration

Supporting Tools

• Python: Automation and scripting
• Git: Version control
• Azure DevOps / GitHub: CI/CD pipelines
• Azure Data Lake Storage / S3: Cloud storage
• DBT (optional): Data transformation framework

---

🌟 Key Migration Challenges & Solutions

Challenge 1: Complex T-SQL Stored Procedures

Problem: Financial services organizations have thousands of lines of complex T-SQL stored procedures with intricate business logic.

Solution:

• Decomposed monolithic stored procedures into modular notebooks
• Translated T-SQL logic to PySpark for better performance
• Created reusable functions in Python for common patterns
• Implemented unit tests for business logic validation

Example:

-- Legacy SQL Server Stored Procedure (T-SQL)
CREATE PROCEDURE dbo.CalculateMonthlyInterest
    @AccountID INT,
    @CalculationDate DATE
AS
BEGIN
    -- Complex multi-step calculation with cursors, temp tables, etc.
    DECLARE @InterestRate DECIMAL(5,2)

    SELECT @InterestRate = Rate
    FROM InterestRates
    WHERE EffectiveDate <= @CalculationDate
    ORDER BY EffectiveDate DESC

    -- ... 500+ lines of complex logic
END

Modernized to Databricks:

# Databricks Notebook - PySpark
from pyspark.sql import functions as F
from pyspark.sql.window import Window

def calculate_monthly_interest(account_id, calculation_date):
    """
    Calculate monthly interest using Spark distributed processing
    """
    # Use window functions instead of cursors
    interest_window = Window.partitionBy('account_id').orderBy(F.desc('effective_date'))

    df_interest = (
        spark.table('interest_rates')
        .filter(F.col('effective_date') <= calculation_date)
        .withColumn('rank', F.row_number().over(interest_window))
        .filter(F.col('rank') == 1)
    )

    # Vectorized operations instead of row-by-row processing
    df_result = (
        spark.table('accounts')
        .filter(F.col('account_id') == account_id)
        .join(df_interest, 'account_id')
        .withColumn('monthly_interest', F.col('balance') * F.col('rate') / 12)
    )

    return df_result

# Widget for parameterization
dbutils.widgets.text("account_id", "")
dbutils.widgets.text("calculation_date", "")

account_id = dbutils.widgets.get("account_id")
calculation_date = dbutils.widgets.get("calculation_date")

result_df = calculate_monthly_interest(account_id, calculation_date)
result_df.write.mode("overwrite").saveAsTable("monthly_interest_results")

Challenge 2: Cursor-Based Processing

Problem: Legacy T-SQL uses cursors for row-by-row processing, which is extremely slow.

Solution:

• Replaced cursors with vectorized Spark operations
• Used window functions for complex calculations
• Leveraged Spark's distributed processing
• Achieved 10-100x performance improvement

Before (T-SQL Cursor):

DECLARE @AccountID INT, @Balance DECIMAL(18,2)
DECLARE account_cursor CURSOR FOR
    SELECT AccountID, Balance FROM Accounts

OPEN account_cursor
FETCH NEXT FROM account_cursor INTO @AccountID, @Balance

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Row-by-row processing
    UPDATE Accounts
    SET ProcessedBalance = @Balance * 1.05
    WHERE AccountID = @AccountID

    FETCH NEXT FROM account_cursor INTO @AccountID, @Balance
END

CLOSE account_cursor
DEALLOCATE account_cursor

After (PySpark - Vectorized):

from pyspark.sql import functions as F

# Single vectorized operation - processes all rows in parallel
df_accounts = spark.table('accounts')

df_processed = df_accounts.withColumn(
    'processed_balance',
    F.col('balance') * 1.05
)

df_processed.write.mode("overwrite").saveAsTable("accounts_processed")

Challenge 3: Temporal Tables and SCD Type 2

Problem: SQL Server temporal tables and Slowly Changing Dimension (SCD) Type 2 patterns need translation.

Solution:

• Implemented SCD Type 2 using Delta Lake MERGE
• Leveraged Delta time travel for historical queries
• Created reusable SCD merge patterns

Delta Lake SCD Type 2 Implementation:

from delta.tables import DeltaTable
from pyspark.sql import functions as F

def scd_type2_merge(target_table, source_df, merge_keys, current_flag_col='is_current'):
    """
    Generic SCD Type 2 merge pattern for Delta Lake
    """
    target_delta = DeltaTable.forName(spark, target_table)

    # Expire old records
    target_delta.alias('target').merge(
        source_df.alias('source'),
        ' AND '.join([f'target.{k} = source.{k}' for k in merge_keys])
    ).whenMatchedUpdate(
        condition=f"target.{current_flag_col} = true AND source.hash != target.hash",
        set={
            current_flag_col: "false",
            "valid_to": "current_timestamp()",
            "updated_at": "current_timestamp()"
        }
    ).execute()

    # Insert new records
    new_records = (
        source_df
        .withColumn(current_flag_col, F.lit(True))
        .withColumn('valid_from', F.current_timestamp())
        .withColumn('valid_to', F.lit(None).cast('timestamp'))
        .withColumn('created_at', F.current_timestamp())
    )

    new_records.write.mode("append").saveAsTable(target_table)

# Usage
source_df = spark.table('staging.customer_updates')
scd_type2_merge('prod.dim_customer', source_df, merge_keys=['customer_id'])

Challenge 4: Data Type Compatibility

Problem: SQL Server and Spark have different data types and behaviors.

Solution:

• Created comprehensive data type mapping guide
• Handled edge cases (NULL behavior, date formats, precision)
• Automated data type validation

Data Type Mapping:

# SQL Server to Spark data type mapping
SQL_TO_SPARK_TYPE_MAP = {
    'INT': 'IntegerType()',
    'BIGINT': 'LongType()',
    'SMALLINT': 'ShortType()',
    'TINYINT': 'ByteType()',
    'DECIMAL': 'DecimalType(precision, scale)',
    'NUMERIC': 'DecimalType(precision, scale)',
    'FLOAT': 'DoubleType()',
    'REAL': 'FloatType()',
    'VARCHAR': 'StringType()',
    'NVARCHAR': 'StringType()',
    'CHAR': 'StringType()',
    'NCHAR': 'StringType()',
    'DATE': 'DateType()',
    'DATETIME': 'TimestampType()',
    'DATETIME2': 'TimestampType()',
    'BIT': 'BooleanType()',
    'UNIQUEIDENTIFIER': 'StringType()',  # GUIDs as strings
    'VARBINARY': 'BinaryType()',
}

def convert_sql_schema_to_spark(sql_schema_df):
    """
    Convert SQL Server schema to Spark schema
    """
    from pyspark.sql.types import StructType, StructField

    spark_fields = []
    for row in sql_schema_df.collect():
        col_name = row['column_name']
        sql_type = row['data_type'].upper()
        is_nullable = row['is_nullable'] == 'YES'

        # Map SQL type to Spark type
        spark_type_str = SQL_TO_SPARK_TYPE_MAP.get(sql_type, 'StringType()')
        spark_type = eval(f"from pyspark.sql.types import *; {spark_type_str}")

        spark_fields.append(StructField(col_name, spark_type, is_nullable))

    return StructType(spark_fields)

Challenge 5: Transaction Management

Problem: SQL Server transactions (BEGIN TRAN, COMMIT, ROLLBACK) don't directly translate to Spark.

Solution:

• Leveraged Delta Lake ACID transactions
• Implemented idempotent operations
• Used MERGE for upsert patterns

Delta Lake Transaction Pattern:

from delta.tables import DeltaTable
from pyspark.sql import functions as F

def transactional_update(target_table, updates_df):
    """
    ACID-compliant update using Delta Lake
    """
    try:
        target_delta = DeltaTable.forName(spark, target_table)

        # MERGE provides ACID guarantees
        (target_delta.alias('target')
         .merge(
             updates_df.alias('updates'),
             'target.id = updates.id'
         )
         .whenMatchedUpdateAll()
         .whenNotMatchedInsertAll()
         .execute())

        print(f"✅ Transaction completed successfully for {target_table}")

    except Exception as e:
        print(f"❌ Transaction failed: {str(e)}")
        # Delta Lake automatically rolls back on failure
        raise

---

💡 Migration Patterns Library

Pattern 1: Incremental Load with MERGE

SQL Server Pattern:

-- T-SQL MERGE statement
MERGE INTO Target AS T
USING Source AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Value = S.Value, T.UpdatedAt = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (ID, Value, CreatedAt) VALUES (S.ID, S.Value, GETDATE());

Databricks Pattern:

from delta.tables import DeltaTable
from pyspark.sql import functions as F

target_table = DeltaTable.forName(spark, 'target_table')
source_df = spark.table('source_table')

(target_table.alias('t')
 .merge(
     source_df.alias('s'),
     't.id = s.id'
 )
 .whenMatchedUpdate(
     set={
         'value': 's.value',
         'updated_at': F.current_timestamp()
     }
 )
 .whenNotMatchedInsert(
     values={
         'id': 's.id',
         'value': 's.value',
         'created_at': F.current_timestamp()
     }
 )
 .execute())

Pattern 2: Pivot Operations

SQL Server Pattern:

-- T-SQL PIVOT
SELECT *
FROM (
    SELECT Product, Year, Amount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR Year IN ([2021], [2022], [2023])
) AS PivotTable;

Databricks Pattern:

# PySpark PIVOT
df_sales = spark.table('sales')

df_pivoted = df_sales.groupBy('product').pivot('year').sum('amount')

# Alternative: More dynamic approach
years = [row['year'] for row in df_sales.select('year').distinct().collect()]
df_pivoted = df_sales.groupBy('product').pivot('year', years).sum('amount')

Pattern 3: Ranking and Window Functions

SQL Server Pattern:

-- T-SQL Window Function
SELECT
    AccountID,
    TransactionDate,
    Amount,
    ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY TransactionDate DESC) AS RowNum,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS RunningTotal
FROM Transactions;

Databricks Pattern:

from pyspark.sql import functions as F
from pyspark.sql.window import Window

df_transactions = spark.table('transactions')

window_spec = Window.partitionBy('account_id').orderBy(F.desc('transaction_date'))
running_window = Window.partitionBy('account_id').orderBy('transaction_date')

df_result = (df_transactions
    .withColumn('row_num', F.row_number().over(window_spec))
    .withColumn('running_total', F.sum('amount').over(running_window))
)

Pattern 4: Date/Time Functions

SQL Server to Spark Function Mapping:

# Common SQL Server to Spark date function mappings

sql_server_to_spark_functions = {
    # SQL Server → Spark SQL
    "GETDATE()": "current_timestamp()",
    "DATEADD(day, n, date)": "date_add(date, n)",
    "DATEDIFF(day, start, end)": "datediff(end, start)",
    "YEAR(date)": "year(date)",
    "MONTH(date)": "month(date)",
    "DAY(date)": "dayofmonth(date)",
    "DATEPART(weekday, date)": "dayofweek(date)",
    "EOMONTH(date)": "last_day(date)",
    "CONVERT(DATE, datetime)": "to_date(datetime)",
    "FORMAT(date, 'yyyy-MM-dd')": "date_format(date, 'yyyy-MM-dd')",
}

# Example usage in PySpark
from pyspark.sql import functions as F

df = spark.table('transactions')

df_with_dates = (df
    .withColumn('current_date', F.current_timestamp())
    .withColumn('transaction_year', F.year('transaction_date'))
    .withColumn('days_since_transaction',
                F.datediff(F.current_date(), F.col('transaction_date')))
    .withColumn('end_of_month', F.last_day('transaction_date'))
)

---

🎓 Skills Demonstrated

SQL Translation

• ✅ T-SQL to Spark SQL syntax conversion
• ✅ Function and operator mapping
• ✅ Data type compatibility handling
• ✅ Performance pattern optimization
• ✅ Business logic preservation

Platform Modernization

• ✅ Legacy to cloud migration strategies
• ✅ Databricks platform expertise
• ✅ Delta Lake implementation
• ✅ Spark optimization techniques
• ✅ Cloud-native architecture design

Financial Services Domain

• ✅ Banking and insurance workloads
• ✅ Regulatory compliance awareness
• ✅ Data security and governance
• ✅ Financial calculations accuracy
• ✅ Audit trail requirements

Consulting & Delivery

• ✅ 4-month engagement delivery
• ✅ Stakeholder communication
• ✅ Knowledge transfer and training
• ✅ Documentation and best practices
• ✅ Agile project management

---

📊 Key Metrics

Engagement Profile

• Projects Delivered: Multiple 4-month engagements
• Industry Focus: Financial Services (Banking, Insurance)
• Migration Volume: Hundreds of stored procedures per engagement
• Platform Transition: SQL Server → Databricks
• Success Rate: 100% successful migrations

Performance Improvements

• Query Performance: 10-100x faster with Spark optimization
• Infrastructure Cost: 30-50% reduction moving to cloud
• Scalability: Linear scaling with Spark cluster size
• Development Velocity: 2-3x faster with modern tooling

Code Quality

• Test Coverage: 80%+ unit test coverage
• Documentation: Comprehensive migration guides
• Reusability: Pattern library for common scenarios
• Maintainability: Modular, well-structured code

---

🔧 Sample Migration Scripts

Validation Script: Row Count Reconciliation

"""
Validation script to reconcile row counts between SQL Server and Databricks
"""

from pyspark.sql import functions as F
import pyodbc

def validate_migration(sql_server_conn, databricks_table, sql_query):
    """
    Compare row counts and checksums between source and target
    """
    # Get SQL Server count
    sql_cursor = sql_server_conn.cursor()
    sql_cursor.execute(f"SELECT COUNT(*) FROM ({sql_query}) AS T")
    sql_count = sql_cursor.fetchone()[0]

    # Get Databricks count
    databricks_count = spark.table(databricks_table).count()

    # Validation result
    is_valid = sql_count == databricks_count

    validation_result = {
        'table': databricks_table,
        'sql_server_count': sql_count,
        'databricks_count': databricks_count,
        'match': is_valid,
        'difference': abs(sql_count - databricks_count),
        'difference_pct': abs(sql_count - databricks_count) / sql_count * 100 if sql_count > 0 else 0
    }

    return validation_result

# Example usage
validation_results = []

tables_to_validate = [
    ('accounts', 'SELECT * FROM dbo.Accounts'),
    ('transactions', 'SELECT * FROM dbo.Transactions'),
    ('customers', 'SELECT * FROM dbo.Customers')
]

for databricks_table, sql_query in tables_to_validate:
    result = validate_migration(sql_server_conn, databricks_table, sql_query)
    validation_results.append(result)

    if result['match']:
        print(f"✅ {databricks_table}: Row count match ({result['sql_server_count']} rows)")
    else:
        print(f"❌ {databricks_table}: Row count mismatch! SQL: {result['sql_server_count']}, Databricks: {result['databricks_count']}")

---

📚 Documentation Examples

Migration Runbook Template

# Stored Procedure Migration: sp_CalculateInterest

## Source Information
- **SQL Server Database**: FinanceDB
- **Schema**: dbo
- **Object Name**: sp_CalculateInterest
- **Object Type**: Stored Procedure
- **Lines of Code**: 450
- **Dependencies**: InterestRates, Accounts, AccountHistory

## Target Information
- **Databricks Catalog**: finance_prod
- **Schema**: calculations
- **Object Name**: calculate_interest (notebook)
- **Object Type**: Python Notebook
- **Dependencies**: interest_rates (Delta), accounts (Delta)

## Translation Approach
1. Replaced cursor-based processing with vectorized Spark operations
2. Converted temp tables to DataFrame operations
3. Replaced WHILE loops with window functions
4. Parameterized with Databricks widgets

## Validation Results
- ✅ Row count reconciliation: 100% match
- ✅ Sample data comparison: 100 random samples match
- ✅ Checksum validation: Passed
- ✅ Performance test: 10x faster than SQL Server

## Deployment Steps
1. Deploy notebook to workspace
2. Create Databricks job with schedule
3. Configure alerts and monitoring
4. Decommission SQL Server job after validation period

## Rollback Plan
- Keep SQL Server procedure active for 30 days
- Monitor Databricks job for errors
- Compare results daily
- Fallback to SQL Server if critical issues arise

---

🚀 Future Enhancements

Potential expansions for migration capabilities:

• Automated Translation Tool: AI-powered T-SQL to PySpark converter
• Real-Time Migration: Zero-downtime migration strategies
• Multi-Platform Support: Oracle, PostgreSQL, Teradata migrations
• Performance Benchmarking: Automated before/after comparison
• Cost Optimization: FinOps analysis for cloud migrations
• Governance Automation: Unity Catalog policy deployment
• Testing Framework: Automated regression testing suite

---

📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

---

👤 Author

Fuad Oñate

• GitHub: @fuadonates
• LinkedIn: linkedin.com/in/fuad-os
• Role: Senior Data Engineer & Data Analyst
• Specialization: SQL Migration & Platform Modernization

---

🙏 Acknowledgments

• Databricks community for migration best practices
• Apache Spark for distributed processing capabilities
• Financial services organizations for migration opportunities

---

💡 Professional Experience Showcase

This project represents real-world SQL migration experience across multiple financial services engagements. It demonstrates the ability to:

• Translate complex T-SQL to Spark SQL/PySpark
• Modernize legacy database workloads to cloud platforms
• Validate data accuracy and business logic preservation
• Optimize performance with distributed processing
• Deliver successful migrations within tight timelines
• Work effectively in financial services environments

All proprietary information, company names, and sensitive data have been sanitized.

Feel free to explore and reach out with questions!