---
name: comment-unused-fields
description: Profile source tables with dbt-profiler, then comment out any base model fields that have only one distinct value across the whole table.
disable-model-invocation: true
argument-hint: "[source_name] [table_name]"
---

# Comment Out Single-Value (Unused) Fields in Base Models

Use `dbt-profiler` to profile each source table referenced by a base model, identify columns with only one distinct value, and comment them out in the SQL. Update the YAML documentation accordingly.

`$ARGUMENTS` is optional:
- No arguments → process **all** `models/base/**/*.sql` files.
- One token → process all SQL files under `models/base/<source_name>/`.
- Two tokens → process only `models/base/<source_name>/base_<source_name>_<table_name>.sql`.

## What this skill does

1. Finds base model SQL files matching the given scope.
2. For each file, resolves the source table it reads from and runs `dbt run-operation print_profile` against that table.
3. Identifies columns where `distinct_count = 1`.
4. Rewrites the SQL to move those columns to a commented-out `-- Single value` section at the bottom of the `SELECT`.
5. Updates the corresponding `_<source_name>.yml` to record the finding in the column metadata.

## Prerequisites

Check `packages.yml` for `dbt-profiler`. If missing, add it and run `dbt deps` before proceeding:

```yaml
packages:
  - package: data-mie/dbt_profiler
    version: ">=1.0.0,<2.0.0"
```

```bash
dbt deps
```

## Procedure

### 1. Resolve scope

Parse `$ARGUMENTS` (may be empty). Build the list of `.sql` files to process:
- No args: `glob("models/base/**/*.sql")`
- One arg `<source>`: `glob("models/base/<source>/*.sql")`
- Two args `<source> <table>`: `["models/base/<source>/base_<source>_<table>.sql"]`

Skip any file whose name does not match the `base_<source>_<table>.sql` pattern (e.g. intermediate or reporting models accidentally in scope). If no files are found, stop and tell the user.

### 2. Resolve source table metadata for each file

For each file at `models/base/<source>/base_<source>_<table>.sql`:

a. Read the SQL file and find the `{{ source('...', '...') }}` macro call. Extract `source_name` and `table_name` from it.

b. Read `models/base/<source>/_<source>.yml`. Under `sources[*]` where `name = <source_name>`, extract:
   - `database` — the BigQuery project / database
   - `schema` — the BigQuery dataset / schema

c. Also extract the column list for this table from the `sources[*].tables[*]` entry (to cross-reference later).

### 3–7. Profile and rewrite SQL — parallel subagents

After resolving metadata for all files, spawn **one subagent per model** and run them all concurrently. Each subagent is responsible for steps 3 through 7 for its assigned model.

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

