I'm loading data into a data warehouse. Customers/transactions are coming from the ERP and from Salesforce. Product codes do not match up between the systems.

Is there a standard pattern for solving this problem?

I'm trying to be careful to avoid a big opinion-based question/answer. I'm really just looking for the fact of how this problem is normally addressed. Maybe it's as simple as, "Put some cross reference tables in the warehouse or transform the product codes in your ETL jobs." But maybe warehouse experts could share their experience.


Yes: the pattern you need is "synthetic keys." For each incoming logical product, you'll assign a key, to be used only within the data warehouse. If two source systems reference the same product, you can give them the same synthetic key (SK). If you source systems use the same ID for two different products, you can give them each a different key. Thus, in your warehouse you have a clean list of products.

As an example, the schema for my current SyntheticKeys.Products table looks like this:

CREATE TABLE SyntheticKeys.Products
SourceSystem    VARCHAR(30) NOT NULL,
-- Housekeeping

In this case, products are de-duplicated before they reach this table, so I can use IDENTITY to allocate synthetic keys, but you may prefer to set up a SEQUENCE object and get IDs from it when a record is identified as being a genuinely new product.

A couple of additional benefits from using synthetic keys:

  • You can use a 32-bit or even 16-bit integer, rather than 144-bit Salesforce IDs or other wide keys. This keeps your fact tables narrow and fast.
  • You can reserve space for special records. My convention is that SK #-1 means "not applicable" and SK #0 means "unknown"; thus, a customer who's never bought a product may have FirstPaidOrderSK of -1, rather than NULL, which is ambiguous.

This is a Master Data Management (MDM) problem.

The previous answer includes this line: "If two source systems reference the same product, you can give them the same synthetic key (SK)." I certainly agree with this idea. But in my mind that ignores an important part of the complexity.

The people building the warehouse (you!) don't know all of the mappings between products. Rather, it's the job of a data steward with knowledge of the products to define those mappings. Then it's your job to load the transactions from the ERP and the product code mappings from the MDM into the warehouse.

Then the transactional system can manage transactions. The MDM manages the product master data (and maybe customer data too!). Your ETL jobs use both of these an input to the warehouse. And the warehouse manages the task of making the data available for reporting and analysis.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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