Data Warehousing Overview

A data warehouse is a system that aggregates data from one or more sources into a single, consistent data store to support data analytics.
Data warehouse systems support various analytical processes, including data mining, artificial intelligence, machine learning, online analytical processing (OLAP), and front-end reporting.
Data warehouses were traditionally hosted on-premises within enterprise data centers on mainframes, then Unix, Windows, and Linux systems.
The 2000s saw the rise of data warehousing appliances (DWAs) consisting of specialized hardware with pre-integrated software.
In the 2010s and onwards, cloud data warehouses emerged, eliminating hardware purchases and offering a scalable service based on a pay-as-you-go model.
Data warehouses are used across many industries, including e-commerce, transportation, medical, banking (including fintech), social media, and government.
Transportation uses data warehouses to optimize routes, travel times, equipment needs, and staffing.
Banking and fintech use data warehouses to evaluate risks, detect fraud, and cross-sell services.

A data mart is a smaller data warehouse focused on a specific business function or department.
Data marts are designed for tactical decision-making, allowing end users to quickly access relevant data without having to search the larger enterprise data warehouse.
Data marts often provide support for operational or departmental issues.
Typical data mart structures use a relational database with a star or snowflake schema.
A star schema features a central fact table surrounded by associated dimension tables.
A snowflake schema is a more normalized version of a star schema where dimension tables are further subdivided into more detailed child tables.
Data marts can be dependent, independent, or hybrid.
Dependent data marts inherit security from the enterprise data warehouse but use simpler pipelines since the data is already cleaned and transformed.
Independent data marts require custom extraction, transformation, and loading (ETL) pipelines but provide flexibility in tailoring the data to the needs of individual departments.
Hybrid data marts combine aspects of both dependent and independent data marts by using both pre-transformed data from the wider data warehouse and also running their own ETL pipelines.
The purpose of data marts is to provide timely relevant data, rapid query responses, cost efficiency and secure access.

A data lake is a centralized repository for all types of data, including structured, semi-structured, and unstructured data, stored in their original format.
Data lakes do not require pre-defined schemas, allowing for flexibility as data needs and analysis methods evolve.
Data lakes store data straight from the source and provide a large storage capacity to handle data volumes growing at an increasing rate.
Data lakes commonly use cloud object storage and large-scale distributed systems, rather than relational database management systems.
Data lakes support both structured and unstructured data, making them useful for a wide range of use cases and providing substantial cost benefits.
Commonly used vendors for data lakes include Amazon, Cloudera, Google, IBM, Informatica, Microsoft, Oracle, SAS, Snowflake, Teradata, and Zaloni.

Data warehouse architectures detail depend on use cases, such as report generation, exploratory data analysis, automation, and machine learning.
A general data warehouse architecture features data sources, a staging area (sandbox), an enterprise data warehouse repository, data marts, and analytics tools.
The staging area is where data is extracted, transformed, and loaded from multiple sources.
The enterprise data warehouse repository holds transformed data (e.g., summarized) alongside raw data.
Data marts contain subsets of the processed data optimized for particular business needs, while analytics tools provide ways to analyze and query this data.
Vendor-specific architectures often involve adaptations of the general model, achieving interoperability with different tools and tested integrations.

Data cubes are a multi-dimensional representation of data, with coordinates representing dimensions and cells representing facts, such as sales figures.
Cube operations like slicing, dicing, drilling, pivoting, and rolling up enable users to extract and analyze specific data subsets and aggregate data in meaningful ways.
Materialized views are precomputed queries that store results in a separate location, expediting subsequent querying and securely working without affecting the source database. They can be set up with different refresh options, such as never, upon request, or immediately after a statement for maximum efficiency.
Facts and dimensions are useful in business analysis as facts are used to measure business activities and dimensions provide context to facts and categorize elements of the business. Fact tables contain quantitative business data while dimension tables contain qualitative descriptive information about the data.
Star and snowflake schemas organize the linking of facts to dimensions using keys.