How to Build a Product Price History Database from Scraped Data

Why Build a Price History Database?

Current prices tell you where the market is right now. Price history tells you where it has been, which is essential for understanding where it is going. Without historical data, every pricing decision is made in isolation, missing the context of trends, cycles, and patterns that repeat across seasons and market conditions.

A well-designed price history database enables you to answer questions that real-time data alone cannot: How did competitors respond to your last price change? What is the typical discount depth during Black Friday in your category? Are competitor prices trending up or down over the past quarter? How long do promotional prices typically last? For a broader introduction to collecting this data, see our price monitoring guide for ecommerce.

Database Schema Design

The schema design is critical because it determines what queries you can run efficiently and how well the database scales. A time-series oriented design works best for price history data, with separate tables for products, sources, and price observations.

-- Products table: canonical product reference
CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  sku TEXT,
  brand TEXT,
  category TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Sources table: where prices come from
CREATE TABLE sources (
  source_id UUID PRIMARY KEY,
  domain TEXT NOT NULL,
  platform TEXT,  -- 'amazon', 'shopify', 'bigcommerce'
  seller_name TEXT
);

-- Price observations: the time-series core
CREATE TABLE price_observations (
  observation_id BIGSERIAL PRIMARY KEY,
  product_id UUID REFERENCES products,
  source_id UUID REFERENCES sources,
  price DECIMAL(10,2) NOT NULL,
  sale_price DECIMAL(10,2),
  currency CHAR(3) DEFAULT 'USD',
  in_stock BOOLEAN,
  observed_at TIMESTAMP NOT NULL,
  INDEX idx_product_time (product_id, observed_at)
);

Building the Data Pipeline

The data pipeline connects your scraping infrastructure to your database. It handles data ingestion, transformation, validation, and loading on a continuous basis. DataWeBot provides structured data outputs that simplify pipeline construction.

Storage Options and Trade-offs

The right storage technology depends on your data volume, query patterns, and budget. Here are the primary options:

Data Quality and Deduplication

Price history data quality directly impacts the reliability of your analysis. Common quality issues include duplicate observations, incorrect currency parsing, scraped promotional overlays misread as base prices, and null values from failed scrapes.

Implement validation rules at the pipeline level: reject prices that are zero or negative, flag prices that change by more than 50% between observations, and deduplicate consecutive identical observations to save storage. DataWeBot pre-validates scraped data, but additional application-level checks ensure the highest data quality in your history database.

Querying and Analysis Patterns

With your price history database populated, several query patterns unlock strategic insights:

Visualization and Dashboards

Connect your price history database to visualization tools to make the data accessible to stakeholders. Time-series charts showing your price versus competitor prices over time are the most impactful visualization. Add annotations for key events like product launches, promotions, and market shifts. Tools like Grafana, Metabase, or Tableau connect directly to most price history databases and support real-time dashboard updates.

Scaling Considerations

As your product catalog and competitor list grow, the observation volume scales multiplicatively. Tracking 10,000 products across 5 competitors with hourly scrapes generates 1.2 million observations per day. Plan your storage and indexing strategy for this growth from the start. Use table partitioning by time period, implement data retention policies for granularity reduction on older data, and consider pre-computing common aggregations to keep query performance fast.