Gaurav Soni

• Background: The legacy data warehouse was built on Oracle, leveraging Slowly Changing Dimension (SCD) Type 2 for tracking historical changes in deferred compensation data. While this approach supported auditability, it introduced significant complexity and performance bottlenecks—especially during year-end compliance reporting and data reconciliation.

Challenges with Oracle and SCD Type 2:

• High latency in processing large datasets.
• Complex joins and versioning logic led to inconsistent outputs.
• It is difficult to scale for real-time or near-real-time reporting.
• Maintenance overhead due to a rigid schema and manual interventions.

Solution: Databricks-Based Modernization. The project aimed to re-engineer the entire data pipeline using Databricks to enable scalable, performant, and maintainable data transformations.

Key Contributions:

• Designed and implemented a modular ETL pipeline using PySpark and Delta Lake.
• Replaced SCD Type 2 logic with optimized data versioning using Delta Lake’s time travel and merge capabilities.
• Ingested and transformed multi-source compensation data into a unified, query-efficient format.
• Automated validations and reconciliation checks to ensure compliance, and audit readiness.
• Enabled real-time reporting capabilities for finance and HR teams.

Impact:

• 60–70% reduction in data processing time.
• Improved data accuracy and consistency across reporting layers.
• Simplified maintenance, and enhanced scalability for future use cases.
• Streamlined compliance reporting, with better traceability and audit support.

Led comprehensive data migration initiatives for enterprise-scale systems, managing a cross-functional team of ~20 professionals and overseeing the complete lifecycle from planning through implementation and validation.

• System Architecture & Performance Optimization: Designed and implemented Oracle PL/SQL framework with parallel processing capabilities, resulting in measurable improvements to data load performance and system throughput.
• Pipeline Development & Automation: Developed automated data orchestration pipeline using Spring Boot/Batch framework to streamline bulk data transfer operations, ensuring consistent reliability and operational efficiency.
• Data Governance & Quality Assurance: Established comprehensive data governance policies and quality assurance protocols, implementing validation frameworks to ensure data integrity throughout migration processes and maintain compliance standards.

• Database Leadership: Led design, development, and maintenance of critical Oracle database systems using PL/SQL, optimizing performance through AWR/ASH analysis and execution plan tuning
• Data Security & Compliance: Implemented enterprise-level security measures including Oracle VPD and row-level security to protect sensitive compensation data
• ETL Architecture: Designed and built complex data integration workflows using Talend Studio, ensuring seamless data flow across multiple systems
• Large-Scale Migration: Successfully managed migration project onboarding 600+ clients with high-volume data transfers while maintaining data integrity
• Team Leadership: Led cross-functional project teams through full project lifecycle, mentored junior developers, and established coding standards and best practices
• Technical Strategy: Contributed to database architecture decisions and implemented data migration solutions ensuring consistency across global compensation systems

Application Development & Client Services

• Designed and implemented CSS applications for 24+ enterprise clients including Nomura Securities, Shire Pharmaceuticals, Adidas, Vodafone, and UBS Group
• Served as dedicated Onsite Coordinator for one year, managing direct client relationships, requirements gathering, and project delivery coordination

Database Development & Optimization

• Utilized advanced SQL diagnostic tools including SQL TRACE and Profiler for comprehensive database debugging and performance analysis
• Developed and maintained PL/SQL solutions using Explain Plan methodology for query optimization, statistical analysis, and strategic index creation
• Architected complex database solutions incorporating composite data types, dynamic SQL, cursors, and global temporary tables
• Designed and implemented sophisticated relational and data warehouse infrastructures, including database objects such as triggers and stored procedures

Technical Leadership & Problem Resolution

• Led comprehensive issue resolution initiatives and conducted detailed root cause analysis for critical system problems
• Managed end-to-end development lifecycle from requirements analysis through implementation and deployment
• Coordinated work distribution and mentored development team members on technical best practices
• Database Technologies: PL/SQL, SQL optimization, data warehousing, database architecture
• Development Tools: SQL TRACE, SQL Profiler, database modeling tools
• Client Management: Requirements gathering, project coordination, stakeholder communication
• Enterprise Solutions: Multi-client application development, scalable system design
• Resolved complex technical issues through rigorous troubleshooting and root-cause analysis, minimizing downtime and disruptions to business operations.