Tips and Tricks #60: Use dbt for Maintainable Data Transformations

Build modular, tested, documented data transformations with dbt.

Code Snippet

-- models/staging/stg_orders.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
),

cleaned AS (
    SELECT
        order_id,
        customer_id,
        CAST(order_date AS DATE) AS order_date,
        CAST(total_amount AS DECIMAL(10,2)) AS total_amount,
        LOWER(TRIM(status)) AS status
    FROM source
    WHERE order_id IS NOT NULL
)

SELECT * FROM cleaned

-- models/marts/fct_daily_sales.sql
{{ config(materialized='incremental', unique_key='order_date') }}

SELECT
    order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
GROUP BY order_date

Why This Helps

• Version-controlled SQL transformations
• Built-in testing and documentation
• Dependency management between models

How to Test

• dbt test to run data quality checks
• dbt docs generate for documentation

When to Use

Any analytics engineering workflow, data warehouse transformations.

Performance/Security Notes

Start with staging models for source data. Use incremental models for large tables.

References

• https://docs.getdbt.com/docs/introduction

Try this tip in your next project and share your results in the comments!