# Design Contract-First

Design a derived data product before writing a single line of SQL.

Source: https://learn.datacontract.com/en/contract-first/

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](https://learn.datacontract.com/en/implement/).

**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

_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._

> **Contract-first**
>
> The interface comes before the implementation, like an OpenAPI spec before the API.
> Consumers review the contract *before* any work is done, and its tests tell you when the implementation is complete: **red → green**.

## 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.

macOS / Linux:

```bash
datacontract edit sku_sales_per_year.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract edit sku_sales_per_year.odcs.yaml
```

Output:

```text
File '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 stop
```

Set 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 `sku` and `year` is unique
- `total_quantity` is never less than `order_count`
- The view is not empty

Use "count the bad rows, expect zero", as in [Exercise 1](https://learn.datacontract.com/en/contract/):

```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: 0
```

> **Tip**
>
> Uniqueness over two columns: `GROUP BY ... HAVING COUNT(*) > 1` in a subquery. Put checks spanning several columns on the schema level (`schema[].quality`), not on a single property.

### Mark 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)
```

![Semantic Type in the property editor (Diagram view, click a property)](https://learn.datacontract.com/screenshots/editor-semantic-type.webp)

> **Measures and dimensions**
>
> A **dimension** is an attribute to group and filter by (`sku`, `year`).
> A **measure** is an aggregated value (`order_count`, `total_quantity`); `transformLogic` says how it is computed.
> Semantic layers, BI tools, and AI agents use these roles to build correct queries, e.g. to sum measures but never sum a year.

### 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](https://learn.datacontract.com/en/contract/)).
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;
```

![Context of the SKU Sales contract](https://learn.datacontract.com/screenshots/editor-sku-context.webp)

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:

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                           │
├────────┼────────────────────────────────────┼────────────────┼───────────────────────────────────┤
│ 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_year
```

The 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.

**Solution: sku_sales_per_year.odcs.yaml**

```yaml title=sku_sales_per_year.odcs.yaml
version: 1.0.0
kind: DataContract
apiVersion: v3.2.0
id: sku_sales_per_year
name: SKU Sales per Year
status: draft
domain: ecommerce
description:
  purpose: Shows how often each SKU is bought, grouped by year. Built for the purchasing team to support supplier negotiations.
  limitations: Aggregated data only, no PII. Based on the orders data product.
tags: ['sku', 'sales', 'analytics']
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;
servers:
- server: postgres
  type: postgres
  host: localhost
  port: 5433
  database: workshop
  schema: analytics
schema:
- name: sku_sales_per_year
  physicalType: view
  logicalType: object
  physicalName: sku_sales_per_year
  properties:
  - name: sku
    semanticType: dimension
    physicalType: text
    logicalType: string
    required: true
    quality:
    - type: sql
      description: Ensure sku and year combination is unique
      query: SELECT COUNT(*) FROM (SELECT sku, year FROM analytics.sku_sales_per_year GROUP BY sku, year HAVING COUNT(*) > 1) duplicates;
      mustBe: 0
  - name: year
    semanticType: dimension
    physicalType: integer
    logicalType: integer
    required: true
    quality:
    - type: sql
      description: Ensure year is plausible
      query: SELECT COUNT(*) FROM analytics.sku_sales_per_year WHERE year < 2020 OR year > 2100;
      mustBe: 0
  - name: order_count
    semanticType: measure
    transformLogic: COUNT(*)
    physicalType: bigint
    logicalType: integer
    quality:
    - type: text
      description: Number of orders containing the SKU in that year
    - type: sql
      description: Ensure order_count is positive
      query: SELECT COUNT(*) FROM analytics.sku_sales_per_year WHERE order_count <= 0;
      mustBe: 0
  - name: total_quantity
    semanticType: measure
    transformLogic: SUM(line_items.quantity)
    physicalType: bigint
    logicalType: integer
    quality:
    - type: text
      description: Total units bought, never less than the number of orders
    - 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: 0
  quality:
  - type: sql
    description: Ensure the view has data
    query: SELECT COUNT(*) FROM analytics.sku_sales_per_year;
    mustBeGreaterThan: 0
team:
  name: purchasing_analytics_team
  members:
  - username: purchasing@example.com
    role: Owner
support:
- channel: "#purchasing-analytics"
  url: https://example.slack.com/archives/purchasing-analytics
  tool: slack
```

## Describe the data product

> **Source-aligned vs. consumer-aligned**
>
> **Orders** is *source-aligned*: it exposes data close to the system that creates it.
> **SKU Sales** is *consumer-aligned*: built for a specific use case of a specific consumer.
> A consumer-aligned product declares the data it builds on through **input ports**, each pointing to the contract it relies on.

### Create the data product file

Create `sku_sales_per_year.odps.yaml` with the same structure as in [Exercise 3](https://learn.datacontract.com/en/data-product/):

| 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_year
```

### Add 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_v2
```

### Lint the data product

Validate against the ODPS standard:

macOS / Linux:

```bash
dataproduct lint sku_sales_per_year.odps.yaml
```

Windows (PowerShell):

```powershell
dataproduct lint sku_sales_per_year.odps.yaml
```

Output:

```text
✅ Data product is valid against ODPS v1.1.0
🟢 Data product is valid.
```

**Solution: sku_sales_per_year.odps.yaml**

```yaml title=sku_sales_per_year.odps.yaml
apiVersion: v1.1.0
kind: DataProduct
id: sku_sales
name: SKU Sales
version: 1.0.0
status: draft
type: consumerAligned
domain: ecommerce
description:
  purpose: Consumer-aligned data product showing how often each SKU is bought per year, used by the purchasing team for supplier negotiations.
tags: ['sku', 'sales', 'purchasing']
inputPorts:
- name: orders
  version: 2.0.0
  contractId: orders_v2
outputPorts:
- name: sku_sales_per_year
  description: Aggregated SKU sales per year as a PostgreSQL view
  version: 1.0.0
  contractId: sku_sales_per_year
team:
  name: purchasing_analytics_team
  members:
  - username: purchasing@example.com
    role: Owner
support:
- channel: "#purchasing-analytics"
  url: https://example.slack.com/archives/purchasing-analytics
  tool: slack
```

**Quick check:** You just designed the sku_sales_per_year contract and datacontract test fails. What does that tell you?

- The contract is wrong and needs to be fixed
- Nothing is implemented yet: the failing tests are your to-do list (correct)
- The database is not running
- You should loosen the quality checks until the tests pass

Exactly. In contract-first, red tests are the expected starting point. They turn green once the implementation fulfills the specification.