Data Modeling Basics: Fact Tables, Dimensions, Keys & Star Schemas

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Declare the grain of Larkfield Home's sales fact table, build a grain key, find the order lines that were loaded twice and compute the true net amount.
• Swap the shop system's product codes for the warehouse's surrogate keys with XLOOKUP, send codes that are not in the dimension to the Unknown member (-1) and measure how much revenue is affected.
• Join Larkfield Home's sales facts to the product and store dimensions, bring in category, region and unit cost, and calculate the gross margin.
• Turn Larkfield Home's enriched sales rows into a category × region summary with SUMIFS, add totals and shares, and chart it as a stacked column chart.
• Build the columns of a date dimension (integer date key, quarter label, weekday name) from plain dates with YEAR, MONTH, ROUNDUP and TEXT, and use the quarter attribute to summarise revenue.
• Close the old versions of Larkfield Home's re-zoned stores with a valid_to date, look up the version that was valid on each sale date and report revenue by the region at the time of sale.
• Join Larkfield Home's product revenue through a product–collection bridge table, measure how much the join double counts and allocate revenue with weighting factors so the totals add up again.
• Test Larkfield Home's model like a data engineer: flag fact rows whose product key has no dimension row, count the orphans, rebuild the totals a report would show and reconcile them with the source system, then chart the gaps.