We achieve this with a clear layering and naming combined with powerful patterns and best practices implemented by easy to use dbt macros collected in a PDP dbt package, all embedded in a modern, DataOps way of working.

The major features of this architecture are:

• three layers with clearly defined tasks and boundaries
• creation of an immutable store of source data facts in the data storage layer, built with a very high level of automation
• implementation of the desired Business Concepts in the refined layer, through application of of the business rules and master data
• delivery of the refined information through data marts, that create a dedicated interface and encapsulate the specific requirements of each use cases
• allow an easy evolution from a single project to a data mesh connecting multiple projects, each implementing only the needed layers and communicating through integration data marts

Let’s dig a bit deeper in the three layers.

Data storage layer

The Data storage layer builds the immutable storage of the source data that powers our data platform.

This is done through four steps that are all highly automatized:

• the ingestion of the raw data in the Landing Tables
• the adaptation of the data in the Staging view
• the storage of the source data in the History tables
• the access to the versioned data in the SCD views

The ingestion, storage and serving of source data is a solved problem that just needs the application of some metadata, like primary key and desired column names, to produce rock solid pipelines.

Looking deeper in this layer the Landing Tables have the role to receive the raw source data loaded “as is”, eventually applying the desired data types, but only if data is extremely reliable.

The staging models adapt and make the data easier to use, as an example by casting to the correct data type, including parsing numbers, dates and timestamps or converting to UTC time zone, with great care to not alter the semantic nor applying any business rule to the data.

The role of the History tables is to recognize and store as a new version every variation in the incoming source data, building a rock solid foundation that has it all: the good, the bad and the ugly.

The version/SCD views provide a uniform interface based on the loading process metadata producing many useful attributes that makes the downstream use of the history simpler, both in the case we want to work with the full history or only with the current versions.

Data refinement layer

In the Refined layer the historical data is transformed into complete and usable information by applying the transformations, including master data and business rules, needed to build the Business Concepts of interest to the organization.

The modular design and domain driven approach that we suggest allows to implement long lasting business concepts, with clearly named models, organized in an easy-to-understand lineage, where each model clearly expose its purpose thanks to its name and position.

This layer, by its own nature, relies less on pre-designed automation and more on best practices and patterns.

Thanks to idempotent models that rely on previous runs only for performance, but not for correctness, this layer can be recreated at any time from the historical data, making the evolution, maintenance and code fixes much simpler than in traditional DWH where the data is stored after transformation.

This makes the projects simpler to manage and more accessible to all the stakeholders.

Data delivery layer

The Data Delivery layer finalizes and expose the curated data sets for the downstream clients that implement the desired use cases.

We organize this layer in data marts that have the goal to deliver the data in the best format and the best data model for each client.

Common examples are facts and dimensions according to Kimball star schemas, wide tables for reporting or AI/ML workloads, data exported in files in the desired formats or operational style tables to serve interactive APIs.

In this layer we have (usually few) transformations that are specific to the data mart, in contrast with those general in nature that belong to the refined layer.

The final models in data marts expose only the needed data for the use case with the desired names and types.

The final models are the public interfaces of your platform and must be managed like any other public APIs, with explicit contracts, versioning and governed access to the data. Keeping interfaces small is another software engineering best practice.