Data Warehousing Explained

How Data Warehouses Work

A data warehouse receives data from multiple source systems through a process called ETL, which stands for extract, transform, and load. Data is extracted from operational databases, flat files, APIs, and other sources. It is then transformed to ensure consistency, applying standardized formats, resolving naming differences, and computing derived values. Finally, the cleaned and transformed data is loaded into the warehouse, where it is organized for efficient querying.

The internal structure of a data warehouse is fundamentally different from an operational database. Operational databases use row-oriented storage, which is efficient for reading and writing individual records. Data warehouses typically use columnar storage, where values for each column are stored together. This design dramatically improves the performance of analytical queries that scan entire columns, such as computing the average temperature across millions of weather observations, because the system reads only the columns needed for the query rather than loading entire rows.

Data warehouses organize information using dimensional modeling, a design approach developed by Ralph Kimball. The central concept is the star schema, which consists of fact tables surrounded by dimension tables. Fact tables store the quantitative measurements being analyzed, such as sales amounts, sensor readings, or experimental results. Dimension tables store the descriptive attributes used to filter and group the facts, such as dates, locations, instruments, and experimental conditions. This structure makes it intuitive to write queries like "show me the average temperature by region and month" because the dimensions naturally map to the grouping and filtering criteria.

Data Warehouses vs. Databases vs. Data Lakes

Operational databases, data warehouses, and data lakes serve different purposes and are optimized for different access patterns. An operational database handles the day-to-day transactions of an application. It is designed for fast reads and writes of individual records, supports concurrent access by many users, and ensures data integrity through transactions. A laboratory information management system that tracks sample locations and experimental status is a typical operational database application.

A data warehouse aggregates data from multiple operational databases and other sources into a single, consistent view optimized for analysis. The data is cleaned, standardized, and organized for query performance. Warehouses excel at answering complex analytical questions that span multiple data sources, time periods, and organizational boundaries. They sacrifice the ability to handle real-time transactional updates in exchange for dramatically better query performance on analytical workloads.

A data lake stores raw data in its native format without imposing any particular structure. This makes ingestion fast and cheap, and preserves all original detail for future analysis. However, querying a data lake requires more technical expertise because users must handle data parsing, cleaning, and structuring themselves. Many organizations use data lakes and data warehouses together, with the lake serving as the long-term raw data archive and the warehouse providing a curated, query-ready view of selected datasets.

Modern Cloud Data Warehouses

The data warehousing landscape has been transformed by cloud-based systems that separate storage from computation. Traditional on-premises data warehouses required careful capacity planning because storage and compute resources were tightly coupled. Adding more query capacity meant buying more hardware, even if storage was adequate. Cloud data warehouses like Snowflake, Google BigQuery, and Amazon Redshift Serverless allow organizations to scale storage and compute independently, paying only for the resources they actually use.

Snowflake popularized the concept of virtual warehouses, which are independent compute clusters that can be started, stopped, and resized on demand. Different teams can run their own virtual warehouses against the same data without interfering with each other, and compute resources can be scaled up for heavy workloads and scaled back down during quiet periods. This elasticity is particularly valuable for scientific teams whose query workloads are highly variable, peaking during analysis sprints and dropping during data collection phases.

Google BigQuery takes a serverless approach where users submit queries without managing any infrastructure. BigQuery automatically allocates the necessary compute resources, executes the query, and releases the resources when the query completes. Users pay per query based on the amount of data scanned. This model eliminates operational overhead entirely and makes ad-hoc analysis of large datasets accessible to researchers who may not have system administration expertise.

Amazon Redshift began as a traditional managed data warehouse but has evolved to include serverless and data sharing capabilities. Redshift Spectrum allows queries to scan data stored in Amazon S3 alongside data in the warehouse itself, blurring the boundary between data warehouse and data lake. This capability lets scientific teams keep their most frequently queried data in the warehouse for fast performance while accessing less frequently used data directly from cheaper cloud storage.

Data Warehousing in Scientific Research

Epidemiological research increasingly relies on data warehouses to integrate health data from multiple sources. Electronic health records, insurance claims data, laboratory results, and public health surveillance systems each capture different aspects of patient health. A clinical data warehouse integrates these sources to enable population-level analyses, such as studying the long-term outcomes of different treatment approaches or identifying risk factors for disease across demographic groups.

Environmental monitoring programs use data warehouses to aggregate observations from diverse sensor networks. The National Oceanic and Atmospheric Administration maintains data warehouses containing decades of weather observations, ocean measurements, and satellite data. Researchers query these warehouses to study climate trends, validate weather forecast models, and analyze extreme weather events across time periods spanning half a century or more.

Astronomical survey projects are increasingly adopting data warehouse approaches for their catalog data. The Sloan Digital Sky Survey stores its catalog of hundreds of millions of stars and galaxies in a SQL Server database designed for analytical queries. Astronomers query this catalog to select objects for further observation, study the distribution of galaxies across the universe, and cross-match objects with catalogs from other surveys. The upcoming Vera Rubin Observatory will maintain a similar but much larger catalog containing billions of objects.

Genomics research uses data warehouse concepts to integrate variant data with clinical and phenotypic information. The UK Biobank, which contains genetic and health data from more than 500,000 participants, provides a cloud-based research analysis platform that includes data warehouse functionality. Researchers can query across genotype data, medical records, imaging results, and lifestyle questionnaires to conduct genome-wide association studies and other analyses that require integrating multiple data types.

Best Practices for Scientific Data Warehousing

Designing a data warehouse for scientific use requires careful attention to data provenance. Every record in the warehouse should be traceable back to its original source, including information about when it was collected, by whom, using what instrument or method, and what transformations have been applied. This metadata supports reproducibility and helps researchers assess data quality when interpreting query results.

Slowly changing dimensions present a common challenge in scientific data warehousing. When a sensor is recalibrated, a species is reclassified, or a geographic boundary is redrawn, the warehouse must handle the change without corrupting historical analyses. Type 2 slowly changing dimensions, which preserve the full history by creating a new row for each change rather than overwriting the existing row, are the most appropriate approach for scientific applications where historical accuracy matters.

Query performance tuning is essential for warehouses that support interactive exploration. Partitioning data by commonly filtered columns like date range or geographic region, creating materialized views for frequently run complex queries, and choosing appropriate sort keys all contribute to fast query performance. Monitoring query patterns and adjusting the physical design accordingly should be an ongoing activity rather than a one-time setup task.

Data refresh schedules should match the needs of the research program. Some scientific warehouses need daily updates from operational systems, while others may be refreshed weekly or monthly. The key is to document the refresh schedule clearly and ensure that users understand how current the data is. Stale data that is mistakenly treated as current can lead to incorrect conclusions.