Define the model before writing SQL

Start with a repeatable question

An analysis can answer a question once. An analytics model needs to answer it again tomorrow, for another person, after the source data changes.

This course uses one request from a commercial team:

Show daily recognized revenue for each customer, including the customer's country.

Before opening an editor, turn that sentence into a model contract. A contract is a short record of what the table means and what must remain true when the data changes.

Define the row

Start by defining what one row represents. Data teams often call this the table's grain. For this request, one row represents one customer on one calendar day.

This row definition rules out several tempting but incorrect designs. One row per order is too detailed for the requested output. One row per day loses the customer breakdown. A table that mixes customer rows with country totals has no consistent row definition.

Write this down:

Model: customer_daily_revenue
Grain: one row per customer per calendar day
Primary key: customer_id + order_date

A primary key identifies one row. This model uses a composite key, which means the two columns identify a row together. Neither customer_id nor order_date is unique by itself.

Define the measures and dimensions

A measure is a value you aggregate, such as recognized revenue or paid order count. A dimension is an attribute used to group or filter the measure, such as customer country.

Use these definitions for the first version:

recognized_revenue: sum of order amount where order status is paid
paid_orders: count of orders where order status is paid
total_orders: count of all orders received
country: the current country recorded for the customer

The words matter. "Revenue" could mean ordered, paid, invoiced, shipped, or cash received. This course uses paid order value and calls it recognized_revenue so the assumption stays visible.

Decide how to check the result

Write down how you will know the model is correct. Start with checks that follow directly from the contract:

• customer_id and order_date cannot be null.
• The composite key customer_id + order_date must be unique.
• Recognized revenue cannot be negative in this first version.
• Joining customer attributes must not increase the number of order rows.
• Running the same date window twice must not create duplicates.

These checks are more useful than a vague requirement to "test the data". Each one tests a specific modeling decision.

Ask your coding agent

Use an agent as a thinking partner before you write SQL:

Checkpoint

You should be able to answer these without looking at SQL:

1. What does one row represent?
2. Which columns identify that row?
3. Which order statuses contribute to recognized revenue?
4. What result would show that a join duplicated rows or a rerun added duplicates?

Keep this contract nearby. Every later lesson will either implement it or test it.