Blog Transforming Real-Time Data with Operational Data Stores: A Dynamic Pricing Use Case

Transforming Real-Time Data with Operational Data Stores: A Dynamic Pricing Use Case

Kevin Bartley
October 23, 2024
Transforming Real-Time Data with Operational Data Stores: A Dynamic Pricing Use Case

It’s a common scenario: a business wants to run complex queries on its production database to harness fresh, operational data.

This makes sense, since some of the most vital data is held on these databases, including transactions, payments, and inventory. The data is also fresh, allowing you to power operational use cases.

However, most of this data is held in databases that are not designed to process this complex query load. As a result, the database strains and becomes unstable. Results take longer to generate, and they become out-of-date.

This is where an operational data store (ODS) comes in. An operational data store allows you to perform complex queries on fresh data, without performance or stability issues. Teams can power their operational use cases, instead of using traditional databases that can’t handle the query load.

To showcase the power of an ODS, we’ve developed a demo for an e-commerce company, based on a dynamic pricing use case. Read on for a step-by-step walkthrough.

What is an ODS?

An operational data store (ODS) offers the best of both worlds: the ability to process data intensive queries like a data warehouse, but with fresh results, like OLTP. Teams can model everything in SQL, and easily manipulate streams of data.

While OLTP is built for fresh results, and OLAP is designed for data intensiveness, ODS does both at the same time. When query loads become too heavy for OLTP, ODS allows teams to perform complex queries on fresh data to power operational use cases, such as fraud detection and personalization.

An ODS works natively on Change Data Capture (CDC) streams from the transactional database. Data from OLTP databases is incorporated within milliseconds, so that the results from the ODS are always fresh.

An ODS can easily handle data intensive workloads, such as joins from normalized tables upstream. ODS also incrementally updates your views as data comes in, ensuring that you don’t use excessive compute.

Demo Use Case: Operational Data Store

We recently developed a demo to showcase Materialize’s ODS in action. In the demo, the e-commerce company Freshmart is trying to incorporate dynamic pricing into its website.

Dynamic pricing allows prices to vary based on a number of factors, including stock levels, trends, and other indicators. The dynamic pricing logic is represented by a single, complex SQL query.

Freshmart has created foreign key constraints and indices to speed up queries as much as possible, but these methods still aren’t enough.

ODS Demo: Step-by-Step Walkthrough

First, let’s take a look at the query for the dynamic pricing model. You can find the SQL code below.

1 
CREATE VIEW dynamic_pricing AS
2 
WITH
3 
    recent_prices AS (
4 
        SELECT
5 
            grp.product_id,
6 
            avg(sub.price) AS avg_price
7 
        FROM (SELECT DISTINCT product_id FROM public.sales) AS grp,
8 
            LATERAL (
9 
                SELECT
10 
                    sales.product_id,
11 
                    sales.price
12 
                FROM public.sales
13 
                WHERE sales.product_id = grp.product_id
14 
                ORDER BY sales.sale_date DESC LIMIT 10
15 
            ) AS sub
16 
        GROUP BY grp.product_id
17 
    ),
18
19 
    promotion_effect AS (
20 
        SELECT
21 
            p.product_id,
22 
            min(pr.promotion_discount) AS promotion_discount
23 
        FROM public.promotions AS pr
24 
        INNER JOIN public.products AS p ON pr.product_id = p.product_id
25 
        WHERE pr.active = TRUE
26 
        GROUP BY p.product_id
27 
    ),
28
29 
    popularity_score AS (
30 
        SELECT
31 
            s.product_id,
32 
            rank() OVER (PARTITION BY p.category_id ORDER BY count(s.sale_id) DESC) AS popularity_rank,
33 
            count(s.sale_id) AS sale_count
34 
        FROM public.sales AS s
35 
        INNER JOIN public.products AS p ON s.product_id = p.product_id
36 
        GROUP BY s.product_id, p.category_id
37 
    ),
38
39 
    inventory_status AS (
40 
        SELECT
41 
            i.product_id,
42 
            sum(i.stock) AS total_stock,
43 
            rank() OVER (ORDER BY sum(i.stock) DESC) AS stock_rank
44 
        FROM public.inventory AS i
45 
        GROUP BY i.product_id
46 
    ),
47
48 
    high_demand_products AS (
49 
        SELECT
50 
            p.product_id,
51 
            avg(s.sale_price) AS avg_sale_price,
52 
            count(s.sale_id) AS total_sales
53 
        FROM public.products AS p
54 
        INNER JOIN public.sales AS s ON p.product_id = s.product_id
55 
        GROUP BY p.product_id
56 
        HAVING count(s.sale_id) > (SELECT avg(total_sales) FROM (SELECT count(*) AS total_sales FROM public.sales GROUP BY product_id) AS subquery)
57 
    ),
