Lab: Validate and harden the layer

Step 1 — Write the suite

1. 1Create test_semantic_layer.py in the starter-kit root (semantic-agent/, next to the semantic_layer/ folder) with the tests from the lesson.
2. 2Add one more invariant: revenue by region should also sum to the total.
3. 3Run it with pytest: pip install pytest, then pytest test_semantic_layer.py. Or skip pytest: add if __name__ == "__main__": test_total_revenue(); test_channels_sum_to_total(); ... at the bottom and run plain python3 test_semantic_layer.py. Get it green.

Step 2 — Try to break it

Here's a subtlety worth breaking on purpose. Dropping the DISTINCT from orders_count does not move the unsliced total (no dimension → no join → nothing to fan out), and channel slices stay correct too (orders→customers is many-to-one). The join that bites is orders→order_items, and only a product dimension forces it. So first add a guard that watches exactly that slice:

def test_orders_count_by_category_counts_orders_not_line_items():
    rows = L.query(["orders_count"], ["product_category"])["rows"]
    counts = {r["product_category"]: r["orders_count"] for r in rows}
    # 3,332 distinct completed orders contain a Blends item.
    assert counts["Blends"] == 3332
    # Orders legitimately count once per category they touch, so the
    # category sum (13,444) exceeds the 8,377 total — but if DISTINCT is
    # ever lost, the join fans out to line items and this sum jumps to 14,753.
    assert sum(counts.values()) == 13444

1. 1Run the suite with the new guard — green. (Why is 13,444 bigger than the 8,377 total? Almost 4,000 orders contain items from more than one category, so they appear once per category. That's expected; it's line-item counting that would be a bug.)
2. 2Now change orders_count to COUNT(orders.order_id) (drop the DISTINCT) in metrics.yml. The total-orders tests still pass — but the guard fails: Blends inflates from 3,332 to 3,699 and the category sum from 13,444 to 14,753, because you're now counting line items, not orders. Revert.
3. 3Remove the completed-only filter from revenue. The total-revenue test fails immediately. Revert.
4. 4Request a genuinely impossible combination and confirm you get a clear SemanticError message, not a wrong number.

Step 3 — Cross-check against raw SQL one more time

Pick two metrics you haven't verified yet (say gross_margin and mrr) and reproduce them with hand-written SQL. Confirm the layer matches. Now you know the numbers are right — which is exactly the trust an AI agent will depend on.