Agentic Dbt Pipeline

dbt Analytics Engineer

Full lifecycle orchestrator for the dbt project. On every new/full-pipeline prompt: agent runs read-only discovery-requirements.md first, explains what it concluded from the source data, and asks for requirements before any build plan. Default full pipeline: discovery -> project setup and configuration -> sources -> bronze -> silver -> gold -> semantic layer -> project evaluator -> documentation -> analytics insight reporting -> presentation layer recommendation -> continuous integration, plus Agents Schema when enabled and supported.

Use workflow_phase: to run a single phase. Project setup and configuration runs automatically for new/full pipeline work unless the user explicitly disables automatic setup.

Install (one command): npx skills add zohaibRT/agentic-dbt-pipeline - see references/install-skill.md. Project setup and configuration auto-installs dbt Agent Skills and dbt packages on first run.

Local resource hydration

Some versions of npx skills add install only this SKILL.md entry file into agent folders. This skill is intentionally multi-file, so before reading any references/, running any scripts/, or using project.config.yml, verify that these resources exist next to this SKILL.md:

references/
scripts/
agents/
templates/
templates/reports/09_analytics_insights/
templates/reports/10_presentation/
docs/
project.config.yml
prompt.md
.env.example

If any are missing (including a partial templates/ tree after a failed multi-agent Windows install), hydrate the installed skill folder from the repository before continuing. Use the directory containing this SKILL.md as {skill_dir}. Do not hydrate into the user's dbt project root.

On Windows, prefer npx skills add zohaibRT/agentic-dbt-pipeline --agent cursor -y so the installer does not race mkdir across many agent copies.

PowerShell:

$skillDir = "<directory containing this SKILL.md>"
$tmp = Join-Path $env:TEMP ("agentic-dbt-pipeline-" + [guid]::NewGuid().ToString())
git clone --depth 1 https://github.com/zohaibRT/agentic-dbt-pipeline.git $tmp
Copy-Item -Path (Join-Path $tmp "references") -Destination $skillDir -Recurse -Force
Copy-Item -Path (Join-Path $tmp "scripts") -Destination $skillDir -Recurse -Force
Copy-Item -Path (Join-Path $tmp "agents") -Destination $skillDir -Recurse -Force
Copy-Item -Path (Join-Path $tmp "templates") -Destination $skillDir -Recurse -Force
Copy-Item -Path (Join-Path $tmp "docs") -Destination $skillDir -Recurse -Force
Copy-Item -Path (Join-Path $tmp "project.config.yml") -Destination $skillDir -Force
Copy-Item -Path (Join-Path $tmp "prompt.md") -Destination $skillDir -Force
Copy-Item -Path (Join-Path $tmp ".env.example") -Destination $skillDir -Force
Remove-Item -LiteralPath $tmp -Recurse -Force

Bash:

skill_dir="<directory containing this SKILL.md>"
tmp="$(mktemp -d)"
git clone --depth 1 https://github.com/zohaibRT/agentic-dbt-pipeline.git "$tmp"
cp -R "$tmp/references" "$skill_dir/"
cp -R "$tmp/scripts" "$skill_dir/"
cp -R "$tmp/agents" "$skill_dir/"
cp -R "$tmp/templates" "$skill_dir/"
cp -R "$tmp/docs" "$skill_dir/"
cp "$tmp/project.config.yml" "$skill_dir/"
cp "$tmp/prompt.md" "$skill_dir/"
cp "$tmp/.env.example" "$skill_dir/"
rm -rf "$tmp"

After hydration, read references and scripts from local disk only. If hydration fails because git or network access is unavailable, stop and tell the user the skill resources are missing instead of pretending the referenced files were read.

Install and environment anti-patterns

Avoid these common first-run mistakes:

Discovery first, then project setup and configuration

Read and execute references/discovery-requirements.md before project setup, project initialization, or full pipeline runs.

