Solution

Tool Selection

As part of the move to the cloud-based environment, all components of the BI value chain were represented by new tools:

Processes and architecture

The new cloud architecture basically consists of thelayers comprising abstraction, integration and presentation.

• The abstraction layer extracts newly delivered source data from Databricks via external tables in Snowflake and stores them in a persistent staging area.
• The integration layer makes the cleaned data available in a raw vault, including historization and versioning. A business vault is also available for business-oriented interpretations of the data.
• The presentation layer serves as the user interface for the specialist departments. Here, aggregations are fetched from the raw vault and business vault in accordance with the business view, and made available in data marts. This layer is also the access layer for the reporting applications.

Despite the resultant tool interface for the abstraction layer, ProSiebenSat.1 opted for a multi-vendor approach: Databricks for the data lake and Snowflake for the DWH.

Snowflake convinces as a DWH database with valuable features: Parallel loading of data sources and product ETL routes as well as simultaneous deployment and testing are trouble-free, thanks to transactional control and massive parallel processing (MPP). Connections to the data lake (via external tables) and to various reporting tools (via provided connectors) can also be easily implemented. The clear "star" among the Snowflake features for ProsiebenSat.1 is zero-copy cloning, which is used daily for the development process (a more detailed explanation of this feature follows in the next section).

The core DWH automation tool used here is Datavault Builder on Snowflake. With the help of process sequences (DAGs) configured in Airflow, ProSiebenSat.1 achieves very fluent and efficient orchestration, from extraction from Databricks to presentation in the data marts.

CI/CD pipelines in Gitlab are used for deployments and daily testing with Soda. The logs produced by all components are collected and processed centrally using Datadog.

In a first project phase, this architecture was tested and fine-tuned by an early adopter team. Via a so-called hackathon, all other teams then began to simultaneously test the development in this environment, without any resultant bottlenecks or conflicts. For ProSiebenSat.1, this combination of tools and process design has proven to be optimal in meeting requirements for CI/CD, code versioning and testing.

Of particular note is the compatibility between Datavault Builder and Snowflake: The metadata generated by Datavault Builder are stored in Snowflake alongside business data, i.e. Datavault Builder does not use its own repository. Although Snowflake is a query-optimized database, it can easily handle the resultant micro-transactions (> 500k transactions per day). The true strength of this tool combination comes into play via sandboxing during the development process. Sandboxing is explained in more detail in the next chapter.

Agile Sandboxing With Datavault Builder and Snowflake

ProSiebenSat.1'sdevelopment philosophy has long focused on the use of modern technologies andprocesses: Developmentin sandboxes with consistent Scrum and CI/CD.

Created during the sandboxing process is an AWS instance equipped with Datavault Builder and Airflow as well as its own warehouse in Snowflake. A feature branch is created too. Snowflake copies the data states of a selected environment along with the metadata required by Datavault Builder. The Datavault Builder's architecture, meant to manage consistently without its own repository, is entirely suited to this.

With the introduction of Snowflake, this copying process can now be implemented much more efficiently. Decisive here is the zero-copy cloning feature. It allows exact replicas of databases to be created within a very short time, without copying and redundant storage of data.

The following diagram illustrates such a sandboxing and development process. The DEV, TEST, and PROD environments are similarly configured according to the architecture described in the last chapter (Snowflake database, Datavault Builder instance, and Airflow instance). The Git branch "master" contains the metadata and code on which the three environments are based. During creation of the development environment, a sandbox instance is generated and, according to the developer's choice, populated with the data from one of the three environments (PROD in this example). At the same time, a feature branch of "master" is created. Changes made to the sandbox in the development phase are synchronized in the feature branch. Once the development phase has been accomplished, the feature branch is merged into the "master", thus triggering a CI/CD pipeline which deploys the development to DEV. Once this has been done successfully, developers can also deploy to TEST and then to PROD.

This sandboxing concept enables developers to work on an exact reproduction of a static environment, including all data, and thus quickly test new developments with existent data. This not only improves the efficiency of the development and testing processes, but also increases the impact rate in terms of CI/CD and supports agile operations with Scrum.