An eminent colleague of mine (Alex Martelli) provided a very nice review of the book. He is very fond of it, but humorously scolded me for the phrase “data science” which he thinks is always sloganeering … He prefers “data engineering.”
While I wouldn’t go full in with that chastisement, this book definitely is on the engineering side of things. I only minimally touch on specific machine learning models, but entirely on the concrete effort needed to get data ready for being modelled, visualized, statistically analyzed, or otherwise put to work. I guess my disagreement with Alex might simply be that I believe that concrete science (like physics, biology, chemistry, ecology, etc) is always just as messy and requires just as much engineering. Hence, I think the word “science” is okay.
To your actual question, I try to provide as much as I can for how to make your data USEFUL, without great attachment to the admittedly buzzword-ish phrase “data science” per se.

I am not really optimistic about something like Autoclean coming to exist on the analogy of AutoML. To simplify greatly, AutoML is kinda just “throw a bunch of different models at a problem, and compare the metrics. Cleaning data requires a much more nuanced understanding of why the data has gone wrong, and how to remediate it.
There’s an epigraph that I start an early chapter with, from Hadley Wickham (borrowing Tolstoy, of course): Tidy datasets are all alike, but every messy dataset is messy in its own way.

Thank you David. It is insightful

I’m afraid I largely have to plead ignorance here. I use, and write about things like NLTK, but I also haven’t worked as much in NLP for a number of years. There could be more tailored tools for this domain that I am not aware of in my attention on mostly tabular, mostly numeric, data.

Maybe. A colleague who advised me during writing really felt I should have a chapter on using models for the data cleaning itself. I haven’t worked with BERT as of date, but in general I felt that this topic was interesting but still at the stage of promising rather than ready for produciton.

I have myself mostly just used regular Git tracking of data, for example with GitHub LFS. That is relatively minimal change sets for textual data formats.
However, I know there is an important limit in this approach. While it is good for saving linear sequences of changes, it doesn’t completely scale to more complex branching patterns when data has shared provenance. I do not know if DVC handles that well, but I probably should.

Thanks David! Will definitely check out about Github LFS

Much of what I talk about doing with Python, R, or other tools with a programmer focus, can be done in those UI front ends as well. In the book, while I give particular examples, I try to remain tool agnostic. So even though I might show some Pandas code to perform a certain step, I do not mean it to be a book about Pandas. Doing that step with Open Refine, or Trifacta, or Cloudingo, is just another option, much as would be doing it in the R tidyverse.
That said, I will remain more fond of the kinds of tools I write about personally. Part is simply my old-timey attachment to command line and programming language tools. The UIs like those you mention are targeted at folks somewhat less comfortable with programming.
But beyond mere personal preference, these “friendly” UI tools have some real drawbacks. On the one hand, many are commercial, which means both that they have extra costs per-seat, but even more importantly, means that they have some vendor lock-in. Once you’ve developed complex work-flows in one tool, it becomes more difficult to switch tools.
Maybe even more important than the lock-in concern is when you reach limitations of a tool. For 90% of what you need to do in data cleanup and preparation, those tools will probably do it. However, once you reach that other 10%, it becomes very difficult to add a new data manipulation, and usually means going back to the programming tools that they are meant to avoid.
In contrast, with Pandas, or scikit-learn, or the R tidyverse, you can also certainly hit limitations in the provided capabilities. But adding more—well, it still might be a pain in the ass, but it’s a PITA using the same toolset—it is always possible to write custom functions that plug into the hooks provided by those general libraries. The tools are designed around such extensibility in significant part.

Thanks, David Mertz! Very helpful. I think one of the big challenges I run into with my team is simply the time it takes for proper data cleaning (says all data scientists everywhere 😂). Do you have any recommendations or best practice suggestions on building efficient and automated pipelines for these data cleaning tasks?

I do. But basically, it takes 300,000 words to express them :-)
… I should go do a word count on the book to fine tune that number.

Hahahaha…fair enough!

For better or worse, I think there really is just a lot of trial and error needed to find best practices, which are often specific to your domain and problem set.
At some end of the process, you do something like train a model and have metrics to judge quality. If those measures do not show success, ONE thing you should try is massaging the data preparation/cleaning process.
Visualizations can definitely often help. Finding the right angle in which to present data can many times make the problems really stand out. But that’s very much an iterative process as well. Many visualizations which are completely sensible in principle, even valuable, may not be the ones that actually expose the problems.

So is it always trial and error, even with experience helping out one understand the data and insights that can be obtained?

This feels like a social question at least as much as a technical question. I might have a variety of kinds of relationships with the upstream provider, and the influence I might have over their processes varies hugely.
Unfortunately, we as data scientists or data engineers often have little influence on that upstream. This can be true within one organization, but that much more so when different companies, agencies, etc are at different ends of the data pipeline.
I don’t think a cut-off or threshold is the best way to think of it. Rather, universally, yes there are problems. But almost always there is some utility in the current data. So it tends to be about incremental, and ongoing, improvements to the upstream data, inasmuch as we influence that.

I think some of these concepts from machine learning models don’t entirely apply to data engineering (cleaning) steps. We don’t really have straightforward metrics in the same way
Of course we can repeat statistical tests, or perform new ones, but doing so is informed by domain knowledge or task purpose, rather than by any independent measures of goodness/badness.
If you assign random values to data cells to validate your remediation, it’s completely predictable how much you’ll detect from the probability distributions you use for randomness. But you don’t really learn anything new thereby.

