Charles Green, Data Warehouse Design Developer in Camden, United States
Charles Green

Data Warehouse Design Developer in Camden, United States

Member since June 22, 2018
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.
Charles is now available for hire

Portfolio

  • eMoney
    SQL, SQL Server 2017, ETL, Data Warehouse Design, Database Performance...
  • Mingle Health
    SQL Server 2019, Business Intelligence (BI), Indexing, SQL Performance...
  • Thorpe Abbotts Capital
    SQL Server 2019, Databases, Database Design, Data Warehouse Design...

Experience

Location

Camden, United States

Availability

Part-time

Preferred Environment

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.

Employment

  • Data Consultant

    2021 - 2022
    eMoney
    • 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.
    Technologies: SQL, SQL Server 2017, ETL, Data Warehouse Design, Database Performance, Dynamic SQL, C#, Database Design, SQL Stored Procedures, Microsoft SQL Server, Agile Software Development, .NET, Data Manipulation, Query Optimization, ETL Development, CSV File Processing, C#.NET, Indexing, SQL Performance, CI/CD Pipelines, T-SQL, Visual Studio 2017, Data Engineering
  • Senior Database Engineer

    2019 - 2022
    Mingle Health
    • 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.
    Technologies: SQL Server 2019, Business Intelligence (BI), Indexing, SQL Performance, Database Performance, Query Plan, ETL, Automation, DevOps, Agile, Database Administration (DBA), Database Design, SQL Stored Procedures, Microsoft SQL Server, Agile Software Development, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, CSV File Processing, C#.NET, Dynamic SQL, Windows PowerShell, T-SQL, Visual Studio 2017, SQL, Data Engineering, Data Analysis
  • Data Solution Architect

    2018 - 2022
    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.
    Technologies: SQL Server 2019, Databases, Database Design, Data Warehouse Design, Data Warehousing, ETL, Python, DevOps, Azure SQL, Automation, SQL Server Integration Services (SSIS), SQL Stored Procedures, Microsoft SQL Server, Agile Software Development, Azure, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, ETL Development, CSV File Processing, Database Administration (DBA), Database Performance, C#.NET, Indexing, SQL Performance, Dynamic SQL, Windows PowerShell, CI/CD Pipelines, T-SQL, Visual Studio 2017, SQL, Data Engineering, Data Analysis
  • Software Developer

    2014 - 2017
    Capsaicin
    • 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.
    Technologies: Amazon Web Services (AWS), Microsoft Power BI, HTML5, Azure, AWS, C#, Microsoft SQL Server, Visual Studio, Databases, Data Warehouse Design, Data Warehousing, Database Performance, ETL, Dimensional Modeling, Database Modeling, DevOps, Dynamic SQL, SQL Stored Procedures, Agile Software Development, Automation, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, ETL Development, CSV File Processing, Database Administration (DBA), C#.NET, Indexing, SQL Performance, Windows PowerShell, CI/CD Pipelines, T-SQL, Visual Studio 2017, SQL, Data Engineering, Data Analysis
  • Senior Software Engineer

    2011 - 2013
    Microsoft
    • 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.
    Technologies: C#, Database Design, ETL, Database Modeling, Database Performance, SQL Server 2016, Automation, DevOps, SQL Stored Procedures, Microsoft SQL Server, Agile Software Development, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, ETL Development, CSV File Processing, Database Administration (DBA), C#.NET, Indexing, SQL Performance, Dynamic SQL, Windows PowerShell, CI/CD Pipelines, T-SQL, Visual Studio 2017, SQL, Data Engineering, Data Analysis
  • Senior Software Engineer

    2010 - 2011
    Huron Group
    • 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.
    Technologies: C#, SSRS, Microsoft SQL Server, ETL, Database Design, SQL Performance, Database Modeling, SQL Stored Procedures, Agile Software Development, Automation, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, CSV File Processing, Database Administration (DBA), Database Performance, C#.NET, Indexing, T-SQL, Visual Studio 2017, SQL, Data Engineering
  • Senior Software Engineer

    2009 - 2010
    Apriva
    • 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.
    Technologies: C#, Microsoft SQL Server, SQL Stored Procedures, Agile Software Development, SQL Server Integration Services (SSIS), Automation, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, CSV File Processing, Database Administration (DBA), Database Performance, C#.NET, Indexing, SQL Performance, T-SQL, Visual Studio 2017, SQL
  • Software Engineer II

    2007 - 2009
    Quest Software
    • 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.
    Technologies: ETL, SQL Server Integration Services (SSIS), SSRS, SSAS, C#, Microsoft SQL Server, SQL Stored Procedures, Agile Software Development, Automation, Test-driven Development (TDD), .NET, Data Manipulation, Query Optimization, CSV File Processing, Database Administration (DBA), Database Performance, Indexing, SQL Performance, T-SQL, Visual Studio 2017, SQL

Experience

  • Automated Warehouse Creation

    I architected and designed a software solution to automate data warehouse creation that reduced daily workload by 40%. The configuration of the warehouse was exposed via an ASP.NET MVC web application that enabled project managers and stakeholders to customize their client-specific data warehouse.

    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.

  • Performance Optimization

    I experienced a period of unexpected company growth, during which a surge of new clients increased ETL processing times from minutes to hours. In one quarter, we experienced 24 months of projected data growth.

    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.

Skills

  • Languages

    T-SQL, SQL, XML, C#, HTML5, C#.NET, Python
  • Tools

    Azure DevOps Services, Microsoft Power BI, SSRS, SSAS, Git, Visual Studio, Query Plan
  • Paradigms

    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
  • Platforms

    Visual Studio 2017, Azure, Amazon Web Services (AWS)
  • Storage

    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
  • Other

    ETL Development, Query Optimization, Requirements & Specifications, Data Warehouse Design, Data Manipulation, Data Warehousing, Data Engineering, CSV File Processing, Automation Tools, Data Analysis, AWS, SQL Server 2019, Indexing, CI/CD Pipelines
  • Frameworks

    .NET, ASP.NET MVC, Windows PowerShell
  • Libraries/APIs

    Web API 2

Education

  • Bachelor of Science Degree in Software Engineering
    2004 - 2007
    University of Advancing Technology - Tempe, AZ, USA

To view more profiles

Join Toptal
Share it with others