Find the same product in two tables

Compare item_code and observed prices between Product Information and Competitor Price Data.

Question for this chapter

How can you prove that an inventory row and a competitor-price observation refer to the same product?

Why this matters now

Branch inventory and competitor prices have different row counts and observation units. Without checking the key first, a price from another product could be applied without an obvious warning.

Try it

Open Product Information in Retail Inventory Analysis, then select Schema. Find item_code, branch_name, inventory_qty, selling_price_krw_synth, competitor_price_krw_synth, and stockout_risk_flag_synth.

Schema tab of the Product Information dataset — Inspect the product key, branch, inventory, selling price, competitor price, and stockout-risk columns.

In Data, verify the 46,700-row count above the table and note that item_code = 7224000894 repeats across branches and work rows.

SELECT item_code, branch_code, price_krw
FROM `retail_inventory_intelligence`.`competitor_prices`
WHERE item_code = 7224000823
ORDER BY branch_code;

Product and branch inventory rows in Product Information — The same product repeats across branches and work rows in Product Information.

Next, open Competitor Price Data, select SQL Scratch Pad, and use this query to inspect 7224000823, whose observed price differs by branch.

Product-by-branch price observations in Competitor Price Data — The same item_code repeats across branch_code values, and price_krw can differ by branch.

Success looks like this

Table	Rows	Where to verify	Shared product key	Observation unit
Product Information	46,700	Row count above Data	`item_code`	Product × branch × work row
Competitor Price Data	56	Row count above Data	`item_code`	Product × branch competitor-price observation

item_code connects the tables, but the current pipeline does not include branch_code in the join. It first keeps the first row for each item_code, reducing 56 competitor observations to prices for eight products. Which branch price remains can therefore depend on input order, and the other branch prices do not reach the output. It then connects those eight product prices to the 46,700 Product Information rows with the same item_code.

Interpret the result

7224000823 repeats across seven branches, with observed prices ranging from KRW 7,010 to KRW 10,280. This variation makes the loss of branch context during the 56-to-8 reduction visible.

Next decision

The tables can be connected safely. Next, run the pipeline and verify that only the competitor-price field changes in the same row.