---
name: scaffold-data-transformation-repo
description: Scaffold a new Gemma data-transformation (dbt) repository from the canonical my_dbt_project template in the gemma-best-practices repo, then customize it for the client (project name, client name, data warehouse).
disable-model-invocation: true
argument-hint: "[project_name] [client_name]"
---

# Scaffold a Data-Transformation Repo

Bootstrap the current (empty or near-empty) repository into a standard Gemma dbt
data-transformation project by copying the canonical `2_transform/my_dbt_project`
template from the [gemma-best-practices](https://github.com/Gemma-Analytics/gemma-best-practices)
repo and customizing it for the client.

Parse `$ARGUMENTS` as: first token = `<project_name>` (snake_case dbt project name,
e.g. `rotop`), second token = `<client_name>` (human-readable, e.g. `Rotop`). Both are
optional — if missing, infer sensible defaults from the repo/folder name and confirm
them with the user before writing files.

## What this skill does

1. Copies the canonical scaffold (dbt_project.yml, packages.yml, mock profiles.yml,
   `.gitignore`, PR template, `pre-push` hook, `.cursorrules`, README, style guide, the
   standard macros, the base/interim/reporting model layers, and the placeholder dirs)
   into the current repo.
2. Replaces the `my_dbt_project` / client-name placeholders with the real project and
   client names, and pins `require-dbt-version` to the latest stable dbt Core.
3. Adjusts the template for the target data warehouse (Snowflake by default; strips the
   Snowflake-only pieces for BigQuery).
4. Installs the `pre-push` git hook, creates a project-local virtualenv, installs dbt +
   the warehouse adapter, and resolves packages with `dbt deps`.
5. For Snowflake, helps the developer set up their RSA key pair and a working
   `profiles.yml`.

## Prerequisites

- The current directory is the target repo and is **empty or nearly empty** (only
  `.git`, `.claude`, or a bare README). If it already contains a dbt project, stop and
  ask the user — do not overwrite existing work.
- Read/clone access to `github.com/Gemma-Analytics/gemma-best-practices` (Gemma VPN /
  SSH or HTTPS as configured).
- `git` available. `dbt` + the relevant adapter (`dbt-snowflake` or `dbt-bigquery`)
  installed for the final verification step.

## Step 1 — Copy the canonical scaffold

Prefer a local clone if one already exists, otherwise do a shallow clone into a temp
directory and copy the template contents (including dotfiles) into the current repo:

```bash
tmp=$(mktemp -d)
git clone --depth 1 https://github.com/Gemma-Analytics/gemma-best-practices.git "$tmp"
cp -r "$tmp/2_transform/my_dbt_project/." .
rm -rf "$tmp"
```

`cp -r <src>/. <dest>` copies dotfiles (`.gitignore`, `.github/`, `.cursorrules`) too.
Do not overwrite an existing `README.md` without confirming with the user first.

## Step 2 — Customize the project identity

Replace the template project name `my_dbt_project` with `<project_name>` and the client
placeholder with `<client_name>` in these files:

- **`dbt_project.yml`**
  - `name: 'my_dbt_project'` → `name: '<project_name>'`
  - `profile: 'my_dbt_project'` → `profile: '<project_name>'`
  - Under `dispatch: search_order:`, `<YOUR_PROJECT_NAME>` → `<project_name>`
  - Under `models:`, the `my_dbt_project:` key → `<project_name>:`
  - `require-dbt-version`: bump from the template default to the **latest stable dbt
    Core**. Look it up at scaffold time (do not hard-code), filtering out pre-releases:
    ```bash
    curl -s https://pypi.org/pypi/dbt-core/json \
      | python3 -c "import sys,json,re; r=json.load(sys.stdin)['releases']; \
    v=sorted((x for x in r if re.fullmatch(r'\d+\.\d+\.\d+',x)), key=lambda s:tuple(map(int,s.split('.')))); print(v[-1])"
    ```
    Then set the range with the **minor pinned as the upper bound** (patches allowed):
    `['>=<major>.<minor>.0', '<<major>.<minor+1>.0']` — e.g. latest `1.11.12` →
    `require-dbt-version: ['>=1.11.0', '<1.12.0']`.
- **`profiles.yml`** (mock, git-ignored): `[project_name]` → `<project_name>`. This file
  is only a reference — the developer's real `profiles.yml` lives outside the repo.
- **`README.md`**: replace every `<mark>…</mark>` placeholder — `[CLIENT NAME]`,
  `<client_name>`, repo/DWH links, GitHub-vs-GitLab notes, dbt adapter — with
  client-specific values. Some you can infer:
  - Clone URL: read it from `git remote get-url origin` if a remote is set.
  - GitHub vs GitLab: pick based on which of `.github/` or `.gitlab/` exists.
  - dbt adapter / "Snowflake profiles" notes: resolve from the chosen warehouse (Step 3).

  A few need Gemma-internal info. Resolve them like this:
  - **DWH link** (README line ~8, `[DWH](<mark>add link</mark>)`): **ask the user** for
    the Snowflake DWH/access doc URL and substitute it.
  - **Snowflake-management repo link** (README line ~44): auto-detect it. Scan the
    sibling directories of this repo (`../*`) for a git repo containing a
    `permifrost.yml` at its root — that is the snowflake-management repo. Read its remote
    with `git -C <dir> remote get-url origin` and convert SSH to a web URL
    (`git@github.com:ORG/REPO.git` → `https://github.com/ORG/REPO`). Substitute that for
    the `[snowflake management repo]([<mark>add link to repo]</mark>)` placeholder.
    - If **no** sibling has `permifrost.yml`, fall back to a name match
      (`*infra*`, `*infrastruc*`); if still none, ask the user.
    - If **more than one** matches, ask the user which repo to use.
  - **Example-PR link** (README line ~44): cannot be inferred — ask the user, or leave
    the `<mark>…</mark>` in place until they provide it.

  Also fix template glitches while here: the RSA `openssl` commands should all use the
  same `<project_name>_<your_username>_snowflake` basename, and the README ships a
  duplicated "Merge the pull request" line — remove the duplicate.

Keep the `<project_name>` in snake_case everywhere; it must be identical in all four
`dbt_project.yml` spots or dispatch/model configs will silently not apply.

## Step 3 — Adjust for the data warehouse

Ask the user which warehouse this project targets (default: **Snowflake**).

- **Snowflake** — keep everything as-is. The template already includes
  `dbt_snowflake_monitoring`, `query-comment`, `+copy_grants`, and the
  `ALTER SESSION SET TIMEZONE` sql_headers.
- **BigQuery / other** — remove the Snowflake-only pieces (each is flagged in the
  template with a `### remove if not Snowflake` / `### valid only for Snowflake` comment):
  - `packages.yml`: drop the `get-select/dbt_snowflake_monitoring` package.
  - `dbt_project.yml`: drop the `dbt_snowflake_monitoring` search-order entry, the
    `query-comment` block, `+copy_grants`, the `dbt_snowflake_monitoring` models block,
    and the `ALTER SESSION SET TIMEZONE` `+sql_header` lines.

## Step 4 — Cursor rules

If the team uses Cursor with these rules, keep `.cursorrules` and remove the
"[Remove section if not in use]" note from the README's Cursor Rules section. Otherwise
delete `.cursorrules` and that README section.

## Step 5 — Install the pre-push hook

The `hooks/pre-push` script runs `dbt build` on changed models (+ up/downstream) before
every push. Install it the way the README documents — copy it into `.git/hooks/` and make
both copies executable:

```bash
cp hooks/pre-push .git/hooks/pre-push
chmod +x .git/hooks/pre-push hooks/pre-push
```

## Step 6 — Set up the environment and install dependencies

Create a project-local virtual environment and install dbt with the adapter matching the
warehouse chosen in Step 3:

```bash
python3 -m venv .venv
source .venv/bin/activate            # Windows: .venv\Scripts\Activate.ps1
pip install --upgrade pip
pip install dbt-core dbt-snowflake   # BigQuery → dbt-bigquery (match Step 3's warehouse)
```

Add `.venv/` to `.gitignore` so the environment is never committed. (Faster alternative:
`uv venv .venv && uv pip install dbt-core dbt-snowflake`.)

`pip install dbt-core` pulls the latest release, which should satisfy the
`require-dbt-version` range pinned in Step 2 — if it doesn't, reconcile the two. Then
resolve the dbt packages (no warehouse profile needed):

```bash
dbt deps   # fetches gemma-dbt-utils, dbt_utils (+ snowflake_monitoring)
```

Do **not** run `dbt debug`/`parse`/`build`/`run` here — those need the developer's real
(external) `profiles.yml`. If you cannot create the venv in this environment, at least
validate that `dbt_project.yml`, `packages.yml`, and `profiles.yml` are well-formed YAML,
and tell the user to run the steps above themselves.

## Step 7 — Snowflake key pair

For a Snowflake project, the developer needs an RSA key pair to authenticate. Finish by
asking the user:

> Do you already have a Snowflake RSA key pair for your user?

- **Yes** → the scaffold is complete. Stop here.
- **No** → invoke the `snowflake-generate-keypair` skill from the **gemma-snowflake**
  plugin (`gemma-snowflake:snowflake-generate-keypair`) to generate it, then stop.

Skip this step entirely for non-Snowflake warehouses.

## Step 8 — Create the developer's `profiles.yml`

Once a key pair exists (Step 7), create the developer's real dbt profile so they can
connect. Snowflake only.

- **Location:** the **project folder** (repo root), replacing the shipped mock
  `profiles.yml`. `profiles.yml` is already in `.gitignore`, so the real one is never
  committed. Run dbt against it with `dbt --profiles-dir . <cmd>` (or export
  `DBT_PROFILES_DIR=.`), since dbt otherwise looks in `~/.dbt/`.
- **Profile name:** the top-level key **must** equal `profile:` in `dbt_project.yml`
  (`<project_name>`), or dbt cannot resolve the profile. The template's
  `[CLIENT NAME]_analytics` is illustrative — substitute the matching name.
- **Fill what you can, ask for the rest.** Auto-fill from what's already known:
  `<project_name>` (profile name), `type: snowflake`, `target: dev`, `schema: DEV`,
  `threads`, and derive `role: USERROLE_<user>` and `database: DEV_<user>_ANALYTICS` once
  the username is known. **Ask the user** for everything else: `account`, `user`,
  `warehouse`, and `private_key_path` (where their `.p8` lives). Present sensible defaults
  where they exist (e.g. `warehouse: compute_xsmall`).
- **Template:**

  ```yaml
  <project_name>:
    target: dev
    outputs:
      dev:
        type: snowflake
        account: <account>                 # ask the user
        user: <your_snowflake_username>    # ask the user
        private_key_path: "/absolute/path/to/key.p8"   # ABSOLUTE path — see note below
        role: USERROLE_<your_snowflake_username>
        private_key_passphrase: "<private_key_passphrase>"
        database: DEV_<your_snowflake_username>_ANALYTICS
        warehouse: <warehouse, e.g. compute_xsmall>
        schema: DEV   # gets overwritten by the generate_schema_name macro
        threads: 4
  ```
- **`account` must be the Snowflake account identifier**, not the Snowsight app-URL slug.
  A DWH link like `https://app.snowflake.com/<slug>` does **not** give you the connector
  account — using `<slug>` (optionally guessing `<slug>.<region>`) causes
  `404 Not Found ... /session/v1/login-request` errors. Get the real value from Snowsight
  (account menu → **Copy account identifier**, form `ORGNAME-ACCOUNTNAME`) or
  `SELECT CURRENT_ORGANIZATION_NAME()||'-'||CURRENT_ACCOUNT_NAME();`. Prefer the hyphenated
  `ORG-ACCOUNT` identifier over the legacy `<locator>.<region>` form (region-agnostic).
- **`private_key_path` must be absolute.** dbt opens the key file directly and does **not**
  expand `~`, `$HOME`, or env vars — a value like `~/.dbt/<project>/key.p8` fails with
  `[Errno 2] No such file or directory: '~/.dbt/...'`. Resolve `~` to the real home path
  (e.g. `/home/<user>/.dbt/<project>/key.p8`) before writing it.
- **Secrets — never write the raw passphrase or private key into the file.** If the key
  lives in **1Password** (the usual Gemma setup), write a `profiles.yml` **template** with
  `op://` references and have the user materialize it with `op inject` (see the
  `gemma-1password` skill), e.g.:

  ```yaml
  private_key_passphrase: "op://<vault>/<item>/passphrase"
  ```
  ```bash
  op inject -i profiles.yml.tpl -o profiles.yml   # user runs this, in the repo root
  ```
  Alternatively reference an env var: `"{{ env_var('DBT_KEY_PASSPHRASE') }}"`. Materialize
  the `.p8` for `private_key_path` from 1Password (`op read`) — do not paste key contents.
- Verify with `dbt debug`, which connects to Snowflake. Run it only where warehouse
  access is permitted (not on the Anthropic-API connection).

> Note on restricted connections: the Gemma secrets-policy hook guards any path named
> `profiles.yml` (Bash/Read), so you cannot inspect it afterwards. Writing the file with the
> Write tool still works. Keep the passphrase/key out of it (env_var or `op://`); never
> paste secret values.

## Placeholder reference

| Placeholder                | Where                                   | Replace with        |
|----------------------------|-----------------------------------------|---------------------|
| `my_dbt_project`           | dbt_project.yml (name, profile, models) | `<project_name>`    |
| `<YOUR_PROJECT_NAME>`      | dbt_project.yml dispatch search_order   | `<project_name>`    |
| `[project_name]`           | profiles.yml (mock)                     | `<project_name>`    |
| `<mark>[CLIENT NAME]</mark>` | README.md                             | `<client_name>`     |
| `<mark>…</mark>` blocks    | README.md                               | client links/values |

## Notes

- The scaffold ships an `models/example/` folder with two starter models — delete it
  once real base models exist (or immediately, if the user prefers a clean start).
- Empty dirs (`analyses/`, `seeds/`, `snapshots/`, `tests/`, `macros/`) keep their
  `.gitkeep` so git tracks them.
- After scaffolding, the usual next steps are the sibling gemma-dbt skills:
  `create-source-file` → `create-base-models`.