How to create a Variant-type Bridge Table for multivalued attributes in Data Modeling (Kimball)

João Barros 11 de October de 2026 4 min read

We will create a Bridge Table to model multivalued attributes (e.g., products with multiple categories) in a Kimball star schema. This solution preserves the integrity of the star schema and facilitates analyses with counts, filters and aggregation by category.

Prerequisites

Basic knowledge of Data Modeling (Kimball) and star schema.
SQL (for example SQL Server, PostgreSQL or another RDBMS).
Example source tables: a fact table with product_id and a Product dimension.

Step 1: Detect the multivalued pattern and choose the bridge type

Identify the multivalued attribute (e.g., product categories). Decide between a simple Bridge (only relates keys) or a Bridge with weights/order if you need to weight or preserve priority. Here we implement a Bridge with a simple weight (1 per relationship) and a role column for future extensions.

Step 2: Model the tables involved

Define the minimal columns: Dimension Product, Dimension Category and the Bridge ProductCategory_Bridge. In the Kimball approach we keep surrogate keys in the dimensions and store the foreign keys in the bridge and in the fact.

-- Dimensão Product (simplificada)
CREATE TABLE dim_product (
  product_sk INT PRIMARY KEY,
  product_id VARCHAR(50) UNIQUE,
  product_name VARCHAR(200)
);

-- Dimensão Category
CREATE TABLE dim_category (
  category_sk INT PRIMARY KEY,
  category_id VARCHAR(50) UNIQUE,
  category_name VARCHAR(200)
);

-- Bridge entre Product e Category
CREATE TABLE bridge_product_category (
  product_sk INT NOT NULL,
  category_sk INT NOT NULL,
  role VARCHAR(50) DEFAULT 'primary', -- permite diferentes tipos de relação
  weight DECIMAL(10,4) DEFAULT 1.0,
  PRIMARY KEY (product_sk, category_sk)
);

Step 3: Extract and transform the multivalued data

Extract from the source system where the attribute is in a comma-separated list, JSON or in a relational table. The goal is to normalize into pairs (product_id, category_id) before loading into dim and bridge.

-- Exemplo: source_products com coluna categories = 'cat1,cat2'
WITH exploded AS (
  SELECT p.product_id,
         TRIM(value) AS category_id
  FROM source_products p,
       UNNEST(string_to_array(p.categories, ',')) AS value
)
SELECT * FROM exploded;

Step 4: Load / update dimensions

Load dim_category and dim_product using surrogate keys. For conformed dimensions, ensure category_id and product_id are unique and perform create/upsert.

-- Inserir categories novas (pseudo-upsert, sintaxe varia por RDBMS)
INSERT INTO dim_category (category_sk, category_id, category_name)
SELECT nextval('seq_cat_sk'), c.category_id, c.category_name
FROM staging_categories c
ON CONFLICT (category_id) DO NOTHING;

-- Similar para dim_product

Step 5: Populate the Bridge Table

Join the normalized pairs to the dimensions to obtain product_sk and category_sk, then insert into the bridge. Avoid duplicates and maintain weight/role as needed.

-- Exemplo de carga da bridge
INSERT INTO bridge_product_category (product_sk, category_sk, role, weight)
SELECT p.product_sk, c.category_sk, 'primary', 1.0
FROM staging_product_category sc
JOIN dim_product p ON p.product_id = sc.product_id
JOIN dim_category c ON c.category_id = sc.category_id
ON CONFLICT (product_sk, category_sk) DO UPDATE
  SET weight = EXCLUDED.weight;

Step 6: Adjust the Fact table for analysis

Keep the fact table with product_sk (or product_id) and, in analytical queries, join to the bridge to distribute measures by categories. For non-duplicated counts use distinct or weighted measures with weight.

-- Agregar vendas por categoria usando a bridge
SELECT c.category_name, SUM(f.amount * b.weight) AS amount_by_category
FROM fact_sales f
JOIN bridge_product_category b ON f.product_sk = b.product_sk
JOIN dim_category c ON b.category_sk = c.category_sk
GROUP BY c.category_name;

Verify the result

Confirm that each product_sk has the correct categories in the bridge and that the sum of weights per product makes sense (e.g., 1 per relation or total 1 if normalized). Test reporting queries: aggregation by category, filter by category and distinct counts by product.

Conclusion

A Bridge Table for multivalued attributes keeps the star schema clean and flexible: it enables analyses by category without inflating the fact table. Next steps: implement logic for dynamic weights, manage history of changes in the bridge, or create a view to simplify Power BI queries. Tip: start with a simple bridge and add role/weight only if necessary — what is the most common multivalued case in your organization?