...

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

Snowflake migration and setup
dbt data modelling
Regular snapshot capabilities

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.

The task

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.
The solution

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

don’t wait

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.

World Health Organisation
Carbon Analytics Platform for a Vehicle Fleet
The Francis Crick Institute
Scalable Global Trusted Research Environment
Railsr
Robust Data Platform for a Digital Bank
Oddbox
Building a Single Source of Truth
Moshi
Integrated Data Analytics Platform
The Halo Trust
Using Artificial Intelligence to Find the Debris of War

We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.

BUILDING A DATA VAULT WITH dbt CLOUD

Thursday, 1 June, 2023

9 am EST | 2 pm BST