On-Demand Webinar · 46 min

Test Coverage: The Software Development Idea That Supercharges Data Quality and Data Engineering

Software teams stopped shipping on a colleague's eyeball thirty years ago. Data teams did not. What full test coverage means table by table, why the arithmetic runs to thousands of tests, and the three design choices open to you depending on what you control.

Presented by Chris Bergh

What you'll learn 6 points
  • Test coverage in data means automated checks on raw data and on data that a tool or piece of code has acted on. Spot checks, ad hoc looks, and a colleague who knows the business saying "that looks right" are not coverage.
  • Two things need covering at once: production, where the code is static and the data keeps changing, and development, where the data is static and the code keeps changing. Many of the same tests serve both.
  • Shift left means catching a data problem earlier in production; shift down means catching a code problem earlier in development. Both follow the 1:10:100 rule — a dollar at the source, ten in a report, a hundred in front of a customer.
  • Full coverage has two halves: every table and every column at every level of the database, plus every business metric someone has memorized, and every tool checked for errors and for timing.
  • Rules of thumb: two tests per table, two per column, at least one custom test per significant metric. In the three-layer worked example that is roughly 2,500 tests — about seven months of work at 30 minutes each, which is why they have to be generated rather than hand-written.
  • Data lineage is the blueprint of the building; a Data Journey with tests is the fire alarm panel. Lineage says five tables could be affected — coverage tells you only one of them actually failed.

Prefer to read it? The written version is in Webinar: Test Coverage: The Software Development Idea That Supercharges Data Quality & Data Engineering.

Slides

59 slides

Transcript

Chris Bergh

Show chapters and dialogue 42 chapters · 7,769 words
  1. 0:00 Welcome, and the three things this session covers
  2. 3:34 Analytic processes are both a factory and a software project
  3. 4:47 What test coverage is for: no surprises, no broken deploys
  4. 5:45 Test every step and every tool in the pipeline
  5. 7:05 Shift left and shift down, defined for data teams
  6. 8:21 Why a defect found earlier costs less
  7. 9:24 The 1:10:100 rule, and a definition of test coverage
  8. 10:37 Spot checks and a colleague's eyeball are not coverage
  9. 11:32 What counts as a test: before the fact and after the fact
  10. 12:47 Avoid the fact, dynamic testing, static analysis, hope-based testing
  11. 13:44 Before the fact: instrumented pipelines that stop on error
  12. 14:48 After the fact: poll the database and poke the right team
  13. 15:39 Development testing: end-to-end runs and static analysis
  14. 16:46 Unit tests, hygiene checks, and where the real leverage is
  15. 17:40 What most teams are actually doing today
  16. 18:32 What full test coverage means: every level, table, column, metric
  17. 19:35 Which test types belong on a table, a column, a metric, a tool
  18. 20:34 Rules of thumb: two tests per table, two per column
  19. 21:34 Counting the tests a three-layer system needs
  20. 22:35 The arithmetic: thousands of tests across bronze, silver, gold
  21. 23:41 Thousands of small, fast tests is normal, and it wins arguments
  22. 24:32 Writing tests by hand doesn't scale: seven months of work
  23. 25:32 Recap, and on to what the open-source tools can do
  24. 26:26 Getting to 80% coverage automatically
  25. 27:20 Profiling characteristics, semantic data types, derived tests
  26. 28:14 Freshness, volume, schema, and drift tests
  27. 29:20 The 20% of tests that are specific to your business
  28. 30:21 Template tests: reference match and distribution shift
  29. 31:22 The aggregate balance test, and the metric a customer memorised
  30. 32:25 Perfect data, broken tool: why tools need monitoring too
  31. 33:28 Data lineage is the blueprint; a Data Journey is the fire panel
  32. 34:24 Lineage guesses at impact, test coverage shows it
  33. 35:25 Hygiene detectors, and red light green light in development
  34. 36:19 Coverage in a medallion architecture across dev, QA, and production
  35. 37:10 Covering bronze, silver, gold and the tools sitting on them
  36. 38:11 The new test coverage report, and an open question for users
  37. 39:10 Quality circles: grow coverage after every incident, without blame
  38. 40:10 Design choice one: improve the source data you don't own
  39. 41:22 Design choice two: poll your database and ping downstream
  40. 42:20 Where the data observability vendors' approach falls short
  41. 43:16 Design choice three: full coverage across data and tools
  42. 44:19 Why teams avoid testing, and the love of heroism

00:00:00 Welcome, and the three things this session covers

Chris Bergh: Hello everyone. I'm Chris Bergh. We'll start in a few minutes. We'll wait a minute or two for people to join. So we might get started. So my name is Chris Bergh. I'm CEO of DataKitchen. and before we start this webinar, I'll have a few things I want to share. the first thing is that the slides and the recording and the transcription, I I'll put up a little website and I'll share it today or tomorrow. if you see on the right hand side of Google Meet at the corner there's a thing looks like a chat window. And so if you have questions, put it in there and and I promise to answer them. I may answer them right away or I may answer them at the end. and we've targeted 45 minutes, but most likely it'll take an hour for this presentation. So, we're going to talk about three things. So, we're going to start with an overview of testing and test coverage in data and analytic systems.

