J is for Join

I recently wrote a children’s book called D is for Data: The ABCs of Data Analytics. This is the tenth in a series of behind-the-scenes, companion articles that will dive a little deeper into each term. We’ll explore the illustration used to define the term, how the word is used in the data world, and other interesting (to me) trivia.

Joining and blending data is one of the most valuable capabilities of a data lake or data warehouse. It is easy to assume that all data is immediately available for analysis, but this is rarely the case. Data is often trapped inside siloed systems and stored in incompatible formats. The data has to be extracted from these source systems and loaded into a data lake or data warehouse first. Once the data is centralized, then it can be combined.

Some joins are an ordinary part of data exploration. There may be a parent-child relationship in your data. This is called a one-to-many relationship. For example, a sales receipt would have one to many items. If you wanted to find all of the sales receipts with a certain item, then you would need to join this data.

Other joins are more analytical in nature. For example, you may want to first sum up all of your sales for each day. Then, bring in some weather data that classifies each day as either having precipitation or not. Finally, you could join daily sales to rainy days and see if there is a correlation between the weather and your sales. You may want to repeat this for certain items as some things like umbrellas may sell better on rainy days. What could you do with analysis like this? You could move umbrellas close to a prominent location on rainy days and make even more sales!