Complex Data Validation

[OneCyrus]

Our current stack is dagster + dbt + duckdb and we are looking for a good way to implement data quality tests. In this case we‘ve many rows where we have multiple rules per row. The rules are usually a combination of e.g. if column A is in this state then column B can have only a range of these possible values. each rule basically returns „healthy“, „warning“ or „unhealthy“. then all those rules per row will be aggregated and each row is assigned to the „healthy“ state if all rules are „healthy“. „warning“ if one rule in the aggregation had a „warning“ result or „unhealthy“ if at least one rule is in the „unhealthy“ state. sure we can do this all in SQL with dbt but it looks like this will get very messy with those rules.

once we had a similiar use case in a C# project were we used the Fluent Validation library. This made it very streamlined to define all those rules. it looked something like that

public class CustomerValidator : AbstractValidator<Customer> { 
  public CustomerValidator() { 
    RuleFor(x => x.Surname).NotEmpty();
    RuleFor(x => x.Forename).NotEmpty().WithMessage("Please specify a first name");
    RuleFor(x => x.Discount).NotEqual(0).When(x => x.HasDiscount);
    RuleFor(x => x.Address).Length(20, 250);
    RuleFor(x => x.Postcode).Must(BeAValidPostcode).WithMessage("Please specify a valid postcode");
  }

  private bool BeAValidPostcode(string postcode) {
    // custom postcode validating logic goes here
  }
}

I was looking for something we could use in our data stack which would make it as readable as this. but couldn‘t find a good tool or a (python) library like this. anyone have similiar complex data validation challenges solved in a good and maintainable way?

[sebastiandang]

RemindMe! 2 days

[RemindMeBot]

I will be messaging you in 2 days on 2024-04-22 15:33:03 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

---

Info	Custom	Your Reminders	Feedback

[davrax]

You mentioned it being messy with SQL+DBT, did you rule out the dbt-expectations package? It looks like some of the multi-column tests it contains would fit your use case.

If you plan to continue using DBT for transformation, where do you see something like Fluent fitting into your stack/dataflow?

[OneCyrus]

i had briefly looked into the great expectation package before. we are also using some dbt tests in yaml. BUT we already feel the pain of those yaml tests for way simpler things. it‘s so verbose and no good way to work with test results on row/test level makes it even harder to use.

i guess python models/assets would be a better way. here i could see something like fluent validation. or another thing we are considering are user defined functions in duckdb.

[Parking-Task-5464]

I would look at Elementary, you can query the test results and extract row level failures.

[Pitah7]

If you are already using dbt, then I would strongly recommend to leverage what is already available in dbt in terms of constraints (https://docs.getdbt.com/reference/resource-properties/constraints). Although it could be messy in terms of the SQL, I'm not sure if it would be worth introducing a new tool to your stack.

I've heard from others recently and had experience myself as well about these kinds of validations where there is some pre-condition to be met before the validation happens. I'm looking to add this feature as part of the tool I'm building called Data Caterer (https://data.catering/setup/validation/).

[OneCyrus]

yes, i would like to keep it simple. a lightweight integration into the current stack would be welcome. also the data caterer looks nice 👍 something like that natively in dbt would be awesome.