> Profile the source table for `<sql_file_path>` and comment out any single-value columns in the SQL. Here is everything you need — do not re-resolve it from the filesystem:
>
> - SQL file: `<sql_file_path>`
> - Source name: `<source_name>`, table name: `<table_name>`
> - Database: `<database>`, schema: `<schema>`
>
> **Step 3 — Profile the source table**
>
> Run:
> ```bash
> dbt run-operation print_profile \
>   --args '{"relation_name": "<table_name>", "schema": "<schema>", "database": "<database>"}'
> ```
> Capture stdout. The output is a Markdown table with a header row like:
> ```
> | column_name | data_type | not_null_proportion | distinct_proportion | distinct_count | is_unique | min | max | avg | ... |
> ```
> Parse every data row and build a map: `column_name → distinct_count`.
> If the command fails or returns no rows, stop and return an error result (see return format below).
>
> **Step 4 — Identify single-value columns**
>
> Collect all columns where `distinct_count = 1` into `single_value_columns`.
>
> Do NOT flag:
> - dlt internal columns (`_dlt_id`, `_dlt_load_id`, `_dlt_root_id`, `_dlt_parent_id`, `_dlt_list_idx`)
> - Columns already commented out in the SQL (from a previous run of this skill or `base-models-comment-piis`)
>
> If `single_value_columns` is empty, stop and return a skipped result.
>
> **Step 5 — Match profile results to the base model SELECT**
>
> Read the base model SQL. Locate the SELECT column list (in the `renamed` CTE or equivalent). For each SELECT item, the source column name is the raw identifier before any `AS` alias and before any table-alias prefix (e.g. `source.status` → `status`). Match against `single_value_columns`.
>
> Produce two lists:
> - `active_columns` — not in `single_value_columns` (plus any already-commented blocks, left as-is)
> - `new_single_value_columns` — source column names found in `single_value_columns`
>
> **Step 6 — Rewrite the SQL**
>
> Reconstruct the SELECT block in this order:
> 1. Active (non-commented) columns in their original order.
> 2. Any already-existing `-- Single value` block (replace it — recompute from current profile).
> 3. Any already-existing `-- PII` block — preserve it exactly.
>
> The new `-- Single value` section:
> ```sql
>     -- Single value (all rows share the same value — excluded from base model output)
>     -- , <col1>
>     -- , <col2>
> ```
>
> Rules: leading commas, 2-space indentation. `-- PII` block always before `-- Single value` block. Each commented column line is prefixed with `-- ` (two dashes, one space). Preserve all other parts of the file exactly.
>
> **Step 7 — Write the updated SQL**
>
> Write the rewritten SQL back to `<sql_file_path>`. Show a compact diff of the column list only.
>
> **Return this JSON result** when done (the parent agent will use it):
> ```json
> {
>   "sql_file": "<sql_file_path>",
>   "source_name": "<source_name>",
>   "table_name": "<table_name>",
>   "new_single_value_columns": ["col1", "col2"],
>   "status": "done" | "skipped" | "error",
>   "message": "<optional detail>"
> }
> ```

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

### 8. Update the YAML — single agent

Collect the JSON results from every subagent. Discard results with `status: "skipped"` or `status: "error"`. For the remaining results, group them by `source_name` (since multiple models may share the same `_<source>.yml` file).

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

> Update YAML documentation for the single-value columns found by the profiling agents. Here is the full list of changes needed, grouped by YAML file:
>
> <for each affected source_name>
> **File:** `models/base/<source_name>/_<source_name>.yml`
> Models to update:
> - `base_<source_name>_<table_name>`: mark columns `[col1, col2, ...]` as single-value
> </for each>
>
> For each `(model, column)` pair:
> 1. Find the `models:` entry matching `name: base_<source_name>_<table_name>`.
> 2. Find the column entry under `columns:`.
> 3. Add `meta: {single_value: true}` (merge with existing `meta` — do not overwrite `pii: true`).
> 4. Prepend `"[Single value — excluded from model output] "` to the existing `description` string.
> 5. If no column entry exists, add one with the metadata and a placeholder description.
> 6. Do not modify any other columns.
>
> Write each YAML file back once all its columns are updated.

### 9. Validate

After all subagents (steps 3–7 and step 8) have completed, run:

```bash
dbt compile
```

Fix any compilation errors before finishing.

### 10. Report

Print a summary table:

```
File                                              Single-value columns found
------------------------------------------------  --------------------------
models/base/foo/base_foo_orders.sql               currency_code, source_system
models/base/foo/base_foo_customers.sql            country_code
models/base/bar/base_bar_events.sql               0 (skipped — no single-value columns)
```

If no single-value columns were found in any file, say so clearly and exit without modifying anything.

## Output

- Modified SQL files (in place).
- Modified YAML file(s) (in place).

## Notes

- **Do NOT remove columns** — keep them as commented lines so developers can see what exists at source. The goal is a clean base model output, not deletion.
- Profiling runs against the **source table** (the raw warehouse table), not the base model. The profiling reflects the full current state of the source data.
- If a column appears in `single_value_columns` but is already in a `-- PII` block, leave it in `-- PII` — do not duplicate it.
- If this skill is re-run after data changes (a previously constant column now has multiple values), remove it from the `-- Single value` block and restore it to the active list. The recompute-from-scratch approach in step 6 handles this automatically.
- `print_profile` does not work in dbt Cloud (console printing is disabled there). This skill is intended for local execution or CI environments where stdout is accessible.
- Always run `dbt compile` at the end, even when only one file was changed.