From scattered data to decisions you can trust

Data warehouses, data lakes and medallion architecture: build a data foundation that moves your organisation forward.

Your sales team checks the CRM. Finance relies on the ERP. Operations maintains its own spreadsheet. At the next meeting, three different figures appear for the same question. Before you can make a decision, you have to work out which number is right.

Sound familiar? The starting point is understanding how your systems, your data and the agreements between people fit together. A data warehouse, data lake or lakehouse can help. The right choice depends on what your organisation needs to achieve.

Start with the decision you want to improve

Do you want to spot orders that may be delayed? Replenish stock more effectively? Understand margin by customer or product? Make that question specific first. Then identify the data you need, where it originates and how current it needs to be.

A daily update may be enough for a management report. A process that must respond immediately to a fault needs something different. That distinction shapes the architecture, cost and maintenance. Technology should follow your operational needs.

Data warehouse, data lake or lakehouse?

These terms describe different approaches to making data available. They can also work alongside each other.

The boundaries between platforms are becoming less distinct. Look at your workloads, your team and the total cost of running the solution. A focused reporting need does not automatically call for an extensive lakehouse. For organisations combining different kinds of data and analysis, it may be a suitable direction.

ETL and ELT: how does your data reach the right place?

A data warehouse or lakehouse is only part of the story. You also need to retrieve, process and make the data available. This is where ETL and ELT come in.

ETL stands for Extract, Transform, Load: you retrieve data from its source, transform it in a separate processing step and load the result into the target system. It is useful when you want to clean data, exclude fields before loading or reduce the processing load on the target.

ELT stands for Extract, Load, Transform: you retrieve the data, load it into the target platform first and transform it there using the available computing resources. It is useful when you want to reuse source data for different analyses and adapt transformations later. The platform needs sufficient capacity to do that.

What does that mean for your operations?

Consider an order export containing contact details. If your analysis does not need a phone number, you can exclude that field before loading. If you later want to calculate delivery reliability using a new definition, retaining permitted source data makes reprocessing possible. You can therefore combine checks before loading with transformations inside the platform.

ELT often fits medallion architecture: load into bronze, then work towards silver and gold. The terms describe different things, though. Medallion describes the layers; ETL and ELT describe where and in what order data is processed. A modern data warehouse can use ELT too.

Base the choice on access rules, data volume, required freshness, costs and your team's skills. ELT does not mean everyone can access raw data, or that everything should be kept indefinitely. And ETL or ELT alone does not determine whether processing happens in batches or continuously.

Medallion architecture: from source data to useful information

Medallion architecture typically organises data processing into three layers. It is a design pattern, rather than a product.

Bronze: incoming source data, with its origin and ingestion time, so you can trace what arrived.

Silver: checked and aligned data, including consistent dates, matched customer identifiers and handled duplicates.

Gold: data prepared for a specific use, such as a report, KPI or analysis.

The layers make the journey from source to result easier to understand. You still need to design the checks and the handling of errors. Creating three layers does not, by itself, make data trustworthy.

A practical example: which orders might arrive late?

Imagine combining sales orders from your ERP with inventory records and delivery updates. This is an illustrative example.

Each source arrives in bronze with a timestamp. In silver, you align product identifiers, check for missing order references and distinguish planned delivery dates from confirmed ones. Incomplete records receive a visible error status and a process for follow-up.

In gold, you create a view of orders at increased risk of delay. You also agree what “late” means: against the customer promise, the internal schedule or the latest confirmation? The view shows when the data was last updated.

The planner now has information they can act on. An unexpected figure can still be traced back to its source.

Data governance: who decides what data means?

Data governance establishes responsibilities and agreements. Who owns the definition of an active customer? Who can change a margin calculation? Who decides which roles can access which data?

Keep the starting point practical: assign an owner for your first use case, define the key terms and agree how quality issues will be handled. A data catalogue helps people find datasets and understand their meaning. Data lineage shows how information changes as it moves through processing. These tools support the agreements; people still need to put them into practice.

Governance applies across all layers. Source data also needs clear access rules, retention periods and deletion arrangements.

Data management: keeping it working every day

Data management puts those agreements into practice. It covers integration, storage, security, quality checks and maintenance. It also includes master data management: ensuring that core records about customers, products and suppliers are identified and managed consistently.

Make quality measurable. How many orders are missing a customer reference? How long has it been since the last successful update? What happens when a source system changes a field? Define who receives an alert and how recovery works. A dashboard that quietly goes out of date can quickly undermine trust.

A foundation for AI, with room to change

AI applications also benefit from data with clear meaning, provenance and access rights. A model using incomplete or misinterpreted data can sound convincing while reaching the wrong conclusion. Data quality never replaces evaluation of the AI application itself.

When choosing a platform, consider how easily the solution can be handed over or changed. Are definitions and transformations documented? Can your team run it? Can you export the data in usable formats? Open formats and clear interfaces can reduce dependencies; the complete solution determines how much freedom you retain.

Think. Build. Maintain.

At DX-Solutions, we look at processes, systems and data together. Through Think, we clarify the challenge and the choices. Through Build, we deliver the solution and connect the necessary systems. Through Maintain, we support its ongoing maintenance and improvement.

A good first step? Choose one decision that currently takes too much investigation. Bring the sources and definitions together, build a useful first version and test it with the people who will use it. Then expand where it makes sense.

Reclaim your IT freedom.