Data Observability: A Practical Approach

We know the importance of implementing data observability mechanisms. In this article, we'll cover a quick and simple way to put this into practice. And the best part? It requires very few changes to your existing stack or integration workflows.

Let's start by using a medallion architecture approach, where we have multiple layers representing data from raw (in green) to ready for analysis (in orange).

It's helpful to understand that each layer of the medallion architecture acts as a security boundary, a kind of logical division of our analytical environment. This way, if an error occurs in a raw layer, we can prevent downstream layers from inheriting the same error. In a practical scenario: if we have an error in the ETL, it's better for your dashboard to show no new data for today rather than displaying broken information.

A great way to implement data modeling following the medallion architecture is DBT Core. This project provides a set of tools that accelerate development, such as data models that can be materialized in different formats, extensive customization options, documentation, data lineage, and several ways to run data quality tests, the latter being what we will explore in this article.

There are two main ways to define data tests in DBT. The first is called singular data tests, which is nothing more than a SQL query that returns failing records according to our test criteria (if the query returns no records, the test passes). To implement this kind of test, simply create a file in the DBT project's test directory, like the following example that tests for sales with a positive total value:

-- ./tests/validate_positive_total_value.sql
select
venda_id,
sum(valor) as valor_total
from {{ ref('fct_vendas' )}}
group by 1
having valor_total < 0

However, some data tests need to be applied across multiple different models, right? To handle this, we have generic tests, which are created using jinja in a format similar to creating macros. Implementing a null check would look like this:

{% test not_null(table, column) %}
<pre><code>select *
from {{ table }}
where {{ column }} is null
</code></pre>

For this test to run on the desired model and column, you need to add it to the project's schema YML file. To test if there are any sales records with a null identifier (venda_id), we would implement something like:

version: 2<p></p>
<p>models:</p>

Finally, we run the following command to execute the configured tests in the project.

dbt test

I want to point out that designing every single data test use case can be a lot of work, and often when I implemented tests with DBT, I felt like I was reinventing the wheel. To address this, I suggest using the dbt_expectations package, which brings the data observability power of Great Expectations right into DBT.

To do this, simply add the package to your dbt project's packages.yml file:

packages:

Now we have a wide range of generic tests available for different scenarios, such as:

• Table structures;
• Data types, null, and unique values;
• Value ranges or sets;
• Aggregation functions;
• Multi-column relationships;
• Distribution functions;
• String matching and comparisons.

The best part is that you use them exactly the same way as any other generic tests, just declare the test for the desired column in your schema YML. The null check we did earlier can be implemented like this:

version: 2

<p>models:</p>

To get more details on which tests you can use, I suggest checking out the dbt_expectations documentation.