A six-step workflow that turns a stakeholder question - "where did the missing inventory go?" - into a materialized dbt model naming the stores with inventory counts above or below the expected shipment-plan quantity.
Watch the inventory misallocation investigation play through end-to-end. Six steps, roughly 90 seconds.
A stakeholder asks where the missing inventory went. Some stores got too much stock, some got too little, ahead of a sale event. You need to find them, name them, and hand back a queryable table - not a screenshot.
The workflow turns the business question into a materialized dbt model: no codebase spelunking, no SQL written from a blank file, no guessing at schemas. You start at the business problem and end with inventory_shipment_variance in your dev schema, listing every store with an over-count or under-count against the expected per-store quantity.
inventory_shipment_variance - one row per misallocated store/item where actual inventory differs from expected. Built into your dev schema.
Copy each prompt as written (recommended) or rephrase in your own words. In Step 2 you'll substitute the specific item and expected quantity your instructor calls out.
Start from the business problem, not the file tree. You do not need to know schema names or directory layout in advance - that is the entire point.
Find the models in this project related to inventory, stores, items, and shipments.
Then ask for the grain and joins.
For those models, show the grain, key columns, and how they join together.
Decide before writing SQL whether the data can answer the question at all. Grain mismatches and missing join keys are the silent killers of analytics work - catch them here, not three hours into a query you have to throw away.
Check [specific item] shipments and inventory. Expected quantity is [N] per store. Show the per-store expected quantity, actual inventory, variance quantity, and variance direction, ordered by absolute variance desc, warehouse_id.
Look at real rows for one item across the affected stores. Read the expected per-store quantity off the data, not off a slide. Capture the over-counted and under-counted store identifiers - Step 5 checks they appear in the preview.
Create a dbt model named inventory_shipment_variance that lists every store where actual inventory for that item differs from the expected per-store quantity. Include store name, city or region, item name, actual inventory count, expected count, variance quantity, and a variance direction showing over-count or under-count.
dbt Wizard writes the inventory_shipment_variance.sql model into the project. This is the difference between an answer and an asset - versioned, reviewable, testable.
Compile the model and preview the first 20 rows using deterministic ordering. Order inventory variances by abs(variance_quantity) desc, warehouse_id, product_id.
The SQL compiles, the first 20 rows render in a deterministic order, nothing lands in the warehouse yet. Confirm the stores from Step 3 appear, the columns are present, the row count is non-zero, and the ordering is deterministic.
Before materializing, confirm the active dbt target, the dev schema, and permission to create the model. Then materialize inventory_shipment_variance into my dev schema as a table. For this timed lab, skip extended verification after the successful compile and deterministic preview. Materialize only after the expected rows appear.
dbt Wizard runs the build against your dev schema (dev_lab_user_N). The instructor drops dev schemas after the lab via a cleanup script, so this build is safe and disposable.
inventory_shipment_variance - one row per misallocated store/item where actual inventory differs from expected, materialized in the participant's dev schema. A queryable, versioned table Operations can call against directly.
The canonical scenario-1 skill definition, prompts, and instructor notes.
One-page lab handout with every prompt in this scenario, formatted for print.
Extend an existing model with a new Fivetran-synced source. Choose-your-path: customer 360, operations, or merchandising.