Testing in DBT: A comprehensive guide to methodologies

Testing is a key component of any data engineering process. It allows us to validate that the data we are generating or transforming meets the expectations defined at each stage. Furthermore, it helps ensure the system’s robustness by facilitating the detection of inconsistencies, errors, or anomalies that could prevent the rest of the model from running correctly.

However, beyond simply running ad hoc tests on our models, it’s important to understand that there are different testing methodologies. Each has its own approach, level of depth, and appropriate time for application. Therefore, choosing the right strategy is vital for building a reliable system.

Although this applies to almost any process and technology, in this post we’ll focus on its implementation with DBT, one of the most popular tools for data transformation using SQL.

Types of tests in DBT

If we look at the official documentation, we see that there are primarily two types of tests in DBT: data tests and unit tests.

Unit tests in DBT

Unit tests are designed to validate a model’s transformation logic in isolation. In this sense, they do not depend on actual data from the data warehouse. This makes this type of test particularly useful for validating infrequent scenarios and ensuring that changes to the logic do not break the code’s previous behavior.

Although very useful, these types of tests tend to be much more customised and specific to each use case. For that reason, in this article—which aims to provide general information on the different testing methodologies—we will not delve into them in depth.

Still, it is important to mention them because they represent a significant change in how models are validated. And, to a large extent, they are essential to implement in DBT models.

Data tests in DBT

Data tests are validations that are run directly on the results of your models in the data warehouse. This verifies that the data meets certain conditions or business rules. The main objective of this type of testing is to ensure the quality and consistency of the information. They can be categorized into two types:

• Generic tests (also known as built-in).
• Custom tests (defined using custom SQL).

Each has its own advantages and limitations. Therefore, the choice between them will depend on the type of data being validated and the system context. Thus, understanding when to use each is key to designing an efficient and maintainable testing strategy.

With that said, in the next section we will analyse the characteristics of each type of test and determine in which scenarios it is more appropriate to choose one over the other.

Generic tests in DBT

Generic tests in DBT are reusable and not tied to a specific SQL query. This feature makes them particularly useful for validating common data quality rules.

When implementing this type of test in DBT, there are two approaches.

Built-in tests

These tests are provided natively by DBT and cover the following four use cases:

1. unique: validates that the values in a column do not repeat.
2. not_null: checks that there are no null values in a column.
3. accepted_values: verifies that the values in a column belong to an allowed list.
4. relationships: verifies referential integrity between two models (foreign key).

Reusable custom generic tests with Jinja

Generic tests are generated using Jinja and SQL macros, which serve as templates that can be applied to any model at any time.

However, there is one minimum requirement these macros must meet: they must accept at least the `models` and `column_name` arguments. This allows the validation logic to be executed on different tables and columns without duplicating code.

Custom tests in DBT

Custom tests are those explicitly defined using SQL queries to validate a specific business rule or to verify that certain quality criteria are met.

Due to their nature, these tests must be constructed according to a set of rules.

1. Query structure. The test’s SQL file must contain a query that selects only those rows that fail to meet the defined rules or conditions.
2. Pass criteria. For the test to be considered passed, the query must not return any rows when executed.
3. Fail criteria. If the query returns one or more rows, the test will be marked as failed, and the set of records that failed the test will be displayed. This is especially useful for debugging the code and identifying which records do not meet the established conditions.

As you can see, custom tests are very useful for validating that the retrieved data meets all specified requirements. Additionally, they serve as automated validation queries, adding robustness to our code.

Placement of DBT tests in the workflow

Once we’ve reviewed the different types of tests available in DBT, the next point to consider is when they will be executed within our process. The placement of our tests can completely change integration, error detection, and the way we manage data quality.

With this in mind, there are two points at which we can introduce our tests.

Testing at the end of the transformation

Following this approach, tests are run once the model has completed all transformations and has been loaded into the data warehouse.

However, this methodology also has a number of advantages and disadvantages.

Advantages

• It allows us to validate processes that depend on fully processed data, providing a comprehensive and consistent view of the final result.
• It facilitates the detection of accumulated errors that may have been introduced in intermediate steps and only become apparent in the final version of the model.

Disadvantages

• One of the main problems with running tests at the end of the transformations is that if an error originates in an early stage, its late detection can make debugging difficult, forcing a review of multiple steps in the flow to find the cause.
• Furthermore, this fault-finding process can become costly and inefficient, especially in complex queries that perform aggregations or depend on different tables.

Intermediate tests

On the other hand, as the name implies, intermediate tests are run at various stages of the transformation. They are typically performed on temporary models or ephemeral tables located in the staging or intermediate layers.

As in the previous case, this approach has both advantages and disadvantages.

Advantages

• Running tests at intermediate points allows for much earlier detection of errors. Additionally, it prevents erroneous data from progressing through the transformations. Thus, this methodology can be much more efficient for debugging the code.
• It enables the application of quality controls on the original or minimally transformed data. This feature is particularly relevant if the model undergoes different transformations throughout the entire process.

