Build a data model with AI

Instructions for an AI agent (Claude, Codex, Gemini, …) to generate a data model in OKF that imports cleanly into OWOX Model Canvas and pushes to OWOX Data Marts in one click.

1. What is OKF

Open Knowledge Format (OKF) represents metadata as a folder of Markdown files with YAML frontmatter. Reference: GoogleCloudPlatform/knowledge-catalog (the okf/ spec) and OWOX's own bundles at github.com/OWOX/models. This canvas reads and writes that bundle format: one document per Data Mart, a ## Overview block, a # Schema table, and a ## Joins section. Export OKF on the canvas produces exactly this format, so anything you generate here round-trips.

2. Two ways to provide a model

Both use the same per-document format (section 3) and differ only in packaging:

• A — Single file / paste. Put every document in one .md (or paste them), each preceded by a marker comment <!-- path/slug.md --> that separates the docs:

  <!-- shop/customers.md -->
  ---
  ...frontmatter...
  ---
  # Customers
  ...
  
  <!-- shop/orders.md -->
  ---
  ...frontmatter...
  ---
  # Orders
  ...

  The <!-- path --> markers are this canvas's convention for packing several documents into one file.
• B — ZIP bundle. A folder with an index.md plus one <slug>.md per Data Mart, zipped. This is exactly what Export OKF downloads and matches the OWOX/models layout. The user uploads the single .zip in Import OKF and the whole model loads at once — no need to upload files one by one.

Pick whichever fits your workflow; the canvas imports both.

3. Anatomy of a Data Mart document

---
type: "OWOX Data Mart"
title: "Orders"
description: "One row per order"
tags: ["owox", "sql"]          # 2nd tag = input source (lowercased), used as a fallback
---

# Orders

One row per order, used for revenue and retention.

## Overview

- **ID:** `—`                  # leave as — ; filled after Push to OWOX
- **Status:** DRAFT
- **Definition type:** SQL      # SQL | CONNECTOR | VIEW | TABLE — sets the input source
- **Storage:** —               # leave as — ; the canvas applies the selected storage

# Schema

| Column | Type | Description |
|--------|------|-------------|
| `id` | STRING | PK. Unique order identifier |
| `customer_id` | STRING | FK to [Customers](./customers.md) |
| `order_date` | DATE | |
| `total` | FLOAT | Order total, gross |

## Definition

```sql
SELECT id, customer_id, order_date, total FROM `project.dataset.orders`
```

## Joins

- [Customers](./customers.md) — `customer_id = id`

Frontmatter fields

• type — always "OWOX Data Mart".
• title — human title (shown on the node). The relationship id is the title's slug — lowercase, spaces → hyphens (e.g. Order Items → order-items.md) — so keep titles distinct.
• description — one-line summary (optional).
• tags — ["owox", "<input source lowercased>"], e.g. ["owox","view"].

No owox: frontmatter block is needed anymore. Identity (input source, status, id) lives in the ## Overview section. The older format — an owox: block plus a 5-column | Column | Type | PK | Alias | Description | schema — still imports for backward compatibility, but prefer the format above.

4. Output schema (fields)

List the mart's columns in a # Schema table with three columns:

• Column — field name in backticks (required).
• Type — one of the allowed types below (required).
• Description — a short note (optional). Two markers live here:
  • Primary key — start the Description with PK. (e.g. PK. Unique id).
  • Foreign key — write FK to [Target Title](./target-slug.md).

Optionally add a 4th Alias column — a business-friendly label for the field. Put it between Type and Description and leave the cell empty for fields that don't need one. The canvas shows the alias instead of the raw column name on the node and pushes it to OWOX; Export OKF emits this column only when at least one field has an alias.

# Schema

| Column | Type | Alias | Description |
|--------|------|-------|-------------|
| `id` | STRING | | PK. Unique order identifier |
| `total` | FLOAT | Order Total | Order total, gross |

Preferred types (the set OWOX accepts, verified against its schema validation):

STRINGINTEGERFLOATNUMERICBOOLEANDATETIMEDATETIMETIMESTAMPBYTESGEOGRAPHYJSON

Other spellings are normalised on import — int64, bool, text, decimal(10,2), timestamptz, ARRAY<STRING> and Snowflake's VARIANT all land on the right type, and anything unrecognised becomes STRING. So an odd cell costs you one field, never the whole schema.

5. Definition (optional)

Optionally include a ## Definition fenced code block. Its meaning depends on the Definition type in Overview:

• SQL — a SQL query (use a ```sql fence), e.g. SELECT … FROM `project.dataset.table`.
• TABLE — a fully-qualified table name, e.g. project.dataset.table.
• VIEW — a fully-qualified view name, e.g. project.dataset.view (a reference, not a SQL query).
• CONNECTOR — omit; configured in OWOX after creation.

6. Relationships (Joins)

Declare joinable relationships in a ## Joins section on the source mart. Each line links to the target document and states the join condition:

## Joins

- [Customers](./customers.md) — `customer_id = id`
- [Products](./products.md) — `product_id = id`

• Format: - [Target Title](./target-slug.md) — `leftField = rightField`. Multiple keys: comma-separate the backticked pairs.
• The filename slug is the target title lowercased with spaces → hyphens (e.g. Order Items → order-items.md).
• Join fields should exist in both marts' # Schema. It also helps to mark the source field with FK to [Target](./target-slug.md) in its Description.
• Bidirectional relationship: add a matching Joins line in both documents (with the key sides swapped). The canvas renders it as a double-headed arrow.
• Cardinality (optional): append a classic ER cardinality after the keys — - [Target](./target-slug.md) — `leftField = rightField` [1:N]. Allowed: 1:1, 1:N, N:1, N:N, oriented source → target. It is for modeling/visualization only (shown as a pill on the canvas); it is ignored by OWOX (its SQL aggregates) and does not affect Push.

7. Complete minimal example

<!-- shop/customers.md -->
---
type: "OWOX Data Mart"
title: "Customers"
tags: ["owox", "view"]
---
# Customers

## Overview
- **Definition type:** VIEW

# Schema
| Column | Type | Description |
|--------|------|-------------|
| `id` | STRING | PK. Unique customer id |
| `email` | STRING | Primary contact email |

<!-- shop/orders.md -->
---
type: "OWOX Data Mart"
title: "Orders"
tags: ["owox", "view"]
---
# Orders

## Overview
- **Definition type:** VIEW

# Schema
| Column | Type | Description |
|--------|------|-------------|
| `id` | STRING | PK. Unique order id |
| `customer_id` | STRING | FK to [Customers](./customers.md) |

## Joins
- [Customers](./customers.md) — `customer_id = id`

8. Workflow

1. Generate the documents above for the user's domain — as a single file with <!-- path --> markers, or as a ZIP bundle.
2. User opens Import OKF, pastes the text / uploads the .md / uploads the .zip, clicks Import.
3. The canvas renders nodes + relationships; the user reviews and tweaks.
4. User picks a Storage and clicks Push to OWOX → draft Data Marts, output schemas and joinable relationships are created in the OWOX project.
5. Optional: Export OKF downloads the model as a .zip bundle in this same format; re-importing that .zip restores the model.