bootstrap

Files (1)

• SKILL.md

SKILL.md (first lines)

# Bootstrap a Semantic Layer

You are bootstrapping a semantic layer for airlayer. This means discovering what's in the user's database and generating `.view.yml` files that define dimensions, measures, and entities.

## Prerequisites

The user needs a `config.yml` with database connection details. If they don't have one, help them create it. The format is:

```yaml
databases:
  - name: <name>
    type: <postgres|snowflake|bigquery|duckdb|motherduck|gsheets|mysql|clickhouse|databricks|sqlite>
    # ... connection fields vary by type (see docs/agent-execution.md)
```

MotherDuck example:
```yaml
databases:
  - name: cloud
    type: motherduck
    token_var: MOTHERDUCK_TOKEN
    database: my_db
```

Google Sheets example (each entry under `sheets` becomes a queryable table):
```yaml
databases:
  - name: sheets
    type: gsheets
    token_var: GSHEET_TOKEN              # Google OAuth access token
    # key_file: ./service-account.json   # or a service-account JSON key
    sheets:
      orders: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID
      customers:                          # detailed form selects a tab/range
        url: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID
        sheet: Customers
        range: A1:F500
```

airlayer must be built with executor support: `cargo build --features exec` (or a specific driver like `exec-postgres`).

## Workflow

### Step 1: Introspect the schema

Run schema introspection to discover all tables, columns, and types:

```bash
airlayer inspect --schema --config <config.yml>
```

Optionally filter to a specific schema/dataset:
```bash
airlayer inspect --schema <schema_name> --config <config.yml>
```

This returns JSON with every table and column. Read the output carefully — it's your source of truth for what's available.

### Step 2: Ask the user which tables to model

Present the discovered tables to the user and ask which ones they want in the semantic layer. Don't model everything — focus on the tables they care about for analytics.

### Step 3: Generate .view.yml files

For each selected table, create a `.view.yml` file in the project directory. Follow these rules:

**Dimensions** (attributes to group/filter by):
- String columns → `type: string`
- Date columns → `type: date`
- Datetime/timestamp columns → `type: datetime`
- Boolean columns → `type: boolean`
- Numeric columns used for grouping (IDs, codes) → `type: string` or `type: number`

**Measures** (aggregations):
- Row count → `type: count` (no expr needed)
- Unique counts → `type: count_distinct` with `expr: <column>`
- Sums → `type: sum` with `expr: <column>`
- Averages → `type: average` with `expr: <column>`
- Computed measures → `type: sum` with `expr: "quantity * price"` etc.

**Entities** (join keys):
- Primary keys → `type: primary`, `key: <column>`
- Foreign keys → `type: foreign`, `key: <column>`
- Name entities after the concept they represent (e.g., `customer`, `order`), not the column name

**Naming conventions**:
- `name:` should be snake_case, semantic (e.g., `total_revenue` not `sum_amount`)
- `expr:` is the raw SQL expression — reference actual column names from the schema
- `description:` add for any non-obvious measures or computed fields

Example view:
```yaml
name: orders
description: "Sales orders with customer and product data"
dialect: postgres
datasource: warehouse
table: public.orders

entities:
  - name: customer
    type: foreign
    key: customer_id

dimensions:
  - name: order_id
    type: string
    expr: order_id

  - name: status
    type: string
    expr: status

  - name: created_at
    type: datetime
    expr: created_at

measures:
  - name: order_count
    type: count

From oxy-hq/airlayer, Apache-2.0.

More from oxy-hq/airlayer