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,
ProductID VARCHAR(30) NOT NULL,
ProductSK INT NOT NULL IDENTITY (2, 1),
ETLCreated INT NOT NULL,
ETLModified INT NOT NULL
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.