Facilitating the Adoption of the Data Cloud
AJ Bell
Providing the expert knowledge in building a cloud-based data warehouse.
AJ Bell is one of the largest investment platforms in the United Kingdom, entrusted with assets totalling £72.8 billion. The company aims to make investing more accessible and currently works with nearly 400,000 customers.
What we did
The Challenge
AJ Bell required a new, cloud-based data warehouse solution to replace their existing on-prem SQL Server system.
They had identified Snowflake and dbt as key technologies to be used and were looking for a partner organisation to assist with development, primarily focusing on the data transformation requirements.
The client had an internal data team but was lacking expert knowledge in data warehousing on the cloud and the tools they had selected.
Adopting the data cloud
- Assist with the re-refinement of the proposed solution architecture;
- Develop dbt jobs to transform data loaded into Snowflake staging tables to a target star schema to satisfy both business & transformation requirements;
- Deploy dbt jobs on AWS ECS over Fargate;
Script and automate the build and release process including Snowflake configuration; - Perform skills transfer to client staff.
Embedded team for immediate action
We worked alongside the client's practitioners in an embedded team, offering ultimate transparency and ensure continuity.
Snowflake accounts
We used multiple accounts within a Snowflake organisation as it allowed us to isolate the development/test and production environments. This approach provided for great control over what was being released to production.Additionally, it presented a neat way to track costs per environment, monitor resources and make adjustments.
We held a number of run-through sessions with the client to discuss various Snowflake functionalities. Infinite Lambda’s experts recommended using the Flows tool to automatically set up all Snowflake accounts, the organisation, the agreed layers, the roles and the permissions.
Data modelling
Together with the client, we decided to use dbt to build intermediate and final data models. The tool would allow for storing all business logic in dbt scripts with version control and contribute to a CI/CD pipeline that ensures production environment reliability.
Data ingestion
We explored different replication options for the Snowflake data ingestion and the client took our recommendation to go with TimeKey. In terms of orchestration, we opted for AWS managed Airflow, which is a gold standard in such cases.
Data snapshots
The client’s internal finance team, who would be the main users of our solution, had specifically requested that we explored different snapshot options as well. They needed this functionality for auditing purposes and due to regulatory and compliance reasons had to keep monthly snapshots of the data for 24 months.
Here, we went with Snowflake Clone because it would provide the instant cloning capability that the client needed without relying on additional infrastructure as everything would be done within Snowflake itself. This was also a reasonable decision from a financial point of view because source updating would not change the target, while charges would only apply if there were data changes.
Knowledge transfer
The client prioritised the ability to move forward with the work after project completion, which would require perfect transparency and continuous knowledge sharing.
To this end, Infinite Lambda worked alongside AJ Bell’s engineers in an embedded team. To make sure client staff were familiar with the technologies and the solution, we would hold weekly demos, comprehensive workshops, shadowing and pairing practices as well as ad hoc knowledge sharing sessions.
The technology we useD
Let’s walk the walk together
We are a generation of engineers and technologists who are passionate about transforming organisations with digital-age solutions and seeing them thrive on the cloud.
see Related Stories
We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.
We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.