Should I keep primary keys in my denormalized views (snowflake)?

MYK 0
Viewed 33

I am planning to build some wide tables in snowflake. The underlying data is highly normalized, so there is a lot of joining involved.

To illustratate the point, consider TRANSACTIONS (1b records), PRODUCTS (10k records) and PRODUCT_CATEGORY (50 records)

I would look to build:

# Creating a view in snowflake
SELECT t.*, p.productName, pc.productCategoryName 
FROM TRANSACTIONS t 
JOIN PPRODUCTS p ON p.product_id = t.product_id
JOIN PRODUCT_CATEGORY pc ON pc.product_category_id = p.product_category_id

My question is whether I should keep product_category_id or product_id in the view? In theory it should be quicker to query based on the (integer) Id rather than on the (string) productName or productCategoryName.

That said, I may be overthinking this. I'm new to snowflake so not 100% sure how much of this is important.

snowflake-cloud-data-platform
1 Answers

In all likelihood, you have both a natural key (the actual value that has semantic meaning to a user - like a unique product SKU or name), and a surrogate key (the _ID that is a unique integer value for each of your natural key values).

Generally, the surrogate key (assumed as integer) which you base your join upon will perform better than the natural key (assumed as string) - so you should use the surrogate key in the join conditions.

As for utility, I would include both the natural and surrogate key values in the select clause of the view. This allows the view to be used with other tables / views where you can join on the surrogate key there as well.

Jim Demitriou0
Related