22576 Chap.2 Data Discovery, Structuring and Joining Datasets
Data Discovery, Structuring and Joining Datasets
Week 2 places wrangling inside the CRISP-DM process and starts with discovery: learning a dataset's origin, update frequency, collection limits and variables from its metadata and data dictionary. It then covers structuring, where separate tables are linked through keys, appended when they share columns, or merged on a key, and it defines the join types that decide which rows survive.
The tutorials apply the same ideas with Power Query merges and KNIME Joiner and Concatenate nodes.
Joins are where many silent errors begin. A null created by an unmatched row, a key stored as a number in one table and as text in the other, or a duplicated identifier can all change counts and averages without any warning.
This chapter therefore teaches you to predict the row count of a merge before running it, which is also a quick way to answer applied quiz questions.
What this chapter covers
- 01
CRISP-DM and its six stages
- 02
Discovery with metadata and a data dictionary
- 03
Primary, foreign and composite keys
- 04
Linking, appending and merging tables
- 05
Left, right, inner, full outer and anti joins
- 06
Merges in Power Query and KNIME
Worked example · free
Predict the rows returned by five joins of customers and orders
- 1Find the shared IDs: C02, C03 and C05, so 3 rows match; an inner join returns 3.
- 1A left outer join keeps every customer: 5 rows, with nulls for C01 and C04.
- 1A right outer join keeps every order row: 4 rows, with a null name for C07.
- 1A full outer join keeps every distinct ID: 5 + 4 − 3 = 6 rows.
- 1A left anti join keeps customers without an order: 5 − 3 = 2 rows.
Key terms
- CRISP-DM
- The Cross-Industry Standard Process for Data Mining, an iterative six-stage framework for analytics projects.
- Metadata
- Descriptive information about a dataset itself, such as its subject, origin, update schedule, permitted users and ownership.
- Foreign key
- A column in one table that matches the primary key of another table so the two can be connected.
- Full outer join
- A merge that keeps every row from both tables, filling unmatched cells with nulls.
- Anti join
- A merge that keeps only the rows that have no match in the other table.
- Data discovery
- Learning where a dataset comes from, how it was collected, what each variable means and what it can be combined with, before any analysis.
Data Discovery, Structuring and Joining Datasets FAQ
When should I append rather than merge?
Append when the tables have the same columns but different rows, such as January and February sales in the same layout; the rows are stacked. Merge when the tables hold different columns about the same entities, such as customer details and order values, joined on a shared key.
Which join keeps customers who have never ordered?
A left outer join with the customer table on the left keeps every customer and fills the order columns with nulls where there is no match. An inner join would drop those customers entirely, which changes counts and averages without any warning in the output.
Why did my merge produce more rows than either table?
Usually because the key is not unique on one side. If a customer ID appears twice in one table, each match is repeated, multiplying rows. Check the key for duplicates before merging, or build a composite key from the columns that together identify each row.
What is a composite key and when do I need one?
A composite key combines two or more columns into a single identifier when no column is unique by itself. The Week 3 tutorial joins brand, product line, class and size with an underscore so a product table can be matched to transactions; both tables need the same combined column before the merge.
Where does data wrangling sit in CRISP-DM?
In the second and third stages, data understanding and data preparation, after business understanding and before modelling, evaluation and deployment. The cycle repeats, so an evaluation that raises a new business question sends the analyst back to understanding and preparing the data again.
Assessment move
Draw two small tables on paper with a few overlapping keys and predict the row count for every join type before checking it in Power Query. Then rebuild the Week 3 and Week 4 merges from scratch and write down, for each one, why that join type answered that question.
Keep a short note for each dataset with its key, its update frequency and any known limits, because that discovery note becomes the first paragraph of a well-documented report.
Working through Data Discovery, Structuring and Joining Datasets in 22576? Sia is AskSia’s AI Business Analytics tutor — ask any 22576 Data Discovery, Structuring and Joining Datasets question and get a clear, step-by-step explanation grounded in how 22576 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