Disadvantages

• However, running tests at intermediate points substantially increases the total execution time. This is because if validation must be performed after each transformation, the number of queries to be executed is effectively doubled, thereby significantly increasing the execution time.
• Furthermore, it requires much more detailed planning and substantially greater coordination. As a result, the process becomes somewhat more complex.
• Development time also increases, since many more queries must be created to evaluate the model’s various critical points.

In short, both approaches have advantages and disadvantages that should be carefully evaluated. The choice between one or the other will depend on the context, how critical it is to maintain data accuracy, and the project’s objectives.

Applying the appropriate approach in each case provides a level of robustness and control over data quality that would be difficult to achieve without these validations.

Frequency and timing of execution

Another important consideration regarding DBT testing methodologies is determining how often and when tests should be run. This significantly affects the execution strategy, as it directly impacts both the speed of the pipeline and the early detection of errors, as well as the computational cost of the pipeline.

With that said, let’s look at three key points at which tests can be applied using DBT.

Automated tests in CI/CD

Automated CI/CD tests are one of the most widespread approaches in testing in general and in DBT testing in particular. In this context, tests run automatically every time a code change is proposed—either when a pull request is created or when a merge is performed on a specific branch (usually “main”).

This type of testing has its advantages and disadvantages, which we will analyse below.

Advantages

• They act as a firewall, ensuring that only code that has passed validation can be deployed and moved to production. This reduces the risk of introducing bugs and passing them from one environment to another.
• They allow for the early detection of defects, before they are integrated into production. If the tests fail, the merge process will be halted, preventing code incompatibility.
• Finally, having testing strategies integrated into CI/CD allows developers to focus more on the quality of the code and the data it produces, since a test failure will prevent the developed code from being integrated.

Disadvantages

• The main disadvantage is that implementing this methodology requires designing, configuring, and maintaining a CI/CD infrastructure. Furthermore, if it is not aligned with the team’s workflow, it can increase both time and resource costs. This means that some of the benefits of test automation may be lost.
• In certain projects or setups, it is not feasible to introduce unit tests into the CI/CD pipeline, as their development and implementation can significantly increase the programming effort, making the entire process less agile.
• As you can see, integrating CI/CD testing not only provides confidence that the results obtained will meet a set of expected characteristics but also adds an additional layer of security. In this way, they ensure that everything deployed to production environments meets minimum, clearly defined quality standards. Therefore, whenever the infrastructure and workflow allow it, it is highly recommended to incorporate these types of additional validations.

Manual or exploratory testing

This approach is the simplest of all and is intended to be carried out during code development. It is designed to allow developers to directly review a model’s results by running ad hoc queries, cross-checking with external sources, or applying their business knowledge to detect anomalies that would be difficult to capture with an automated test.

The advantages and disadvantages of this testing approach are as follows:

Advantages

• They allow you to discover unexpected patterns or unforeseen errors in tests that have already been defined.
• They give developers the ability to test different changes made to the code without having to modify the final tests, and provide the flexibility to make changes quickly as needed.

Disadvantages

• Due to the nature of these tests, they are not easily reproducible or applicable to other parts of the code. It is the developer who executes them, drawing on their knowledge of the business and the implemented logic.
• If the number of manual tests increases, they can end up becoming a burden for the developer. This happens because the developer must invest a significant amount of time in executing and subsequently validating the tests, which reduces the scalability of the process.

In summary, it could be said that manual tests play a complementary role, as they allow the developer to do a better job and ensure that the code does not cause unexpected problems and complies with business rules. However, these tests should not replace automated tests, since both serve clearly distinct and complementary functions.

Tests scheduled within orchestration

Finally, regarding when tests are executed, it’s important to consider those scheduled to run during orchestration (such as in Airflow). In this approach, tests do not depend directly on the development workflow (as in CI/CD), but rather are launched as part of the production data pipelines, following the same schedule as data loads and transformations.

Let’s look at the advantages and disadvantages of this methodology.

Advantages

• Since they are configure d to run within the orchestration workflow, they ensure that data is validated every time the process runs. This means the process is constantly validated, preventing potential errors from propagating and only being detected during production deployment. As a result, development costs and time can be reduced.
• Furthermore, they also allow for the detection of errors originating from external sources (for example, external tables) or the ingestion of data from tables in other formato that are ingested directly. This provides a level of data assurance that could not be achieved through CI/CD testing alone.

Disadvantages