00:03:34 Analytic processes are both a factory and a software project

Chris Bergh: Then, we're going to kind of talk about the art of the possible to achieve test coverage. And then we'll give some design choices on how you can achieve test coverage. So first of all kind of going from the beginning so when you produce a report or a model or an extract or dashboard we think of analytic processes like manufacturing materials i.e. data are go through a series of steps. and the manufacturing stations are the tools that you go on and you have an output the the data and the charts and the model and however analytic processes are also like software development in that you build something usually on your desktop and then you deploy it into production and you've got and both of those things have to happen together. And so we've written several books about this idea that you've got this what we call the value in the innovation pipeline. And both of these things are often happening at the same time. You know, you're running something in production and you have a data engineer or a data scientist working on a new model or a change or adding a new data set.

00:04:47 What test coverage is for: no surprises, no broken deploys

Chris Bergh: And so the thing that you want to avoid and sort of why do test coverage is number one you don't want to learn about problems from your customer. You don't want them to say hey look this chart looks weird or this data is wrong or I can't trust you team you you my data team you're always getting data wrong. And then the second is you don't want to put something you don't want to have that change to a model or change to some SQL break what's in production once you deploy. So you want to have speed and low risk of deployment. And so test coverage helps you with both not breaking things not having embarrassing efforts and then not breaking things when you change it. And so where is test coverage? So I guess we're going to in this discussion we're going to break it down into two buckets. One is what we call production test coverage and that's in that value pipeline. That's in the data and analytic system that you have in production.

00:05:45 Test every step and every tool in the pipeline

Chris Bergh: And then the second is in development where you're making changes to that data and analytic system usually code that you're doing or it could be environment or could be new data sets. and so the idea of test coverage, and we'll talk about what testing is in a little bit, but that in the production, your code itself that's acting upon data is static, but the data itself changes. You're getting updates. And then in development, your data is static. You're using test data, but your code is changing. And so there are tests across all these parts. And often these tests can be the same. They can they they can they can serve double duty. And so where is testing? So in the in your in your production pipeline or sometimes we call it the value pipeline, you want to test every step and every tool in your pipeline. So you want to know that things have worked. So maybe you get some poor data from your data provider or maybe you've joined that data to another table and something happened in the ETL process or maybe that joined data is being added to from a segmentation model or that's being actually shown in some charts and graphs at the end and maybe they that didn't get loaded or someone applied some business logic.

00:07:05 Shift left and shift down, defined for data teams

Chris Bergh: So all these places are where you can find problems and the whole goal of this of having coverage is to make sure that you don't find errors in front of your customer likewise when is it well I we're going to break it into two parts and and sort of shift left and shift down and and unfortunately these terms mean different things in different contexts right and so the I'm going to use them in in in our context. but the term shift left means executing data quality tests earlier. So if you find a problem in raw data, it doesn't get to your customer. So therefore you have saved yourself time and effort. So finding problems earlier in production that means shifting left is a great benefit. Likewise, if you find problems earlier in development, you don't deploy them into production. So, you shift down. And so, these actually have different meanings in software development. Shift left is actually equivalent to shift down in our in our in our nomenclature, but we're going to use these terms kind of as part of the discussion.

00:08:21 Why a defect found earlier costs less

Chris Bergh: So, we mean shift left means finding things earlier in production. Shift down means finding them earlier in development because again you've got two processes going on that you have to get coverage for. So why why shift down? Well, this is a a chart that if you come from the software world makes sense. If you find problems earlier and they don't get into production, you have a multiplicity of cost savings. You know, 10x, 40x, 640x, right? defects found earlier. And if you notice this graph on the bottom right here where it says coding, unit test, functional test, system test, release, these are levels of testing, and we'll talk a little bit about these words and how they apply to data and analytic systems. But if you shift down, i.e. you find them more towards the coding and unit test level, it's so much cheaper than breaking production and having to rebuild it or do something else. Likewise, there's a similar curve in shifting left, i.e. I find the problems in the source data or in the raw data.

00:09:24 The 1:10:100 rule, and a definition of test coverage

Chris Bergh: A very similar curve applies and one way to think of it is the 1:10:100 rule. So for instance, if I find a problem at its source, that costs me a dollar to fix it. If I find that gets farther, it gets into a report that's $10. And if it's failures, it shows up in front of your customer. Well, that's $100. And so that 1:10:100 rule applies here. Finding it earlier is better. and you notice these curves sort of look alike. but it's it's a very similar finding it earlier, shifting it left or shifting it down. It saves time, it saves money, it saves hassle. And so let's just define what test coverage is. So it's a term that comes there's test coverage in software and so I'm using it in data and analytics systems. So I I think it makes sense to define it. So we're talking about something where it's automated checks and specifically it means checking data that has been act either the raw data or data that has been acted upon by some tool or code like a pipeline, a model, a visualization, a transformation, an output.