58
59 
    dynamic_pricing AS (
60 
        SELECT
61 
            p.product_id,
62 
            p.base_price,
63 
            CASE
64 
                WHEN pop.popularity_rank <= 3 THEN 1.2
65 
                WHEN pop.popularity_rank BETWEEN 4 AND 10 THEN 1.1
66 
                ELSE 0.9
67 
            END AS popularity_adjustment,
68 
            rp.avg_price,
69 
            coalesce(1.0 - (pe.promotion_discount / 100), 1) AS promotion_discount,
70 
            CASE
71 
                WHEN inv.stock_rank <= 3 THEN 1.1
72 
                WHEN inv.stock_rank BETWEEN 4 AND 10 THEN 1.05
73 
                ELSE 1
74 
            END AS stock_adjustment,
75 
            CASE
76 
                WHEN p.base_price > rp.avg_price THEN 1 + (p.base_price - rp.avg_price) / rp.avg_price
77 
                ELSE 1 - (rp.avg_price - p.base_price) / rp.avg_price
78 
            END AS demand_multiplier,
79 
            hd.avg_sale_price,
80 
            CASE
81 
                WHEN p.product_name ILIKE '%cheap%' THEN 0.8
82 
                ELSE 1.0
83 
            END AS additional_discount
84 
        FROM public.products AS p
85 
        LEFT JOIN recent_prices AS rp ON p.product_id = rp.product_id
86 
        LEFT JOIN promotion_effect AS pe ON p.product_id = pe.product_id
87 
        INNER JOIN popularity_score AS pop ON p.product_id = pop.product_id
88 
        LEFT JOIN inventory_status AS inv ON p.product_id = inv.product_id
89 
        LEFT JOIN high_demand_products AS hd ON p.product_id = hd.product_id
90 
    )
91
92 
SELECT
93 
    dp.product_id,
94 
    round(dp.base_price * dp.popularity_adjustment * dp.stock_adjustment * dp.demand_multiplier, 2) AS adjusted_price,
95 
    round(dp.base_price * dp.popularity_adjustment * dp.stock_adjustment * dp.demand_multiplier * dp.promotion_discount * dp.additional_discount, 2) AS discounted_price
96 
FROM dynamic_pricing AS dp;
97
98
99
100 
ALTER TABLE public.inventory ADD CONSTRAINT inventory_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products (product_id);
101 
ALTER TABLE public.promotions ADD CONSTRAINT promotions_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products (product_id);
102 
ALTER TABLE public.sales ADD CONSTRAINT sales_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products (product_id);
103
104 
CREATE INDEX idx_products_product_name ON products (product_name);
105 
CREATE INDEX idx_sales_product_id ON sales (product_id);
106 
CREATE INDEX idx_sales_sale_date ON sales (sale_date);
107 
CREATE INDEX idx_sales_product_id_sale_date ON sales (product_id, sale_date);
108 
CREATE INDEX idx_promotions_product_id ON promotions (product_id);
109 
CREATE INDEX idx_promotions_active ON promotions (active);
110 
CREATE INDEX idx_promotions_product_id_active ON promotions (product_id, active);
111 
CREATE INDEX idx_inventory_product_id ON inventory (product_id);
112
sql

As you can see, the query is relatively complicated, containing complex lateral joins, group bys, aggregations, and left joins. The indexes are added to make the query run efficiently in Postgres.

Postgres will give us access to real-time data about purchases, inventory, and other vital business metrics. But Postgres will have difficulty with the complexity of the query. The query is too compute-intensive, and the OLTP architecture of Postgres will strain.

To show this in action, we’ll connect to a Postgres instance. Let’s perform the dynamic pricing query.

The output is as follows:

In our testing, performing a single query took ten seconds. However, performing ten queries concurrently took 30 seconds. Concurrent computations are likely to occur on the e-commerce website, since more than one user will shop at a time.

However, pricing that takes 30 seconds to generate is not necessarily accurate. By then, data on the site has changed, and the price may not reflect the latest information.

So if we want to perform this dynamic pricing query at an acceptable speed, we’ll have to take it off Postgres.

What other solutions could you use? A read replica allows you to unload queries off of your primary, but they’re designed for transactional queries, not analytical queries.

You could ETL the data into a data warehouse, but then the data would be stale. You could try a cache, but that pulls you away from SQL.

This is where an operational data store (ODS) comes in.

An operational data store allows you to perform this complex query over fresh business data, such as inventory levels.

For this demo, we’ll use Materialize as a cloud operational data store (ODS). Materialize allows you to use SQL to transform, deliver, and act on fast-changing data. By performing incremental and consistent data transformations, Materialize enables you to serve always-fresh query results to power real-time use cases.

To get started with Materialize, sign up for a free trial now. Then log in to the Materialize Console.

There are several ways you can bring business data into Materialize. You can read data off of Kafka, post off a Webhook, and ingest data from databases by consuming CDC data from a replication log. Learn how to import data sources into PostgreSQL by reading our documentation.

Let’s perform the same dynamic pricing query from our first example in Materialize.

In our test, Materialize provides the same answer as Postgres, but it is generated in 46 milliseconds instead of 10 seconds. This is much quicker, and takes place fast enough to serve as accurate pricing on the website.

Freshmart Demo: See Why ODS Works Best

It makes sense that many teams end up performing complex queries on databases such as Postgres. The transactional data is fresh and vital for business operations, such as purchases and account balances. But OLTP databases are ill-equipped to handle these compute-intensive queries.

That’s where the operational data store is relevant. Operational data stores such as Materialize allow you to perform complex queries on fresh data, enabling you to power operational use cases.

Sign up for a free trial of Materialize now, so you can power your real-time business use cases with a cloud ODS.

Kevin

Kevin Bartley

Materialize

Kevin is a content marketer with 10+ years of experience in SaaS B2B and PLG companies. In his career, he's repeatedly scaled content engines that drive acquisition and revenue. He's also led SEO and product marketing functions. Kevin holds a B.A. in English from Harvard University.