The Best Data Engineers Ask Boring Questions First

“We need yesterday’s successful applications sent every morning.”

Pretty much a routine task any data engineer working at data engineering companies would have been asked to do in their career. At first glance, the requirement is very clear. 

• What are we sending? – “successful applications”
• When to send? – “every morning”
• What time period of data? – “yesterday’s”

All good! Naturally, we can think of how to write the query to extract the data, run it on a schedule and have an upload logic. This opens up a plethora of options in deciding which frameworks to be used. But along the way on your build you may end up asking these questions:

• Which SQL should I write?
• What defines an application to be successful?
• What timezone?
• Is it application-created-date or application-success-date?
• Can an application that was created yesterday become successful today?
• What happens if the size of the data is too large?
• How do I handle retries and do-overs without affecting the downstream application?

In the end, these questions are the most important of all as they take away the ambiguity in the requirement. And suddenly your pipeline architecture becomes reliable and robust. In this article, let’s go through some of the aspects on how to question the requirements.

A. Questions about meaning

The idea here is to understand what does the data actually represent?

For eg, if someone asks, 

“Build a table containing one row per customers”

Immediately:

• Customer or account?
• Can one person own several accounts?
• What identifies a customer?
• What happens when two records are later discovered to represent the same person?
• Do we want the current customer state or historical state?
• Does a deleted customer disappear?

These questions show that a technically correct query can still produce semantically wrong data.

B. Questions about time 

If someone asks to “Load yesterday’s transactions every morning.”,

and if we assume that “Every day’s data is complete by midnight.”,

it leads us to – “WHERE event_date = CURRENT_DATE – 1”

Now, what if the events arrive two days late? We are gonna lose records even with a successfully running pipeline.

So for the above requirement, we could ask

• Is the timezone to be considered UTC or local?
• Is the timestamp event time or ingestion time?
• Can yesterday’s transactions arrive today?
• Can yesterday’s transaction be corrected tomorrow?
• When is yesterday considered complete?
• What happens around daylight-saving changes?
• How far back do corrections occur?

These answers completely change the design, especially when dealing with real time data processing.

C. Questions about failure

One useful mental model is: 

Instead of only asking:

“How will this work?”

ask:

“How will this fail?”

For a pipeline:

• What happens if the source is unavailable?
• What happens if only half the files arrive?
• What if the schema changes?
• What if the job succeeds but processes zero rows?
• What if duplicate files arrive?
• What if the downstream table isn’t available?
• What if this runs twice?
• Can we replay it?
• How far can we backfill?
• Will a rerun duplicate data?

These questions lead to the concept “Idempotency”. In its simplest form, idempotency means that running the same pipeline multiple times should not cause any negative impact.

Sometimes, the boring question is simply:

“What happens if I run it twice?”

That one sentence can uncover a surprising number of architectural flaws.

D. Questions about scale

Another common mistake is solving an imaginary scale problem. For having a scalable solution one might jump into distributed processing. But before that, ask: 

• How many rows?
• How many GB?
• How quickly is it growing?
• How often does it run?
• What’s the acceptable runtime?
• What’s the concurrency?
• What does it cost today?

A pipeline processing 400 MB once a day probably doesn’t need the same kind of architecture as one handling several TB every hour. Data engineering companies need to account for these differences when designing solutions for different production environments. Adding complexity where it isn’t needed only makes the system harder to operate and maintain. Keeping things simple usually makes pipelines more reliable and easier to change.

E. Questions about ownership and observability

As a naive data engineer, this is something that always eluded me. A very important distinction I learned in my journey is that Pipeline health ≠ data health.

Important questions include:

• Who owns this pipeline?
• Who gets alerted?
• What counts as abnormal?
• How fresh should the data be?
• How many records normally arrive?
• What should never be null?
• What downstream systems depend on this?
• How will someone investigate a failure six months from now?

A job can finish successfully while producing:

0 rows

Or:

10× the normal volume

Or:

NULL customer_id for 40% of records

Grain

Probably the most important boring question in analytics engineering:

• “What does one row represent?”

Is it : 

• “one row per customer”
• “one row per order”
• “one row per customer per day”
• “one row per product per transaction” ? 

Imagine three tables: 

• Customers

• • • 1 row/customer

• Orders

• • • many rows/customer

• Payments
  • • many rows/order

Joining everything without understanding the relationships may multiply records.

Data Contracts

Think of a contract simply as answering:

What assumptions are producers and consumers allowed to make about this data?

Can this field be ever null?

Data contracts can provide a way to make these assumptions stricter thereby reducing the pipeline failures.

Observability

As a data engineer, don’t reduce observability to dashboards.

Think in terms of questions like:

• Did the pipeline run?
• Did the expected amount of data arrive?
• Was the data structurally valid?
• Was the data semantically reasonable?
• Is it fresh?
• Did something unusual change?

Final Note

As a budding data engineer, I had this misconception 

• “Senior engineers know the solution faster.”

In time, this changed to 

• “Senior engineers are often slower to accept the problem statement.” 

When dealing with production pipelines one usually asks

• “What should happen?”

But production engineering demands us to ask:

• “What else could happen?”

For example : 

Expected scenario:

• One file arrives every day.

But in production:

• The file arrives twice.
• The file arrives late.
• The file doesn’t arrive.
• The file is empty.
• The filename changes.
• Yesterday’s file arrives today.
• The schema gains a column.
• The source resends last week’s file.

These “boring” questions are the ones which help you identify the right solution for your production environment, which is ultimately what good Data engineering services should deliver.