Discovery is read-only and project-oriented. It may inspect schemas, tables, columns, row counts, keys, relationships, dates, measures, and statuses. Its input/report/output must focus on the source data and analytics project, not environment setup. It must write reports/agent/00_discovery/discovery_report.md, reports/agent/00_discovery/requirements.md, reports/agent/00_discovery/core_profile.json, reports/agent/00_discovery/discovery_raw.json, reports/agent/00_discovery/sql_proofs/, reports/agent/PIPELINE_STATUS.md, and reports/agent/CONTEXT_TREE.md before the chat summary, even when the dbt project has not been initialized yet. Use the canonical templates under templates/reports/00_discovery/ and templates/reports/root/ for every file created or updated by the discovery checkpoint, including discovery report, requirements, structured JSON evidence, cardinality, relationship profile, discovery approval checklist, proof index, SQL proof files, pipeline status, context tree, report index, requirements traceability, and next-phase prompt. The template structure should stay consistent across projects; the content must change based on source evidence and user-approved rules. The requirements file must capture inferred requirements, recommended defaults, unknowns, user-decision needs, and blocked/deferred scope derived from the source evidence and business domain. The discovery sql_proofs/ folder must include reusable source proof queries with captured results for table inventory, per-table row counts, candidate keys, important statuses such as active/open/closed counts, date coverage, numeric summaries, and relationship/cardinality checks wherever the source supports them. discovery_raw.json.queries_executed[] must link to the SQL proof files that support the discovery claims. It must create Mermaid discovery diagrams when the source evidence supports them, including an entity relationship diagram when credible relationships exist, plus other necessary source inventory, business process, or medallion direction diagrams. It must not install packages, run codegen, create warehouse schemas, or change profiles.

Do not assume the business domain. During discovery, understand source tables, table relationships, business processes, metrics required, data quality rules, required output models, and reporting needs before proposing dbt models.

If any of those areas cannot be properly understood or proven, do not assume. Ask the user for missing business meaning or approval, and defer dependent models, tests, metrics, semantic definitions, or presentation outputs until the uncertainty is resolved.

After discovery, send a normal assistant message with a visible Markdown Discovery Complete control-panel summary before asking for approval. Do not put the discovery findings only inside a native question card, approval widget, or request_user_input body. The chat summary must include status, source reviewed, key findings, validation or SQL proof highlights, reports written, open decisions, the recommended next step, what the next step will and will not include, and how to approve. Then ask a short native/clickable question only when the normal Markdown summary is visibly present directly above the question. The question should be compact, for example: Do you approve this discovery scope and want automatic project setup to run next? Recommended option: Yes, continue to setup. Other options: Add requirements first and Tell me what to change. If the runtime cannot guarantee that the normal summary is visible directly above the clickable question, do not use the clickable question; use the text fallback instead: Do you approve this discovery scope and want automatic project setup to run next? Reply Yes to continue, or tell me what to change.

After discovery, summarize what the agent concluded from the source data and ask whether the user wants to add requirements such as mappings, metrics, privacy rules, naming rules, included/excluded tables, or priority facts/dimensions. User responses are interpreted by the active workflow checkpoint, not by broad intent. At the discovery checkpoint, the next allowed action is only source confirmation and automatic project setup and configuration. Do not treat discovery acceptance as approval for sources, bronze/staging, silver/intermediate, gold/marts, semantic layer, evaluator, documentation, analytics insight reporting, presentation layer, continuous integration, Agents Schema, commits, pushes, or future schema switching.

Discovery approval gate

After discovery completes, create reports/agent/00_discovery/DISCOVERY_APPROVAL_CHECKLIST.md and reports/agent/REQUIREMENTS_TRACEABILITY_MATRIX.md using discovery-approval-checklist.md and requirements-traceability-matrix.md. Do not continue to bootstrap/build until the checklist decision is APPROVED or APPROVED WITH CONDITIONS. If approved with conditions, write those conditions to reports/agent/CONTEXT_TREE.md, AGENT_PLAN.md, and reports/agent/REQUIREMENTS_TRACEABILITY_MATRIX.md.

Project setup and configuration (automatic setup-only phase after discovery)

Read and execute references/bootstrap.md before any layer work:

1. Check/install software prerequisites - Python, venv, dbt-core, matching adapter, skill requirements - software-prerequisites.md
2. Install dbt Agent Skills + all dbt packages - see dbt-packages-and-skills.md
3. dbt debug - verify connection
4. dbt deps + codegen - when sources/full pipeline
5. Resolve git mode - local commits by default; GitHub only when push is requested - github-repo-resolution.md
6. Create continuous integration and Agents Schema workflows - when requested, or when auto_agents_schema: true and the destination is supported

User one-time manual steps: profiles.yml password, plus GitHub repository or secret only when remote push, continuous integration, or Agents Schema synchronization is requested.

If dbt_profile_name is provided in the prompt, use it as {project.profile} for dbt commands and generated dbt_project.yml. If it is missing and multiple profiles exist in ~/.dbt/profiles.yml, ask the user which profile to use before running dbt commands. Never guess from the first profile.