00:10:37 Spot checks and a colleague's eyeball are not coverage

Chris Bergh: And so the higher the level of your coverage, the more confident you can be that the data you produce is accurate. And a lot of organizations kind of rely on spot checks or ad hoc or manual. And that doesn't give you test coverage. Having, you know, your buddy look at it who happens to know a little bit about the business say that looks right. and it's the same thing in software. And so I have some gray hair. I was part of the software industry back in the 90s when the way we tested it was the same way we test data and analytic systems. Now your buddy looked at it. You said it was good. released in the production and then you were up at midnight fixing things or and so I still remember in 1997 being on a website with three million people recoding it while they were there and kicking them on and off. And so we don't do that anymore in software, right? We every software system has automated tests against it.

00:11:32 What counts as a test: before the fact and after the fact

Chris Bergh: And so we don't do that in data and analytics. And so, and one way that software teams measure the amount of testing they have is is test coverage. And I think that we need to have that same idea here in data and analytic systems. And so that sort of begs the question, what's a test? And so tests are automated ways to check for the correctness of a system. And so what that means, we're checking for the correctness of data or we're correcting correctness of outputs of data. And so tests are run in production and development automatically. And so if we think about production testing and development testing obviously you want in production you want to find the problems kind of before it gets before the fact right. testing is done as sort of part of the production process because then you can stop it or and hold off production in order to find and so you want to have tests sort of part and parcel of your production process and well what if you can't do that what if you don't control your production process some other team runs the ETL you're just a data quality person well you can still do testing you just do it after the fact right you can periodically test your database That's good, right?

00:12:47 Avoid the fact, dynamic testing, static analysis, hope-based testing

Chris Bergh: You could catch it before you get to production. you know, you still have the remediation, but you it's better than what I think a lot of teams do is you just sort of avoid the fact there's no automated testing in production and and failures. The only time you know about failures is because your customers tell you about it. And a similar sort of trio happens in development, right? There there's what I call dynamic testing. You're executing validations against real data and real code. And then there's another one called static analysis. You're checking the code or SQL for kind of syntax problems without kind of running real data. And then I there's another one that I cheekily say is hopebased testing. you're no formal tasks. You're kind of reliant on intuition or you know you're having somebody look at it and they click around and go that that feels right. So, kind of going into these production testing types. So, we've got, you know, before the fact, after the fact, and avoid the fact.

00:13:44 Before the fact: instrumented pipelines that stop on error

Chris Bergh: So, how to do before the fact testing? you want to be able to test every step of the process and stop it if necessary and find the impact across all the production steps and tools. And so, this allows you to find problems before your customer sees them. And you need to instrument your pipelines. And so we're going to talk about how to do that or use a testware orchestrator like DataKitchen or Dagster or dbt where the tests are part of the step-by-step process and as the step is going through you're checking and stopping and then you've got to perhaps redo it or restart it. and so this way the the problems don't get into production. or if you don't have a testware orchestrator you know I think the I'm not sure what you do but like that's part of it having tests across your tools looking for errors errors from data errors from tools failing and then having a process that controls that. So after the fact testing, well, let's say you don't have that and you're just running it, right?

00:14:48 After the fact: poll the database and poke the right team

Chris Bergh: Some other teams running the ETL process. well, what you can do is kind of pull. You can hit your you can kind of pull your database every 20 minutes, every hour and look for failures. And then you use data lineage to actually be able to poke the team, the correct team that has the impact. So, it's kind of a pole and poke metaphor. I'm going to pull the database and I'm going to poke people when there's problems. And the benefit is you don't actually need to instrument your pipelines. You just have this polling your database. The problem is you actually allowed problems to get it into production. And now people have to scramble to fix them. But at least you found them, right? It's better than the bottom, right? I'm just avoiding it and kind of I'm eyeing it up. I'm looking at it. I'm waiting for my customers to yell. And so these are the three different sort of categories or types of production testing.

00:15:39 Development testing: end-to-end runs and static analysis

Chris Bergh: And then development testing and and I think these are at least the first two are not mutually exclusive. So you want to catch real world failures at runtime and that means integration tests or what are called endtoend tests. So I'm pouring in realistic test data. I'm running the system in development and I've decorated that system with a bunch of checks across each step across each tool and I've got good coverage on it and so that's dynamic testing real data against a real system with tests in a development environment. There's another case called static analysis and this is a case where I'm running tests as part of your deployment process like when you commit to git and you are then have an integration step your what's called continuous integration continuous deployment CI and CD there's different ways that you can do it one is called linting which is a syntactic way to look for incorrectness in the data there's SQLFluff which is a linter and it there's a dbt it's got a static analysis feature and and that's good You know, my experience with software is it's kind of nice to lint it.

00:16:46 Unit tests, hygiene checks, and where the real leverage is

