Nejra Husak - Dejan Andjelic

Introduction to Data Warehouse When you think about a large amount of unstructured data coming from different sources, it seems impossible at first to get any useful information from it in any reasonable time. Luckily, there are systems that are designed especially for this purpose. These systems offer support for data lakes and data warehouses as well as specific reporting tools - all integrated as one.  In this blog, we will focus on building effective data warehouses, which can be interesting to anyone involved in data engineering. Data Warehouse Ecosystem A data warehouse ecosystem consists of multiple stages that must be considered when designing an enterprise data warehouse. The main components of a data warehouse are shown in the image below: data ingestion, data storage, data presentation.  These components will be explained in more detail in the blog. All parts of the data warehouse must be built for high performance and offer quick analytics. Before anyone starts to work on data warehouse architecture, there are 3 major aspects one should think about: data, process, maintenance. We will discuss this in the blog in the simplest possible way. Thinking About Data Domain and Users Before we start thinking about data warehouse design, we should consider the users and the actual business value that having this warehouse will provide. Warehouses can be collections of different types and structures of data, but this data should finally be used for some meaningful reporting that will drive important decisions for the business. Defining the scope and usage of the data warehouse will help us think about the best possible solution for the architectural decisions. There are two useful questions that we should be asking ourselves: What would be the optimal granularity of data, considering final reporting requirements? What is the needed refresh rate for the data?  So, once more, ask yourself what value a data warehouse will bring before diving into the complex design process. Data Modeling Since a data warehouse considers data in a structured form, some kind of data modeling needs to be agreed upon to support effective decision-making and different types of reporting tools. Organizing data to match business needs can be challenging, so take time to consider different approaches. These approaches are not mutually exclusive. In fact, the best is to take what suits your case from all of them. Inmon’s modeling approach This approach is also known as top-down approach. It uses a central data warehouse and focuses on normalization. Normalization minimizes redundancy and provides data consistency. From this central warehouse, data marts are derived for specific business needs. The main advantage of this approach is providing a complete view of data and data accuracy for the whole organization. It is suitable for large enterprises. The disadvantage is incompatibility with analytics frameworks because it requires joining and ordering data making it very complex for maintenance. Kimball’s modeling approach This approach is also known as the bottom-up or Dimensional Modeling approach. It focuses on data marts to solve specific business needs. Data marts are integrated in a warehouse and organized in a form of star or snowflake schema (dimensional models).  The main advantage of this approach is simplicity and flexibility in maintenance. It is quite agile and it supports organizations that are in constant change of business requirements.  The disadvantage is real-time processing support because of delays in refreshing dimensions due to missing a single source of truth.  Linstedt’s modeling approach This approach is also known as the hybrid or Data Vault modeling approach. It focuses on agility and scalability by using tables that represent hubs, links, and satellites (hubs are business entities, links are relationships between entities, and satellites are historical data). The main advantage of this approach is the ability to scale while data volume increases. It supports easy integration of new data sources in order to provide new business value. Also, Data Vault modeling is suitable for industries with strict regulatory requirements, i.e. finance and healthcare. This approach's disadvantage lies in the work's complexity and the lack of data integrity. Data Source and Ingestion Data can reside anywhere in any form. It can come from relational databases, spreadsheets, social media, devices, apps, etc. Data warehouse systems can have predefined tools to ingest this data, but some unified process must often be created to support this. This is the difference between a data lake and a data warehouse. A data lake is a centralized storage of unstructured data. It can store any type of raw data without predefined rules. On the other hand, a warehouse requires some ETL/ELT process to extract clean data in specific format. Data in a data warehouse needs to be structured, deduplicated, sorted and aggregated to keep integrity and quality.  In the next chapter, we will talk about the data transformation process.   Thinking About Process Data Transformation Pipeline Extract data from the raw dataset to a more structured dataset, using only necessary data. Use this step to sanitize data, such as ensuring the correct data type, removing or stripping too large strings, verifying date and time formats, enum fields, etc. Extract and process data in small deltas of newly arrived data, meaning take small batches each couple of minutes, depending on the amount of data. Processing larger batches can lead to exponentially larger processing time. Recognize which way you want data to be aggregated based on your requirements. The goal is to get a record that can be further aggregated but also filtered and searched by all necessary fields. Data should be formatted in a way that covers all use cases. Ensure this step is performed in small batches and as often as possible. Aggregating steps can be costly, due to the complex nature of such operations. Recognize data that is not changed often, and keep the latest value up to date. Based on your requirements, aggregated data can be additionally aggregated and prepared for a specific use case. Consider using views or materialized views or even additional aggregating steps if necessary. Reprocessing Data There is always a possibility that there is a bug in the transformation pipeline or that ingested data may not be correct in some way. The transformation pipeline should be built so that the whole or partial raw dataset may need to be processed again to fix the abovementioned issues. Always take into account that requirements can change. Sometimes, new data will need to be extracted from the raw dataset, or existing data may need to be processed differently. Either way, reprocessing is a possibility that should be considered.  Reprocessing the whole raw dataset can become a nonviable option when the dataset becomes really large. Transformation operations usually require some sort of joins, which makes execution time rise exponentially. In such cases, consider having a separate pipeline or set of queries which should work with large datasets, and be run on-demand only.   Data Deduplication Ingested data can contain identical records even if it’s not intended. Reasons for this can vary, from issues in systems that produce data to underlying issues in technologies used to consume data. One way to deal with duplicated data is to use a subset of record fields to compute another field, using some sort of hashing algorithm. This computed field can be used to recognize duplicate records in the transformation pipeline so they can be dealt with accordingly. Backing Up Data Make sure to investigate backup options of technologies chosen for the data warehouse. To ensure that your data is safely backed up, consider storing raw data to a separate system, like S3 or similar. Certain data warehouse tools include time-travel options. They are usually limited to a certain amount of time, so it can be a useful tool for retrieving data lost in a short time span. Always ensure that your backup is functional and that data can be restored. Pipeline changes may not be compatible with backed-up data, so consider it when updating each pipeline.   Exposing Data to Reporting Tools An important part of the warehouse ecosystem is finding a way to organize and expose parts of data to different reporting tools. This can be done through SQL or API with a specific set of access roles for different types of reports. Tabes in this layer should be optimized for high performance since business teams will use them.  We can say that there are three user groups and different types of reporting tools within the presentation layer: Automated reporting tools, where specific data warehouse tables are being queried to provide reports. Examples of reporting tools are Tableau and Power BI. People with limited coding experience tend to use these tools since they expose graphical UI.  Business intelligence / data analytics tools, explore data and patterns and produce actionable insights. These tools have a graphical interface and enable users to write code directly. Direct queries, where custom queries can be used to generate reports. This is usually used by people with great technical skills, along with R and Python. Thinking About Maintenance Deployment and Release Management Once you figure out how your data should be ingested into a warehouse and how this data should be transformed and modeled to bring some business value, it’s time to think about setting up such architecture on chosen platforms. Choosing the right platforms for your needs is also important, but it will not be a part of this blog. Some potential architectures include: Kafka Connect, S3, Amazon Redshift or Snowflake. With architecture in mind, the deployment and release process should be established following these best practices as part of a release framework: it should be automated (on single instruction / on click / on schedule / on change), it should be modular to have the option to deploy a single part of the system and to include or exclude different components, it should be platform agnostic, it should be idempotent (lead to the same result when executed multiple times), it should have a failure recovery system (reverting changes ), it should support releasing deltas if needed (i.e. evolution scripts). These are some things to consider; not all must be included in every release procedure. Many available tools can help automate this process but don’t hesitate to try and invent your own (as we have done multiple times as a company). Monitoring and Cost Analysis Many platforms for building data warehouses offer different insights. You should always take some time to get familiar with this. Insights can be useful for anomaly detection, performance degradations, and many other things. Using available insights to build different analytical dashboards can help provide different metrics, such as query duration, storage usage, compute usage, system failures, and cost analysis. All this can be used to create different alerts to make the system reliable.  Cost analysis is an important thing to track when talking about data warehouses. Since a lot of power needs to be used to support the ingestion of large data, fast transformations of this data and reading final reports, it’s mandatory to keep track of monthly and yearly costs and do regular reviews. Avoid getting caught in a trap of unpredictable expenses due to unrealistic requirements and unplanned costs for their realization.   Final Thoughts This blog summarizes all the important aspects of building a data warehouse. When talking about a warehouse, it’s important to emphasize that it cannot be considered just a storage of structured data and that there is much more to explore and understand within the warehouse ecosystem. All mentioned topics are just briefly introduced, and it is expected to look into them more thoroughly. Additional steps to take along with designing the data warehouse: investigate technologies depending on warehouse purposes, check if technologies are compatible out of the box, do POC before deciding on an approach. Some important definitions to have in mind: Data engineering - building systems that collect, load, transform, and organize large data to enable valuable insights. Data pipeline - process for ingesting raw data from different data sources, transforming them and storing them in a data lake or data warehouse. Data lake - centralized storage of unstructured data. Data warehouse - requires some ETL/ELT process to extract clean data in a specific format. Data mart - a subset of the data warehouse usually oriented to specific business needs. ETL/ELT - sequential processes that prepare data for further analysis where ETL stands for extract-transform-load and ELT for extract-load-transform. (more…)