AWS Datalake leveraging dbt – VRT

We helped with

AWS
Datalake
dbt
Business Intelligence
Metabase

VRT is the Flemish public radio and television broadcaster. VRT wants to strengthen the Flemish society by informing, inspiring, and connecting. As a radio and television broadcaster, they focus on informative and cultural programs, as well as sports, drama and entertainment.

Some of VRT’s well-known brands are:

  • VRT MAX, the on-demand video and audio offer
  • VRT NWS, the place where consumers can follow up on the latest news
  • Sporza for the latest news in sports
  • Radio stations MNM, Studio Brussel, etc.

The briefing

VRT requested support for the development of a data warehouse solution to make analytics more performant, transparent, actionable and user-friendly.

VRT had been experimenting with dbt, a transformation framework that enables analysts and engineers collaborate with their shared knowledge of SQL to deploy analytics code. They were looking for Business Intelligence experts to strengthen VRT’s Data & Intelligence team. Their focus was to set up a datawarehouse solution with dbt and to develop dashboards in Metabase to translate data into insights.

Main goals of Business Intelligence experts:

  • The development of a Data Warehouse (dbt)
  • Development of an analytical environment (data-layer)
  • Creating self-service dashboards in Metabase with executive summaries
  • Strategic support based on insights from dashboards, to drive actions

Our solution

Our BI experts created data transformation pipelines to make data stored in data lakes more accessible for analytical purposes. In addition, as part of VRT’s data-driven team, insights were extracted from this data and communicated to relevant stakeholders.

To implement the solution, our BI experts followed best practices as recommended by the dbt Labs team. These best practices were adjusted to the context, data landscape and requirements of VRT resulting in the following four-layer architecture:

  • The staging layer used to perform simple transformations to accommodate various naming conventions and ensure valid data types
  • A data preparation layer where transformations and business logic are developed and applied as intermediate steps towards curated tables
  • A conformed layer where facts and dimensions (in line with the Kimball modelling strategy) are created that can be exposed to eventual data consumers like Metabase
  • A marts layer containing different views of data and different aggregations for specific use cases.

Thanks to dbt’s data lineage capabilities, all data transformations and data flows are made insightful. The resulting data structure allows for more simplified and robust dashboard design in Metabase.

See below for an example data lineage

Different technologies are used to successfully deliver this data analysis solution.

Dbt is used for data transformations, Bitbucket is used for version control, Airflow for data orchestration, and Cloudwatch for monitoring the data transformation flow.

AWS Athena is being leveraged as underlying query serverless service. In addition Metabase is used for dashboarding.

All these tools and technologies required further development from Cloubis’ team in order to offer actionable insights from data.

The outcome

Cloubis was able to offer an end-to-end data solution to VRT by setting up dbt models in a layered structure that can be easily queried by front-end tools and other data consumers. VRT can now rely on a robust and performant data warehouse solution, as well as documented best practices, templates and guidelines.

This solution results in a lower cost and better performance by aligning data partitioning and data modelling in a way that data is queried for business use cases.

In addition, by setting up different dashboards in Metabase, the customer VRT is now able to deliver automated answers for common use cases. Furthermore, multiple insights were also distributed actively throughout the organization, stimulating an action-oriented data-driven mindset.

Does your project need our expertise?