Chris Bergh: It happens fast. The same thing with unit tests. They're meant to run kind of like a snap of your fingers. And so what happens is I check something in and I run a whole bunch of quick tests on it. And so things like a Python, if you're doing your ETL in Python, there's unit test frameworks. But you know, these are not enough, right? another case is kind of things like what we call in data a data hygiene test. So I go in and look at a data file and look for kind of static I do a static analysis of the of the problems in in it. And both of these I think can be used together. I'm much bigger fan like you won't be successful with just static analysis. You need to have dynamic testing and obviously hoping never works. So like where do people actually where should you do it? I think doing kind of, you know, it some static analysis is good.

00:17:40 What most teams are actually doing today

Chris Bergh: If you have to do it after the fact, but doing before the fact dynamic testing, that's where you get your most leverage in coverage. where do people actually do it? Honestly, I think most people are kind of in the hope and avoid the fact most teams are. I think with some newer tools, people are doing after the fact. They're doing some static analysis. Like to me if you look at testing in the market they say do data observability which we're going to talk about as an after the fact type test and then they're doing unit tests but I I I think this is this is where very few c very few people are actually doing and I think that's where we should spend our time. so I've gone through kind of you know what are tasks how to test different test types what teams are doing it. Now let's go to the measurement part. So test coverage has got a you want to have a report saying I'm fully covered.

00:18:32 What full test coverage means: every level, table, column, metric

Chris Bergh: So what does full coverage mean? What does full test coverage mean? So it should be in two parts. One is all the levels or zones in your database. So every data analytic system I've ever seen is at least got two. I've got raw data and integrated data. There's the so-called medallion architecture where I've got raw data kind of lightly mastered or cleaned up data and then I've got integrated data. Oftentimes there's more. There's raw data, there's lightly mastered and cleaned up data, there's integrated data, there's customer focused data. I've seen L5s, L6s. It's crazy how often people are are are putting data in different places. But every table that you have, whether you have two levels or six levels, should have data quality tests on. And every column and every table should have data quality tests on. And every significant business metric, every kind of domain specific thing that you look at, maybe you're not in business, maybe you're in government, but like everything that you measure should have tests.

00:19:35 Which test types belong on a table, a column, a metric, a tool

Chris Bergh: And why is that? Well, somebody who is your customer will have memorized that metric and are looking for significant shift shifts on it. And then every tool that uses data should be checked for errors in timing, right? Because again, your your data could be perfect, but your tool could have an error or take too long. So, what types of tests? So, every table should have what are called consistency checks. I'm looking to see if something changed. It's inconsistent from the way it was before. That's volume, freshness, schema. Every column in every table, I should look at it and then say, did something significantly change in that column? Volume, freshness, drift. And every business metric should have tests. Is it out of whack? Did it drift somehow? and then every tool as part of your system should be checked for errors and timing. You want to look in logs to see if there's an error. You want to check metrics.

00:20:34 Rules of thumb: two tests per table, two per column

Chris Bergh: Maybe my database is at 95%. You want to check every task and sub-task for errors. And then check timing and duration. So all these things have to be right to to judge the correctness of the system. The data has to be right. The tools have to be right. Right. And and the tools tell us things like logs and metrics and task status and duration and start and end. Those things are you know whether you call them tests or monitors those have to be checked and have to be right so you can ensure the correctness of the system so you have adequate coverage. So what are some rules of thumb? So every table should have minimum two tests. Things like did it get really big or really small right? Did every column should have two tests. every significant business metric should have at least one test and then every tool that uses data. So that's a lot of tests there. So before I go on, that's a lot of tests, right?

00:21:34 Counting the tests a three-layer system needs

Chris Bergh: And so but what's the challenge here in having errors? Again, it's that one to 10 to 100 rule. having good test coverage is worth it because you're saving yourself time later on. And so yes, you have a lot of tables. and so yes, you have a lot of columns, but having adequate coverage is important. And then every tool that uses data should be checked for errors and timing. Sometimes there's two, three, four, but you want to look for errors in your logs. sometimes you want to check metrics. I guess that could be optional. you want to check every job for for failures in task status. You also want to measure every tool and every job in that for timing and duration. So what what's an example of that? So in the system here there's it's a little bit hard to see but there's sort of StreamSets Airflow and dbt then Airflow and dbt again there's an L1 database an L2 and an L3.

00:22:35 The arithmetic: thousands of tests across bronze, silver, gold

Chris Bergh: So and there's if you think about it there's three database levels L1 L2 L3. There's three tools, StreamSets, Airflow, and dbt. And then there's five jobs to monitor, right? Because and so let's let's take an example. Let's say L1 has 100 table with 10 columns per table. So you have a minimum of 200 table tests and 2,000 column tests. Now L2 that also has 100 table and 10 columns. So again, you have 2,000 tests. L3, it's got 10 tables because again, it's an integrated database. plus 30 columns. So you get 20 table tests for 600 column tests plus a handful of domain specific business metric tests. And again I maybe it's more than a handful. I actually think these things are amazingly good. three tools. Well, I've got to check my tools for errors and and six jobs, right? I've got to have 12 checks for problems. And so that's a lot of checks, right? And so, having sort of thousands of tests that run across every level is, certainly sounds like a lot to people.

