Helpful Extract & Load Practices for High-Quality Raw Data : #2/5

ELT is becoming the default choice for data architectures and yet, many best practices focus primarily on “T”: the transformations.

But the extract and load phase is where data quality is determined for transformation and beyond. As the saying goes, “garbage in, garbage out.”

Robust EL pipelines provide the foundation for delivering accurate, timely, and error-free data.

Here are top data practices used & loved by the industry experts that will drive up quality for all your data sets, no matter what tool you use.

Setting the Stage: We need E&L practices, because “copying raw data” is more complex than it sounds.

The idea of ELT as a pattern sounds easy, “just copy over the source data first and then run your transformations over the raw data inside your own space”. However, both the word “copy” as well as “raw data” are two words with hidden obstacles.?

“Copying” sounds easy. But source data changes, and unless you know what changed, “copying” turns out to be more complicated than you think. Imagine a production table of 150 million “orders” that does come with a “timestamp” but not with a “modified” data. And yes, these exist, all over the place. How do you know that orders got modified, and if so, which ones? For instance, how would you know which orders got “canceled”, an operation that usually takes place in the same data record and just “modifies” it in place.

“Raw data” sounds clear. And yet the idea of extracting & loading implicitly means that usually, you copy between two different technical systems A and B, where you need to adjust the data to match system, B. You ingest from REST APIs and put it into Snowflake, or from an Oracle database into Redshift. Every time you change systems, you will need to modify the “raw data” to adhere to the rules of the new system. You need to do type casting, you need to think about whether you want to “flatten JSONs”, or whether you want to add additional metadata to your data.?

Simply “copying raw data” will bring up new questions every time you add a new data source or data target to your list. Even if it is just a new table from the same production database you’ve always been copying from.?

These practices will be your guide whenever you take on a new source for ingesting data into your data system.

Tip #2 : Deduplicate data at a level beyond the raw level.

There are usually three cases of duplicate data hitting your data systems you will want to “deduplicate”. But no matter the case, don’t do it at the raw/landing level!

The first case is intentional duplicate data, where a source system contains something your end-users or you consider to be duplicates. For instance your CRM system might have two entries for a certain customer that canceled and signed up again. If you deduplicate at the raw level, this means either merging the two or deleting one. Both of which will delete data that is present in the source system.

The second case is unintentional duplicate data, where the source system either deleted a record you still have in your data warehouse, or the source system unintentionally produces duplicate data it will likely delete in the future. Even though this is an “error”, I don’t recommend deleting this data in your raw ingestion area but rather filter it further down the line, for instance in the next stage of your modelling. Otherwise you will end up adding a logic to your ingestion that is hard to follow up later.?

The third case is duplication happening due to technical restrictions. It might be the case that your ingestion tooling prefers a “at least once delivery” strategy or it might even be a bug in an ingestion process. With “at least once delivery” incremental load strategies, you’re ensuring to get all data rows, but might duplicate some. Again we recommend to keep the duplicate data at the raw level, and filter it down at a later level.