This is a great question! I’d be very interested to read how others in this thread weigh these concerns.
For myself, I find that data cleaning is sufficiently task driven that it often not possible—but more specifically, not even meaningful—to “clean the data” without that’s to some particular goal.

Agreed! I’ll be interested to hear what others indicate, as well. Some of the artful balance we are trying to strike is figuring out what data might be meaningful/relevant to various business decisions/operations before they specifically request it so that we can more efficiently respond to higher probability data requests.

I totally agree with this approach as well. I think cleaning is highly contextual and use case driven. Some teams take this decision based on the frequency with which the particular data is being used. However in my personal experience, devil lies in the detail. A financial data which needs to be used once a year but needs to be of correct quality and on the other hand, tweet/retweet stream where you are reading continuously but doesn’t care much about text encoding (or other elements) of each and every tweet. So all is about use case binding for me as well.

Use of sentinels for missing (or unreliable) data is definitely relevant and sensible. It’s downstream from the point where that is done that value imputation or discarding rows becomes an important decision.
For example, numeric values like 999.9 and -1 are often used to mark missing data in formats that require numeric columns. As long as those do not overlap with plausible measurement values, they are good sentinels.
However, when you want to perform visualization, or statistical summary, or machine learning modeling, on the data, those sentinels become problems… specifically because they are implausible data values. At that point, doing things like imputing median values in their place is often more useful (when accompanied by appropriate footnotes or metadata that make clear what you have done).

I take your point regd numeric fields and defaults like 999.9 and -1 or -99 etc. Normally we should just have Nulls instead of these special values. Median calculations should ignore such null value records.
However, for categorical fields, i find the approach of imputing most prevalent value (mode) during Cleaning slightly worse than choosing sentinels (unassigned but valid, default value indicating missing data) as in my opinion that decision is not reversible (even if documented) and ought to be in the hands of the ultimate users of the data - The Analysts/Data Scientists etc.
I don’t see what difference having implausible data values has for categorical variables (or for Viz/Stats/ML activities) when they too have been documented and catered for via defaults for missing as has been the case with BI typically.
I would rather have 90 valid records with valid values and 10 records with missing categorized as “unassigned” letting me choose what to do with those 10 records than all valid values for this field across 100 records with metadata note: 10 missing have been replaced by median value <xyz>.

Data catalogs are definitely valuable. Part of metadata can and absolutely should include known problems… as well, of course, as version information on datasets that have undergone various remediations (cleaning).
I avoided discussing closed source tools in the book. This is both a natural distrust of them (who knows if it will become unavailable or lose features), and because they create walled gardens. But some tools like Apache Atlas and Truedat do exist.

Many thanks for your insights! Another quick follow up question, do you recommend a way/process in keeping data catalogues up to date? That’s also something that I have seen quite a bit in industry as a challenge. 🙂

I’m afraid I don’t have any specific insight about that currently.

I very deliberately eschew “recipes.” Understanding the specific problem and specific dataset is crucial, and reducing a solution to a cargo-culted recipe is almost always going to do the wrong thing for your specific purpose.
What I do discuss in considerable depth is how to think about the problem in hand, and likely techniques with which to approach it.

Practice and an understanding of your domain. I mean, of course understanding particular tools like Pandas or R tidyverse are of huge value… but knowing what you want to do must come before simply learning APIs.

Been seeing APIs appearing continuously.. just wondering what exactly is an API? David Mertz

Oh… Application Programming Interface. It just means the names and arguments of the functions and methods that some particular software library or tool provides.

thank you 🙂 David Mertz I guess, there is no easy way around data cleaning and having domain knowledge.

Obviously automation save a whole lot of time. I’d have a certain caution about some of it though. If you really are automating cleanup/transformation of the SAME data source that has characteristic problems, of course it’s good to reuse the scripts you wrote in your first analysis.
But applying those to very different data is likely to miss crucial issues. Moreover, sometimes data coming from the same source can change character in certain ways, which may mean that you need to be sensitive to new kinds of cleanup issues that arise.

I’ll go by parts.

1. Having (versioned) schema registries might help a certain proportion of data integrity issues. My feeling is that it’s a fairly small percentage though.
   I find that errors because of instrumentation faults, human transcription errors, bias in selection, recording failures, etc. are much more prevalent. All of those apply just as much even when the schema is clearly understood and followed by all ends of the data processing.

1. I think the question is about plain text descriptions that may describe the same thing but be worded differently. I’m not sure. I discuss in some moderate detail analyzing string similarity in the book.
   That said, for something like a product description, anything mechanical will probably fail. Version 16 and Version 17 of the same line are likely to use largely the same marketing language (and hence have similar strings). More sophisticated semantic concept clustering doesn’t improve that problem. In fact, even a different product, in whatever version, is likely to use similar marketing language, especially from the same maker.
   Anything you do that says “equivalent descriptions name the same product” will get as many false positives as true positives.

1. In your utopia, do all instruments record measurements correctly? I guess some religious doctrines have a notion of an afterlife of such perfection, so I suppose if you follow one of those, it’s something to hope for.

Thank you David for your time and replies. I wish you all the best for the book!