---
name: create-base-models
description: Generate dbt base model SQL and YAML files for all tables in one or more sources, using the dbt-codegen package.
disable-model-invocation: true
argument-hint: "<source_name> [source2 ...]"
---

# Create Base Models

Generate dbt base model SQL and YAML files for all tables in the sources provided in `$ARGUMENTS`.

Parse `$ARGUMENTS` as: one or more `<source_name>` tokens. Each token is a source name.

## What this skill does

Uses the [`dbt-codegen`](https://github.com/dbt-labs/dbt-codegen) package to scaffold base model SQL for each table, then:
1. Writes the SQL to `models/base/<source_name>/base_<source_name>_<table>.sql`
2. Appends model documentation and tests into the existing `models/base/<source_name>/_<source_name>.yml`

## Prerequisites

Check that `dbt-codegen` is listed in `packages.yml`. If it is missing, add it and run `dbt deps` before proceeding:

```yaml
# packages.yml
packages:
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<1.0.0"]
```

```bash
dbt deps
```

Also confirm that the source exists in a `_<source_name>.yml` file under `models/base/<source_name>/`. If it does not, stop and tell the user to run `/create-source-file` first.

## Procedure

### 1. Parse arguments

Split `$ARGUMENTS` on whitespace — every token is a `<source_name>`.

If no tokens are provided, stop and ask the user for at least one source name.

### 2. Read the source YAML for each source

For each `source_name`, read `models/base/<source_name>/_<source_name>.yml`.

- If the file does not exist, stop and tell the user to run `/create-source-file` first.
- Extract the list of all table names from the `tables:` key under the matching source entry. These are the `tables` to process for this source.

### 3. Filter out already-existing models

For each table discovered in step 2, check whether `models/base/<source_name>/base_<source_name>_<table>.sql` already exists.

- **Exists** → skip it entirely (no SQL generation, no YAML update). Collect these in a "skipped" list to report at the end.
- **Does not exist** → include it in the list of tables to generate.

If all tables for a source are already present, inform the user and move on to the next source.

### 4–6. Generate and write SQL — parallel subagents

After step 3, spawn **one subagent per table** across all sources and run them all concurrently. Each subagent handles SQL generation, post-processing, and writing for its assigned table.

**Instructions to pass to each subagent** (include all resolved metadata in the prompt):

> Generate the base model SQL for table `<table>` in source `<source_name>` and write it to disk. Here is everything you need — do not re-read the source YAML.
>
> **Step 4 — Generate the SQL**
>
> Run:
> ```bash
> dbt run-operation generate_base_model \
>   --args '{"source_name": "<source_name>", "table_name": "<table>"}'
> ```
> Capture stdout — it contains a `WITH source AS (...)` SQL block.
> If the command fails or produces no output, stop and return an error result (see return format below).
>
> **Step 5 — Post-process the SQL**
>
> Apply the project's coding standards:
> - Rename any columns that violate naming conventions (snake_case, boolean prefixes `is_/has_/was_/does_`, date suffixes `_on/_date`, timestamp suffixes `_at`). Add `AS` aliases for every renamed column.
> - Move the primary key column first, foreign keys second, then remaining attributes.
> - Replace any boilerplate `SELECT *` with an explicit column list.
> - Ensure SQL keywords are uppercase, indentation is 2 spaces, lines ≤ 88 characters.
> Do NOT add transformations beyond renaming and casting — base models must remain minimal.
>
> **Step 6 — Write the SQL file**
>
> Write the processed SQL to `models/base/<source_name>/base_<source_name>_<table>.sql`.
>
> **Return this JSON result** when done (the parent agent will use it for the YAML update):
> ```json
> {
>   "source_name": "<source_name>",
>   "table_name": "<table>",
>   "sql_file": "models/base/<source_name>/base_<source_name>_<table>.sql",
>   "primary_key": "<post-processed primary key column name>",
>   "columns": [
>     {"name": "<post-processed column name>", "description": "<inferred description>"},
>     ...
>   ],
>   "status": "done" | "error",
>   "message": "<optional detail>"
> }
> ```
>
> For `primary_key`: use the column named `<entity>_id`, or `id` if no prefixed id exists.
>
> For `columns`: list all post-processed column names with inferred descriptions using standard conventions (e.g. `created_at` → "Timestamp when the record was created.", `customer_id` → "Unique identifier for the customer."). Write `"TODO: add description."` when purpose cannot be inferred.

Wait for **all** subagents to finish before proceeding.

### 7. Update the source YAML — single agent

Collect the JSON results from every subagent. Discard results with `status: "error"`. Group the remaining results by `source_name` (multiple tables share the same `_<source_name>.yml`).

Spawn **one subagent** to handle all YAML updates sequentially across all affected files:

> Update the source YAML files with the base model entries produced by the SQL generation agents. Here is the full list of changes needed, grouped by file:
>
> <for each affected source_name>
> **File:** `models/base/<source_name>/_<source_name>.yml`
> Tables to add:
> - `<table>`: primary_key=`<pk>`, columns: `[{name, description}, ...]`
> - ...
> </for each>
>
> For each table, read the file first, then apply the update below:
>
> If a `models:` key already exists in the file, append to it; otherwise add it after the `sources:` block.
> Add one entry per table:
> ```yaml
> models:
>   - name: base_<source_name>_<table>
>     description: "<Inferred one-sentence description of the table.>"
>     columns:
>       - name: <primary_key>
>         description: "Unique identifier for the <entity>."
>         data_tests:
>           - unique
>           - not_null
>       - name: <other_column>
>         description: "<Inferred description.>"
> ```
> Rules: primary key column always has both `unique` and `not_null` tests. Cast all id fields to STRING. Use post-processed column names. If a `models:` entry for this table already exists, leave it as-is.
>
> Write each YAML file back once all its tables are updated.

### 8. Validate

Compile the project to confirm dbt can resolve the new models:

```bash
dbt compile
```

Fix any errors before finishing.

## Output

One SQL file per new table, plus one updated YAML per source. At the end, print a summary:

```
models/base/<source_name>/base_<source_name>_<table>.sql   # one per new table
models/base/<source_name>/_<source_name>.yml               # updated in place

Skipped (SQL file already exists):
  - base_<source_name>_<table>
  ...
```

## Notes

- If `$ARGUMENTS` is empty, ask the user for at least one source name before proceeding.
- Do not delete or overwrite existing files without user confirmation.
- Only `base_` models select from sources — never use `ref()` in a base model.
- Do not add joins, aggregations, or window functions — those belong in `interim_` models.