Data Warehouses and Analytical Modeling
Data Warehouses and Analytical Modeling is an intermediate-level course of 5 lessons (about 1 h 30 min), with 19 exercises and 19 quiz questions. Prerequisites: Advanced SQL (CTEs, window functions), Database Modeling. Exercises run in the browser, with nothing to install.
- Data engineering
- Intermediate
- 5 lessons
- 1 h 30 min
- 19 exercises
- Exercises run in the browser
The course in brief
This course teaches you to build the analytical layer of a data platform. You will place the warehouse, the lake and the lakehouse, build a star schema in SQL from a transactional database, historize dimensions as type 2, then optimize storage with partitioning and aggregate tables. You will finish by analyzing costs from the query log.
What you will be able to do
- Choose between a warehouse, a lake and a lakehouse, and organize transformations into layers
- Design and build in SQL a fact table and its dimensions at an explicit grain
- Implement a type 2 slowly changing dimension and attach facts to it by date
- Choose a partitioning and clustering strategy and build aggregate tables
- Analyze the query log to reduce a warehouse's costs
Syllabus: 5 lessons
- Distinguish a data warehouse, a data lake and a lakehouse
- Describe a layered analytical architecture (raw, cleaned, business)
- Write a transformation from the raw layer to the cleaned layer in SQL
Data warehouseData lakeLakehouseELT and transformation layers
- 02
Building a Star Schema in SQL
- Apply the dimensional design process (process, grain, dimensions, facts)
- Build a fact table and dimensions from a transactional database
- Generate a date dimension and surrogate keys in SQL
- Query the model to produce metrics
- 03
Slowly Changing Dimensions (SCD)
- Choose between SCD type 0, 1, 2 and 3 according to the need for history
- Write a point-in-time join between facts and a type 2 dimension
- Detect new records and the changes to historize
- 04
Partitioning, Clustering and Aggregate Tables
- Explain how partitioning reduces the volume read by a query
- Choose a partitioning and clustering key suited to the queries
- Build an aggregate table for recurring reports
- 05
Controlling a Warehouse's Costs
- Explain the two main billing models of a cloud warehouse
- Analyze a query log to find the most expensive queries
- Propose concrete cost-reduction actions
Final project
Analytical Warehouse for an Online Shop
A fictional shop sells home goods. You have its transactional tables (customers, products, orders, order lines) and a history of changes to its customers' segments. Design and build in SQL the warehouse that will make it possible to track revenue, average order value and loyalty, while preserving the history of segments.
Deliverables
- A diagram of the layered architecture (raw, cleaned, business) with the role of each layer
- The SQL queries of the cleaned layer (typing, deduplication of loads)
- A fact table at the order-line grain and the date, product and customer dimensions, with surrogate keys
- A customer dimension as SCD type 2 and the load query that detects changes
- Five metric queries, including a comparison of historical segment versus current segment
- A design note on partitioning, aggregate tables and cost monitoring
Assessment criteria
- The grain of each fact table is written down and verified by a check query
- The type 2 dimension follows a single interval convention and the point-in-time join duplicates no sale
- Amounts are exact (integers in cents or decimal) and ratios are recalculated from their components
- Partitioning and clustering choices are justified by the expected queries
- The SQL code is readable, commented and replayable end to end
Frequently asked questions
What is the difference between a data warehouse and a data lake?
A warehouse stores structured, modeled data, with a schema enforced at load time, for SQL analysis. A lake stores files of any format at low cost and applies the schema at read time. A lakehouse combines the two thanks to open table formats.
What is a type 2 slowly changing dimension?
It is a dimension that keeps the history of an attribute by creating a new row at each change, with validity dates. Past facts remain attached to the version that was valid at the time of the event.
What level do I need for this course?
You need to be comfortable with advanced SQL (CTEs, window functions) and the basics of modeling (keys, normalization, the principle of the star schema), taught in the Advanced SQL and Database Modeling courses.
Do the exercises use a real cloud warehouse?
The exercises run in your browser with SQLite. Syntax specific to BigQuery, Snowflake or PostgreSQL is presented in the lessons, and the concepts are the same.
This course, told for your job
Say what you want to be able to do: “Data Warehouses and Analytical Modeling” takes its place in your path, with examples from your field. The concepts stay those of the course.