# Put Your Data Under Contract

Write your first ODCS data contract and test it against PostgreSQL.

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

You own the **orders** data of your company's e-commerce platform.
It lives in PostgreSQL in two tables: `orders` (timestamps, totals, customer information) and `line_items` (the items of each order, linked by `order_id`).
Consumers keep asking: *Which columns can I rely on? What does `order_total` mean? Who do I ask when something looks off?*
Answer them once, in a data contract.

**You will learn:**
- Create an ODCS data contract with the Data Contract Editor
- Test the contract against the real database, and see tests fail
- Add terms of use, classification, relationships, and quality checks
- Give AI agents context: instructions, verified answers, and constraints (new in ODCS 3.2)
- Add ownership, support channels, and service levels

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

## Explore the data

### Look at the data

Make sure the database is running (see [Setup](https://learn.datacontract.com/en/setup/)), then open a SQL prompt:

macOS / Linux:

```bash
docker compose exec postgres psql -U workshop -d workshop
```

Windows (PowerShell):

```powershell
docker compose exec postgres psql -U workshop -d workshop
```

Output:

```text
psql (17.10 (Debian 17.10-1.pgdg13+1))
Type "help" for help.

workshop=#
```

List the tables and look at a few rows:

```sql
\dt orders_v1.*
SELECT * FROM orders_v1.orders LIMIT 5;
SELECT * FROM orders_v1.line_items LIMIT 5;
```

You should see something like this:

```text
               order_id               |    order_timestamp     | order_total |     customer_id      | customer_email_address
--------------------------------------+------------------------+-------------+----------------------+------------------------
 a8c38fec-2acd-4b55-883b-4b48572d4a26 | 2020-01-01 00:00:00+00 |       29747 | 6GSHKOZIEN           | test394@example.org
 9e44da97-4f72-4bcf-821a-9d9500d06651 | 2020-01-01 10:37:00+00 |       55156 | ZN661MOMVMQXRJ       | test4757@example.org
 8fc4621c-66ae-4031-91f1-5313beb9f541 | 2020-01-01 20:14:00+00 |       85365 | NF0PRHKQP9W9Q0MTC87P | test1991@example.org
```

No output? Check for typos: `psql` stays silent for a misspelled schema or table name.
Leave the prompt with `\q`.

## Create the contract

### Create the contract file

From the repository root, create a contract and open it in the Data Contract Editor:

macOS / Linux:

```bash
datacontract edit orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract edit orders_v1.odcs.yaml
```

Output:

```text
File 'orders_v1.odcs.yaml' does not exist. Initialize a new data contract? [y/N]: y
📄 data contract written to orders_v1.odcs.yaml
Editing: /Users/you/learn.datacontract.com/orders_v1.odcs.yaml
Data Contract Editor running at http://localhost:4243
Press Ctrl+C to stop
```

The CLI asks whether to create the file. Confirm with `y`. The new file uses ODCS `apiVersion: v3.2.0`.
The editor opens in your browser. **Saving writes directly to the file on disk.**

In the **Form** view, set the fundamentals:

| Field | Value |
|---|---|
| Name | `Orders` |
| ID | `orders_v1` |
| Version | `1.0.0` (leave as is) |
| Status | `draft` (leave as is) |

![Data Contract Editor: the Form view with the fundamentals](https://learn.datacontract.com/screenshots/editor-fundamentals.webp)

> **Why version 1.0.0 and status draft?**
>
> Contracts use semantic versioning: a new **major** version signals a breaking change.
> The `status` tells consumers whether they can rely on the contract. `draft` means "work in progress, don't build on it yet".

### Add the server

Go to **Servers** and add a server. It tells tools *where* the data lives, so they can test it:

| Field | Value |
|---|---|
| Server | `Orders` |
| Type | `postgres` |
| Host | `localhost` |
| Port | `5433` |
| Database | `workshop` |
| Schema | `orders_v1` |

![The server tells tools where the data lives](https://learn.datacontract.com/screenshots/editor-server.webp)

> **Variables (ODCS 3.2)**
>
> Contracts may contain variables like `host: ${DB_HOST:-localhost}`. Tools resolve them at runtime from the environment, with the value after `:-` as default.
> One contract then works in dev, test, and production, without secrets in Git.

### Describe the schema

Go to **Schemas** and add two schemas with their properties.

**`orders`**

| Property | Logical Type | Physical Type |
|---|---|---|
| `order_id` | `string` | `TEXT` |
| `order_timestamp` | `date` | `TIMESTAMPTZ` |
| `order_total` | `integer` | `BIGINT` |
| `customer_id` | `string` | `TEXT` |
| `customer_email_address` | `string` | `TEXT` |

**`line_items`**

| Property | Logical Type | Physical Type |
|---|---|---|
| `lines_item_id` | `string` | `TEXT` |
| `order_id` | `string` | `TEXT` |
| `sku` | `string` | `TEXT` |

![The orders schema with its properties, the preview on the right](https://learn.datacontract.com/screenshots/editor-schema.webp)

> **Logical vs. physical type**
>
> The **logical type** is the technology-independent meaning (`integer`, `string`, `date`). Consumers reason about it.
> The **physical type** is how the database stores it (`BIGINT`, `TEXT`, `TIMESTAMPTZ`). Tests check against it.

Click **Save** (top right). Keep the editor open.

## Test the contract

Testing checks your contract against the *real* database: do the described tables, columns, and types exist?
This catches drift between documentation and reality.

### Run the tests

The repository's `.env` file holds the database credentials (`workshop` / `workshop`). The CLI reads it automatically from the repository root.

macOS / Linux:

```bash
datacontract test orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
Server: Orders (type=postgres, host=localhost, port=5433, database=workshop, schema=orders_v1)
╭────────┬────────────────────────────────────────────────────────────────┬───────────────────────────────┬─────────╮
│ Result │ Check                                                          │ Field                         │ Details │
├────────┼────────────────────────────────────────────────────────────────┼───────────────────────────────┼─────────┤
│ passed │ Check that field 'lines_item_id' is present                    │ line_items.lines_item_id      │         │
│ passed │ Check that field lines_item_id has physical type TEXT          │ line_items.lines_item_id      │         │
│ passed │ Check that field 'order_id' is present                         │ line_items.order_id           │         │
│ passed │ Check that field order_id has physical type TEXT               │ line_items.order_id           │         │
…
│ passed │ Check that field 'order_total' is present                      │ orders.order_total            │         │
│ passed │ Check that field order_total has physical type BIGINT          │ orders.order_total            │         │
╰────────┴────────────────────────────────────────────────────────────────┴───────────────────────────────┴─────────╯
🟢 data contract is valid. Run 16 checks. Took 0.52 seconds.
```

All checks should pass. If **all** fail, the database is probably not running: `docker compose up -d`.

### Make a test fail

Tests are only useful if they can fail. In `orders_v1.odcs.yaml`, change the `physicalType` of `customer_email_address` to `integer` and test again:

macOS / Linux:

```bash
datacontract test orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
Server: Orders (type=postgres, host=localhost, port=5433, database=workshop, schema=orders_v1)
╭────────┬──────────────────────────────────────────────────────┬───────────────────────────────┬────────────────────────────────────────────────────╮
│ Result │ Check                                                │ Field                         │ Details                                            │
├────────┼──────────────────────────────────────────────────────┼───────────────────────────────┼────────────────────────────────────────────────────┤
│ failed │ Check that field customer_email_address has physical │ orders.customer_email_address │ expected physical type 'integer' but the column is │
│        │ type integer                                         │                               │ 'text'                                             │
│ passed │ Check that field 'lines_item_id' is present          │ line_items.lines_item_id      │                                                    │
…
╰────────┴──────────────────────────────────────────────────────┴───────────────────────────────┴────────────────────────────────────────────────────╯
🔴 data contract is invalid, found the following errors:
1) orders.customer_email_address Check that field customer_email_address has physical type integer: expected physical type 'integer' but the column is 'text'
```

Which check fails, and why? Try other mistakes: a misspelled column, a missing table.
**Revert your changes** afterwards, until all tests are green again.

## Enrich the contract

Choose how you work:

- **Data Contract Editor**: great for discovering fields. Reopen it with `datacontract edit orders_v1.odcs.yaml`.
- **Your IDE** (e.g. VS Code): faster once you know the fields. Every form field is a YAML key, see the [ODCS reference](https://bitol-io.github.io/open-data-contract-standard/latest/).

> **Warning**
>
> Changed the file in your IDE while the editor is open? Reload the editor page before saving there, or it overwrites your IDE changes.

### Add terms of use and tags

- In **Terms of Use**, add a **Description**, a **Purpose**, and **Limitations**. The ✨ AI buttons can draft them.
- In **Fundamentals**, add **Tags** such as `orders` and `ecommerce`.

### Classify the email address

In **Schemas**, edit `orders.customer_email_address`:

- add **Examples** from the data, e.g. `test394@example.org`,
- set **Classification & Security → Classification** to `confidential` (it's personal data),
- set **Constraints → Required** to `true`.

Save, then find the new keys in the YAML file.

![Diagram view: click a property to edit its classification and quality rules](https://learn.datacontract.com/screenshots/editor-property-email.webp)

### Test again and spot the new check

macOS / Linux:

```bash
datacontract test orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
…
│ passed │ Check that field customer_email_address has no missing values  │ orders.customer_email_address │         │
…
🟢 data contract is valid. Run 17 checks. Took 0.55 seconds.
```

Spot the new check? A `required` field adds a "no missing values" check.

Optionally, render the contract as HTML and open `orders_v1.odcs.html` in your browser:

macOS / Linux:

```bash
datacontract export html orders_v1.odcs.yaml --output orders_v1.odcs.html
```

Windows (PowerShell):

```powershell
datacontract export html orders_v1.odcs.yaml --output orders_v1.odcs.html
```

Output:

```text
Written result to orders_v1.odcs.html
```

## Relationships and quality

### Define the relationship

Express that `line_items.order_id` references `orders.order_id`, so consumers know the tables join safely.
Add it to the `order_id` **property** in the `line_items` schema:

```yaml
schema:
  # ...
  - name: line_items
    properties:
      # ...
      - name: order_id
        logicalType: string
        physicalType: TEXT
        relationships:
          - type: foreignKey
            to: orders.order_id
```

![The Diagram view shows the relationship between line_items and orders](https://learn.datacontract.com/screenshots/editor-diagram.webp)

### Add a quality check

Schema checks verify *structure*. Quality checks verify *content*.
Add a SQL check on `customer_email_address` that every email contains an `@`:

```yaml
schema:
  - name: orders
    properties:
      # ...
      - name: customer_email_address
        # ... (types, examples, classification)
        quality:
          - type: sql
            description: Ensure email addresses are valid
            query: SELECT COUNT(*) FROM orders_v1.orders WHERE customer_email_address NOT LIKE '%@%';
            mustBe: 0
```

The query counts *invalid* rows, `mustBe: 0` asserts there are none. Run the tests and find the new check:

macOS / Linux:

```bash
datacontract test orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
…
│ passed │ Ensure email addresses are valid                               │ orders.customer_email_address │         │
…
🟢 data contract is valid. Run 18 checks. Took 0.58 seconds.
```

> **Tip**
>
> "Count the bad rows, expect zero" works for almost any rule. When the check fails, the query helps you debug.

### Add more quality checks

Add more rules and test after each one:

- `order_total` is never negative
- every `order_id` in `line_items` exists in `orders`
- the `orders` table is not empty (hint: `mustBeGreaterThan: 0`)
- your own ideas

For rules you can't automate (yet), use a text check:

```yaml
quality:
  - type: text
    description: Order total is in cents
  - type: sql
    description: Ensure that ...
    query: SELECT COUNT(*) FROM ... WHERE ...;
    mustBe: 0
```

All options: [ODCS data quality reference](https://bitol-io.github.io/open-data-contract-standard/latest/data-quality/#sql).

## Context for AI

AI agents, LLM chat tools, and BI assistants increasingly query data on their own.
They read the schema, but they don't know that `order_total` is in cents, which questions have a trusted answer, or what they must never do.
ODCS 3.2 adds a `context` block for exactly that.

> **The context block (ODCS 3.2)**
>
> `context` exists on the contract level (the dataset as a whole) and on each schema object (one table). It has three parts:
>
> - **instructions**: natural-language guidance, like a system prompt scoped to this data.
> - **verifiedStatements**: canonical business questions. With an `answer`, agents should return the curated answer when a question is close. Without, they are sample questions that prime the agent.
> - **constraints**: negative guidance. What agents must **not** do, e.g. expose personal data.

### Add context for AI agents

In the editor, open **Context** in the left navigation (or edit the YAML) and add:

- **Instructions**: what the data is, units, and time zone.
- **Verified statements**: a question with a SQL answer you checked yourself.
- **Constraints**: never output personal data.

```yaml
context:
  instructions: >-
    Orders of the e-commerce platform and their line items.
    order_total is in cents. Timestamps are in UTC.
    Use this data for order volume and revenue analysis.
  verifiedStatements:
    - question: How many orders were placed in 2023?
      answer: SELECT COUNT(*) FROM orders_v1.orders WHERE EXTRACT(YEAR FROM order_timestamp) = 2023;
    - question: What was the revenue per year?
      answer: SELECT EXTRACT(YEAR FROM order_timestamp)::int AS year, SUM(order_total) / 100.0 AS revenue FROM orders_v1.orders GROUP BY 1 ORDER BY 1;
  constraints:
    - constraint: Never output customer_email_address or customer_id. Aggregate the data instead.
      tags: ['pii']
```

![The Context section: instructions, verified statements, and constraints](https://learn.datacontract.com/screenshots/editor-context.webp)

Check your verified answer before you publish it. Run the query:

macOS / Linux:

```bash
docker compose exec postgres psql -U workshop -d workshop -c "SELECT COUNT(*) FROM orders_v1.orders WHERE EXTRACT(YEAR FROM order_timestamp) = 2023;"
```

Windows (PowerShell):

```powershell
docker compose exec postgres psql -U workshop -d workshop -c "SELECT COUNT(*) FROM orders_v1.orders WHERE EXTRACT(YEAR FROM order_timestamp) = 2023;"
```

Output:

```text
 count
-------
   876
(1 row)
```

### Add context to the orders table

Context on a schema object explains how to use that one table: granularity, joins, filters.
In **Schemas → orders**, add:

```yaml
schema:
  - name: orders
    context:
      instructions: One row per order. Join line_items on order_id to get the purchased SKUs.
    # ...
```

Do the same for `line_items`, e.g. "One row per purchased SKU in an order."

### Add synonyms

People search with their own words: "purchases", "Bestellungen", "article number". ODCS 3.2 records them as `synonyms` on schema objects and properties, so catalogs and agents find the right table.
Add synonyms in the editor or in YAML. The `locale` (a BCP 47 tag) marks a synonym in another language:

```yaml
schema:
  - name: orders
    synonyms:
      - synonym: purchases
      - synonym: Bestellungen
        locale: de
    # ...
  - name: line_items
    synonyms:
      - synonym: order lines
    properties:
      # ...
      - name: sku
        synonyms:
          - synonym: article number
          - synonym: Artikelnummer
            locale: de
```

Validate the contract against the ODCS 3.2 schema:

macOS / Linux:

```bash
datacontract lint orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract lint orders_v1.odcs.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.13 seconds.
```

![Synonyms and schema-level context under Advanced Metadata](https://learn.datacontract.com/screenshots/editor-synonyms.webp)

### Optional: ask your AI agent

Start your AI coding agent (Claude Code, Codex, Copilot, ...) in the repository and ask:

> Using the data contract `orders_v1.odcs.yaml`, how many orders were placed in 2023? Then list the email addresses of the customers with the most orders.

Watch what it does:

- Does it reuse the verified answer? (Expected result: **876**.)
- Does it refuse to list email addresses, because of the constraint?

**Quick check:** An AI agent is asked "How many orders were placed in 2023?". The contract has a verified statement with this question and an SQL answer. What should the agent do?

- Write its own SQL from scratch, it knows the schema
- Reuse the curated answer of the verified statement (correct)
- Refuse, because the contract has constraints
- Use the verified statement only if the question matches word for word

Verified statements with an `answer` are curated answers that agents should reuse when a question is semantically close. Statements without an answer only prime the agent with sample questions.

## Ownership and service levels

### Add team and support

Consumers need to know who owns the data and where to get help. Add these **top-level** keys:

```yaml
team:
  name: order_data_team
  members:
    - username: owner@example.com
      role: Owner

support:
  - channel: "#order-data-help"
    url: https://example.slack.com/archives/order-data-help
    tool: slack
```

### Add service levels

How long is the data kept, and how fresh is it? Add top-level `slaProperties`:

```yaml
slaProperties:
  - property: retention
    value: 10
    unit: y
    element: orders.order_timestamp
    description: Orders are deleted after 10 years
  - property: frequency
    value: 1
    unit: d
    description: Data updated daily
```

> **Which service levels are tested?**
>
> The CLI turns two SLA properties into checks, if they name a timestamp column in `element` (`schema.property`):
>
> - `retention`: the **oldest** value (MIN) must be younger than the period, so old data really gets deleted.
> - `freshness`: the **newest** value (MAX) must be younger than the threshold, so new data keeps arriving.
>
> Other properties such as `frequency` or `latency` are documentation only. Careful: `unit: m` means minutes for freshness but months for retention. See the [service levels docs](https://docs.datacontract.com/service-levels).

Run only the service level checks:

macOS / Linux:

```bash
datacontract test --checks slaProperties orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test --checks slaProperties orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
Server: Orders (type=postgres, host=localhost, port=5433, database=workshop, schema=orders_v1)
╭────────┬──────────────────────────────────────────────────┬─────────────────┬─────────╮
│ Result │ Check                                            │ Field           │ Details │
├────────┼──────────────────────────────────────────────────┼─────────────────┼─────────┤
│ passed │ Retention of orders.order_timestamp < 315360000s │ order_timestamp │         │
╰────────┴──────────────────────────────────────────────────┴─────────────────┴─────────╯
🟢 data contract is valid. Run 1 checks. Took 0.52 seconds.
```

### Try a freshness check

Add a `freshness` service level: new orders should arrive within 24 hours.

```yaml
slaProperties:
  - property: freshness
    value: 24
    unit: h
    element: orders.order_timestamp
    description: New orders arrive within 24 hours
  # ... retention and frequency as before
```

Run the service level checks again:

macOS / Linux:

```bash
datacontract test --checks slaProperties orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test --checks slaProperties orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
Server: Orders (type=postgres, host=localhost, port=5433, database=workshop, schema=orders_v1)
╭────────┬─────────────────────────────────────────────┬─────────────────┬─────────────────────────────────────────────╮
│ Result │ Check                                       │ Field           │ Details                                     │
├────────┼─────────────────────────────────────────────┼─────────────────┼─────────────────────────────────────────────┤
│ failed │ Freshness of orders.order_timestamp < 24h   │ order_timestamp │ Freshness is 33337535s, which exceeds the   │
│        │                                             │                 │ threshold of 86400s                         │
│ passed │ Retention of orders.order_timestamp <       │ order_timestamp │                                             │
│        │ 315360000s                                  │                 │                                             │
╰────────┴─────────────────────────────────────────────┴─────────────────┴─────────────────────────────────────────────╯
🔴 data contract is invalid, found the following errors:
1) order_timestamp Freshness of orders.order_timestamp < 24h: Freshness is 33337535s, which exceeds the threshold of 
86400s
```

The check fails: the newest order is from September 2025. The workshop data is a static snapshot, so it is never fresh. In production, exactly this check alerts you when a pipeline has stopped loading.
**Remove the `freshness` entry again**, so your contract (and later your CI pipeline) stays green.

### Set the contract to active

Your contract is complete. Set `status` to `active`: consumers can now rely on it.
Run the tests one last time:

macOS / Linux:

```bash
datacontract test orders_v1.odcs.yaml
```

Windows (PowerShell):

```powershell
datacontract test orders_v1.odcs.yaml
```

Output:

```text
Testing orders_v1.odcs.yaml
Server: Orders (type=postgres, host=localhost, port=5433, database=workshop, schema=orders_v1)
╭────────┬───────────────────────────────────────────────────────────────────┬───────────────────────────────┬─────────╮
│ Result │ Check                                                             │ Field                         │ Details │
├────────┼───────────────────────────────────────────────────────────────────┼───────────────────────────────┼─────────┤
│ passed │ Ensure line_items table has data                                  │ line_items                    │         │
│ passed │ Ensure every line_items.order_id exists in orders                 │ line_items                    │         │
…
│ passed │ Ensure email addresses contain @                                  │ orders.customer_email_address │         │
…
╰────────┴───────────────────────────────────────────────────────────────────┴───────────────────────────────┴─────────╯
🟢 data contract is valid. Run 34 checks. Took 0.65 seconds.
```

**Solution: orders_v1.odcs.yaml**

```yaml title=orders_v1.odcs.yaml
version: 1.0.0
kind: DataContract
apiVersion: v3.2.0
id: orders_v1
name: Orders
status: active
domain: ecommerce
description:
  purpose: Order data for analytics and reporting
  limitations: Contains PII (customer email addresses)
tags: ['orders', 'ecommerce']
context:
  instructions: >-
    Orders of the e-commerce platform and their line items.
    order_total is in cents. Timestamps are in UTC.
    Use this data for order volume and revenue analysis.
  verifiedStatements:
  - question: How many orders were placed in 2023?
    answer: SELECT COUNT(*) FROM orders_v1.orders WHERE EXTRACT(YEAR FROM order_timestamp) = 2023;
  - question: What was the revenue per year?
    answer: SELECT EXTRACT(YEAR FROM order_timestamp)::int AS year, SUM(order_total) / 100.0 AS revenue FROM orders_v1.orders GROUP BY 1 ORDER BY 1;
  constraints:
  - constraint: Never output customer_email_address or customer_id. Aggregate the data instead.
    tags: ['pii']
servers:
- server: postgres
  type: postgres
  host: localhost
  port: 5433
  database: workshop
  schema: orders_v1
schema:
- name: orders
  synonyms:
  - synonym: purchases
  - synonym: Bestellungen
    locale: de
  context:
    instructions: One row per order. Join line_items on order_id to get the purchased SKUs.
  physicalType: table
  logicalType: object
  physicalName: orders
  properties:
  - name: order_id
    physicalType: text
    logicalType: string
    required: true
    primaryKey: true
    quality:
    - type: text
      description: Must be a valid UUID
    - type: sql
      description: Ensure order_id is unique
      query: SELECT COUNT(*) FROM (SELECT order_id FROM orders_v1.orders GROUP BY order_id HAVING COUNT(*) > 1) duplicates;
      mustBe: 0
  - name: order_timestamp
    physicalType: timestamptz
    logicalType: date
    quality:
    - type: text
      description: Must not be in the future
    - type: sql
      description: Ensure order_timestamp is not in the future
      query: SELECT COUNT(*) FROM orders_v1.orders WHERE order_timestamp > CURRENT_TIMESTAMP;
      mustBe: 0
  - name: order_total
    physicalType: bigint
    logicalType: integer
    quality:
    - type: text
      description: Order total in cents, must be non-negative
    - type: sql
      description: Ensure order_total is non-negative
      query: SELECT COUNT(*) FROM orders_v1.orders WHERE order_total < 0;
      mustBe: 0
  - name: customer_id
    physicalType: text
    logicalType: string
    quality:
    - type: text
      description: Must be a non-empty alphanumeric identifier
    - type: sql
      description: Ensure customer_id is not empty
      query: SELECT COUNT(*) FROM orders_v1.orders WHERE customer_id IS NULL OR customer_id = '';
      mustBe: 0
  - name: customer_email_address
    physicalType: text
    logicalType: string
    required: true
    classification: confidential
    examples: ['test394@example.org', 'test4757@example.org']
    quality:
    - type: text
      description: Must be a valid email address
    - type: sql
      description: Ensure email addresses contain @
      query: SELECT COUNT(*) FROM orders_v1.orders WHERE customer_email_address NOT LIKE '%@%';
      mustBe: 0
  quality:
  - type: text
    description: Orders table must not be empty
  - type: sql
    description: Ensure orders table has data
    query: SELECT COUNT(*) FROM orders_v1.orders;
    mustBeGreaterThan: 0
  - type: sql
    description: Ensure every order has at least one line item
    query: SELECT COUNT(*) FROM orders_v1.orders o LEFT JOIN orders_v1.line_items li ON o.order_id = li.order_id WHERE li.order_id IS NULL;
    mustBe: 0
- name: line_items
  synonyms:
  - synonym: order lines
  - synonym: Bestellpositionen
    locale: de
  context:
    instructions: One row per purchased SKU in an order.
  physicalType: table
  logicalType: object
  physicalName: line_items
  properties:
  - name: lines_item_id
    physicalType: text
    logicalType: string
    required: true
    primaryKey: true
    quality:
    - type: text
      description: Must be a valid UUID
    - type: sql
      description: Ensure lines_item_id is unique
      query: SELECT COUNT(*) FROM (SELECT lines_item_id FROM orders_v1.line_items GROUP BY lines_item_id HAVING COUNT(*) > 1) duplicates;
      mustBe: 0
  - name: order_id
    physicalType: text
    logicalType: string
    required: true
    relationships:
    - type: foreignKey
      to: orders.order_id
  - name: sku
    synonyms:
    - synonym: article number
    - synonym: Artikelnummer
      locale: de
    physicalType: text
    logicalType: string
    quality:
    - type: text
      description: Must be a non-empty product SKU
    - type: sql
      description: Ensure sku is not empty
      query: SELECT COUNT(*) FROM orders_v1.line_items WHERE sku IS NULL OR sku = '';
      mustBe: 0
  quality:
  - type: text
    description: Line items table must not be empty
  - type: sql
    description: Ensure line_items table has data
    query: SELECT COUNT(*) FROM orders_v1.line_items;
    mustBeGreaterThan: 0
  - type: sql
    description: Ensure every line_items.order_id exists in orders
    query: SELECT COUNT(*) FROM orders_v1.line_items li LEFT JOIN orders_v1.orders o ON li.order_id = o.order_id WHERE o.order_id IS NULL;
    mustBe: 0
team:
  name: order_data_team
  members:
  - username: owner@example.com
    role: Owner
support:
- channel: "#order-data-help"
  url: https://example.slack.com/archives/order-data-help
  tool: slack
slaProperties:
- property: retention
  value: 10
  unit: y
  element: orders.order_timestamp
  description: Orders are deleted after 10 years
- property: frequency
  value: 1
  unit: d
  description: Data updated daily
```

**Quick check:** You declared customer_email_address as required. What happens when you run datacontract test?

- Nothing, required is documentation only
- It checks that the column has no missing values (correct)
- It adds a NOT NULL constraint to the database
- The test fails until the database has a NOT NULL constraint

`required: true` becomes a "no missing values" check on the actual data. The CLI never changes your database, it only reads.

## Bonus

- Generate SQL DDL: `datacontract export sql orders_v1.odcs.yaml` (all [export formats](https://cli.datacontract.com/#export))
- Lint against the ODCS schema: `datacontract lint orders_v1.odcs.yaml`
- Build an HTML catalog of all contracts in the folder: `datacontract catalog`