Module 5: Intermediate Models

Build reusable transformation steps without exposing half-finished business tables.

95 minutes. 1 inline exercise. Free course module.

Learning Objectives

Know when to create an intermediate model
Separate reusable logic from final reporting shape
Reduce duplication across marts

Why This Matters

Intermediate models hold reusable transformation logic that is too complex for staging but not final enough for business users.

Architecture diagram for Module 5: Intermediate Models.

Lesson Content

The Mental Model

Intermediate models hold reusable transformation logic that is too complex for staging but not final enough for business users.

Intermediate models are the prep bowls in a kitchen. They are useful while cooking, but you do not serve them as the final dish.

Tiny Example

We will use a small ecommerce dataset throughout the course. Think of these as the only tables in your first warehouse:

Table	Grain	Example columns
`raw_orders`	one row per order event	`order_id`, `customer_id`, `amount`, `status`, `created_at`
`raw_order_items`	one row per item inside an order	`order_id`, `product_id`, `quantity`, `item_price`
`raw_customers`	one row per customer	`customer_id`, `email`, `country`, `created_at`

Interactive Check

Question: Two marts need the same order refund calculation. Should both copy the SQL?

Reveal the answer

No. Put the shared refund logic in an intermediate model, then let both marts ref it.

Inline Practice Lab

This lab is intentionally small. You can solve it by reading the table, writing the SQL/YAML mentally, or pasting the snippet into any SQL scratchpad later.

-- Example starter table
select
  order_id,
  customer_id,
  amount,
  status,
  created_at
from raw_orders;

The goal is not tooling setup. The goal is learning the production habit: state the grain, clean one thing, test one assumption, and explain the downstream impact.

Self-Check Quiz

What is the grain of the table you are building?
Which downstream metric or dashboard would be wrong if this model broke?
What test would catch the most likely beginner mistake here?

Real-World Use Cases

Reliable executive dashboards that do not disagree across teams
AI analytics agents that query governed metrics instead of guessing SQL
Auditable metric changes where owners can see downstream impact before merge

Production Notes

Intermediate models are useful, but too many create a maze. Each one should remove real duplication or clarify complex logic.

Common Mistakes

Creating intermediate models for every tiny SELECT
Letting BI tools query intermediate models directly
Hiding important business definitions without documentation

Think Like an Engineer

Can you explain the grain of this model in one sentence?
What breaks downstream if this field becomes null tomorrow?
Where should this logic live so it is reused instead of copied?

Career Relevance

Analytics engineering is the bridge between SQL skill and production data ownership. Freshers who learn tests, lineage, metrics, and semantic modeling early stand out because they can reason about trust, not just queries.

Key Terms

Intermediate model: A model that captures reusable logic between staging and final marts.
DRY: Do not repeat yourself; centralize shared logic once.

Inline Exercises

Extract Shared Logic

Move repeated refund and order status logic into one intermediate model.

30-45 minutes - Beginner to Intermediate
- Find duplicated CASE expressions
- Create int_order_status_enriched
- Point downstream marts to the intermediate model
- Explain what duplication disappeared
Inline lab: complete the exercise directly in the course page.

Key Takeaways

Intermediate models reduce repeated business logic
They should usually not be consumed directly by BI users
Good naming makes hidden transformation steps easier to debug