What does a data warehousing tutorial need to cover?
A useful tutorial ends with something you built, not a list of definitions. The path on this page has five steps: understand what a warehouse is for, design one business process, build the star schema, load it, and query it. You need basic SQL (SELECT, JOIN, GROUP BY) and nothing more.
The running example is a small retail sales warehouse. It is deliberately narrow. One process modeled well teaches more than ten processes sketched badly. Microsoft's own Fabric warehouse tutorial takes the same approach: its end-to-end walkthrough centers on a single fact table, fact_sale, and the dimensions around it.
Step 1: What is a data warehouse for?
Oracle's documentation gives a clear starting definition: a data warehouse is designed for query and analysis rather than transaction processing, and it usually holds historical data derived from transaction data. A shop's order system records one sale at a time. The warehouse keeps every sale for years so someone can ask which product lines grew last quarter without slowing down checkout.
Keep that split in mind for the rest of the tutorial. Source systems are tuned for writing single records. The warehouse is tuned for reading many rows, summarizing them and comparing periods. If a design choice makes reading harder so that writing is easier, it probably belongs in the source system, not the warehouse.
Step 2: How do you choose a business process and declare the grain?
The Kimball Group's four-step design process is the backbone of this tutorial. The four decisions, in order, are: select the business process, declare the grain, identify the dimensions, and identify the facts.
- Business process: pick an event the business measures, such as retail sales at the register. Avoid starting from a report someone wants; reports change more often than processes do.
- Grain: Kimball calls declaring the grain the pivotal step, because the grain establishes exactly what a single fact table row represents. In the example, one row is one product on one sales receipt line.
- Dimensions: the who, what, where and when that describe that row: date, product, store and customer.
- Facts: the numbers the event produces: quantity sold, sales amount and discount amount.
Two grain rules save beginners the most pain. Start with atomic grain, which Kimball defines as the lowest level at which a business process captures data, because it can answer questions nobody predicted. And never mix grains: each grain gets its own physical fact table. A daily store summary and individual receipt lines do not share a table.
Step 3: How do you build the fact and dimension tables?
Arrange the tables as a star: one fact table in the middle with dimension tables around it. Microsoft's star schema guidance for Power BI describes the roles well. A fact table contains dimension key columns that relate to dimension tables, plus numeric measure columns. Dimension tables supply the columns people filter and group by. Dimension tables usually stay fairly small, while fact tables can hold a large number of rows and keep growing.
Here is the schema for the retail example:
| Table | Type | One row represents | Example columns |
|---|---|---|---|
| fact_sales | Fact | One product on one receipt line | date_key, product_key, store_key, customer_key, quantity, sales_amount, discount_amount |
| dim_date | Dimension | One calendar day | date_key, full_date, calendar_year, month_name, fiscal_quarter, is_holiday |
| dim_product | Dimension | One version of a product | product_key, sku, product_name, brand, category |
| dim_store | Dimension | One store | store_key, store_name, city, state, region |
| dim_customer | Dimension | One version of a customer | customer_key, customer_id, segment, row_effective_date, row_expiration_date, is_current |
Two details are worth doing from day one. First, build a real date dimension. Microsoft notes it is the most consistent table you will find in a star schema, and it lets reports group by fiscal quarter or holiday without date arithmetic in every query. Second, give dimensions surrogate keys: integer keys generated by the warehouse instead of the source system's IDs. They make history tracking possible. A Type 2 slowly changing dimension adds a new row when an attribute changes, and Kimball says it needs at least three extra columns: a row effective date, a row expiration date and a current row indicator. That is why dim_customer above carries them.
Step 4: How do you load data with ETL or ELT?
Loading has three jobs: extract rows from the source, transform them to fit the model, and load them into the tables. Microsoft's architecture guide defines ETL as a data integration process that consolidates data from diverse sources into a unified data store. A separate engine applies business rules, often using staging tables that hold data while it is processed. The transformations it lists are the ones you will actually write: filtering, sorting, aggregating, joining, cleaning, deduplicating and validating.
ELT, according to the same guide, differs from ETL only in where the transformation happens: inside the target data store, using its own processing power. With a cloud warehouse, a practical order is to land raw extracts in a staging schema, then write SQL that builds the dimension tables first and the fact table last. Dimensions go first because each fact row has to look up the surrogate keys of its dimensions. A fact row whose product cannot be found should go to an unknown-product row or an error table, not quietly disappear.
Step 5: How do you query the warehouse and check the results?
Almost every warehouse query follows one pattern: start from the fact table, join the dimensions you need, filter and group by dimension columns, and sum the facts. Sales by region and month, for example, joins fact_sales to dim_store and dim_date, groups by region, calendar year and month, and sums sales_amount. If a question needs a far stranger query than that, recheck the grain before writing clever SQL.
Then test the load the way a reviewer would:
- Row counts: receipt lines in the source for a given day should match rows in fact_sales for that day.
- Totals: total sales for a period should match the source system or the finance report.
- Orphans: no fact row should point to a dimension key that does not exist.
- History: change a customer's segment in the source, reload, and confirm that a new dimension row appears while older facts keep the old segment.
Passing these four checks is what separates a warehouse from a pile of copied tables.
Where can you practice data warehousing for free?
You can build the example on any relational database, including one on your own laptop. To practice on a cloud warehouse, these vendor options were listed on official pages when this tutorial was checked in September 2026:
- Google BigQuery sandbox: no credit card or billing account needed. Google lists a lifetime limit of 10 GiB of storage, the free tier's 1 TiB of processed query data each month, and tables, views and partitions that expire after 60 days. The sandbox does not support DML statements, so updates such as a Type 2 change need another environment.
- Snowflake trial: signup needs only a valid email address. The trial lasts 30 days from signup or until the free usage balance runs out, whichever comes first. The shared SNOWFLAKE_SAMPLE_DATA database includes TPC-DS, a benchmark that models a retail product supplier, in 10 TB and 100 TB versions, so it makes a good second exercise after the example here if you filter queries tightly to save trial credits.
- Amazon Redshift Serverless: first-time Redshift Serverless users are eligible for a $300 credit, usable within 90 days of signup.
- Microsoft Fabric: the official Fabric tutorial casts you as a warehouse developer at the fictional Wide World Importers company and walks from creating a workspace to building a Power BI report. It starts from a Power BI account, and Microsoft points new users to a free trial.
Trial terms change, so confirm them on the vendor page before you sign up.
What should you learn after your first star schema?
Once one process works end to end, the next topics build directly on it:
- Slowly changing dimensions: the Kimball technique list runs from Type 0 to Type 7. Learn Type 1, overwrite, and Type 2, add new row, before the rest.
- Conformed dimensions: dimensions with the same column names and contents, reused across fact tables so results from separate processes line up in one report.
- The bus matrix: rows are business processes and columns are dimensions. Kimball advises implementing one row of the matrix at a time.
- Performance features: partitioning, clustering and materialized views on the platform you chose.
- Business intelligence: connecting a reporting tool to the star schema, which is where the design pays off.