#Free word deduplicator update#
#Free word deduplicator full#

Create a unique, hashed grain_id for each row of your table from every significant column value.Here’s a brief overview of the steps we’ll take: Only then can you find the more complex partial duplicates you want to eliminate. The TL DR is that you need to think about the grain of your data, and the entity you want to capture as you are tracking changes. Hopefully this is making a bit more sense.

#Free word deduplicator full#

In our full process below, we’ll make this concatenated key into a proper id, but this is funamentally what's happening under the hood. Now the entity_grain clearly shows us which rows are duplicates! We only need to keep the most recent one, so row 2 above can be removed from the cleaned dataset, giving us the output below. In our example, we are looking to capture data at the grain of entity_important_status which we'll call our entity_grain for now.

The combination of entity_id + you want to capture becomes the grain of your model.

What column value(s) are changing, but you don’t care about tracking in your new model? These are the columns that you’ll ignore when building your grain_id.

#Free word deduplicator update#

What timestamp provides the most reliable update time for these records? You’ll need this to ensure you’re picking the most recent row among the partial duplicates.

What other column(s) are capturing the changes in the data you want to track in your new model? These columns will become part of your new unique primary key.

What is the primary key of the entity which you are tacking historical data for? You should be able to group by this id in order to identify the duplicates for each id.

These questions can help you figure out the core entity that you are tracking, and the real grain at which changes should be captured in your new model. If you were to ignore the two columns whose values you don’t want to track, you’d find the dupes that need to be banished! Identifying key columns įirst, let’s look a little closer at identifying the desired grain of the model you are building before you go removing these partial duplicates from your dataset. As a result, rows 2 and 3 are what we’ll call partial duplicates. So in this case, the grain is entity_id + important_status, not just entity_id. As discussed, the grain of the dataset you want to capture is the combination of the columns we deem important that make each row unique. Fine so far, but here's the rub: you don’t actually care about the changes that happen in the unimportant_value column, and a thus a fresh updated_date column value and a new row doesn’t always indicate a real change (real here is defined in terms of what matters to you modeling the data for our stakeholders).

You have this historical record that captures all the changes made to the entities. (Or, as mentioned, your loader may just be appending data indiscriminately on a schedule without much care for your time and sanity.) Whatever has brought you here, you now have a table where the grain is not your entity’s primary key, but instead the entity’s primary key + the column values that you’re tracking. You may be capturing historical, type-two slowly changing dimensional data, or incrementally building a table with an append-only strategy, because you actually want to capture some change over time for the entity your recording. These are partial duplicates, meaning your entity of concern's primary key is not unique on purpose (or perhaps you're just dealing with some less than ideal data syncing). These special dupes are not the basic ones that have same exact values in every column and duplicate primary keys that can be easily fixed by haphazardly throwing in a distinct (yeah that’s right, I called using distinct haphazard!). You’re here because your duplicates are special duplicates. But first, let’s make sure we’re all on the same page. Let’s get rid of these dupes and send you on your way to do the rest of the super-fun-analytics-engineering that you want to be doing, on top of super-sparkly-clean data.

For you, friend, we wrote it down for you. Hey data champion - so glad you’re here! Sometimes datasets need a team of engineers to tackle their deduplification (totz a real word), and that’s why we wrote this down.