Each Monday morning, a finance analyst opens three exports, a CRM report, and a two-year-old spreadsheet to reconcile numbers that ought to already match. The sales department reports one revenue figure, the finance department reports a different one, and no one can verify which system is correct. By Wednesday, the figures are finally aligned. On Thursday, the process starts over. This situation happens when an organisation has a large amount of data but no centralised system to manage it.
Data warehousing refers to the process of gathering data from multiple operational systems and placing it into one central repository. The storage system is built specifically for reporting and analysis rather than for daily business transactions.
Typically, this process involves extracting data from source systems and converting it into a consistent format. After that, the data is loaded into a platform such as Snowflake or BigQuery. This enables analysts to query the information directly, without needing to access production databases.
The purpose of data warehousing
Most organisations end up with data scattered across a CRM, an accounting package, an ecommerce platform, a handful of spreadsheets, and whatever operational tool each team selected for itself. Each of those systems is well built for its own job. No system was built to answer a question that spans all of them at once, such as true profit per customer once support costs and returns are included.
Executing analytical queries straight on live systems generates performance problems. These sizable queries vie for resources against the transactions that the system was designed to process. Consequently, the software employed for daily business operations becomes slower when reports are generated. Data warehousing tackles these issues by copying data from each source and arranging it into a standard structure. The data is stored in a system built for large scans and joins across the entire organisation. This process enables the finance, sales, and operations departments to use the same figures and trust their accuracy.
The aim of this process is not to collect data without a specific reason.
A warehouse is valuable only if it lets an individual pose a cross-functional question and get a response within minutes. For instance, a user must be able to rapidly identify the marketing channel that yields the most loyal customers without having to spend a week manually joining data in a spreadsheet.
The internal operations of a data warehouse
Data is fed into a warehouse via a pipeline. Traditionally, this process is called ETL, which stands for extracting data from source systems, transforming it into a consistent format, and loading it into the warehouse. Most modern systems swap the last two steps, forming a process known as ELT. In this model, raw data gets loaded first. The transformation then occurs inside the warehouse using SQL, often managed with a tool such as dbt. This shift enables transformations to be version controlled, tested, and easily rerun.
Data in a warehouse is typically arranged in layers rather than in a single collection.
The raw or staging layer keeps data in a format that matches its original source. The transformation layer cleans the data, merges it from different sources, and applies business logic. The presentation layer, also called a data mart, provides organised tables with clear names. This lets analysts or business intelligence tools query the data without having to manage the underlying complexity.
Pipelines operate on a schedule, which can be hourly, overnight, or, for certain metrics, nearly in real time. Correctly configuring this scheduling and the relationships between pipeline steps requires specialised expertise. This is typically managed by an orchestration tool that functions separately from the data warehouse itself.
Data warehousing vs a database
Transactional databases, such as those used for CRM systems or ecommerce checkouts, are designed to handle many small and fast write operations. When a user places an order, the system inserts or updates a row and must respond within milliseconds. This type of workload is known as online transaction processing, or OLTP. The indexing and structure of these databases are optimised specifically for this function.
Data warehouses are designed for a different type of workload. Rather than processing many small write operations, they handle infrequent large data loads followed by many complex read operations. These tasks might include summing a column across ten million rows and grouping the results by region and month. This process is called online analytical processing, or OLAP. Warehouses generally use columnar storage to make these aggregations faster. Running these workloads on a transactional database would stop it from functioning properly. This is why the two systems are kept separate.
Star schemas, facts, and dimensions
Once data is in a warehouse, it needs to be organised for analysis. The most common method, dimensional modelling, was developed by Ralph Kimball and divides data into two types of table. Fact tables contain the quantifiable events of the business: an order placed, a payment received, a unit shipped, with each row representing a numerical value at a specific time.
Dimension tables hold the descriptive context around those facts: which customer, which product, which store, which date. A fact table for sales sits at the centre, joined out to dimension tables for customer, product, and date, and when you draw that relationship it looks like a star, which is where the name comes from. It reads intuitively to a human analyst and it performs well for a BI tool, which is why it has stayed the standard pattern for warehouse design for decades.
Current cloud data warehouses
Platforms like Snowflake, Google BigQuery, Amazon Redshift, and Microsoft Fabric have replaced the practice of purchasing and maintaining physical warehouse servers. These platforms function by separating storage from compute. This allows you to scale each resource independently, keep long-term historical data at a low cost, and pay for processing power only during active queries.
This change has removed most of the infrastructure tasks from the team. Staff no longer need to tune indexes manually or provision hardware for high-traffic periods. The remaining work involves modelling data correctly. This task determines the usefulness of the warehouse, and it is the most effective way for the team to use their time.
These platforms have removed the distinction between different data types. Most systems now process semi-structured formats like JSON alongside standard tables. Features such as immediate scaling and environment cloning without extra storage use are now common. Because of these changes, small teams can manage data warehouses that formerly required a specialised infrastructure department.
Data warehouse vs data lake vs lakehouse
A data lake keeps both structured and unstructured data in raw form while remaining inexpensive. It does not need a predefined schema when ingesting data. This flexibility benefits machine learning teams that work with images, logs, or documents. However, data lakes are unsuitable for governed business reporting since they do not enforce data consistency or quality standards at save time.
A lakehouse serves as the architectural model for the Databricks platform. It brings the structure and governance of a data warehouse to the storage approach of a data lake. This enables a single platform to handle both business intelligence dashboards and machine learning tasks. Choosing among these three options depends on your specific goals. Data warehouses are more suitable for organised and regulated reporting. Data lakes or lakehouses are more appropriate for handling large volumes of unstructured data and for training models.
How data warehousing works in practice
Retail businesses gather data from multiple sources, such as point-of-sale systems, ecommerce orders, inventory records, and marketing expenses. When this data is integrated into a central warehouse and modelled properly, a single dashboard can show the actual profit margin for each product across all sales channels. Individual source systems cannot deliver this full view because each records only specific parts of a transaction.
In manufacturing, sensor readings from the production line, ERP records, and quality inspection data are consolidated into a single structure. This enables an engineer to spot a yield problem across multiple plants before it leads to a large amount of scrapped stock. The process remains consistent in these scenarios. Separate systems deliver accurate data on their own, yet they become valuable only when combined in a correctly modelled data store.
When you need a data warehouse
You can spot these signs by observing specific patterns. These include conflicting reports from different systems and analysts who spend their time reconciling spreadsheets rather than performing analysis. You may also notice a push to implement artificial intelligence or forecasting models using data that is not yet deemed reliable. These issues point to a problem with the underlying data foundation rather than with reporting processes.
You don't have to warehouse everything on day one. Begin with the handful of tables that drive decisions, model them correctly, and expand from there. We build data and AI systems at Shipshape Data, and that is why we treat a data foundation built for AI as the starting point for any serious analytics or AI work, instead of something added after dashboards begin to disagree.
Planning a warehouse build or migration? That is the core of what our data consultancy provides: Snowflake, Databricks, BigQuery, or Fabric, selected for your estate rather than ours.
When you move an existing warehouse to a new platform, our guide to data warehouse migration costs outlines the items you should include in your budget.
For a side-by-side view of two common cloud platforms, read choosing between Fabric and Snowflake by fit.
Frequently asked questions
What is data warehousing?
Data warehousing is the process of gathering data from various operational systems and placing it in a single location. This central store is designed for reporting and analysis rather than daily transactions. Users extract data from source systems, transform it into a uniform format, and load it into platforms such as Snowflake or BigQuery. This enables users to run queries without impacting the performance of the primary business systems.
What is the difference between data warehousing and a database?
Standard transactional databases are built to handle many small, fast write operations, such as placing an order or updating a customer record. Data warehouses serve a different purpose. They manage occasional large data loads followed by complex read operations across millions of rows for reporting and analysis. If you run heavy analytical queries against a transactional database, you will slow down the system that supports your daily operations.
How does Snowflake fit into data warehousing?
Snowflake is a major cloud data warehouse platform, comparable to Google BigQuery and Amazon Redshift. Its main advantage is that it separates storage from compute. This enables an organisation to store large amounts of data at low cost and to pay for processing power only when running queries. The system does not require managing physical servers.
What are facts and dimensions in a data warehouse?
Fact tables hold measurable business events such as orders, payments, or shipments, along with their associated numerical data. Dimension tables provide the descriptive context for these events, including information about customers, products, dates, or stores. When you join dimension tables to a single fact table, you create a star schema. This is the standard design pattern for most data warehouses.
What is an example of data warehousing in practice?
A retail business might feed point-of-sale data, ecommerce orders, inventory, and marketing spend into a single warehouse, enabling a dashboard to display true profit margin per product across every sales channel simultaneously. No single source system could answer that question alone, because each only holds its own slice of the picture.
If you want an objective assessment of how AI can assist your business, Talk to us. We provide a clear analysis instead of a vendor demonstration.