Skip to content
Skills Library
Data & analytics · Building & coding

effective-dbt-sql

/effective-dbt-sql

Use when writing or modifying dbt model SQL — deciding whether to add a subquery or reuse an existing dimension/metric, structuring a query, joining models, or refactoring a metric's SQL. Encodes SQL semantic-correctness rules: reuse existing fields, prefer CTEs over correlated subqueries, and make joins and column lists explicit.

Repository
lightdash ↗
Licence
MIT
Last repository change
25.10.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
3
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 25.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Use when writing or modifying dbt model SQL — deciding whether to add a subquery or reuse an existing dimension/metric, structuring a query, joining models, or refactoring a metric's SQL. Encodes SQL semantic-correctness rules: reuse existing fields, prefer CTEs over correlated subqueries, and make joins and column lists explicit.

Original author description

Use when writing or modifying dbt model SQL — deciding whether to add a subquery or reuse an existing dimension/metric, structuring a query, joining models, or refactoring a metric's SQL. Encodes SQL semantic-correctness rules: reuse existing fields, prefer CTEs over correlated subqueries, and make joins and column lists explicit.

How it works

  1. Scope
  2. The rules
  3. Not in scope
Original SKILL.md headings · 32 lines, 427 words
SKILL.md preview
---
name: effective-dbt-sql
description: Use when writing or modifying dbt model SQL — deciding whether to add a subquery or reuse an existing dimension/metric, structuring a query, joining models, or refactoring a metric's SQL. Encodes SQL semantic-correctness rules: reuse existing fields, prefer CTEs over correlated subqueries, and make joins and column lists explicit.
---

# Effective dbt SQL

Write dbt model SQL that is correct, readable, and idiomatic. This skill is about the **semantics** of the SQL you write — not naming or formatting.

## Scope

**Use this when** you are about to write or change SQL inside a dbt model (`.sql`), or a metric/dimension whose value is defined by SQL.

**This skill covers semantics only.** It does **not** prescribe naming schemes, file layout, or indentation. For those, follow the rule below.

> **Match the target repo first.** These are fallback defaults, not mandates. Before applying any rule, read the models around the one you are changing and match the repo's established conventions where they differ. Apply these rules to new or changed SQL only — do **not** reformat, rename, or restructure existing code that already works.

## The rules

1. **Reuse before you re-derive.** If the value you are asked for is already expressed by an existing dimension or metric — or by two of them combined — compose those fields instead of writing new SQL to recompute it. Read the model's existing dimensions/metrics before adding one. See [reuse-over-subqueries](./resources/reuse-over-subqueries.md).

2. **No correlated subqueries.** Never compute a value with a subquery that references the outer query's rows (a per-row correlated subquery in `SELECT` or `WHERE`). Reuse an existing field, or join to an aggregated CTE instead. This is the single most common mistake. See [cte-pipelines](./resources/cte-pipelines.md).

3. **CTEs over nested subqueries.** Structure a query as a pipeline of named CTEs, each referenced by the next, ending in a final `select`. Prefer this over subqueries nested inside `from`/`where`. See [cte-pipelines](./resources/cte-pipelines.md).

4. **Explicit joins.** Always state the join type (`inner`/`left`/…) and the `on` condition. Never comma-join. Know the grain: a join that fans out rows silently changes every downstream aggregate.

5. **Explicit columns.** Select named columns rather than `select *` when adding or changing what a model emits, so a change to an upstream model can't silently alter this model's output. (A pass-through `select * from final` at the very end of a CTE pipeline is fine — the columns are already pinned by the CTEs.)

6. **Don't break references.** Before editing a model's SQL, read the `source()`/`ref()` models it selects from. Do not rename or remove a column that downstream models or `schema.yml` reference without checking the impact first.

## Not in scope

- **Emitted-type / casting safety** (e.g. boolean-vs-numeric column types): follow the **warehouse skill** the system prompt points you to (`/home/user/.lightdash-skills/`). This skill does not restate casting rules.
- **Naming and formatting conventions:** match the target repo (see the rule above).
Files in this package (3)
  • SKILL.md 3 KB
  • resources/cte-pipelines.md 2 KB
  • resources/reuse-over-subqueries.md 2 KB

Versions

The database has no other skills named effective-dbt-sql.

Database snapshot: 13.11.2026 · package version cfa5b2d3ca8d (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 95dbt-modeling
    Writing dbt models with proper CTE patterns, SQL structure, and layer-specific templates. Use this skill when writing or refactoring dbt models, implementing CTE patterns, creating staging/intermediate/mart models, or ensuring proper SQL structure and dependencies.
  • 95dbt-bigquery
    Expert guidance for creating, modifying, and optimizing dbt pipelines for BigQuery. Use this skill whenever user asks for generating or modifying a dbt model or project. Activate this skill when the user - Creates, modifies, or troubleshoots **dbt models or pipelines** - Needs…
  • 100dbt-bigquery
    Expert guidance for creating, modifying, and optimizing dbt pipelines for BigQuery. Use this skill whenever user asks for generating or modifying a dbt model or project. Activate this skill when the user - Creates, modifies, or troubleshoots **dbt models or pipelines** - Needs…
  • 100creating-dbt-models
    Creates dbt models following project conventions. Use when working with dbt models for: (1) Creating new models (any layer - discovers project's naming conventions first) (2) Task mentions "create", "build", "add", "write", "new", or "implement" with model, table, or SQL (3)…
  • 100dbt
    dbt (data build tool) project structure, SQL patterns, and best practices for the analytics warehouse. Use this skill when working with dbt models, testing SQL queries, or creating new analytical tables.
  • 100using-dbt-for-analytics-engineering
    Builds and modifies dbt models, writes SQL transformations using ref() and source(), creates tests, and validates results with dbt show. Use when doing any dbt work - building or modifying models, debugging errors, exploring unfamiliar data sources, writing tests, or evaluating impact of changes.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.