Hernan Revale

Testing Data Pipelines

In today’s data-driven world, the reliability of your analytics depends on the integrity of your data pipelines. Even the most sophisticated transformations can be undermined by bad source data, schema changes, or simple human error. That’s where dbt (data build tool) shines: it provides a framework not only for transforming and modeling your data, but also for validating it at every step. In this article, we’ll explore five essential testing strategies you can implement with dbt to catch issues early, enforce data contracts, and build confidence in your analytics.

1. Custom SQL Tests

What they are: Custom SQL tests allow you to write bespoke SQL queries against your models to enforce complex business rules or edge-case validations. They live alongside your models and execute as part of your dbt test suite.

Why you need them: Out-of-the-box tests cover many common cases, but sometimes you have unique conditions—like “no customer may have more than one active subscription”—that require a tailored query.

-- Example: Ensure no customer has more than one active subscription
SELECT
  customer_id,
  COUNT(*) AS active_sub_count
FROM {{ ref('subscriptions') }}
WHERE status = 'active'
GROUP BY customer_id
HAVING COUNT(*) > 1;

If this query returns any rows, the test will fail, alerting you to a data integrity issue before downstream models consume bad data.

2. Personalized Macros

What they are: Macros in dbt are reusable snippets of SQL or Jinja logic. By building custom macros for your testing patterns, you can enforce consistent checks without repeating verbose SQL.

Why you need them: Many projects share recurring validation needs—like ensuring date fields are never in the future or that monetary amounts are non-negative. A macro lets you encapsulate that logic once and apply it across multiple models.

{% macro expect_dates_in_past(model, column) -%}
SELECT
  *
FROM {{ ref(model) }}
WHERE {{ column }} > current_date
{%- endmacro %}

-- Usage in schema.yml
tests:
  - expect_dates_in_past:
      args:
        model: 'orders'
        column: 'order_date'

With just a few lines in your YAML, you’ve enabled a robust, maintainable check across your entire project.

3. Built-In dbt Data Tests

What they are: dbt ships with four standard data tests: unique, not_null, accepted_values, and relationships. These cover the most frequent requirements for uniqueness, presence, domain constraints, and referential integrity.

Why you need them: They require zero custom SQL. You simply declare your expectations in your model’s .yml file, and dbt will generate and execute the underlying queries.

models:
  - name: users
    columns:
      - name: user_id
        tests:
          - unique
          - not_null

      - name: country_code
        tests:
          - accepted_values:
              values: ['US', 'CA', 'MX']

Within minutes, you’ve added core validation checks to your critical tables, ensuring nulls or duplicates never slip through.

4. Unit Tests

What they are: Unit tests isolate a model’s logic by feeding it controlled input data and verifying that its output matches expected results. In dbt, you can implement unit tests using seed files or temporary models.

Why you need them: When your transformations involve complex calculations—like computing a customer’s lifetime value—you want airtight proof that the logic works under all conditions, including edge cases.

-- seed file: tests/lv_input.csv
customer_id,order_amount
1,100
1,200
2,50
2,75

-- model: tests/lv_test.sql
with input_data as (
  select * from {{ ref('lv_input') }}
),

calculated as (
  select
    customer_id,
    sum(order_amount) as lifetime_value
  from input_data
  group by customer_id
)

select *
from calculated
where
  (customer_id = 1 and lifetime_value = 300)
  or (customer_id = 2 and lifetime_value = 125);

If the calculated results don’t match the hardcoded expectations, the test fails, pinpointing exactly where your logic diverged.

5. dbt Model Contracts

What they are: Model contracts let you define strict schema expectations—column types, required fields, accepted value sets—directly in your schema.yml files. They act as formal agreements between your data producers and consumers.

Why you need them: Without explicit contracts, a subtle change in upstream data can silently break your analytics. Contracts ensure that any structural or type deviations immediately surface as test failures.

models:
  - name: orders
    columns:
      - name: order_id
        data_type: integer
        tests:
          - not_null

      - name: order_amount
        data_type: decimal
        tests:
          - not_null

With contracts in place, if someone accidentally returns order_amount as text or drops a required field, dbt stops execution and flags the violation, protecting downstream dashboards and reports.

Putting It All Together: A Comprehensive Testing Strategy

Testing in dbt is more than an afterthought—it’s a core pillar of a robust analytics engineering workflow. By combining custom SQL tests, personalized macros, built-in data tests, unit tests, and model contracts, you establish multiple layers of defense against data quality issues. Here’s a sample workflow:

1. YAML Configuration: Start by declaring built-in tests and contracts in your schema.yml for each model.
2. Custom Tests: Add bespoke SQL tests in tests/ for any project-specific rules.
3. Macros: Create a macros/ folder with reusable test macros for common patterns.
4. Unit Tests: Define seed files and test models under tests/unit/ to validate critical transformations.
5. CI/CD Integration: Hook dbt test into your CI pipeline so that every pull request runs the full suite, ensuring no faulty code or data reaches production.

This layered approach not only catches errors early but also documents your data expectations for new team members and stakeholders.

Conclusion

