Install any skill in seconds. Free to start, no credit card required.
Get Started Free →Build out the transformation layers (`staging/`, `intermediate/`) of a data product and run dbt against them, following project-wide conventions adapted from dbt's best practices (v1.12). Trigger when the user asks to "add a staging model", "build out the staging layer", "create an intermediate model", "refactor this output port into staging + intermediate", "make this model incremental", or "run dbt for this data product".
.claude/skills/hashgraph-online-dataproduct-dbt/SKILL.md| Model | Eval pass | Runs |
|---|---|---|
| gemini-3.6-flash | 100% | 6 |
| Test case | Without → With | Effect | Δ tokens | Δ turns |
|---|---|---|---|---|
| case-11 | ✗→✓ | ▲ Improved | 300% | 0% |
| case-04 | ✗→✓ | ▲ Improved | 121% | 0% |
| case-05 | ✗→✓ | ▲ Improved | 193% | 0% |
| case-06 | ✗→✓ | ▲ Improved | 486% | 0% |
| case-12 | ✗→✓ | ▲ Improved | 245% | 0% |
dataproduct-implement generates the ends of the pipeline: input-port sources (from access agreements) and output-port models (from the data contract). This skill fills in the middle, the staging/ and intermediate/ layers, and runs dbt against the result. The conventions below are adapted from dbt's structure and materialization best practices, and are meant to be edited by the organization adopting this plugin.
dataproduct-bootstrap first.dataproduct-implement first./review or /simplify, not this skill.These rules are the contract between this skill and the rest of the plugin. Organizations forking the plugin should treat this section as the place to encode their own style.
| Layer | Purpose | Materialization | References | Naming | |---|---|---|---|---| | models/input_ports/<op-id>.source.yaml | External raw data, one file per active access agreement | n/a (source) | n/a | source name = <provider-dp-id>_<provider-op-id> | | models/staging/stg_<provider-dp-id>__<table>.sql | One staging model per source table: rename, cast, light cleanup, dedup | view | {{ source(...) }} only | double underscore separates source from entity | | models/intermediate/int_<purpose>.sql | Joins, aggregations, pivots, surrogate keys, single-purpose | view (or ephemeral if used once) | {{ ref(stg_*) }} and {{ ref(int_*) }} only | verb-based filename describing what it does | | models/output_ports/v1/<table>.sql | Published, contract-governed tables | table (or incremental past the threshold below) | {{ ref(int_*) }} or {{ ref(stg_*) }} | table name from the ODCS contract's models: key |
Deviations from upstream dbt conventions, called out so the lineage stays visible:
input_ports/ and output_ports/v1/ instead of dbt's sources / marts, to match the data-product lifecycle terminology.fct_ / dim_. The contract dictates the published name, and consumers see that name directly.staging/ and intermediate/ flat for single-data-product repos. Use subfolders by output port (e.g. staging/<output-port-id>/) only if a data product has more than two or three output ports.Every staging model uses this three-CTE skeleton so the structure is uniform and a reader knows where to find the rename block.
sql-- Staging model for source <provider-dp-id>_<provider-op-id>.<table> with source as ( select * from {{ source('<provider-dp-id>_<provider-op-id>', '<table>') }} ), renamed as ( select cast(<raw_col> as <warehouse_type>) as <canonical_name>, ... from source ), final as ( select * from renamed ) select * from final
Allowed transformations in renamed: column renames, casts, simple case rewrites, unit conversions (e.g. cents to dollars), trim/lower. Disallowed: joins, aggregations, any cross-source logic. Those belong in intermediate/.
int_foo_and_bar, split it.ref(), never source(). A source() call in intermediate/ means a staging model is missing.view unless the model is referenced once and is cheap, in which case ephemeral keeps it out of the warehouse.Tests are declared in the _models.yml next to the file.
| Layer | Default tests | |---|---| | input_ports/ (sources) | freshness: block when the upstream contract publishes an SLA; otherwise none | | staging/ | not_null + unique on the natural key, accepted_values on enum columns | | intermediate/ | relationships on every join key, not_null on any column the next layer depends on | | output_ports/v1/ | Derived from the ODCS contract by dataproduct-implement |
Custom singular tests live in tests/<purpose>.sql and assert cross-model invariants the schema-tests cannot express.
dbt's tiered rule applies: start with view, promote to table when query latency hurts, promote to incremental when the build window hurts.
dbt_project.yml): staging → view, intermediate → view, output_ports → table.incremental when one of these is true:When switching to incremental, configure all three keys; defaults vary by warehouse:
yaml{{ config( materialized='incremental', unique_key='<natural_key>', on_schema_change='append_new_columns', incremental_strategy='merge' # databricks/snowflake/bigquery; use 'delete+insert' on postgres ) }}
Inside the model body, gate new-row logic with {% if is_incremental() %} where updated_at > (select max(updated_at) from {{ this }}) {% endif %}.
Each layer lands in its own warehouse schema so consumers see only the published output, not the scaffolding.
| Layer | Schema | Example | |---|---|---| | input_ports/ | n/a (sources read from upstream schemas; not materialized here) | — | | staging/ | internal_<data-product-id> | internal_dp_acme_customer_activity | | intermediate/ | internal_<data-product-id> (same as staging) | internal_dp_acme_customer_activity | | output_ports/v<N>/<table>.sql | op_<output-port-id>_v<N> | op_customer_activity_v1 |
Why this split:
op_<output-port-id>_v<N> only.To wire this up in dbt, the project needs two pieces:
generate_schema_name so +schema: is taken literally instead of being suffixed onto the target's default schema. Place this in macros/get_custom_schema.sql:sql {% macro generate_schema_name(custom_schema_name, node) -%} {%- if custom_schema_name is none -%} {{ target.schema }} {%- else -%} {{ custom_schema_name | trim }} {%- endif -%} {%- endmacro %}
sql -- top of models/output_ports/v1/<table>.sql {{ config(schema='op_<output_port_id>_v1') }}
dbt_project.yml:yaml models: <data_product_id>: staging: +schema: internal_<data_product_id> intermediate: +schema: internal_<data_product_id>
With these in place, dbt run creates exactly op_<op-id>_v1, op_<op-id>_v2, and internal_<dp-id> in the warehouse, no extra prefixes.
description: in _models.yml. Single sentence: what it represents, not how it is built.description:. Obvious ones (customer_id, created_at) can skip it.datacontract-edit.> ${PLUGIN_ROOT} below refers to the root of this plugin (the directory that contains skills/). On Claude Code it is set automatically as ${CLAUDE_PLUGIN_ROOT}; use that. On any other agent it is unset; resolve it as ../.. relative to this SKILL.md file's directory.
Before running Step 0, print this plan to the user verbatim:
> Running dataproduct-dbt. I'll: > 1. Pre-checks: confirm this is a dbt project with input_ports/, staging/, intermediate/, output_ports/v1/. > 2. Identify which operation you want (build staging, add intermediate, refactor an output port, run dbt, switch to incremental). > 3. Apply the operation following the conventions in this skill (which you can edit to match your org's style). > 4. Verify with dbt parse and, when you ask, dbt build --select <scope>. > 5. Summarize what changed and what's open.
Then proceed.
uv run --quiet dbt --version succeeds from the project root. If it fails, run uv sync and retry; if still missing, stop and tell the user to add the dbt adapter for their warehouse to pyproject.toml's [dependency-groups].dev (e.g. dbt-snowflake, dbt-databricks) and re-run uv sync. Use uv run dbt … for every dbt CLI invocation in this skill.dbt_project.yml exists at the working directory root. If not, route to dataproduct-bootstrap.input_ports/, staging/, intermediate/, output_ports/v1/). If any are missing, route to dataproduct-bootstrap or entropy-data-sync (whichever the user prefers) and stop.Match the user's ask to one of the operations below. If two fit, ask which one. The operations are deliberately small so the skill stays focused; run it again for the next operation.
| If the user says... | Operation | |---|---| | "build out staging", "create staging models for input ports" | A | | "add an intermediate model for X", "join staging models" | B | | "refactor this output port into staging + intermediate", "this output port has direct source refs" | C | | "run dbt", "test the models", "compile only", "build the models" | D | | "make this output port incremental", "switch X to incremental" | E | | "add tests / descriptions to staging or intermediate" | F |
For each models/input_ports/<provider-op-id>.source.yaml (skip files the user did not select if they narrowed the scope):
sources[0].name (the <dp-id>_<op-id> reference) and each tables[].name (from the provider's contract).models/staging/stg_<provider-dp-id>__<table>.sql using the staging CTE pattern above. Apply column renames only when the raw name violates the project's snake_case convention or the project's column-name policy; otherwise pass them through with explicit casts.models/staging/_models.yml:yaml models:
description: Cleaned, renamed, and type-cast columns from <provider-dp-id>.<table>. columns:
tests: not_null, unique]
-- TODO: comment in the model and surface it in the final report.int_<verb>_<entity>.sql (e.g. int_payments_pivoted_to_orders.sql).models/intermediate/<filename>.sql. Use only {{ ref(...) }} references to staging or other intermediate models._models.yml entry with a description and tests on join keys (relationships, not_null).dbt parse to verify. Do not run the model in the warehouse without the user asking.When models/output_ports/v1/<table>.sql contains {{ source(...) }} calls or pile-up joins:
source() call. Generate a staging model for each (operation A).ref(int_*) and apply the contract-driven casts and column order.dbt parse. If the rewrite changes column counts or order, surface a diff and ask before saving.Pick the right command based on the user's ask. Always scope with --select unless the user explicitly asks for the full project.
| User intent | Command | |---|---| | Compile only (no warehouse roundtrip) | dbt parse | | Materialize a model and its upstream | dbt run --select +<model> | | Run tests for a model and its upstream | dbt test --select +<model> | | Materialize + test in DAG order | dbt build --select +<model> | | Rebuild an incremental model from scratch | dbt build --full-refresh --select <model> |
Confirm before any dbt run, dbt test, or dbt build on the whole project. Those touch the warehouse and can be expensive.
{{ config(...) }} block at the top of the model (see the Materializations section above).{% if is_incremental() %} where <ts> > (select max(<ts>) from {{ this }}) {% endif %} filter on the deepest CTE that reads from the source.dbt build --full-refresh --select <model> once with the user's confirmation to seed the table.dbt build --select <model> only inserts new rows (check target/run/.../<model>.sql for the merge / delete+insert shape)._models.yml.description: lines from the user's input or from the contract (for output ports, the contract is the source of truth — do not paraphrase it).dbt parse to verify YAML.After any operation that wrote SQL or YAML:
dbt parse to catch syntax errors and dangling refs.dbt build --select <scope> to materialize and test the affected models.dbt run separately from dbt test — dbt build orders them correctly and stops on failures.End with this two-part recap. Use the shared Status enum: created, updated, already present, deferred, skipped.
Part 1 — outcome table. One row per operation applied.
| Artifact | Status | Details | |---|---|---| | Operation | … | A / B / C / D / E / F (one row per operation run) | | Staging models | … | <N> files at models/staging/stg_<...>.sql | | Intermediate models | … | <N> files at models/intermediate/int_<...>.sql | | Output-port refactor | … | <table>.sql rewritten to ref staging/intermediate / not applicable | | _models.yml entries | … | counts per layer | | dbt parse | … | "passed" / "failed: <reason>" / "skipped" | | dbt build --select <scope> | … | "passed" / "failed: <reason>" / "not run (user did not authorize)" |
Part 2 — next steps. Bullet list, include only what applies:
-- TODO: left in a generated model, list the model and the open question.dbt build but it was skipped (e.g. missing warehouse creds), the exact env vars they need to set.If there is nothing in Part 2, write a single line: No further action required.
case involving multiple columns shows up there, push it to intermediate/.source() outside staging. Intermediate and output ports use ref() exclusively. If you find a source() call elsewhere, surface it and propose operation C.datacontract-edit.already present.| Case | Status | Duration (ms) | Turns | Tokens | Tool calls | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Without | With | Δ | Without | With | Δ | Without | With | Δ | Without | With | Δ | ||
case-01 | fail→fail | 9,652 | 11,145 | +15% | 1 | 1 | 0% | 245 | 5,305 | +2065% | 0 | 0 | — |
case-11 | fail→pass | 9,022 | 14,305 | +59% | 1 | 1 | 0% | 1,525 | 6,096 | +300% | 0 | 0 | — |
case-02 | fail→fail | 18,849 | 4,849 | -74% | 1 | 1 | 0% | 2,977 | 5,278 | +77% | 0 | 0 | — |
case-03 | fail→fail | 10,646 | 9,960 | -6% | 1 | 1 | 0% | 290 | 5,179 | +1686% | 0 | 0 | — |
case-04 | fail→pass | 14,862 | 7,734 | -48% | 1 | 1 | 0% | 2,240 | 4,946 | +121% | 0 | 0 | — |
case-05 | fail→pass | 11,902 | 11,439 | -4% | 1 | 1 | 0% | 1,842 | 5,395 | +193% | 0 | 0 | — |
case-06 | fail→pass | 11,018 | 9,228 | -16% | 1 | 1 | 0% | 996 | 5,839 | +486% | 0 | 0 | — |
case-07 | fail→fail | 12,922 | 10,847 | -16% | 1 | 1 | 0% | 2,235 | 5,393 | +141% | 0 | 0 | — |
case-08 | pass→pass | 22,057 | 8,100 | -63% | 1 | 1 | 0% | 1,981 | 5,911 | +198% | 0 | 0 | — |
case-09 | fail→fail | 12,275 | 10,259 | -16% | 1 | 1 | 0% | 2,586 | 5,296 | +105% | 0 | 0 | — |
case-10 | pass→pass | 15,586 | 8,568 | -45% | 1 | 1 | 0% | 1,711 | 5,770 | +237% | 0 | 0 | — |
case-12 | fail→pass | 9,142 | 10,821 | +18% | 1 | 1 | 0% | 1,593 | 5,501 | +245% | 0 | 0 | — |
case-13 | pass→pass | 13,772 | 10,173 | -26% | 1 | 1 | 0% | 1,629 | 5,504 | +238% | 0 | 0 | — |
case-14 | fail→pass | 12,457 | 8,738 | -30% | 1 | 1 | 0% | 1,159 | 5,128 | +342% | 0 | 0 | — |
case-15 | pass→fail | 5,690 | 5,340 | -6% | 1 | 1 | 0% | 907 | 5,309 | +485% | 0 | 0 | — |
case-16 | fail→fail | 9,858 | 12,714 | +29% | 1 | 1 | 0% | 1,236 | 5,745 | +365% | 0 | 0 | — |
case-17 | fail→pass | 14,025 | 3,248 | -77% | 1 | 1 | 0% | 1,460 | 5,082 | +248% | 0 | 0 | — |
case-18 | pass→pass | 14,281 | 7,353 | -49% | 1 | 1 | 0% | 2,045 | 5,521 | +170% | 0 | 0 | — |
case-19 | pass→pass | 16,950 | 4,003 | -76% | 1 | 1 | 0% | 2,190 | 5,047 | +130% | 0 | 0 | — |
case-20 | pass→pass | 8,517 | 8,854 | +4% | 1 | 1 | 0% | 1,253 | 5,161 | +312% | 0 | 0 | — |
case-21 | fail→pass | 14,475 | 4,749 | -67% | 1 | 1 | 0% | 1,408 | 5,216 | +270% | 0 | 0 | — |
case-22 | fail→pass | 15,106 | 5,018 | -67% | 1 | 1 | 0% | 2,169 | 5,408 | +149% | 0 | 0 | — |
DecimalAI ran this skill against gemini-3.6-flash twice over the same eval suite — once with the skill loaded and once without — and compared the two runs case by case. 22 cases were attempted, and 20 counted toward the lift figure. The other 2 produced results that are not comparable between the two arms, so they are excluded from the headline rather than averaged into it. The headline lift of +27 percentage points is the difference between those two pass rates over the 20 comparable cases. 4 cases got worse with the skill loaded, and they are included in that figure.
Without the skill loaded, the model failed this case. With it loaded, the same prompt on the same model passed. This is one improved case from the latest verified run; every case, including any that regressed, is in the table above.
Other measured skills in the registry, with their headline benchmark lift.