Data Flow Optimisation: Automating and Synchronising Systems with Apache Airflow and dbt

Sandaya, a major player in the outdoor hospitality industry, undertook the modernisation of its Property Management System (PMS), the core tool used to manage its operations.

The main challenge was to redefine all flows connecting the PMS to the Accounting, CRM and Business Intelligence systems in order to ensure a migration with no loss of information.

We had to carry out a complete and compliant redesign of the data flows (ISO, meaning a migration with an identical functional scope) while reducing transfer errors.

At the same time, we needed to provide clear monitoring interfaces, ensure data traceability, centralise flow management and create a robust architecture for simplified maintenance.

The solution combines Apache Airflow and dbt to orchestrate and transform data flows between the PMS, CRM, Accounting and BI systems.

• Apache Airflow provides powerful orchestration and real-time monitoring of each flow, ensuring full visibility across information system exchanges.
• dbt (data build tool) makes it possible to model and document data according to industrial-grade standards, transforming Shadow Data into structured, reliable assets.

The “Data as Code” approach applies DevOps best practices to industrialise and improve the reliability of data flows. Unlike “black box” SaaS solutions, combining Open Source technologies with on-premise hosting gives Sandaya full sovereignty, control over its costs and a fully extensible, tailor-made architecture.

System quality is secured through a rigorous development cycle: Git versioning, systematic code reviews and automated testing. This methodology provides full traceability and enables each update to be deployed to production with confidence.

The project began with a strategic POC to validate the technical feasibility of the target architecture. This stage confirmed the relevance of combining the Open Source solutions Apache Airflow and dbt before rolling them out at scale.

• Inventory and reverse engineering: In-depth work was carried out through business workshops to identify Shadow Data scattered across the information system. Together with Sandaya, we performed comprehensive reverse engineering of the existing flows to precisely map the fields, calculation rules and mapping required from the new PMS data.
• Medallion architecture: Development was structured around a layered architecture (Bronze, Silver, Gold). This approach makes it possible to consolidate the loading of raw data, which is then transformed using dbt to feed several outbound flows consistently.
• The pre-production challenge: Validation was a major challenge, as some target systems, such as Accounting, were difficult to simulate in a test environment. We had to apply additional rigour to validate behaviour under normal operating conditions despite the absence of certain mirror environments.
• Deployment and stabilisation: The production rollout included a critical stabilisation phase. A technical and functional squad was mobilised to handle edge cases and failures associated with real-world data volumes.
• Resilience and observability: Apache Airflow’s advanced features were instrumental in monitoring flows and ensuring their resilience. dbt’s flexibility made it possible to quickly adjust business rules based on field feedback, enabling rapid stabilisation and compliance with SLAs.

In conclusion, this iterative deployment transformed an architecture that was previously difficult to control into a managed system, with technical control of the stabilisation phase proving key to Sandaya’s operational success.

The project transformed Sandaya’s data into a manageable industrial asset. From an approach fragmented and siloed by business function, we converged towards a centralised data layer serving as the single foundation for the entire information system.

• Quantitative indicators: Daily synchronisation of 35 sources, more than 3,000 API calls per day and 50 million rows processed annually with maximum reliability.
• Operational gains: Elimination of “Shadow Data” flows and manual tasks (CSV). The system can now be updated dynamically, with some flows refreshed as frequently as every 10 minutes.
• Sovereignty: Control over infrastructure costs and data assets through a self-hosted solution on a sovereign cloud, ensuring strong strategic autonomy and avoiding vendor lock-in.

Next steps: Building on this new centralised foundation, the ambition is to extend the data pipeline to all business functions (Marketing, HR, etc.). This foundation is becoming the company’s single data repository, directly queryable by BI tools to provide a 360° view of the business.

This centralisation provides the foundation for future data governance initiatives. The objective is now to ensure clean, sustainable and shared information, thereby initiating a genuine long-term transformation of Sandaya’s data culture.