Handling Degenerate Dimensions in Fact Tables

In this lesson, you will learn how to identify and store "degenerate dimensions"—transaction identifiers that don't need their own separate tables—directly within your fact table.

What is a Degenerate Dimension?

When you model data, you usually separate your descriptive attributes into dimension tables (like dim_customers or dim_products). However, some pieces of data don't actually describe an entity; they simply represent a specific transaction event, such as an invoice_number, receipt_id, or order_confirmation_code.

Because these IDs don't have other descriptive attributes (like "invoice color" or "receipt category"), creating a full dimension table for them would be overkill. Instead, we keep them inside the fact table. We call these degenerate dimensions because they have "degenerated" from being a dimension table into a simple column in your fact table.

Practical Example

Imagine you are building a sales fact table. You have a customer_key and a product_key that link to your dimension tables. You also have an invoice_number from your source system. Since you don't have an invoice_dimension table with extra metadata, you simply include the invoice_number as a field in your fact table.

Your fact table schema might look like this:

CREATE TABLE fact_sales (
    date_key INT,
    customer_key INT,
    product_key INT,
    invoice_number VARCHAR(50), -- This is your degenerate dimension
    sales_amount DECIMAL(10, 2),
    quantity INT
);

Why do we do this?

1. Efficiency: You avoid the overhead of maintaining an extra dimension table that would only contain a single column.
2. Granularity: It allows you to keep the fact table at the most granular level (the transaction level).
3. Simplicity: It keeps your Star Schema cleaner by reducing the total number of tables you need to join during analysis.

By placing these transactional identifiers directly in the fact table, you maintain the link back to the source system without cluttering your schema with unnecessary tables.

Key Takeaway: A degenerate dimension is a transaction ID stored directly in a fact table because it lacks sufficient descriptive attributes to warrant its own dimension table.