Phase 3: Data Pipelines & ETL

Schema tests, data tests & custom macros

Intermediate ~2 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you're a super chef in a big restaurant. Your job isn't just to cook, but to make sure every dish is perfect and delicious for the customers. A 'Data Engineer' is a bit like that chef. They take lots of raw 'ingredients' (like numbers, words, dates) and follow 'recipes' to turn them into useful information, like figuring out everyone's favorite meal. But just like a chef needs to check their ingredients and their cooking, we need ways to check our data to make sure it's always correct and makes sense.

This is where 'tests' come in. Think of it like this: First, you have basic ingredient checks, let's call them Schema tests. Before you even start cooking, you'd check: 'Is this milk fresh and not missing?' 'Do I have exactly one whole chicken?' (unique). 'Am I using chicken for the chicken curry, not accidentally fish?' (accepted_values). These are common sense checks that any chef would do for any recipe to make sure the basics are right. Then, you have special 'taste tests' for your specific recipe, these are Data tests. Maybe your curry recipe says the sauce must be extra creamy, or the vegetables need to be chopped into tiny cubes. You'd do a quick taste or visual check. If your special test finds something wrong – like the sauce isn't creamy enough – it points out exactly which part of the dish needs fixing, so you know what to adjust. If everything passes, it means your specific recipe rules were followed perfectly.

But what if you have to do the same tricky chopping technique or make the same special sauce base for many different dishes? You wouldn't want to write down all the steps for it every single time. That's where custom macros are super helpful! Think of them as your secret chef shortcuts or special cooking tools. Instead of writing out 'chop onions, carrots, and celery finely, then sauté until soft' every time, you could have a 'Mirepoix Prep' macro. You just say 'use Mirepoix Prep,' and your computer knows all those steps automatically. It saves you time, makes sure you do it the same way every time (so your dishes are consistent), and keeps your main recipe much tidier.

So, when Data Engineers use these tools, they're making sure that all the data they handle is as perfect and reliable as possible. This means you can build amazing things with data, knowing it's trustworthy – like creating an app that recommends the best books, or building a system that helps scientists understand important patterns, all because the ingredients were checked, the recipe rules were followed, and repetitive tasks were made easy with smart shortcuts!

As a Data Engineer, ensuring data quality and pipeline reliability is paramount, and dbt provides powerful tools to achieve this through its testing framework and custom macros. At its core, dbt offers two main types of tests: schema tests and data tests. Schema tests are built-in, common checks applied at the column level, such as unique, not_null, accepted_values, and relationships. These enforce basic structural integrity and data consistency. Data tests, on the other hand, allow for highly flexible, custom validation of your business logic. You define a data test as a SQL query where a passing test returns zero rows, and a failing test returns one or more rows, highlighting the exact data that violates your defined rules (e.g., order_total must always be positive, or start_date must precede end_date).

Beyond just testing, dbt's custom macros significantly boost developer efficiency and consistency. Macros are reusable Jinja code snippets that can encapsulate complex SQL logic, generate dynamic SQL, or abstract away repetitive patterns. Think of them as functions in programming languages. A Data Engineer might use a macro to standardize how updated_at timestamps are generated across multiple models, create a common WHERE clause for filtering soft-deleted records, or even build more sophisticated generic tests. By centralizing logic in macros, you adhere to the DRY (Don't Repeat Yourself) principle, making your codebase more maintainable and less prone to errors.

Together, schema tests, data tests, and custom macros form a robust toolkit for building trustworthy data pipelines. Schema tests provide the foundational quality checks, data tests allow for deep, business-specific validation, and custom macros streamline development and enforce consistency. This integrated approach ensures that the data flowing through your transformations is accurate, reliable, and adheres to critical business rules, giving you and your stakeholders confidence in the derived insights.

Key Takeaways

  • Schema tests enforce basic column-level data integrity (e.g., not_null, unique).
  • Data tests validate complex business rules using custom SQL queries.
  • Custom macros encapsulate reusable SQL or Jinja logic, promoting DRY principles.
  • Macros enhance efficiency by standardizing patterns and generating dynamic code.
  • Combined, these tools are essential for building robust, reliable, and maintainable data pipelines.

Code Example

sql
-- macros/standardize_timestamp.sql
{% macro standardize_timestamp(column_name, default_value='current_timestamp()') %}
  coalesce(
    cast({{ column_name }} as timestamp),
    {{ default_value }}
  ) as {{ column_name }}_standardized
{% endmacro %}

-- Usage in a dbt model (e.g., models/staging/stg_events.sql):
SELECT
  event_id,
  user_id,
  {{ standardize_timestamp('created_at') }},
  {{ standardize_timestamp('updated_at', "'1970-01-01'::timestamp") }}
FROM {{ source('raw', 'events') }}

How this code works

This dbt macro, named standardize_timestamp, is a reusable piece of code designed to ensure all timestamp columns in a dataset are consistently formatted and valid. Its job is to clean up potentially missing or incorrectly formatted date/time values, replacing them with a reliable default if necessary. This promotes data quality and makes subsequent analysis easier. The macro is defined using {% macro standardize_timestamp(...) %} and accepts the column_name to process and an optional default_value.

Within the macro, the coalesce function is central. It first tries to cast the column_name to a standard timestamp format. If this cast fails (e.g., the original data was null or invalid), coalesce then falls back to the provided default_value. By default, this is current_timestamp(), but it can be explicitly overridden like '1970-01-01'::timestamp for updated_at. A subtle beginner gotcha is ensuring any custom literal default_value is properly quoted and explicitly cast (e.g., 'YYYY-MM-DD'::timestamp) as the macro passes it directly. Finally, the macro assigns an alias by appending _standardized to the original column_name, creating new, clean columns for use in the SELECT statement.

Related Articles

Hand-picked reads to go deeper on data transformation (dbt).

Back to Data Engineer Roadmap