Industrialising a multinational company’s data with dbt and Airflow.

Murata is a Japanese group specialising in the design and production of electronic components. Among other activities, the group is expanding through a subsidiary based in Caen. This development has led to increased data-related needs for the subsidiary.

Built progressively as use cases evolved, the data architecture now needed to be standardised and industrialised to meet new requirements. The challenge was to gain better control over existing business logic, standardise processing and establish a sufficiently robust foundation to handle higher data volumes and new data flows.

The project also involves reverse engineering to reproduce existing business rules within a more readable, maintainable and scalable architecture. All of this work must also comply with the constraints of an on-premise environment in order to maintain full control over data security.

The requirement for an on-premise environment led the project towards Open Source technologies, allowing full control over the code, architecture and data.

• a Data Lake using Apache Iceberg to store raw data
• a PostgreSQL Data Warehouse to store transformed data
• Apache Airflow to orchestrate processing
• dbt to model and transform data

dbt to structure transformations

Data goes through several transformation layers:

Sources → Inbound → Staging → Datamart, corresponding to a medallion architecture. Data is progressively transferred, validated, transformed and then made available to business users.

This logic is validated through automated unit and integration tests.

Airflow to organise data flows

Apache Airflow orchestrates processing execution and standardises the organisation of the various pipelines.

A common structure can be adapted to different frequencies, business rules or retry strategies. Airflow also provides an overall view of system status and has already demonstrated its ability to manage several thousand data flows simultaneously.

“Data as Code” to optimise maintenance

All the solutions used define their processes as code and text files. Data flows and transformations can therefore be structured as version-controlled modular components, making them easier to maintain, evolve and reuse.

The project was organised into two phases:

20-day POC to test and validate the technical choices:

• Building the development environment
• Designing the core Data Lake + PostgreSQL Data Warehouse architecture
• Developing and industrialising a pilot pipeline with dbt + Airflow, from data collection through to qualified business data
• Validating the technical choices and proposed solution

Ongoing support – 20 days/month:

• Scaling the new architecture by integrating new, more complex data flows
• Improving pipeline reliability by automating tests in CI/CD
• Rigorous validation of data quality in the new architecture to avoid any impact on end users

Industrialisation and modernisation of existing data flows

• Design and validation of a hybrid Data Lake + Data Warehouse architecture
• Organisation of processing according to a standard pipeline: Sources → Inbound → Staging → Datamart
• Consolidation of processes within a single codebase based on a unified technical stack and modular, reusable components
• Standardisation of the process for integrating new data flows into the new platform

What are the next steps?

• Integrating additional data flows into the new architecture
• Industrialising the data visualisation layer
• Strengthening quality processes by further developing CI/CD practices
• Evaluating and optimising performance at each stage