Zo Dataset Creator

---
name: zo-dataset-creator
description: >
  Create Zo Datasets correctly formatted for the Zo UI. Use when creating a
  new dataset from scratch, setting up a dataset with proper datapackage.json,
  schema.yaml, and data.duckdb structure, fixing existing datasets that don't
  display tables in the Zo UI, or generating schema.yaml from an existing
  DuckDB database. Handles common pitfalls of manual schema formatting and
  ensures all required files are present and correctly structured.
compatibility: Created for Zo Computer
metadata:
  author: skeletorjs
  category: Community
---

# Zo Dataset Creator

Create Zo Datasets with correct formatting for the Zo Datasets UI.

## Quick Start

### Creating a New Dataset

Use `scripts/create_dataset.py` to scaffold a new dataset:

```bash
python3 scripts/create_dataset.py <dataset-name>
```

This creates:
- `datapackage.json` with required metadata
- `generate_schema.py` for schema generation
- `schema.yaml` (auto-generated after database creation)
- `ingest/`, `source/`, `assets/` directories
- `README.md` and `PROCESS.md` templates

### Fixing an Existing Dataset

If tables don't show in the Zo UI:

1. Verify `datapackage.json` exists
2. Ensure `data.duckdb` is a valid database
3. Re-generate schema: `python3 generate_schema.py`

See [TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) for common issues.

---

## Dataset Structure

A valid Zo Dataset requires:

```
dataset-name/
├── datapackage.json          # Required: Dataset metadata
├── schema.yaml                # Required: Auto-generated from database
├── data.duckdb                # Required: DuckDB database
├── generate_schema.py         # Required: Script to generate schema
├── README.md                  # Recommended: Dataset documentation
├── PROCESS.md                 # Recommended: Ingestion instructions
├── ingest/                    # Optional: Ingestion scripts
├── source/                    # Optional: Raw source files
└── assets/                    # Optional: Generated files
```

---

## Critical: Schema Format

**ALWAYS auto-generate `schema.yaml` from the database. Never write it manually.**

The Zo UI expects this format (list of tables with `name:` keys):

```yaml
tables:
- name: my_table
  row_count: 10
  columns:
  - name: id
    type: VARCHAR
  - name: title
    type: VARCHAR
  - name: created_at
    type: TIMESTAMP
```

This format is generated by `generate_schema.py`.

**Do NOT use manual YAML format** (nested dictionaries):

```yaml
# WRONG - Will not display in Zo UI
tables:
  my_table:
    columns:
      id:
        type: VARCHAR
```

---

## Using the Scripts

### `create_dataset.py`

Create a new dataset scaffold:

```bash
python3 scripts/create_dataset.py my-dataset
```

Creates the dataset in `Datasets/my-dataset/` with all required files.

### `generate_schema.py`

Generate schema from an existing database:

```bash
cd /home/workspace/Datasets/my-dataset
python3 generate_schema.py
```

**When to run:**
- After creating `data.duckdb`
- After modifying table structure (add/remove columns, create tables)
- After changing COMMENT annotations
- After any database schema changes

### `validate_dataset.py`

Validate a dataset structure:

```bash
python3 scripts/validate_dataset.py /home/workspace/Datasets/my-dataset
```

Checks:
- `datapackage.json` exists and is valid
- `data.duckdb` exists and is readable
- `schema.yaml` exists and is in correct format
- Tables in schema match tables in database

---

## Workflow Examples

### Example 1: New Dataset from CSV

```bash
# 1. Create scaffold
python3 scripts/create_dataset.py sales-data

# 2. Copy CSV to source/
cp sales.csv /home/workspace/Datasets/sales-data/source/

# 3. Create database and import data
cd /home/workspace/Datasets/sales-data
python3 -c "
import duckdb
con = duckdb.connect('data.duckdb')
con.execute(\"CREATE TABLE sales AS SELECT * FROM 'source/sales.csv'\")
con.execute(\"COMMENT ON TABLE sales IS 'Monthly sales data'\")
con.close()
"

# 4. Generate schema
python3 generate_schema.py

# 5. View in Zo UI at /?t=datasets
```

### Example 2: Fixing Broken Dataset

```bash
# 1. Validate to identify issues
python3 scripts/validate_dataset.py /home/workspace/Datasets/broken-dataset

# 2. If schema format is wrong, re-generate
cd /home/workspace/Datasets/broken-dataset
python3 generate_schema.py

# 3. Validate again
python3 scripts/validate_dataset.py /home/workspace/Datasets/broken-dataset
```

### Example 3: Adding Comments to Tables

```python
import duckdb

con = duckdb.connect('data.duckdb')

# Add table comment
con.execute("COMMENT ON TABLE videos IS 'YouTube videos from watchlist playlist'")

# Add column comments
con.execute("COMMENT ON COLUMN videos.title IS 'Video title from YouTube'")
con.execute("COMMENT ON COLUMN videos.view_count IS 'Total view count'")

con.close()

# Re-generate schema to include comments
# (run: python3 generate_schema.py)
```

---

## Best Practices

### Use COMMENT Annotations

Add inline comments to tables and columns — they're extracted into `schema.yaml`:

```sql
CREATE TABLE videos (
    id VARCHAR PRIMARY KEY COMMENT 'YouTube video ID',
    title VARCHAR COMMENT 'Video title from YouTube metadata',
    published_at TIMESTAMP COMMENT 'When the video was published'
) COMMENT 'Collection of videos from the watchlist playlist'
```

### Keep Schema in Sync

Always re-run `generate_schema.py` after database changes:
- Adding/removing columns
- Changing column types
- Creating new tables
- Updating COMMENT annotations

### Use Descriptive Names

- Tables: `playlist_videos`, `user_sessions`, `transactions`
- Columns: `video_id`, `created_at`, `total_amount`
- Use snake_case consistently

### Document in README.md

Include:
- Purpose of the dataset
- What data it contains
- How to query it
- Example queries
- Business rules and caveats

---

## Common Pitfalls

See [TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) for detailed solutions to:
- Tables not showing in Zo UI
- Invalid schema.yaml format
- Missing datapackage.json
- Stale schema after database changes
- Locked database files

---

## Reference Materials

- [TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) - Common issues and solutions
- [SCHEMA_GUIDE.md](references/SCHEMA_GUIDE.md) - Schema formatting details
- [DATAPACKAGE_SPEC.md](references/DATAPACKAGE_SPEC.md) - datapackage.json reference
- [DUCKDB_BASICS.md](references/DUCKDB_BASICS.md) - DuckDB usage examples