Inventory Misallocation Investigation

Prompts, in order

Copy each prompt as written, or rephrase. In Step 2, substitute the specific item and expected quantity the instructor provides.

1. Discovery

   Locate the inventory, store, item, and shipment models.

   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.

   Exercises: status, search
2. Schema understanding

   Confirm the data can answer the question before writing SQL. Grain mismatches and missing join keys are common silent failures.

   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.

   Exercises: describe, lineage
3. Data inspection

   Inspect real rows for one item across the affected stores. 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.

   Exercises: warehouse, dbt_show on a targeted slice
4. Model creation

   dbt Wizard writes inventory_shipment_variance.sql into the project.

   Compile the model and preview the first 20 rows using deterministic ordering. Order inventory variances by abs(variance_quantity) desc, warehouse_id, product_id.

   Exercises: file edits, model generation
5. Safe preview

   The SQL compiles, the first 20 rows render in deterministic order, nothing lands in the warehouse. Confirm the stores from Step 3 appear, the columns are present, and the row count is non-zero.

   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.

   If the preview does not match the expected row count, columns, or stores from Step 3, stop and diagnose before materializing.
   Exercises: dbt_compile, dbt_show
6. Materialize

   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.

   Why skip verification? Scoped to this timed lab only. Step 5's deterministic preview already validates correctness; a full verification pass would consume ~10% of the lab budget on duplicate work.
   Exercises: dbt_run against dev schema

References and next scenario