After evaluating several options, CapTech selected AWS Glue as the best method for Extract, Load, Transform (ELT) and capturing changes as they occur. CapTech successfully migrated dozens of functions that were previously encapsulated within the SQL Server/Mulesoft environment into the AWS Cloud implementation. Additionally, CapTech utilized canary testing to release a few code changes to a small number of end users at a time to test for accuracy. This approach reduces customer impact and risk ahead of a full-scale deployment into production.
The selected DevOps approach utilized TeamCity to generate NuGet packages, which would then be deployed to various AWS environments via Octopus worker agents. Within the D&A project space, there were two primary vectors for deployments:
- Scripts/infrastructure for AWS Glue jobs and workflow orchestration
- Data Definition Language (DDL) and Data Manipulation Language (DML) for updates to the various RDS Postgres databases
The AWS Glue deployments utilized Terraform for infrastructure deployments, as well as S3 agents to update Python script files. The DDL/DML deployments utilized dbUp, which provides version management and other safety features which prevent unexpected changes to the underlying data structures.
Performance and reliability of these new processes have been within expected parameters, with additional scalability now possible via the decoupled cloud services-based approach. As an example, the team saw success in scaling up Glue jobs when worker memory constraints were encountered. This approach allows the independent broker-dealer to seamlessly sunset the SQL server and transition customers to the new system without any awareness that a change has taken place.