Project setup and configuration is setup-only and auto-runs by default after the discovery requirements checkpoint is accepted. Do not ask for a separate setup approval response unless a setup safety gate is triggered. This phase may create the local dbt project scaffold, install requirements.txt from the installed skill or workspace, create the managed reports/agent/ skeleton with SQL proof index files, install missing dbt Agent Skills and dbt packages, run dbt debug, run dbt deps, run dbt parse, and write setup reports. This phase does not approve source YAML generation, bronze/staging models, silver/intermediate models, gold/marts models, semantic layer files, documentation changes, continuous integration workflows, Agents Schema synchronization, warehouse model replacement, commits, or pushes.

Stop and ask before project setup and configuration if required .env values are missing, the selected profile is ambiguous or failing, the profile target schema equals the source schema and needs a user-approved change, existing project files would be overwritten, warehouse objects would be created or replaced beyond setup validation, credentials or secrets are needed, automatic project setup is explicitly disabled, or the user explicitly asked to approve setup manually.

dbt packages & agent skills (mandatory stack)

Read references/dbt-packages-and-skills.md.

Install agent skills: references/install-dbt-agent-skills.md

Phase map

Context prompt template: agent-context-prompt.md

Step 0 - Load configuration

Read project.config.yml, skill-inputs.md, profile-listing.md, project-naming.md, schema-isolation.md, env-configuration.md, source-confirmation.md, warehouse-adapter-routing.md, skill-knowledge.md, project-knowledge.md, discovery-requirements.md, discovery-artifacts.md, discovery-status-vocabulary.md, phased-discovery.md, recommendation-and-review.md, writing-style.md, reporting-standards.md, universal-analytics-framework.md, analytics-insight-reporting.md, mermaid-diagrams.md, principal-data-engineering-standards.md, evidence-driven-dbt-process.md, layer-data-validation.md, cardinality-validation.md, kpi-definitions.md, kpi-definition-contract.md, metric-verification.md, metric-verification-checklist.md, kpi-reconciliation.md, advanced-data-engineering-review.md, phase-plan-approval.md, data-engineer-decision-gate.md, phase-completion-report.md, report-artifact-organization.md, next-phase-prompt.md, and context-tree.md.

For smaller context windows, read required references fully, extract the rules that apply to the active checkpoint into the phase plan or working notes, and avoid carrying unused details forward. Prefer loading phase-specific references only when entering that phase. Do not skip required safety references, but summarize-and-discard details that are not relevant to the active checkpoint.

Resolve paths relative to workspace root. dbt project root = {project.root}.

Read skill-knowledge.md and project-knowledge.md after loading configuration and before discovery summaries or phase plans. Use built-in skill knowledge for reusable dbt, big data, warehouse optimization, Power BI, semantic, privacy, and validation standards. Use project knowledge files such as AGENT_KNOWLEDGE.md, docs/dbt_knowledge.md, docs/business_rules.md, .agents/project_knowledge.md, and reports/agent/CONTEXT_TREE.md for local/domain overrides when they exist. Apply prompt project_rules first when there is a conflict. Ask before persisting new knowledge from chat.

User prompt overrides .env and configuration for schema, domain, layers, materialization, commit mode. Use .env for non-secret reusable inputs before asking the user. Workspace .env is not created by npx skills add; create it on first run when missing. If .env is missing in a fresh clone, follow env-configuration.md: resolve .env.example from the workspace root, dbt project root, or installed skill folder, create a safe local workspace .env from that template with placeholder values only, list available dbt profiles with profile-listing.md, stop before discovery or dbt commands, and ask the user for DBT_DOMAIN, DBT_PROFILE_NAME, and DBT_SOURCE_SCHEMA. Do not fill .env from profiles, profile target schemas, warehouse schemas, old runs, terminal output, examples, nearby workspaces, or guesses. Do not search the repository, inspect terminal output, infer, suggest, or summarize values from other workspaces or previous runs.

Read warehouse-adapter-routing.md immediately after loading .env and before any discovery. Resolve the active dbt profile and adapter from ~/.dbt/profiles.yml; use only that adapter's discovery path. Do not call AWS, Redshift, PostgreSQL, Snowflake, BigQuery, Databricks, cloud identity checks, warehouse connectors, metadata queries, or Model Context Protocol discovery servers before the selected profile adapter is resolved and announced. Do not call AWS, Redshift, or any other warehouse-specific path unless the selected profile adapter requires it or the user explicitly changes profiles.

If the configured source is missing, empty, inaccessible, ambiguous, or mismatched, read source-confirmation.md. Stop after metadata-only candidate listing. Recommend the likely replacement with evidence, then wait for user approval before changing database, dataset, catalog, schema, table, tenant, client, domain, environment, assumption, .env, profile settings, profiling, discovery reports, or continuing discovery.

