Verified Expert in Engineering
Data Warehouse Design Developer
Charles has over a decade of experience architecting, designing, and implementing SQL solutions and software applications. He's led technical teams of up to ten, directly and indirectly, and worked in up to 100 TB data environments. Charles specializes in BI, data warehousing, ETL, automation, databases, and data management solutions in the Microsoft technology stack.
SQL Server 2019, Visual Studio, C#.NET, Databases, Azure SQL, Azure, DevOps, Agile
The most amazing...
...project I've architected was automating the creation of new data warehouses from an XML configuration managed through a web application.
- Wrote C# codes to simulate the activities of financial advisors.
- Extended the automation to be driven through a JSON configuration, allowing other teams to generate millions of subscriptions with millions of accounts.
- Architected a framework with T-SQL to generate 26 TB of historical transactions. The code would review current data allocation and then scale the historical data according to a JSON configuration.
Senior Database Engineer
- Architected an archival process separating historical data into a new database with page compression for 150+ tables. Reduced the production database from 1.2 TB to 300 GB and removed contention from audit triggers.
- Designed data transfer processes to allow PHI data for select practices to be migrated to QA. Data were de-identified during transfer and included 115 tables.
- Identified problematic code patterns, reducing several 15+ hour executions to under 1 hour.
- Implemented memory-optimized objects to relieve disk pressure related to temp tables usage, which resulted in a 70% recovery of execution time.
Data Solution Architect
Thorpe Abbotts Capital
- Architected an ETL process responsible for generating 750 GB per execution from a 1 TB data source that contains financials and market data from S&P Global.
- Designed CI/CD utilizing DevOps. I extended deployments to multiple environments and multiple deployments to one environment augmented with PowerShell, allowing side-by-side ETLs to compare.
- Integrated SSIS for parallel execution of procedures and reduced ETL run time by 50%.
- Worked side by side with financial experts in creating and tuning custom algorithms. As they devised complex equations spanning thousands of lines of code, I ensured efficient execution and accuracy.
- Implemented the company’s first continuous integration data solution via Visual Studio Team Services. Incorporated automated deployments to test and development environments, where unit tests were automatically triggered.
- Created service broker processes to migrate gated user changes from isolated internal environments to client-facing AWS machines.
- Developed and implemented the company’s first consolidated central reporting platform with email delivery providing the daily status of SQL job executions and SQL backups across 20+ SQL servers.
- Reduced the ETL query performance by over 80% by reengineering ETL processes using binary hashing. The previous codes took over 10 hours to run, invoking race conditions, deadlocks, and excessive IO consumption, leading to failed data migrations.
- Circumvented performance problems related to normalization by utilizing Bitwise coding patterns.
Senior Software Engineer
- Spearheaded five refactoring projects to reduce database size reduction from 1 TB to 300 GB for one project and averaged a 15% reduction for the remaining four projects.
- Built and maintained SQL scripts, indexes, and complex queries for analysis and extraction of data vital to product roadmaps and strategic decision-making.
- Conducted SQL server tuning. Designed C# libraries, data warehouses, and ETL processes.
- Performed code and design reviews and introduced enhancements to meet established time frames for query execution.
- Liaised with international team members for product training and facilitating product handoff.
- Collaborated with project managers and internal stakeholders to develop KPIs and metrics for reporting performance and growth of the Windows Azure product line to division heads.
- Led training, web demonstrations, and knowledge transfers between three teams across three continents.
- Headed up the SQL code review; evaluated TSQL peer code for query optimization.
- Was routinely commended for superior critical thinking skills and ability to quickly assess and resolve issues on the fly, with traditional and outside-the-box solutions to sustain forward momentum.
Senior Software Engineer
- Reduced 35-minute queries to eight seconds by rearchitecting the reporting for a real-time BI data warehouse.
- Discovered and eliminated design flaws from inaccurate business assumptions that had indexed views of 800 million records (45 GB), with 52% of those records never being reported on.
- Designed a configurable C# solution that exposed a CSS-type functionality to SSRS reports. Provided visual consistency between reports and allowed customization due to the XML base.
- Minimized the footprint of queries by 45% after utilizing various techniques with cross applies and grouping sets in conjunction with pivots.
- Presented a novel idea to address a critical performance bug that crippled the application during the initial pilot release; resolved the issue within 45 minutes, saving days of development cycles in other solutions.
Senior Software Engineer
- Recruited to design query requests and reports, which quickly progressed into a leadership role coordinating the design and maintenance of a web portal for managing credit card transactions, merchant accounts, and provision of credit card devices.
- Migrate code from four web applications into a common solution, illuminating business logic previously buried in the application to unit tests, subsequently hailed as the single most important product improvement to their products in five years.
- Refactored 50 stored procedures to use more efficient joins while removing redundant codes and logic.
- Normalized core databases to reduce redundancy and prevented duplicate work by employees by eradicating the need to update several records for a single change.
- Implemented the company’s first data mart to resolve the long history of problems with reporting performance.
Software Engineer II
- Created SSIS packages for five different products; successfully developed data mashup from the Exchange, Active Directory, event logs, and network alerts.
- Completed a complex project of modifying 200 SSRS reports four weeks ahead of schedule.
- Teamed with offshore developers in designing OLAP database and SSRS reporting strategy for an Exchange reporting solution.
- Tuned and refactored sprocs (stored procedures) necessary for generating dynamic SQL for an ad-hoc Active Directory reporting product. Incorporated configurable logging to lessen the time required for creating root cause analysis of bugs.
- Played an integral role in the development of the company’s first OLAP cube.
Automated Warehouse Creation
The configuration for each client was stored in XML and managed under source control—enabling us to track schema changes for each client. This approach allowed the team to clone a star schema from one client to another, or to propose multiple variations of a schema with ease and no additional coding. The solution generated persisted code based on the configuration from XML, ensuring the client facing warehouse contained zero dynamic code for maximum query optimization.
I assured the stability and reliability through continuous integration and the replacement of bugs with proactive validation. The extensibility of this solution allowed us to devise multiple internal testing databases covering all use-cases. The quality was guaranteed through unit and regression testing.
As my team worked to devise an overhaul, I took a more pragmatic approach and reviewed the existing architecture. During the root-cause analysis, I identified the following errors in logic that hindered the ETL processes.
1. Each insertion of data caused page breaks in the clustered index.
2. Foreign keys on staging tables also hindered the data insertion.
3. Dimensional tables with large record counts and numerous text fields caused unnecessary table scans during upsert procedures.
To resolve these issues, I first devised a new clustered indexing strategy prioritizing record insertion speed. Removal of the foreign keys in the staging tables eliminated a bottleneck during record insertion. I then created a hash key derived from the text columns. Utilizing this field, I was able to ensure that only one column was needed to identify new or updated records. This change turned table scans into index seeks.
Once my solutions were implemented, we were able to reduce the ETL processing time to mere minutes.
T-SQL (Transact-SQL), SQL, XML, C#, HTML5, C#.NET, Python
Azure DevOps Services, Microsoft Power BI, SSAS, Git, Visual Studio, Query Plan
Database Design, Agile Software Development, ETL Implementation & Design, Continuous Integration (CI), Automation, Test-driven Development (TDD), ETL, DevOps, Agile, Business Intelligence (BI), Dimensional Modeling, Unit Testing
Visual Studio 2017, Azure, Amazon Web Services (AWS)
SQL Stored Procedures, Microsoft SQL Server, Database Modeling, Database Performance, JSON, SQL Server Integration Services (SSIS), Database Administration (DBA), SQL Server 2017, SQL Server Reporting Services (SSRS), Databases, Azure SQL, SQL Performance, Dynamic SQL, SQL Server 2016
ETL Development, Query Optimization, Requirements & Specifications, Data Warehouse Design, Data Manipulation, Data Warehousing, Data Engineering, CSV File Processing, Automation Tools, Data Analysis, SQL Server 2019, Indexing, CI/CD Pipelines
.NET, ASP.NET MVC, Windows PowerShell
Web API 2
Bachelor of Science Degree in Software Engineering
University of Advancing Technology - Tempe, AZ, USA