dbt-labs / dbt-labs/dbt-utils

Allow setting different compare_columns for each table/model in equality generic test

Open
#1,000 2 comments 0 reactions 0 assignees View on GitHub
enhancement triage
Dominant language
Makefile
Stars
1.8k
Forks
632
PR merge metrics
No merged PRs in 30d

Description

### Describe the feature
The [equality test](https://github.com/dbt-labs/dbt-utils/?tab=readme-ov-file#equality-source) supports passing a `compare_columns` setting, which allows one to limit the columns which will be compared from each relation. Critically, this setting assumes that column names are identical in both models being compared. In some scenarios, they might not be!

What I would like to do instead is be able to provide different lists of columns for each model, if the column names are different. It would be assumed that the columns are listed in order of comparison, i.e. first column compared with first, second column compared with second, etc.

### Describe alternatives you've considered
I could skip using the `equality` test completely, and write a custom one-off singular data test. Of course, this loses the advantages of using generic tests.

I could also copy/paste the `equality` test into my project and then add the above functionality in my fork. But I'd prefer submitting a PR upstream, because most of the code would be the same. `equality` is a long macro, and to implement this feature would require only minor tweaks... thus it's a significant violation of DRY.

### Additional context

For example, suppose we have two models:

- `old_businesses`: contains columns `business_id`, `introduction`, `business_name`, `random_field_we_do_not_care_about`
- `new_businesses`: contains columns `business_pk`, `name`, `introduction`, `something_else`

I would like to compare the first three columns in each model. The data should be identical - it is just that the column names and order are sometimes slightly different and I need to tell `equality` how to match it all up.

Ideally, I should be able to write a test like:
```
models:
# only compare some of the columns
- name: old_businesses
tests:
- dbt_utils.equality:
compare_model: ref('new_businesses')
model_columns:
- business_id
- business_name
- introduction
compare_columns:
- business_pk
- name
- introduction
```
which says to pick columns `business_id, business_name, introduction` from `old_businesses`, and pick columns `business_pk`, `name`, `introduction` from `new_businesses`, and compare them. If `model_columns` is not provided, then we assume a default equal to `compare_columns`.

Alternatively, it could be structured like this:
```
models:
# only compare some of the columns
- name: old_businesses
tests:
- dbt_utils.equality:
compare_model: ref('new_businesses')
compare_columns:
- [business_id, business_pk]
- [business_name, name]
- introduction
```
which means exactly the same as the first example, except that a list is optionally used in the case when the column names are different.

(Any other ideas?)

### Who will this benefit?
In my case, the two tables being compared are undergoing a migration in the underlying system. There is a double-writing process taking place: when a write is made to one table, then a similar write is supposed to be made to the other table.

Of course, mistakes can be made when double-writing. That's where this dbt equality test could be helpful. We can monitor the two tables and use the equality test to flag whenever there is a difference.

However, the new table has different column names than the old one.

### Are you interested in contributing this feature?
Yes, I can submit a PR. Just would like to know if the feature would be welcomed.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.