00:23:41 Thousands of small, fast tests is normal, and it wins arguments

Chris Bergh: It's something that my company and I have been doing for decades. Tests are small. Tests are fast. Having thousands of tests. I think a decade ago, we had a customer, we had 10,000 tests running against all his, he had hundreds of tables, sort of 50 different data sources. And he loved to say that. He loved it. every time a customer says, "Oh, this looks weird." He goes, "Well, we have 10,000 tests." help me understand. And then the customer would say, "Oh, you have 10,000 tests." And so, yes, they have some computational cost to run, but it's so much better to have these tests not only from being able to say that your data team's awesome, but also finding problems earlier, saving yourself time, getting adequate test coverage. So, here's the challenge here. Writing these tests manually doesn't scale. Right. so we're going to talk a little bit about our our tool in a minute, but like let's say I'm a trade and data engineer.

00:24:32 Writing tests by hand doesn't scale: seven months of work

Chris Bergh: I've got 2500 tests to write on 20 tables, 20 columns. I've got 30 minutes to do each test. That could take sort of seven months to write all those tests. Now, that's not particularly bad, right? If you if you look at it, maybe 20% of your time is writing tests. I've said that in many times. And as a software engineer, I've had this built-in bias that good software engineers write their own tests. And so, yeah, it takes some time. but it's always worth it. And I've sort of had this view that like, you know, damn it, do you know, do your own testing. however, over the past several years, we've noticed nobody writes enough tests. And so, we built this technology called TestGen that actually writes tests for you. And so now a junior operator can generate these 2500 tests in just two two button clicks. So this is like minutes instead of months. And so that's a really big sea change I think in how we can work.

00:25:32 Recap, and on to what the open-source tools can do

Chris Bergh: Not only because me being grumpy old software engineer data engineer says damn it you should write tests but now it gives you enables you to do that very quickly. and also and it tries to get you these a whole bunch 80% of the way there and writing all your tests. So think of it this way. How long does it take to run wipe tests for you? Do your engineers know what tests to write? How much does how much is one customer finding one error going to cost you and your teams in time? So that sort of is the beginning sort of overview of testing and test coverage and data and analytics systems. We talked about shift left. We talked about shift down. We talked about the types of testing. We talked about how to count test coverage. we talked about, you know, testing in development, testing in production. Now, let's look at what's possible and and with our free open source tools. So, we're going to walk through the next part is sort of using those tools and walking through them.

00:26:26 Getting to 80% coverage automatically

Chris Bergh: And so, if there's any comments or questions, feel free to put them in the chat. I'm I'm looking. I don't actually see any right now. And then when we get done with the section, we're going to talk about your design choices and test coverage. So we've got about 30 minutes left. So so the first part is like how do I how do I get all these tests? Like how do I get 80% there? How do I get 80% coverage? So our tool TestGen is is a way to sort of automatically get test coverage, automatically get consistency testing across all the tables and all the columns on tables. And so what it does is it learns the data sort of enables you to set have tests on every table and every column quickly and easily. And there are a lot of small fast tests and enables you to get broad coverage. So, think of it like sensors or burglar alarms where you're putting it on every door, every window.

00:27:20 Profiling characteristics, semantic data types, derived tests

Chris Bergh: You know, lasers across the front of the room that people walk in. but it does it in a smart way. It infers it from profiling data and from what we call semantic data types and then it generates these tests. And so what it does is the real key is deriving the right tests. So there's characteristics of the data that TestGen does. has got 51 profiling characteristics. Part of that is developing what we call semantic data types and then it takes those semantic data types and automatically generates the right tests for the right column. So there's some a lot of smarts in here to be able to make that happen from the profiling data and the semantic data types to automatically generate tests. And this is really good because it can help you get coverage. And I won't go through all these tests here, but like here's the kind of test that it has. So the first one is freshness. like how to tell if you got something new in a table or something new in a column, right?

00:28:14 Freshness, volume, schema, and drift tests

Chris Bergh: Or something wrong that's in in in there. And then there's volume. Well, there's volume in the table, but there could be volume in the records, right? There could be duplicate rows. and so this is again testing the data table itself, but the column itself for inconsistency in volume. And then lastly, our second to last, how do you tell if something's wrong in the schema if the data fits? Well, there's the schema, the DDL, but then there's what's in the data. Like, does it not fit the pattern? is the format wrong? Is the address wrong? you know, if I've if when I looked at the data first, there was a million or records, there were only five different types of things. Did I get a six now when I got new things? So, this consistency is is really important. And then drift like how do I tell if the data is sort of good enough? And this is like using sort of statistics like Cohen's d algorithm the z-score to look at the shift in values like percent missing percent unique and so they're using statistical methods to see if there's some drift based on the data.

00:29:20 The 20% of tests that are specific to your business

