Progress

Part B · The Consumer-Aligned Data Product

Exercise 5 · Implement Your Data Product

Turn red tests green, with an AI coding agent or by hand.

~25 min0 of 5 steps done

You designed the contract in the previous exercise. Now fulfill it: a SQL view analytics.sku_sales_per_year on top of the orders_v2 tables that makes your contract tests pass.

A perfect task for an AI coding agent: the contract specifies exactly what to build, and datacontract test lets the agent verify its own work.

In this exercise: the SQL view behind SKU Sales, reading from orders_v2, until all contract tests pass.

You will learn

Implement a data product from its contract
Let an AI coding agent work against a contract and a test feedback loop
Learn why types are part of the contract
Check a verified statement from the contract's AI context
Take a data product live

Implement

The view definition goes into sql/sku_sales_per_year.sql. It is versioned with the contract, and the CI pipeline in Part C can apply it automatically.

Implement with an AI coding agent

Start your AI coding agent (Claude Code, Codex, Copilot, …) in the repository and prompt it, for example:

Implement a PostgreSQL view that fulfills the data contract in sku_sales_per_year.odcs.yaml. The source data is described by the contract orders_v2.odcs.yaml. Write the SQL to sql/sku_sales_per_year.sql. It must be idempotent (CREATE SCHEMA IF NOT EXISTS, CREATE OR REPLACE VIEW). Apply it to the database via docker compose exec -T postgres psql -U workshop -d workshop. Then verify with datacontract test sku_sales_per_year.odcs.yaml and iterate until all tests pass.

Watch what the agent does:

Does it read both contracts (yours for the target, orders_v2 for the source)?
Does it use their context? The instructions say how to join the tables, and the verified statements show working queries.
Does it run the tests and react to failures?
The repository tells agents not to peek into solutions/, so it has to work from the contract, like an engineer.

No AI agent at hand? Do it by hand in the next step.

…or implement it by hand

Create the folder sql and in it the file sku_sales_per_year.sql, and fill in the transformation:

sql/sku_sales_per_year.sql

CREATE SCHEMA IF NOT EXISTS analytics;

CREATE OR REPLACE VIEW analytics.sku_sales_per_year AS
SELECT
    -- your transformation here
FROM orders_v2.line_items li
JOIN orders_v2.orders o ON li.order_id = o.order_id
GROUP BY ...;

Apply it to the database:

docker compose exec -T postgres psql -U workshop -d workshop < sql/sku_sales_per_year.sql

CREATE SCHEMA
CREATE VIEW

Go live

Turn the tests green

Test, fix the SQL, re-apply, repeat until everything passes:

datacontract test sku_sales_per_year.odcs.yaml

Testing sku_sales_per_year.odcs.yaml
Server: postgres (type=postgres, host=localhost, port=5433, database=workshop, schema=analytics)
╭────────┬──────────────────────────────────────────────────────────┬────────────────┬─────────╮
│ Result │ Check                                                    │ Field          │ Details │
├────────┼──────────────────────────────────────────────────────────┼────────────────┼─────────┤
│ passed │ Ensure the view has data                                 │                │         │
│ passed │ Check that field 'order_count' is present                │ order_count    │         │
│ passed │ Check that field order_count has physical type bigint    │ order_count    │         │
│ passed │ Ensure order_count is positive                           │ order_count    │         │
…
│ passed │ Check that field year has physical type integer          │ year           │         │
│ passed │ Check that field year has no missing values              │ year           │         │
│ passed │ Ensure year is plausible                                 │ year           │         │
╰────────┴──────────────────────────────────────────────────────────┴────────────────┴─────────╯
🟢 data contract is valid. Run 15 checks. Took 0.588051 seconds.

Check the verified statement

Your contract's context promises an answer to "Which three SKUs sold the most units in 2024?". Run it:

docker compose exec -T postgres psql -U workshop -d workshop -c "SELECT sku, total_quantity FROM analytics.sku_sales_per_year WHERE year = 2024 ORDER BY total_quantity DESC LIMIT 3;"

      sku      | total_quantity
---------------+----------------
 D3KT74L5EV46T |            146
 IWMJ3ZX164    |             62
 TFH11HYOR     |             46
(3 rows)

Now ask your AI agent the same question, with only the contract as input. Does it come up with this query?

Set the data product to active

Your data product is live. Set status to active in sku_sales_per_year.odcs.yaml and sku_sales_per_year.odps.yaml, then lint both:

datacontract lint sku_sales_per_year.odcs.yaml
dataproduct lint sku_sales_per_year.odps.yaml

╭────────┬────────────────────────────────────────────┬───────┬─────────╮
│ Result │ Check                                      │ Field │ Details │
├────────┼────────────────────────────────────────────┼───────┼─────────┤
│ passed │ Data contract is valid against ODCS v3.2.0 │       │         │
╰────────┴────────────────────────────────────────────┴───────┴─────────╯
🟢 data contract is valid. Run 1 checks. Took 0.12012 seconds.
✅ Data product is valid against ODPS v1.1.0
🟢 Data product is valid.

Quick check

Your view returns the right numbers, but the physical type check for the year column fails. Most likely reason?