1

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!

  • You need to execute logic when loading the data. This leaves you with stored procedures, or running some other scripting language or programming language. I can't tell you much about some of the tools each database comes with for loading data. – Greg Burghardt Jan 18 at 18:42
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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.