Ensuring the accuracy, reliability, and trustworthiness of your data requires more than ad-hoc checks—it demands a structured, repeatable testing framework. dbt provides the tools you need, from built-in quick-start tests to fully customized SQL validations and contracts. By implementing these five testing methods, you’ll be well on your way to bulletproofing your data pipelines and empowering stakeholders with confidence in their analytics.

Ready to level up your data quality? Start by adding one new test to your next dbt model, and watch your data reliability soar.

Watch the Video

Introduction

dbt Labs has recently acquired SDF, a company known for its cutting-edge SQL comprehension technology. This acquisition brings several key benefits, including:

• Faster dbt project compilation
• Improved developer experience
• High-fidelity data lineage tracking

But what does this mean for dbt users? Let’s explore.

Background & Context

dbt simplifies the creation of data models using SQL SELECT statements, making data engineering accessible to anyone familiar with SQL. Historically, however, dbt has treated SQL as mere strings, limiting its ability to understand the deeper meaning behind queries.

The Acquisition Explained

In January 2025, dbt Labs announced the acquisition of SDF. According to dbt Labs’ founder and CEO Tristan Handy:

“We are acquiring SDF to bring SQL comprehension into dbt and usher in a new era of ‘what’s possible’ for analytics: supercharging developer productivity and heightening data quality, all while optimizing data platform costs.”

What is SDF Technology?

Unlike dbt’s traditional approach of treating SQL as text, SDF understands the deeper structure of SQL, recognizing objects, types, syntax, and semantics. It emulates the SQL compilers of various data platforms (e.g., Snowflake, Redshift, BigQuery), allowing developers to:

• Validate SQL queries before execution
• Catch breaking changes early
• Ensure platform compatibility
• Perform real-time impact analysis
• Reduce computational costs

What Does “SQL Comprehension” Mean?

SQL comprehension enables dbt to:

• Identify query components
• Generate structured artifacts
• Validate SQL syntax and semantics
• Predict query outcomes, including column creation and datatype assignments
• Execute queries efficiently

Impact on dbt

1. Validate: Will My SQL Really Work?

With SDF, dbt can ensure that SQL queries are correct before execution, reducing errors and debugging time.

2. Analyze: Precise Column-Level Lineage

SQL comprehension improves data lineage tracking, leading to:

• Better debugging workflows
• Optimized CI builds (only rebuilding models affected by changes)
• Enhanced metadata propagation (e.g., PII tagging, test descriptions)

3. Optimize: Right Query, Right Place

With a better understanding of SQL, dbt can:

• Optimize queries for specific engines
• Identify performance bottlenecks before execution
• Support query pruning (scanning only relevant data subsets)
• Optimize DAG execution across multiple platforms

Future Implications

The integration of SDF into dbt is in progress. While it won’t be part of the Apache 2.0 code base, dbt Labs plans to make key SDF capabilities available to all users—both in dbt Core and dbt Cloud.

With SDF, dbt users can look forward to:

• Faster performance
• Optimized data platform costs
• Improved data lineage tracking

All without needing to change existing dbt projects.

Conclusion

The acquisition of SDF marks a significant milestone for dbt, bringing true SQL comprehension into the platform. Developers will benefit from enhanced query validation, real-time impact analysis, and optimized execution strategies. As dbt evolves, its users can expect more powerful, efficient, and cost-effective data transformations.

Watch the Video

Unlocking Analytics with Semantic Models and Metrics

A semantic model is a layer of abstraction that defines business-friendly terms and metrics on top of raw or transformed data. It bridges the gap between data transformations and end-user reporting, ensuring accuracy, consistency, and clarity across analytics tools. By providing a unified way to define and calculate key metrics, semantic models empower businesses with reusability and precision in reporting.

Understanding Semantic Models

Semantic models form the foundation of the dbt Semantic Layer. Configured using YAML files, they correspond to dbt models in your DAG. Each model requires a unique YAML configuration, enabling dynamic and reliable dataset refinement. You can even create multiple semantic models from a single dbt model, provided each has a distinct name.
These models comprise three key components:

• Entities: Define relationships between semantic models (e.g., IDs).
• Dimensions: Columns used for slicing, grouping, and filtering data (e.g., timestamps, categories).
• Measures: Quantitative values aggregated in analyses.

Diving into Metrics

Metrics are calculations representing essential business measures, built from entities, measures, and dimensions. They ensure centralized definitions, reusability across tools, and consistency in analysis. Metrics encapsulate both logic (e.g., aggregations, filters) and context (e.g., time granularity, dimensions).
Types of metrics include:

• Conversion Metrics: Track events like purchases per user.
• Cumulative Metrics: Aggregate measures over specified windows.
• Derived Metrics: Expressions combining multiple metrics.
• Ratio Metrics: Comparisons of numerator and denominator metrics.
• Simple Metrics: Directly reference a single measure.

Commanding Metrics with dbt

dbt Cloud CLI provides MetricFlow commands to interact with the semantic layer. For instance, dbt sl query executes queries and validates metrics, while dbt sl list dimensions retrieves dimensions for specific metrics. These tools streamline metric management and ensure robust analytics workflows.

Semantic models and metrics are vital for bridging data transformations and actionable insights. They provide a foundation for scalable, consistent, and reusable analytics frameworks, enabling businesses to thrive in data-driven environments.

Watch the Video