Chris Bergh: And so all these tests are really trying to say press a button, give me 80% coverage across 80 across all my columns and all my tables and a bunch of tests that can computationally run in seconds or minutes. So that that's what's trying to drive this get data test coverage across all your levels in your database. Now, we believe that it's not 100%. There's always going to be the 20% that's specific to your world, specific to your business. And I'm a big believer that these things are really important. Like I think I think of testing like if I can find the right metric to test and I look at if there's some shift in it that metric that's a really that represents a whole bunch of stuff. So I'm a big fan of these tests, right? And they tend to be kind of very specific to your industry like the number of medical practices shouldn't exceed the number of doctors or there's no shipments on Friday or sales of our product shouldn't increase more than percent that percent you know as compared to last time and they can be built by data engineers.

00:30:21 Template tests: reference match and distribution shift

Chris Bergh: And what we've done is kind of we have templates and we've embedded statistical me measures like the relative entropy comparison to look at two different ones. So, we've got a bunch of these what we call referential tests. They're sort of fill-in-theblank tests. And so, here's an example of two. One is called a reference match. so here's a case like I want to look to see that one table's got a bunch of values and then another table's got a bunch of values and are they different in kind or you know in in that these combination of values have shifted and then here's another case of distribution shift using something called Jensen-Shannon divergence. So, let's say I want to look at I've got I've got shipments of materials per state and construction projects per state. Is that like I'm shipping a whole bunch to to a state, but they don't have a whole lot of construction projects. That's that doesn't make sense. And so, that's a way to catch the shift between those things.

00:31:22 The aggregate balance test, and the metric a customer memorised

Chris Bergh: And again, is it states and construction materials? That has to do with your domain. But these are sort of quick template driven fill-in-theblank tests that you can run and then manage. And then one of my favorite ones here is what we call an aggregate balance test or what I call a historic balance test. And if I look at sort of time one and I see I've got volume 575, but I I've got these product groups here and one is 225, one is 350. Now time two, the volume hasn't changed very much. 585 compared to 575. Well, that's good. You know that these are pretty close. But this product group here shifted and therefore my aggregate balance is now 370 versus 215. That's a big change. 370 to 225. And I personally have been yelled at by this these types of things where a customer looks at it and goes, "This is wrong." And I'm like, "No, the data is right." He goes, "No, this metric is wrong." you know, our product group G1, it was 225 last week, now it's 370 this week.

00:32:25 Perfect data, broken tool: why tools need monitoring too

Chris Bergh: Your data team's all idiots. So again, being able to build these things, looking at the tip of the pyramid, very specific to your industry, we help you do that quickly. And so I've talked a lot about our statistical methods. Now, so we talked about sort of broad consistency testing in data and domain specific consency. now your data in your database could be perfect. I've got coverage everywhere. You could still get it wrong. Well, how is that possible? Well, something happened with your tool. It took too long. It had an error and and didn't get complete. You know, the export didn't run. So, the so the reports didn't refresh. so we have this idea that of this idea called a data journey that's in our DataOps observability tool and that you've got lots of things acting upon data, your viz tools, your pipeline tools. Now those things need to be checked and so you've got lots of these data journeys in your organization with lots of tools.

00:33:28 Data lineage is the blueprint; a Data Journey is the fire panel

Chris Bergh: And what we're trying to do with this idea of a data journey is judge the expectation versus reality. Like I've expected all these the data to be perfect and it to take two hours. It took four hours. I expect the data to be perfect and I expect the reports to be refreshed. They're not refreshed. So what's the variance between expectations in the reality? And so we think that this is complements the idea of data lineage. So I think of data lineage as a blueprint of a building, right? And I think of data alert data journeys as a fire alarm control panel. Now something goes goes wrong? if I'm a fireman, I walk into a building, which do I look at first? Does he or she look at the blueprint or look at the fire alarm control panel? Well, obviously they look at the fire alarm control panel, right? So data lineage, think of it this way, data lineage shows where the fire might spread. So having the blueprint says, okay, there's a fire on floor one.

00:34:24 Lineage guesses at impact, test coverage shows it

Chris Bergh: It may go up to floor two and look at this room above it. Fantastic. but if I've got but data journeys with tests show where it's actually burning. So you could look at your blueprint say, yeah, it could spread to the second floor, but you but is the fire alarm actually going off on the second floor? And so data lineage is kind of a guess on the impact, but complete test coverage shown in a G data journey is clarity. And so data lineage may show five tables impacted, but if you've got test coverage on all those tests, you can see that it has only one error. So this is why we think this is a really exciting idea on how it complements the idea of data lineage. So this is testing in production, broad automatic consistency testing, domain specific testing, testing your tools during the data journey for errors and SLA. Now testing it in development. So this is again we're talking about testing in production, testing in development and and we're going to talk a little bit about offline in a second.

00:35:25 Hygiene detectors, and red light green light in development

