How/when to normalize during ETL?

Question

Let's say you're loading a denormalized flat file of purchase transactions that looks like this:

| location_name | location_zip | product | product_price |
|---------------|--------------|---------|---------------|
|  downtown     |    90001     | fries   |    2.99       |
|  west side    |    90048     | burger  |    5.99       |
etc....

into a SQL database. In a normalized star schema DB, you would have tables for locations where the zip fact is stored, and for products where the price is stored.

So what you should be loading into the purchases table is this:

| location_id | product_id |
|-------------|------------|
|     01      |     01     |
|     02      |     02     |
etc....

My question is, how can we normalize the data like this during the ETL process, before it enters the database? The process is complicated by the fact that some locations may already exist in the database with assigned IDs, and some do not. It would be very inefficient to query the DB before inserting each purchase row to determine (or insert a new) location and product ID.

Any general advice on how to approach this problem would be greatly appreciated!

Answer 1

If the number of data elements is small enough to cache in memory, you can load the data in a structure to keep track of it before you start the ETL. At that point, you are querying your local structure before queuing up your insert statements.

If the number of data elements is too large to cache, then you have no choice but to query before insert. Even though it is not as efficient, the impact usually isn't so terrible that it's cost prohibitive.