TokioMarineKiln (TMK), an international insurer operating in the Lloyd’s insurance market, engaged Digital Endeavours to unlock their tier 1 application data for analytics and reporting in Snowflake whilst maintaining existing SQL Server operations.
Digital Endeavours delivered a greenfield change data capture solution, transforming and delivering near real-time data from SQL Server to Snowflake. The project implemented AWS best practices from the outset, establishing automated, secure infrastructure patterns.
The greenfield project enabled Digital Endeavours to establish secure, automated infrastructure patterns from inception. Infrastructure-as-code was delivered through automated GitLab CI pipelines, implementing the architectural design with built-in validation and testing. Technical collaboration with the CDC product vendor validated configuration approaches, whilst InfoSec non-functional requirements were satisfied through AWS best practice implementation.
Operational SQL Server databases required protection from analytics query load whilst enabling near real-time reporting. The solution deployed containerised CDC services on Auto Scaling Groups, capturing SQL Server changes and transforming data for Snowflake consumption whilst maintaining complete separation from source systems. This enabled near real-time data delivery for business intelligence teams whilst ensuring no performance impact on operational databases.
Infrastructure automation implemented immutable infrastructure principles with comprehensive validation. AMI builds using Packer and Ansible were triggered frequently for security patching, with each build validated through automated GitLab CI pipelines. These pipelines performed security scanning and compliance checks before AMIs were approved for deployment. Terraform then orchestrated infrastructure deployment, automatically selecting the latest validated AMI. This approach ensured security patches were applied rapidly whilst maintaining deployment consistency.
Comprehensive security controls were implemented from inception. Refined IAM policies enforced least-privilege access, KMS encryption protected data at rest in S3, and security groups restricted network access to essential communication paths. This security-first approach satisfied InfoSec non-functional requirements whilst establishing a secure foundation for the CDC pipeline.
Data transformation processing times presented a challenge in managing business expectations. Initial analysis revealed transformation would require several hours due to data volume, with the CDC product providing no progress indicators during processing. Clear communication with project management established realistic timelines, enabling accurate planning and appropriate stakeholder expectation management.
Operational handover to TMK’s BAU teams required thorough knowledge transfer. Comprehensive documentation covered infrastructure architecture, CDC configuration, operational procedures, and troubleshooting guides, enabling independent platform maintenance and issue resolution.
Results
The solution successfully delivered tier 1 application data to Snowflake, enabling advanced analytics and reporting capabilities. Near real-time change data capture ensured analytics worked with current data whilst maintaining complete separation from operational systems. The automated infrastructure reduced operational overhead through self-healing capabilities and streamlined security patching processes.
The technical delivery established the foundation for enhanced analytics, though realising the full business value required a cultural shift in how the business approached data analysis. Moving from established SQL Server workflows to Snowflake-based analytics represented a change in working practices that would develop as familiarity with the platform’s capabilities grew.
Key Technologies
CI/CD & IaC: GitLab CI, Terraform, Ansible, Packer
Infrastructure: AWS (EC2, Auto Scaling Groups, S3, KMS)
Data Platforms: SQL Server, Snowflake
Security: OpenSSL, AWS KMS