• As for the disadvantages, the most obvious one is the increase in DAG execution time. If multiple checks need to be performed, they can add a significant amount of valuable time to the total execution time. Therefore, it will always be necessary to take this aspect into account in order to weigh the extent to which you should deepen the tests against the security they provide.
• Another critical aspect to consider is that, dependiente on the configuration, the tests may block the entire execution of the orchestration process. The advantage of this is that you do not introduce corrupted data into your tables; however, the downside is that if the data is not particularly significant to the process, it can paralyze the entire system, causing problems greater than those the tests are intended to solve.
• Finally, it will be necessary to establish a notification or alert strategy. If this is not done, these tests may be ignored, thereby losing their usefulness and compromising the reliability of the process.

In short, scheduled orchestration tests are a fundamental pillar of any data validation strategy. They enable errors to be detected early on. Furthermore, they prevent corrupted data from moving through the system, making the system as a whole much more reliable, robust, and fail-safe.

Strategic approach to testing

At this point, it’s worth noting that tests in DBT can be applied using different approaches dependiente on the objective at hand. It doesn’t always make sense to validate the same things or with the same intensity, since priorities change dependiente on the context.

For example, we won’t need to apply the same approach if our goal is to ensure the stability of the pipeline as we would if it were to maintain and ensure business consistency or optimise data quality. For this reason, the strategic approach is so important to consider when planning tests in DBT.

With this in mind, let’s distinguish between three types of strategies.

Test-as-documentation methodology

This approach is based on the idea that tests not only validate the data obtained during the process but are also the best way to understand which business rules are being applied.

In practice, the goal is to use tests as a fundamental part of the documentation. Fundamentally, they must show what is supported by the business rules and what is not, with the test failing accordingly.

The main advantage of this approach is that it makes it easier for new team members or people outside the project to understand the business model. This information will not only be captured in a text document but can also be gleaned by tracking the tests.

However, there are not only advantages. Generating tests intended to be used as part of the process documentation requires strict discipline. This is because, if the tests become outdated or if the appropriate tests for new developments are not correctly included, the entire framework designed to document the process could easily collapse.

Test Driven Development methodology

The Test-Driven Development (TDD) approach, widely used in software development, can also be applied to the field of data engineering with DBT. The central idea of this methodology is to define the tests first and then implement the transformation. This provides a much more solid foundation for development. Since the expected results of the transformations are already known, it is much faster and more efficient to determine how to achieve them. This approach also helps prevent errors in the quality of the resulting data.

Once you understand what this approach entails, the most important step is to understand the workflow you should follow.

1. The first step is to understand the business rules and then define them as tests that need to be validated.
2. Next, these tests must be run, allowing them to fail, since there is currently no data to validate.
3. Then, the model is implemented according to the established rules.
4. Next, the tests are run again to verify that the data obtained now complies with the business rules defined in the tests.
5. If the tests fail again, the code would need to be refactored to resolve the issue, but in a much more targeted manner and with less effort.

Implications of this methodology

As can be seen, implementing this testing methodology is a recommended practice that is also widely used in software development environments. Not only does it allow for the validation of business rules from the outset, but it also facilitates code refactoring in the event that the results are not as expected.

However, this methodology also has some limitations. For example, it requires more effort at the start of the project, since business rules must be precisely defined. Furthermore, not all scenarios can be covered by tests. There may always be parts of the code for which this methodology is not the most appropriate, while other approaches may be better suited to the circumstances.

Ultimately, adopting this methodology is usually a good idea as long as three fundamental factors are met. The first is that the business rules are very clear; the second is that there is a willingness to make the initial effort to develop the tests first; and, finally, the type of project must be suitable for this methodology.

Test coverage by model layer

Finally, another key strategy in a DBT testing approach is to define the level of test coverage based on the project layer—that is, staging, intermediate, marts, etc.

This is a key point because not all tables require the same degree of validation. Obviously, the closer they are to the final layer, the more critical it is to ensure their quality.

For this reason, although it is considered a testing methodology, in my opinion it is more accurate to understand it as a validation strategy in which the level of rigor is adjusted according to the importance of each model. This helps avoid testing overload in the initial layers of the model. Furthermore, it maximises rigor in the most critical parts where value is truly delivered.

In summary, the goal of test coverage by model layer is more about efficiency and relevance than the application of specific testing strategies. Furthermore, it allows for applying the appropriate test with the appropriate intensity based on the criticality of the data being validated.

Conclusion

Testing in DBT is a broad and in-depth process that should not be viewed as an isolated set of techniques. It must be considered a comprehensive validation strategy that spans all phases of the data lifecycle and must be adapted based on what is to be validated.

For this reason, a good testing system does more than just ensure data quality. It also helps optimise the process and reflects the business model underlying all data models, facilitating their scalability and enabling them to scale in a much more reliable and stable manner than they would without such a system.

In short, testing is absolutely essential (especially in DBT), and the choice of one methodology over another will depend on your needs and those of the business.

If you found this article interesting, check out the Data Engineering category on our blog to see similar posts and share it on social media with all your contacts. Don’t forget to mention us to let us know what you think @Damavisstudio. See you soon!