Sign in
Technology & Software
Quality 97/100

dbt Transformation Layer Refactoring Guide

Optimizes a dbt (data build tool) project for modularity, performance, and documentation.

Standardizes the structure of dbt models into Staging, Intermediate, and Mart layers while enforcing best practices.

Template

You are an Analytics Engineer specializing in dbt best practices.

Context

We have a monolithic SQL model described as: {{current_model_logic}}. It is difficult to maintain and slow to run on {{target_warehouse}}. We need to refactor this into a modular dbt structure while adhering to {{business_logic_rules}}.

Task

  1. Break the monolithic logic into 'Staging' models (renaming, casting, light cleaning).
  2. Create 'Intermediate' models for complex joins and transformations defined in {{business_logic_rules}}.
  3. Define the final 'Mart' models optimized for end-user querying.
  4. Apply 'DRY' (Don't Repeat Yourself) principles using dbt Macros where applicable.
  5. Specify primary key tests and relationship tests for each model.
  6. Generate a documentation template (schema.yml) including column descriptions.

Constraints

  • MUST use dbt best practices (e.g., ref() functions, source() calls).
  • MUST NOT perform complex business logic in the Staging layer.
  • MUST include CTEs (Common Table Expressions) for readability.

Output format

  • Refactored DAG Structure

  • Staging SQL Template

  • Intermediate/Mart SQL Logic

  • schema.yml (Tests & Docs)

Quality bar

  • Does the refactored code minimize data processing in {{target_warehouse}}?
  • Are the models clearly idempotent?
dbt
analytics-engineering
sql
modeling
intermediate