When you are coding data solutions, sooner or later you should come to a decision point – how to test that what you did is actually doing what your stakeholders expect it to. You may get tempted to start “coding” data tests in XYZ coding language… you name it. For instance, Python, Scala, Java or R … just in the language you personally know the best.
Let’s go a bit deeper and let’s discuss why this proprietary coding approach might not be leading where you, your team and your stakeholders want to go…
The stage
When you process data for analytical purposes, AI ingestion, or for any other reason, your solution looks similar to this:
Lots of source data. You ingest them. You cleanse them, enrich them, transform them, model them for analytical purposes. And eventually people see the results in their reports.
In this blog post we’ll try to persuade you it is not enough to test data only in your code, during your data transformations, or after your transformation logic is executed.
I am “coding” data tests using language XYZ (like Python). Is that wrong?
You may have a nice notebook with many lines of hardcoded crosschecks. You may be using Great Expectations, dbt test, pytest, whatever other tool, or even your custom Python scripts to check the data.
You may feel all your data is tested, and therefore correct.
However.
At the end of the day (processing) are you able to answer the following questions?
- Do your users see correct data?
- Are you sure?
- How sure are you?
What you have is NOT wrong. It is great you did it. It might have helped to catch some problems. But still – do your users see correct data? Do your tests give you and your users enough confidence? Because that is what matters – that is the purpose of data testing.
What your “coded” data tests might be missing
We have a couple of questions for you. Please think about each for a while. We will wait.
- Are your users aware that data might be outdated?
If your ingestion or transformation pipeline(s) fail or do not trigger at all, do users know they are looking at wrong or outdated data?
- Are your users aware that the data might be even wrong?
If any of your tests fail, are your users aware that something is wrong? Do they understand whether they should or should not use their dashboards for decision making?
- Do you collect feedback from everywhere?
When there is a bug in a Power BI measure (e.g. due to a new or changed relationship, due to a fix that introduces other bugs in dependent measures, …) – will you even know that this happened?
- Do you personally plan to maintain the “coded” tests, in the future?
Are you OK with the fact that you OWN the tests, and thus are responsible for them? And in case you have another plan, are those maintainable by junior developers, business users, or other stakeholders (maintaining your tests require Python knowledge, right?)
How to gain the necessary confidence
Tests. But smarter ones. Lightweight tests where you cross-check almost everything – sources, transformations, reporting layers etc. Something that stands outside your system and checks the results (what users work with) against expectations!
Imagine a user watching a visualization of customer segmentation. Does it show all the customers a user expects?
For instance, simple comparison of data from CRM and data from Power BI semantic model (SQL vs DAX) might reveal that. And if that finds a mismatch, you know the user is NOT working with correct data.
Consider the following:
- In the first place, it does not matter what caused the problem. The user is not working with correct data – that is what matters. Whether it was a missed pipeline run (that runs your tests in XYZ by the way), a bug in transformation logic, an incorrect incremental load to Power BI, problems in your semantic model – it is not important, and users even do not want to understand that. Test the results the users work with.
- The comparison can be extremely simple and can be automated. Junior developers or even business users can do that with proper tooling.
- Such tests, with proper names and descriptions, are comprehensible for end users. They explain the extent of the problem more clearly – a feeling that “it seems all UK customers are missing” is more worth for end user than information that “tests for customers_silver_to_gold_transformation job failed”
- These tests should be completely independent of your workflow coded in your PySpark notebooks.
Ensure you are not missing anything
Keep your tests coded in XYZ language. No problem with that. But be aware that they are useful to catch only some of the problems.
So enrich your system with tooling that supports at least:
Easy cross-system checks
Go above your systems. Have an overview of how your data is doing, collected from all over the place. Compare data between various systems, layers, etc. And make it simple!
It’s very difficult to report to users the quality of the data they are using on top of several, proprietary coded crosschecks.
Transparency
Your users should understand WHAT is wrong and TO WHAT EXTENT. They need to understand the problem, and mostly understand what impact it has on them! They need to have this info at hand constantly.
This means that you need to be able to present the results in various formats – technical ones for Azure DevOps or other CI/CD platforms, MS Excel for your team or stakeholders, fancy reports for end users, etc.
Collaboration
We believe anybody who knows very basics of SQL can author tests. We make it possible. Drag your junior developers into the process. Chances are that your business users know SQL too (it is common in every mid-sized and bigger company) – drag them into the testing process as well – they care about their data, they want it to be correct, thus they are always willing to help.
Cost-effectiveness
The previous point, collaboration, has also an impact on costs. Anybody with basic knowledge of SQL can find problems in your solution. And not only that – you can implement the above things, to some extent, using Python. But then compare the cost of creating/testing/documenting/maintaining such a solution with cost of a ready to use solution.
CAT was designed to address these things.
It’s in its DNA.
Try it. You’ll be amazed by what all you’ll uncover and what all nasty problems you are able so easily to prevent.
The ease of setup (first automated tests in a few minutes), ease of integration with other systems, very flat learning curve, up-to-date documentation, rapid release cadence, all of that together with low cost makes CAT a go to solution. Coding of proprietary data testing approaches just is not worth it.