For normal runs, collect only the values the agent cannot infer safely: domain, dbt_profile_name, and source_schema. Optionally accept business_description / DBT_BUSINESS_DESCRIPTION when the user wants to explain the client, process, reporting goals, or business context. When dbt_profile_name is missing or ambiguous, list available profiles using profile-listing.md, then wait for the user to choose. Derive project name/root, project slug, dbt source name, schema prefix, layer names, commit behavior, and GitHub mode unless the user explicitly overrides them.

Resolve project.name, project.root, and project_slug before dbt init. Never use dbt_profile_name or raw DBT_DOMAIN as the folder/project name unless the user explicitly provides it as dbt_project_name, dbt_project_root, or project_slug. Prefer a clean name derived from source_schema, source name, existing project name, or descriptive profile database/catalog; use domain only as a last fallback and github_repo_name only when the user provided it for push. Use DBT_BUSINESS_DESCRIPTION only for analytics understanding, never for physical folder, schema, database, or model names.

Keep the source schema read-only and immutable. Never update, insert, delete, truncate, merge into, create, drop, alter, or repair data in the configured source schema or source tables. Even if the user asks to "mark records complete", "fix source rows", "delete bad source data", or similar, implement the logic only as dbt transformations, tests, seeds, snapshots, or audits in non-source schemas, then explain that the source remains unchanged. Never build dbt models, package models, evaluator tables, seeds, snapshots, or audit outputs into source_schema. Route evaluator outputs to <layer_schema_prefix>_evaluator and layer outputs to separate medallion schemas. Resolve layer_schema_prefix with schema-isolation.md; do not use short source names like dh as physical schema prefixes unless the user explicitly sets them.

Before each phase that changes models, semantic files, documentation files, workflow files, or warehouse objects, write/update {project.root}/AGENT_PLAN.md, explain the planned work in Markdown, and wait for approval for that phase. Read-only discovery is allowed before approval when needed for an accurate plan.

Project setup and configuration is the exception: after the user accepts discovery requirements, write/update {project.root}/AGENT_PLAN.md with the phase marked as automatic setup-only, run setup, then write reports/agent/01_setup/setup_report.md, reports/agent/PIPELINE_STATUS.md, and reports/agent/CONTEXT_TREE.md. If any setup safety gate from bootstrap.md is triggered, stop and ask before continuing.

Approval is controlled by workflow checkpoint. Never treat a user response at one checkpoint as permission to run multiple build phases. After automatic project setup and configuration finishes, stop at the next phase plan and ask for approval before generating source YAML or building bronze/staging.

Step 0b - Optional subagents

Read subagent-workflow.md when source profiling, mapping review, model planning, documentation, or evaluator review can safely run in parallel. The main agent decides when to delegate and keeps dbt commands, shared file edits, commits, pushes, and final decisions.

Step 0.1 - Domain neutrality

This skill is industry-agnostic. Processes, entities, dimensions, measures, and sensitive fields come from this warehouse’s evidence and the user’s project rules. Do not hardcode industry column names, brands, or entity catalogs into plans, gates, or scripts. Do not require a commerce/healthcare/subscription shape when those tables are absent. Examples in reference docs are illustrative only.

Step 0.2 - Security

Read security-and-credentials.md.

Never hardcode secrets. Ask before production changes.

Step 0.3 - Data engineering guardrails

Read data-engineering-best-practices.md and principal-data-engineering-standards.md before model design and again before final delivery. Apply grain, test, incremental, snapshot, documentation, lineage, directed acyclic graph, freshness, macros, packages, build process, privacy, performance, state-based continuous integration, contracts/versioning, SQL style, warehouse optimization, modern table format, and downstream presentation guardrails.

Read privacy-and-unknown-fields.md and reporting-coverage-requirements.md when discovery finds direct identifiers, sensitive fields, protected health information, personally identifiable information, or ambiguous, placeholder, abbreviated, or poorly named fields. The agent must recommend a safe default, document the recommendation, and ask only for approval or business definitions instead of leaving the whole decision to the user. When the user opts out of privacy minimization (for example Do NOT apply privacy minimization unless I explicitly request it), record that rule, build conformed reporting dimensions with business labels, and show reporting attributes from gold on the presentation when useful — discover fields from this project’s evidence; do not hardcode industry field lists. Do not hide attributes “to be safe” and do not write report copy that the presentation still avoids/hides identifiers after opt-out. Close OPEN Attention Board and KPI Gap Register privacy-minimization rows; only always-exclude classes (secrets/OTP/full bank dumps/national ID/PHI) stay excluded unless the user explicitly asks.

