How to create a price history table in a Data Warehouse: step by step

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

This tutorial shows how to create a price history table in a Data Warehouse to record product price changes over time. Having a price history enables temporal analyses, calculating trends and reconstructing valid prices for any date.

Prerequisites

Basic knowledge of SQL (SELECT, INSERT, UPDATE)
Data Warehouse environment with SQL support (e.g.: Azure Synapse, SQL Server, Snowflake)
Source product table with identifier and current price
Permissions to create tables and triggers/procedures (or ETL processes)

Step 1: Choose the history model (SCD vs timeline)

There are two common approaches: use SCD Type 2 (records with date_effective/date_end and active flag) or maintain a price events table (each change is an event). For simple price history and date-based queries, the SCD Type 2 pattern or a timeline with date_effective is ideal. Here we use a timeline with date_effective and date_end for easy queries.

Step 2: Create the price history table

Create the table that will store price versions. We keep essential fields: product_id, price, currency, date_effective (start of validity), date_end (end of validity, NULL if current), is_current.

CREATE TABLE price_history (
  price_history_id BIGINT IDENTITY(1,1) PRIMARY KEY,
  product_id INT NOT NULL,
  price DECIMAL(18,4) NOT NULL,
  currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
  date_effective DATE NOT NULL,
  date_end DATE NULL,
  is_current BIT NOT NULL DEFAULT 1,
  created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

Step 3: Populate the initial table from the products table

Copy the current state of prices into price_history with date_effective equal to the load date. This step ensures there is at least one record per product.

INSERT INTO price_history (product_id, price, currency, date_effective, date_end, is_current)
SELECT p.product_id, p.price, p.currency, CAST(GETDATE() AS DATE), NULL, 1
FROM products p
WHERE p.price IS NOT NULL;

Step 4: Process price changes (ETL or trigger)

When a new price load arrives (daily feed or event), compare with price_history and close old records before inserting the new one. Below is a SQL ETL example that processes the staging_prices table with the most recent prices.

-- 1) Find products with a price change
WITH changes AS (
  SELECT s.product_id, s.price, s.currency
  FROM staging_prices s
  JOIN price_history ph ON ph.product_id = s.product_id AND ph.is_current = 1
  WHERE ph.price <> s.price OR ph.currency <> s.currency
)
-- 2) Close current records, setting date_end and is_current = 0
UPDATE ph
SET ph.date_end = CAST(GETDATE() AS DATE), ph.is_current = 0
FROM price_history ph
JOIN changes c ON ph.product_id = c.product_id
WHERE ph.is_current = 1;

-- 3) Insert new records with the new validity
INSERT INTO price_history (product_id, price, currency, date_effective, date_end, is_current)
SELECT c.product_id, c.price, c.currency, CAST(GETDATE() AS DATE), NULL, 1
FROM changes c;

Step 5: Common queries and optimizations

Query the valid price on a date, trend aggregations and indexes for performance. Create an index on (product_id, is_current) and on (product_id, date_effective, date_end) if there are many historical lookups.

-- Current price by product
SELECT product_id, price
FROM price_history
WHERE product_id = 123 AND is_current = 1;

-- Price valid on a specific date
SELECT price
FROM price_history
WHERE product_id = 123
  AND date_effective <= '2026-10-01'
  AND (date_end IS NULL OR date_end >= '2026-10-01');

-- Suggested index (SQL Server example)
CREATE INDEX IX_price_history_product_current ON price_history(product_id, is_current);

Verify the result

Confirm that each product has exactly one record with is_current = 1 (if there is a current price) and that old records have date_end populated. Run sample date-based queries to validate historical values. Examples:

-- Verify current unicity
SELECT product_id, COUNT(*) as cnt
FROM price_history
WHERE is_current = 1
GROUP BY product_id
HAVING COUNT(*) > 1;

-- Verify products without a record
SELECT p.product_id
FROM products p
LEFT JOIN price_history ph ON p.product_id = ph.product_id
WHERE ph.price_history_id IS NULL;

Conclusion

A well-designed price history table enables temporal analyses and reporting consistency. Next steps: automate the ETL process, handle product merges and manage currency rules. Tip: test the flow with real data and check edge cases such as multiple changes on the same day.