Leveraging the Modern Data Stack: dbt, Snowflake, Azure and Power BI

OVERVIEW

The client is a prominent full-service financial institution headquartered in Sydney, Australia, that offers a comprehensive suite of banking products and services tailored to the needs of individuals, businesses and corporations. To execute their innovation objectives, the bank sought to elevate the maturity of its data management practices while maintaining its commitment to security and regulatory compliance.

Data Army was engaged to design and implement a cloud-native Data Warehouse and Business Intelligence (BI) solution whilst implementing best-practice data operations. By leveraging a modern data stack using dbt, Snowflake, and Microsoft Azure, the new solution delivered by Data Army ensures reliable, scalable and secure data capabilities that serves the Bank’s ever increasing data-driven decision making.

Business Objectives

The client outlined the following business objectives at the start of the project:

• Centralise the Bank’s data servicing business intelligence reporting,
• Ensure all data security and compliance responsibilities are met,
• Be custom built for the cloud, not a lift and shift solution,
• Be able to scale and grow with the bank,
• Leverage low-code development practices,
• Be maintainable by a modest, but talented data team, and
• Establish a platform for future data analytics including artificial intelligence (AI) and machine learning (ML).

PROJECT OUTCOMES

• Seamless integration with plug-and-play marketplace datasets, such as regularly maintained Australian addresses, enhancing data quality and utility.
• Enhanced security and availability through features like disaster recovery, and network policy control, with adherence to SOC 2 Type II, RAP – Protected (P), and PCI DSS standards.
• Reduced total cost of ownership with minimal implementation costs using consumption-based pricing, lowering infrastructure overhead.

THE DATA ARMY SOLUTION

The Data Warehouse architecture is built on these key principles:

1. Having a cloud-first approach, preferring Software as a Service (SaaS) solutions over self-managed approaches,
2. Utilising SQL as the primary programming language, and
3. Minimising manual setup by leveraging Infrastructure as Code (IaC).

These principles streamline the implementation and management of the data warehouse.

The solution employs dbt for data warehouse transformations due to its ability to apply software engineering best practices to data engineering. A custom generator was created to generate scripts for data modelling in dbt from a single configuration file.

This allows management of the Snowflake schemas to be easily automated, so the team can readily develop, execute, test, and document data warehouse transformations using dbt Cloud.

Once new data lands in the data lake, Azure Data Factory initiates calls to dbt to start the transformations. To maintain the private and secure connectivity between the services, Azure PrivateLink was implemented to protect the data infrastructure from external exposure.