Streamlining Data Validation

A Practical Guide to Data Recce for dbt Users

As a data or analytics engineer working with dbt (Data Build Tool), you’re likely familiar with the challenges of ensuring data accuracy and integrity, especially as projects grow in scale and complexity. In this article, I’ll look at how Data Recce, a validation toolkit for dbt projects, can simplify your workflow and boost your productivity. I’ll provide a practical examples of its use, building on my previous article of setting up dbt with DuckDB.

The Power of Data Build Tool (dbt)

Note: this is not a paid or sponsored article. I connected with a team member at the company on LinkedIn, which has prompted me to check out their tool. I’m not giving an opinion, only a walkthrough of how it can be used.

What Problem Does Data Recce Solve?

Before jumping into the tool itself, let’s consider the common pain points when performing code reviews and impact analysis:

1. Time-consuming manual checks between environments
2. Difficulty in visualising the impact of changes across models
3. Catching subtle data discrepancies that can lead to significant issues
4. Ensuring schema consistency across environments
5. Streamlining the code review process for data changes

Data Recce aims to addresses these challenges by providing a set of features designed to automate and simplify the validation process.

Setting Up Data Recce in Your Workflow

Let’s walk through setting up Data Recce in an existing dbt project:

First, install it with a simple pip install recce. After that, you’ll need to ensure that within your dbt profiles yaml you have both a development and production environment set. I’m basing this article on my prior dbt setup using DuckDB so, I’ve added a production block as you can see below:

Updated profiles.yml with production schema

After that, I’ve added some packages. These are optional however help with some of the additional functionality and profiling.

Add packages to use some of the advanced diff functionality

I then ran dbt ‘in production’. Note that I have specified the location of my target path, so I’ll end up with my dev target and prod target folders sitting next to each other for comparison.

dbt deps
dbt snapshot --target prod
dbt run --target prod
dbt docs generate --target prod --target-path ./target-base

I then ran dbt again without specifying my target (as my default is the dev environment). I also re-ran the docs. This has generated my target folder.

• ./target-base contains my production build
• ./target contains my development build

Using Recce to Show Changes

Now lets run the app by running recce server from the command prompt:

You can access the UI from localhost:8000. I get to see the lineage for my project. Nothing is in colour/highlighted, as both my development and production environments are the same.

Lineage showing no differences between environments

The change I want to make is to add a dim_date table as a date dimension. This is going to involve adding a new table, and also linking this table into my fact table. I’m going to make the primary key for this table a smartint rather than a date type.

Get Andy Sawyer’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

I’ll keep the dimension relatively simple:

The SQL for my date dimension

My date range runs for the year of 2024, with 1 day per row. I’ve added in some additional columns including a formatted date, quarter etc. Let’s see what this does in the UI:

dim_date now appears in the UI

You can see that dim_date has been added, and is green to show it’s a new model. Next, I’m going to link it to my fact table. This is going to change the field in the fact table which used to simply be a date type and is now a smartint that is linked back to my new dimension table. Once updated, the system shows my fct_sale table as amber — it’s changed.

Once linked the fct_sale table shows as altered

Note that it also flags that the sales_dashboard exposure which we’ve not touched, may be impacted by these changes.

If I select the fact table, I can have a look at the difference. I’ve removed the original sale_date field that was a date type and replaced it with _date_hk which is an integer, and will be linked back to my new date dimension:

Details of the change to the fact table

Once I commit this change through, and there is again no difference between development and production, everything will turn grey, but the changes will now show, with dim_date now in my lineage:

Updated production

Let’s try something different. I’m going to add ‘Not Applicable’ and ‘Unknown’ records to my date dimension. This is a simple union at the end of the SQL query. After doing so, I do a dbt run && dbt docs generate. In the UI, my date dimension is now flagged as amber. Something has changed.

Changes to my date dimension

Clicking on this, and performing a row count, I can see there has been a change:

I can also see this from the Checks tab:

A row increase due to my two new records

We can even run some more advanced profiling (given we installed the additional dependencies) on our data to see what’s going on:

Profile Diff

Histogram Diff

These can be added as screenshots into your PR to make it really easy to understand what is going on and what you’re changing.

You can access the branch with all of my changes by clicking here.

Running SQL Queries

One of the less obvious reasons to use Recce as a dbt-core user is the query screen it gives you. This allows you to use {{ ref() }} tags directly in your SQL. So no more switching them in and out as you transition between VSCode and Snowflake UI:

While out of scope for this article, which focused on the UI component of recce server, you can also use recce run as a CLI option that can be added into your automated builds as part of CI/CD pipelines.

Real-World Benefits

By incorporating Data Recce into your workflow, you can:

1. Reduce manual checking time: Automate comparison tasks that used to take hours (or got skipped over with fingers crossed).
2. Catch issues earlier: Identify problems in development before they reach production.
3. Improve code review quality: Provide reviewers with clear, actionable insights.
4. Enhance cross-team communication: Use Data Recce’s reports to explain changes to non-technical stakeholders.
5. Build confidence in changes: Validate complex transformations with ease.
6. Leverage Ref tags in your development: No longer need to fully qualify your tables and then switch them out for ref tags at the last minute.

Conclusion: Empowering Data Engineers with Data Recce

By automating tedious validation tasks and providing clear, actionable insights, Data Recce allows you to focus on what really matters: building robust data pipelines and delivering value to your company.

Whether you’re dealing with complex data transformations, frequent schema changes, or just looking to speed up your development cycle, it can help you work smarter, not harder. Give it a try in your next dbt project, and experience how much easier it can make your life.

Remember, the key to success with any tool is integration into your daily workflow. Start small, perhaps with cross-environment diffs, and gradually incorporate more features as you become comfortable. Before you know it, you’ll wonder how you ever managed without it.