Part B · The Consumer-Aligned Data Product
Exercise 4 · Design Contract-First
Design a derived data product before writing a single line of SQL.
~30 min0 of 11 steps done
The purchasing team wants to know how often each SKU is bought per year, to negotiate better deals with suppliers. You build them a new data product on top of Orders: SKU Sales.
This time you work contract-first: you design the data contract and the data product description before writing any SQL. The contract is the specification. You implement it in the next exercise.
You will learn
- Design a data contract for a data product that does not exist yet
- Express the semantics of a view as quality checks
- Mark measures and dimensions, and give AI agents context (ODCS 3.2)
- Describe a consumer-aligned data product with input and output ports
- Understand why failing tests are the expected starting point
Design the contract
Create the contract file
Create a new contract and open it in the editor. Confirm when the CLI asks to create the file.
datacontract edit sku_sales_per_year.odcs.yamlFile 'sku_sales_per_year.odcs.yaml' does not exist. Initialize a new data contract? [y/N]: y
📄 data contract written to sku_sales_per_year.odcs.yaml
Editing: /Users/you/learn.datacontract.com/sku_sales_per_year.odcs.yaml
Data Contract Editor running at http://localhost:4243
Press Ctrl+C to stopSet the fundamentals:
| Field | Value |
|---|---|
| Name | SKU Sales per Year |
| ID | sku_sales_per_year |
| Version | 1.0.0 |
| Status | draft |
Add the server
The view will live in a new schema analytics in the same database. Add a Server:
| Field | Value |
|---|---|
| Server | postgres |
| Type | postgres |
| Host | localhost |
| Port | 5433 |
| Database | workshop |
| Schema | analytics |
Describe the schema
Add a Schema sku_sales_per_year, set Advanced Metadata → Physical Type to VIEW, and add these properties:
| Property | Logical Type | Physical Type | Meaning |
|---|---|---|---|
sku | string | TEXT | The product SKU |
year | integer | INTEGER | Year of the order |
order_count | integer | BIGINT | How many orders contained the SKU |
total_quantity | integer | BIGINT | Total units bought |
Put the meaning into each property's Description. That is what the purchasing team reads.
Capture the semantics as quality checks
The schema says which columns exist. Quality checks say what must be true about them. Add checks such as:
- The combination of
skuandyearis unique total_quantityis never less thanorder_count- The view is not empty
Use "count the bad rows, expect zero", as in Exercise 1:
YAML
quality:
- type: sql
description: Ensure total_quantity is at least order_count
query: SELECT COUNT(*) FROM analytics.sku_sales_per_year WHERE total_quantity < order_count;
mustBe: 0Mark measures and dimensions
ODCS 3.2 lets you declare the role a property plays with semanticType.
In the editor, open a property in Schemas and set Semantic Type and Transform Logic, or edit the YAML:
YAML
properties:
- name: sku
semanticType: dimension
- name: year
semanticType: dimension
- name: order_count
semanticType: measure
transformLogic: COUNT(*)
- name: total_quantity
semanticType: measure
transformLogic: SUM(line_items.quantity)Add context for AI agents
Analysts will ask AI assistants about this data product. Tell them how to use it with a context block (new in ODCS 3.2, see Exercise 1).
In the editor, open Context in the navigation:
YAML
context:
instructions: >-
One row per SKU and year. Sum order_count or total_quantity across years for totals.
verifiedStatements:
- question: Which three SKUs sold the most units in 2024?
answer: SELECT sku, total_quantity FROM analytics.sku_sales_per_year WHERE year = 2024 ORDER BY total_quantity DESC LIMIT 3;The verified statement is a question with a known-good answer. You will check it once the view exists.
Run the tests and watch them fail
Save and run the tests:
datacontract test sku_sales_per_year.odcs.yamlTesting sku_sales_per_year.odcs.yaml
Server: postgres (type=postgres, host=localhost, port=5433, database=workshop, schema=analytics)
╭────────┬────────────────────────────────────┬────────────────┬───────────────────────────────────╮
│ Result │ Check │ Field │ Details │
├────────┼────────────────────────────────────┼────────────────┼───────────────────────────────────┤
│ failed │ Ensure the view has data │ │ Could not read model │
│ │ │ │ 'sku_sales_per_year': │
│ │ │ │ sku_sales_per_year │
│ failed │ Check that field 'order_count' is │ order_count │ Could not read model │
│ │ present │ │ 'sku_sales_per_year': │
│ │ │ │ sku_sales_per_year │
…
╰────────┴────────────────────────────────────┴────────────────┴───────────────────────────────────╯
🔴 data contract is invalid, found the following errors:
1) 15 checks on sku, year, order_count, total_quantity: Could not read model 'sku_sales_per_year':
sku_sales_per_yearThe tests fail, of course: nothing is implemented yet. That is the point. The purchasing team can review the interface while you turn the tests green in the next exercise.
Describe the data product
Create the data product file
Create sku_sales_per_year.odps.yaml with the same structure as in Exercise 3:
| Field | Value |
|---|---|
id | sku_sales |
name | SKU Sales |
version | 1.0.0 |
status | draft |
domain | ecommerce |
Add a description with purpose, plus team and support for the purchasing analytics team (e.g. purchasing_analytics_team).
Add the output port
The product offers one output port, described by your new contract:
YAML
outputPorts:
- name: sku_sales_per_year
description: Aggregated SKU sales per year as a PostgreSQL view
version: 1.0.0
contractId: sku_sales_per_yearAdd the input port
Declare which data (and which guarantees) your product builds on. You consume orders_v2, because only v2 has quantity:
YAML
inputPorts:
- name: orders
version: 2.0.0
contractId: orders_v2Lint the data product
Validate against the ODPS standard:
dataproduct lint sku_sales_per_year.odps.yaml✅ Data product is valid against ODPS v1.1.0
🟢 Data product is valid.Quick check
You just designed the sku_sales_per_year contract and datacontract test fails. What does that tell you?