Read writing-style.md and reporting-standards.md before writing user-facing prompts, plans, reports, summaries, diagram notes, presentation artifacts, or final handoffs. Use full wording instead of shorthand, except for official tool names, commands, filenames, environment variables, and code identifiers. Every report must include the five reporting pillars when relevant: context and strategy, key performance indicators, trend analysis and variance, insights and attribution, and recommendations and next steps. If a pillar is not supported yet, mark it deferred with the reason instead of guessing.

Every WARN, FAIL, BLOCKED, or SKIPPED status in PIPELINE_STATUS.md, phase reports, approval checklists, SQL proof indexes, and final summaries must include why the status was used, the evidence path, what the data engineer should review, the required action, and whether it blocks the next checkpoint. Do not leave non-PASS statuses as unexplained labels.

Read data-engineer-decision-gate.md before writing each phase plan. The phase plan must show the agent's data-engineering decisions, evidence, and approval needs; do not hide grain, key, join, bridge table, mapping, privacy, metric, materialization, or validation choices inside code.

If the agent cannot understand the source tables, relationships, business processes, required metrics, data quality rules, required output models, or reporting needs for the current scope, stop the dependent scope and ask instead of guessing.

Read phased-discovery.md before each phase. Discover only what is needed for the next layer or workflow step; do not fully design silver/gold/semantic outputs during initial discovery or bronze work.

Read recommendation-and-review.md before writing discovery summaries, phase plans, phase reports, and final handoffs. The agent must recommend the best path with evidence, show what looks right and what is not ready, state confidence about proven vs uncertain items, and ask the data engineer only for business-impacting approvals. Do not make the user design everything from scratch.

Read mermaid-diagrams.md before creating or changing any diagram. All diagrams must be Mermaid blocks, entity relationships must use Mermaid erDiagram, and every added or changed Mermaid diagram must be verified as visible/parseable before the phase is marked complete.

Read evidence-driven-dbt-process.md, layer-data-validation.md, cardinality-validation.md, layer-verification-ledger.md, and assumption-tests.md before building bronze/staging, silver/intermediate, or gold/marts. After each layer build, run warehouse validation queries for row presence, expected emptiness, grain, keys, relationships, cardinality, row-count movement, row loss, row multiplication, date coverage, status/category distributions, measures, mapping coverage, and privacy exposure. Save each validation query as a reusable SQL proof file under the phase sql_proofs/ folder with purpose, expected result, captured result, status, and runnable SQL. Promote approved assumptions from discovery or the phase report into dbt singular or generic tests using assumption-tests.md and templates/dbt/tests/. Add Data Verification Results, SQL Proof Files, Assumption Tests, and cardinality/grain evidence to the layer report, update reports/agent/LAYER_VERIFICATION_LEDGER.md, reports/agent/PIPELINE_STATUS.md, and reports/agent/CONTEXT_TREE.md, share the important results with the user, and stop before the next layer when a model that should contain data is empty or any validation issue is unexplained.

Read universal-analytics-framework.md, kpi-discovery-framework.md, kpi-definitions.md, kpi-definition-contract.md, metric-verification.md, metric-verification-checklist.md, kpi-reconciliation.md, and cardinality-validation.md before analytics insight reporting, semantic layer, presentation layer, and final delivery. The agent must construct business process, fact, dimension, broad measure, and contextual metric catalogs first, then propose strategic key performance indicators with business meaning, source model, grain, numerator, denominator, filters, time field, caveats, validation evidence, approval status, cardinality proof, and expected versus actual reconciliation. Create as many useful supported measures and contextual metrics as the validated data safely allows, but promote only decision-relevant, validated, and approved metrics to key performance indicators. Maintain reports/agent/KPI_DEFINITION_CONTRACTS.md and reports/agent/METRIC_VERIFICATION_MATRIX.md; these files are acceptance-gate inputs, not optional summaries. Do not create semantic metrics or presentation calculations from ambiguous or unreconciled key performance indicators. No key performance indicator is trusted until its source-to-final value, grain, cardinality, and SQL proof file are proven.

Read advanced-data-engineering-review.md before final delivery. The pipeline is not complete until advanced review areas are reported, including source lock, schema hygiene, layer validation, grain, tests, data quality, privacy, key performance indicators, semantic layer, evaluator, documentation, analytics insight reporting, presentation-layer recommendation, and operations.

Step 0.5 - Resolve layer names

Read references/dbt-project-layers.md.

For a full pipeline, plan to deliver all model layers, but build them one approved phase at a time. If workflow_phase limits scope, plan and build only that requested phase.

Layer role

…(truncated)