What is a Data Warehouse Diagram?

A data warehouse diagram is a visual representation of a data warehouse architecture that demonstrates how the data flows from source systems, through various layers, to analytics tools. Data warehouse diagrams show how components interact, such as the extraction, transformation, and loading (ETL) processes, storage systems, data marts, and analytics and AI applications. Data warehouse diagrams help organizations design, track, and demonstrate their data warehouse architecture to technical and non-technical audiences.

Data warehouse diagrams map out the structure, operations, and end uses of a data warehouse. Any team that interacts with the warehouse, from engineering to business decision makers, can use the diagram to better understand how data flows through the organization.

Architecture planning and design

Data warehouse diagrams help to support the warehouse planning and implementation process. Instead of building a data warehouse from scratch or ad hoc, using a diagram ensures that all of the engineers working on its construction have the same vision and goal to work toward. It maps out key components like data sources, the processing steps that data moves through, any dependencies, and integration points.

Knowing as much of the data process as possible ahead of time helps to reduce risks in the implementation and operational phases.

Stakeholder communication

Although they can contain technical documentation, data warehouse diagrams can turn an abstract data flow into an easy-to-understand visual representation. By presenting this visual to stakeholders, especially those without a technical background, you can clearly communicate how the warehouse works, what systems it integrates with, and why it’s needed.

Documentation and knowledge transfer

You can continuously add and refine data warehouse diagrams over time, with versioned documentation of the changes made and why. When it comes time to transfer knowledge about how a warehouse is structured and how it interconnects with other parts of your data system, having an up-to-date diagram, and its history, helps to educate system workers.

System optimization and troubleshooting

Logically mapping out a data warehouse into a diagram format will let you precisely trace data lineage through your organization. If you’re experiencing performance issues, you can inspect different layers of the diagram to see, for example, where redundant transformations or failure points occur. A visual reference can help engineers investigate more accurately when it comes to troubleshooting and mitigating errors.

There are different types of data warehouse diagrams, each of which contains a distinct form of information.

Logical architecture diagrams

Logical data warehouse architecture refers to the technical details of how data flows through a system. A logical data warehouse diagram doesn’t contain detail about implementation techniques or the specific technologies behind components. Instead, it focuses on describing the sources, transformation processes, data management system, data integration tools, cloud data warehouses, and overall data architecture that allow a system to function.

Physical architecture diagrams

Physical architecture diagrams are practical diagrams that also describe the infrastructure and technologies of a data warehouse. Designers will use data flow diagrams to show the flow of data through ETL pipelines and connected components. They’ll also use entity relationship diagrams (ERDs) to define the actual structure of data within the warehouse, touching on structures, primary and foreign keys, and relationships between tables.

The point of a physical architecture diagram is to fill in all of the details that the logical diagram omits. Server models, containers, database configuration, and more, should be within this diagram to help data architects understand exactly what they need to implement.

Conceptual architecture diagrams

Although significantly less technical, conceptual warehouse diagrams provide a top-level overview of structure. Instead of covering any of the more specific details, it instead focuses on relationships between key components and the overall structure that data moves through. Conceptual architecture diagrams are typically what you would take to non-technical stakeholders when looking to demonstrate the need for a data warehouse, or how it works from a hierarchical perspective.

A data warehouse diagram is made up of different components to show the overall data warehouse architecture.

Data sources and data management

Data sources are the origins of raw data that enters your system. These can span operational databases, APIs, cloud applications, SaaS apps, and other sources. Data sources comprise the incoming business data that will move through transformations before going into analytics, AI and business intelligence (BI) tools.

ETL layer

The ETL (extract, transform, load) layer handles data integration, extracting data from its sources, using data transformation to enrich, clean, and standardize it, and then loading it into storage. Outlining this process and how information will actually flow across the ETL pipeline helps ensure data quality is consistent throughout.

Staging area

The staging area is where data is temporarily stored when moving through a data pipeline. Raw data is often hosted here for processing before being loaded into a data warehouse. Isolating the data like this enables transformation and validation without impacting any other data streams going through the process. If there are errors here, you can simply roll the data back due to its isolated state.

Data warehouse core

The data warehouse core is the central data warehouse that holds all integrated, cleansed, and historical data. Optimizing this component will help support all the current and historical data that businesses need to develop long-term analytical strategies.

Data marts

Data marts are subsets of a data warehouse for specific purposes or teams. These enhance data access by organizing warehouse data around specific use cases, rather than in a potentially overwhelming single source. Businesses also may use an operational data store for a similar data integration and storage purpose.

Presentation layer

The presentation layer is what allows business users to interact with the data, by dashboards, reports, data mining tools, and other downstream systems. It abstracts the underlying complexity of these tools to provide easy-to-use and intuitive self-service applications.

