# Implement Your Data Product Turn red tests green, with an AI coding agent or by hand. Source: https://learn.datacontract.com/en/implement/ You designed the contract in the [previous exercise](https://learn.datacontract.com/en/contract-first/). 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. _Scenario: the Orders data product (output ports orders_v1 and orders_v2) is consumed by the SKU Sales data product, which the purchasing team uses._ **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 > **Why contracts and AI agents fit together** > > An agent is only as good as its specification and its feedback. A data contract provides both: > the **target** (columns, types, semantics, quality rules) and the **source** (the `orders_v2` contract) as precise input, and `datacontract test` as an objective check of whether it is done. > No guessing, no "looks right to me". ## 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](https://learn.datacontract.com/en/ci-cd/) 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 title=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: macOS / Linux: ```bash docker compose exec -T postgres psql -U workshop -d workshop < sql/sku_sales_per_year.sql ``` Windows (PowerShell): ```powershell Get-Content sql/sku_sales_per_year.sql | docker compose exec -T postgres psql -U workshop -d workshop ``` Output: ```text CREATE SCHEMA CREATE VIEW ``` > **Types are part of your contract** > > `EXTRACT(YEAR FROM order_timestamp)` returns `numeric` in PostgreSQL: cast it with `::int`. > `SUM(quantity)` also returns `numeric`: cast it with `::bigint`. > The contract says `INTEGER` and `BIGINT`, and the tests check exactly that. > **Tip** > > `CREATE OR REPLACE VIEW` cannot change the name or type of an existing column. If PostgreSQL complains, add `DROP VIEW IF EXISTS analytics.sku_sales_per_year;` before it. ## Go live ### Turn the tests green Test, fix the SQL, re-apply, repeat until everything passes: macOS / Linux: ```bash datacontract test sku_sales_per_year.odcs.yaml ``` Windows (PowerShell): ```powershell datacontract test sku_sales_per_year.odcs.yaml ``` Output: ```text 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. ``` **Solution: sql/sku_sales_per_year.sql** ```sql title=sku_sales_per_year.sql CREATE SCHEMA IF NOT EXISTS analytics; CREATE OR REPLACE VIEW analytics.sku_sales_per_year AS SELECT li.sku, EXTRACT(YEAR FROM o.order_timestamp)::int AS year, COUNT(*)::bigint AS order_count, SUM(li.quantity)::bigint AS total_quantity FROM orders_v2.line_items li JOIN orders_v2.orders o ON li.order_id = o.order_id GROUP BY li.sku, EXTRACT(YEAR FROM o.order_timestamp); ``` ### Check the verified statement Your contract's `context` promises an answer to "Which three SKUs sold the most units in 2024?". Run it: macOS / Linux: ```bash 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;" ``` Windows (PowerShell): ```powershell 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;" ``` Output: ```text 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: macOS / Linux: ```bash datacontract lint sku_sales_per_year.odcs.yaml dataproduct lint sku_sales_per_year.odps.yaml ``` Windows (PowerShell): ```powershell datacontract lint sku_sales_per_year.odcs.yaml dataproduct lint sku_sales_per_year.odps.yaml ``` Output: ```text ╭────────┬────────────────────────────────────────────┬───────┬─────────╮ │ 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? - The contract is too strict, remove the physicalType - EXTRACT returns numeric, but the contract promises INTEGER (correct) - PostgreSQL views cannot have typed columns - The year values are out of range Right. The physical type is part of the contract. Cast `EXTRACT(YEAR FROM ...)` with `::int` so consumers get exactly the promised type.