Data Quality

Fundamentals of Data Engineering

A cautionary tale

There is no 31 Apr on the Gregorian calendar.

There is no 31 Apr

The image above is from Excel, arguably the most popular data store on planet Earth. If you enter 31 Apr into an Excel cell that is formatted for date values, Excel will inform you that there’s an issue with the value.

<ad>

Enterprise Data & Analytics is hosting two ADF training opportunities this week! Register today for Azure Data Factory training Wednesday and Thursday, 23 and 24 Apr 2025.

Save 10% at checkout! Use the coupon code “eodsubstack” and score a discount just for being a subscriber to my newsletter.

</ad>

If you click on the cell, Excel will display a helpful indicator, a yellow square offset 45° with a bold, black exclamation point in the center. Yellow indicates “caution.” The exclamation point indicates “attention.” Together, the indicator communicates “pay attention to this cautionary indicator.”

If you hover over the attention-caution yellow square, a drop-down indicator invites you to click for more information:

Click the indicator to reveal a list of helpful options:

“Error in Value” is the title of of this list, clicking it produces no results. Clicking any other item in the list produces pre-defined options.

Notice the flow

When I use Excel and encounter bad data, I don’t think through all the steps I wrote above. I believe I’ve been classically conditioned to examine the formula in the cell without a second thought.

The flow:

• Draws my attention to the issue

• Unobtrusively informs me there is more information available

• Invites me, should I desire - or even need - to view more information

• Displays options upon request

For me personally, the discrete steps of the logical flow I describe above occurred once, or maybe a few times, in the mid-1990’s when I first encountered Microsoft Excel. When developing products such as Excel, Microsoft went to great lengths to test user interfaces (UIs). The results are evident in the flow documented above.

Applied to data quality in data engineering

Data quality is an important tenet of data engineering. The flow, the steps, the process described above? That’s something data engineers have to think about Every. Single. Time.

Data engineers not only need to detect bad data, we need to fix bad data, if possible.
The best detection methods are automated.
The best fixes are automated.

In addition to bad dates, data engineers need to check for malformed data - stuff like x-separated-value delimiters inline with data combined with non-text-qualified values. Making sure numeric values are actually numeric sounds simple, but consider not all numeric-detection functions test for only base 10 numerals. You might expect the expression IsNumeric(“10A”) to return false, for example. But if the IsNumeric function considers hexadecimal values numeric? “10A” is a number.

But wait there’s more

One must also pay attention to source data types. All data contained in a flat file source is actually text, so it’s possible to store “2025-04-31” as a string in a flat file’s date data type column, even though the value “2025-04-31” represents an invalid date value.

In the file?
It’s just text.

The same applies to numeric data stored in flat files - or all non-string data stored in string formats - even inside database tables (delimited strings in string data type columns, XML, JSON, anyone?).

An evolutionary process

I hold data engineering is an exercise in evolutionary coding whereby developers start small and build. This is evident in exception handling inside pipelines and processes. It’s a maturity effort. Data engineers should begin by trying to elegantly manage errors that they imagine may happen. (Paranoia is a data engineering virtue.)

Elegantly managing predicted errors is merely the beginning of the process.

Data engineers should consider designing for “unknown unknowns.” Try-catch blocks are excellent engines for capturing - and storing! - unaccounted and unimagined error scenarios.

An evolutionary approach to error management maturity starts with proactive potential error condition management, but it does not end there. Whether errors metadata are written to text log files or stored in a table, these data require regular observation.

One reason? Trend detection.
I suggest applying the Pareto Principle.

The error, or class of error, that is occurring 80% of the time probably lends itself to some form of proactive interdiction. Catch that error and manage it, automatically. Capture more errors. Rinse and repeat until you’re violating the rule of diminishing returns. Elon Musk is a proponent of over-automating until efficiency declines, and then replacing automation “bottlenecks” with more efficient, manual, processes.

Inspiration for this newsletter

Returning to my earlier example: I was inspired to write this newsletter by legends of the developers of Excel. Reading Kevin Hazzard’s (excellent) substack titled Are you an authority or an authority figure? did the trick for me. In his post, Kevin shares how he was influenced by Jim McCarthy’s 1995 book, Dynamics of Software Development.

Kevin and I have been friends for a number of years, and years ago he recommended Dynamics of Software Development to me. His recommendation may have been motivated by Rule #31, “Beware of a guy in a room,” and he may have been so motivated because I was, at that time, in fact, an SSIS “guy in a room.”

Friendly advice from an old guy: Pray for friends who love you enough to tell you when you are being a bonehead.

A final note regarding a disturbing trend

Recently, a number of friends and respected acquaintances have shared sentiments about the quality of more recent Microsoft software deliveries. Kendra Little blogged What the Decline of Sql Server Quality means for Developers and DBAs. Frank La Vigne wrote Broken Glass Everywhere: Shattered Windows. I recommend reading both. There are more.

The trend indicates many people with years - decades, even - of experience working with Microsoft products have noticed less quality and fewer features shipped out of Redmond in recent years. I, too, note many of the items they mention, and concur with most of what they (and others) share.

Something has changed - maybe more than one thing - and the quality of the products has suffered as a result.

Some of you may recall my earlier social media post: “I’m all in on Fabric” - Andy, circa 2024. I revise my current state as “mostly in on Fabric.” My dilemma is I want to be all-in. I am hopeful and cautiously optimistic that features currently missing from the platform - along with features that appear less-than-ready for prime time - would be shipped and / or shored up soon-ish.

Based purely on observation and anecdote, I share the following quote C. S. Lewis wrote in “Mere Christianity” (if I may be so bold):

"If you are on the wrong road, progress means doing an about-turn and walking back to the right road; and in that case, the man who turns back soonest is the most progressive man."