Metadata repository

The metadata repository is a component where you store all data document schemas, data models, lineage, and transformation rules. Metadata is also important for governance, making this an important component to map out and detail for effective regulatory alignment.

When building out a diagram for modern data warehouses, businesses can opt for a few different patterns to best express design.

Kimball architecture

Kimball data models use a bottom-up system where the specific use cases or business processes required are considered first. Based on the needs of certain business users, this model first outlines data marts. Next, this model works up from this bottom layer to design a cohesive analytical system that will deliver data to these data marts. It’s a very simple system to build out and integrate, using conformed dimensions to deliver data across multiple data marts.

Inmon architecture

The Inmon system takes a top-down approach, mapping out a central data warehouse design. It’s a subject-oriented model, meaning that it organizes data around core business entities like customers, orders, products, and so on. You integrate data from operational systems into this model in a normalized structure before then distributing that data downstream to data marts for reporting. This model provides a consistent standard of data quality but requires more extensive logical design.

Data vault architecture

Data vault architecture separates data into three components, known as hubs, links, and satellites. The hubs are core business concepts, links are relationships between these concepts, and satellites contain further information or attributes about hubs. This distinction allows you to load raw data that enters your system into the data vault with minimal transformation. In this structure, you apply business rules and reporting structures downstream, meaning that the core model remains stable even if how you analyze data downstream changes.

Lambda architecture and Kappa architecture for data processing

Lambda architecture is a form of data processing pattern that combines two parallel processing paths. The first of these is a batch layer, which processes large quantities of historical data. The second, the speed layer, processes real-time data. Mapping these out can help illustrate how a data system will work when it needs both real-time and historical insights.

Kappa architecture, on the other hand, focuses entirely on a streaming data pipeline. Unlike Lambda, which divides up batch and real-time layers into different processing streams, each type of data that moves through this architecture is processed as a continuous stream. For batching, it replays historical information to consider it as a stream. The Kappa approach is simpler and works best for event-driven systems.

When creating a data warehouse diagram, you should consider the following details.

Audience and purpose

Depending on who the diagram is for, a different level of detail will be most appropriate. A technical audience, for example, or the engineers implementing a data warehouse, will need the physical and logical models. However, a non-technical business stakeholder might only need to examine a conceptual model for a high-level overview.

Level of detail

There is a clear distinction between the level of detail that a physical and logical diagram needs. When creating your model, decide how detailed it needs to be, aligning with your intended audience, to save time during the process. In circumstances such as product handover, you will need to take the time to carefully detail as much of the system as possible. Doing so earlier in the data warehousing process will help to make sure that you capture the system as accurately as possible.

Notation standards

Where possible, use a consistent notation to make sure everyone in your organization can understand the model you create. Company-wide consistency will ensure that all of your data models are easy to understand and replicate across different teams.

Scalability representation

Data warehouse diagrams should also demonstrate how they will scale different components. This also expands to specific modifications you make to enable better processing or data storage, such as implementing a data lake into the diagram or flexible data storage layers.

Security and compliance layers

You can implement broad depictions of different access control systems or security technologies that protect your data warehouses into these diagrams. Understanding how your company manages data access can help you ensure all sensitive data is protected before you deploy the system.

Data lineage

Documenting data lineage across your business demonstrates how data moves from its origins to operational systems. It presents a clear diagram of data movement, helping to trace datasets as they transmit through your company.

Transformation logic and processing flow

Although often only included in a logical diagram, outlining the transformation logic you’re going to use will help teams better integrate your system when it comes time for deployment or optimizations.

Performance considerations

If there are any performance-critical components or areas where bottlenecks could form, you can use your diagram to map them out in greater detail. You can then optimize or implement strategies to mitigate any potential problems you might have ahead of time.

Version control

Using version control within your diagrams will ensure you always have backups of diagrams and previous drafts that you can turn to after systems changes. Version control helps to show how your data warehouse architecture evolves over time. If any major errors occur, you can see what changed between versions to help with diagnostics.

AWS offers a range of solutions to help build, visualize, and manage your data warehouses:

• Amazon Redshift is a unified, open, and secure data warehouse that powers modern data analytics at scale. Redshift seamlessly integrates with the next generation of Amazon SageMaker, allowing you to leverage its powerful SQL analytics capabilities on unified data across the lakehouse in Amazon SageMaker.
• AWS Glue discovers, prepares, and integrates all your data at any scale. Glue allows you to connect to more than 100 diverse sources, manage your data in a centralized data catalog, and visually create, run, and monitor data pipelines to load data into your data lake, data warehouse, and lakehouse.
• AWS Lake Formation is a service to centrally govern, secure, and share data for analytics and machine learning.

Get started with data warehousing on AWS by creating a free account today.