Chris Bergh: So one of the first things that you do in development is you get new data. And so I want to find out if that new data is reasonable. And so we have something called hygiene detectors. There are 31 checks. We've released four just this just yesterday. Four new ones to look at kind of hidden problems in data. And the new ones that we found are like non-printing characters, inconsistent casing, invalid zip codes, and so these things are they could be there are sort of ways to look at the data and say, is that right? Is that wrong? Maybe you want to patch the data, maybe you want to push back, maybe you just want to let it go on. Another type of what you do in in again we talked about this is sort of testing as part of your CI and CD process and conceptually here in production you're running and I've got this big red green light. I take production data. I've got my analytic customers.

00:36:19 Coverage in a medallion architecture across dev, QA, and production

Chris Bergh: I've got a nice green light here. Fantastic. However, in development you're using test data and you don't have customers. You have your developers or your data team. And what you want to do is basically say, yeah, you want to have this show up green that all my tests and all my monitors are passing. I did not break any code. I can safely push to production. That's basically the idea. having the same sort of stop stoplight on your data and your tools and your data in development as into production. So what that looks like in a let's say a medallion architecture is that I've got coverage. And so this medallion architecture. So first on the left here, this diagram, I've got a development environment. I've got a QA test environment. I've got a production environment. So I'm doing CI and CD here. So the environment itself is a is a medallion architecture. So I've got a bronze layer, a silver layer, and a gold layer.

00:37:10 Covering bronze, silver, gold and the tools sitting on them

Chris Bergh: And then I've got some tools, Airflow, dbt, Power BI acting upon it. So I want to have test coverage on all my tables in the bronze and the silver and the gold. And then I want to cover all my tools. Airflow, dbt, Power BI. So again, data is perfect and the tools don't have any errors. And then catching that during production, catching that while, excuse me, in development, while you're pouring in test data, that this coverage, adequate testing, adequate tool monitoring, that those things give you the red light, green light that you're actually correct. And so just running things like unit tests or eyeing it up is insufficient here. The sufficiency comes from having good test data, good environment. So lastly, how do you improve offline? And so we're getting close to the end here. We got one more section. So one way to do get improvement offline is to do data quality dashboards. And our last webinar we have a new feature on this.

00:38:11 The new test coverage report, and an open question for users

Chris Bergh: And this is a way to judge and measure source data quality kind of I want to make sure all my source data has changed or is good. now we have a second we have actually a new feature in TestGen we call it test coverage reports and I threw out some sort of rules of thumb two tests per column two tests per table tests across all levels in your database. And so we we've have a what what's called a data catalog. And then that data catalog will show the number of active tests per table. Here's the table and here's the column. And so this is done by an export. So we just released this. it helps you spot gaps in test coverage. I don't know, one of the things that we'd like to have asked the sort of community of users, do we actually need a a coverage dashboard like we have our data quality dashboard in the previous slide? Do we actually need to have a test coverage dashboard?

00:39:10 Quality circles: grow coverage after every incident, without blame

Chris Bergh: But right now, you can do it by doing an export. So, that's a good question for people. Love to hear your answer. And then lastly, kind of offline looking at how you run your data journeys. Did they take too long? Is there one that has problems? And then I think lastly, but not least, I think if you're going to have good test coverage, that means you're going to have to sit down and look at when the test failed or when you had problems and then kind of react to those in a no shame, no blame, love your errors. Look for operative needs and improvement. These are very complicated systems, multiple tools, hundreds of data points. and so what you want to do is look at when you have incidents and then reflect and add tests, add more coverage. The whole point here is is you want your test coverage to increase over time. And you want to basically at every point along the way, you want to have meetings called quality circles where you're looking at what's the right improvement I can make to improve my coverage.

00:40:10 Design choice one: improve the source data you don't own

Chris Bergh: And do that in a sort of way where you don't have shame and blame. So lastly, what are your design choices? So we've talked about the art of the possible with our open source tools. so where do you start? Well, the first is like, you know, do you have any data quality checks in your data? Do you have any visibility across your production steps? And so I think there's sort of three choices here. like if you're a data qual and they're based sort of on who you are. So if you're a data quality person if you're sort of a data ingestion engineer or have no control beyond your database or if you're a data engineering team and you have some control beyond your database. So let's go through each three. The first one is let's say I'm just a data quality person right I'm really focused on trying to improve source data or my L1 data I want to check that data maybe develop a data quality dashboard use that for leverage and the sort of downstream all the transform task predicted sort of I I'm not really focused on that I'm trying to make our raw data our source data better and so that's a great thing right and using dashboards affecting change in this area.

00:41:22 Design choice two: poll your database and ping downstream

Chris Bergh: It's it's really important. but it doesn't give you you don't have coverage down here. But again, it's better than nothing, right? and and this is could be if you're this is a way and again improving your source data has the secondary effect of lessening errors in all these systems, but you don't have coverage on them. So the second one is sort of maybe I just have I have this dastardly dark database that I control. I don't know what's happening after where it I don't know what's happening kind of before it. All I can do is look at the database. And so you want to pull that database and then use lineage to ping people where they may have an error. And so that's great, right? I've found I've got thousands of tables. I use lineage to say, well, this table is connected to these 10 downstream tables. They're in this report here. They're in this dbt model. I can then ping them to say, hey, look, I found a problem in this data.

00:42:20 Where the data observability vendors' approach falls short

Chris Bergh: Is it your model? Is it your report? can you actually check to see if there's problems? And so the the the that's great, right? It is that errors can still get into production as we saw, right? because you're polling it, production could happen and lo and behold, you have a bad report. So the limitation here is you get sort of irrelevant pings like because lineage doesn't actually have coverage, you're saying this one table could affect these 10, but which one of the tables actually has the problem and then it doesn't really help in development very much at all. And so this is kind of where the current data observability vendors, they all share this weakness, but again, it's better, it's better than nothing, right? And so and if you only control your database and have no control beyond your database, this can be this can be helpful. And lastly, I think this is where our focus has been is sort of the getting full coverage across all your data and all your tools.

00:43:16 Design choice three: full coverage across data and tools

Chris Bergh: You know, put kind of tests and monitors as part of the data production process and then stop on errors before they get into production and then use that same setup for development testing and test coverage and our observability and TestGen software can help you. So we think this is the right way to go. again it gives you full co coverage. So lastly in sum and this is my my last slide. So the lack of test coverage kind of means costs and costs could mean wasted time, could mean frustration, could mean errors get to production and people are upset. But every problem you find earlier is cheaper. And my co-founder Gil says tests are the gift you give to your future self. I think that's so true, right? The 1:10:100 rule applies. Remember to shift left earlier in the production process and shift down earlier in development. And make sure every table is covered and make sure every tool is covered. And our open source tools, they're free.

00:44:19 Why teams avoid testing, and the love of heroism

Chris Bergh: You can run them on there's no limitations. You can run them on thousands of tables. automate the creation of that coverage and also help you quickly build specific business metric tests. And so if you want to learn more, we have you can download TestGen first recommend that observability. We've got a whole bunch on DataOps and then if you like to read or want to share it with your friends, we have a blog on this. So that's it for today. I'll again I'll share the slides. I'll share the recording. is there any questions I can answer for people before I go? You know, I think one you know, question that we were talking around is is like I think a lot of people are sort of afraid of writing tests. there's sort of or this question of why don't people why don't people have adequate test coverage and I think a lot of the case is that people are afraid of testing that it's not part of what they normally do and their mental model is that it's good to go in at like I did in the 1990s and bang on the system and fix it fast there sort of the love of heroism right and I think that's a challenge for a lot of people we don't have to be heroes and So, okay, I'm not seeing any questions. All right. Well, thank you so much. Thanks for the the feedback on this. again, I'll send out the slides and the recording in a little bit. Have a great rest of your

Machine-generated transcript, lightly edited: filler words removed and product and speaker names corrected. Speaker attribution is as captured on the call; chapter times are scaled from the meeting clock onto the recording, which is shorter than the meeting.

Questions from this session

How much data test coverage is actually achievable?

Three things: an overview of testing and test coverage in data and analytic systems, what is achievable today with automated test generation, and three design choices for implementing coverage depending on your role and what you control. Along the way: shift left and shift down, before-the-fact and after-the-fact testing, how to count coverage, and how coverage relates to data lineage.

What is the difference between shift left and shift down?

In this session's vocabulary, shift left means executing data quality tests earlier in production, so a problem in raw data never reaches a customer. Shift down means finding problems earlier in development, so broken code never reaches production. Chris Bergh notes the terms are overloaded — in software, shift left is closer to what he calls shift down here — and defines them explicitly for that reason.

What are the three types of production testing?

Before the fact: tests are part of the production process, so a step can be stopped before bad data moves on — this needs instrumented pipelines or a testware orchestrator. After the fact: you don't control the pipeline, so you poll the database every 20 minutes or every hour and use lineage to poke the team that owns the problem. Avoid the fact: no automated testing at all, and customers tell you when something broke.

How many tests does adequate coverage actually take?

In the worked example — three database levels, three tools, six jobs — 100 tables of 10 columns at L1 gives 200 table tests and 2,000 column tests, roughly the same again at L2, then 20 table tests and 600 column tests at L3, plus domain-specific metric tests and 12 checks on the tools. Thousands of tests is the answer, and Chris Bergh's point is that tests are small and fast, so that number is normal rather than alarming.

How does test coverage relate to data lineage?

They complement each other, and coverage is the more useful of the two during an incident. Lineage is the blueprint that shows where a fire could spread; a Data Journey laced with tests is the fire alarm panel that shows where it is actually burning. Lineage may flag five affected tables, but with coverage on all of them you can see that only one has an error.

Why don't more data teams have adequate test coverage?

Chris Bergh raised this himself at the end. His answer is that a lot of people are afraid of writing tests because it isn't part of what they normally do, and their mental model is the one he had in the 1990s: go in, bang on the system, fix it fast. That is the love of heroism, and his closing line is that we don't have to be heroes.

Where to go next