How to Calculate Redshift SaaS Pricing in PostgreSQL

How do I calculate Redshift costs for my SaaS using SQL?

Run SQL against the AWS Cost & Usage Report (CUR) to break down Amazon Redshift costs per customer for accurate SaaS pricing.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use SQL to calculate Redshift SaaS pricing?

SQL lets you join Amazon Redshift usage data from the Cost & Usage Report (CUR) with your application tables, so you can allocate costs to customers, orders, or features without leaving your database workflow.

How do I query the CUR from Redshift?

Load the hourly CUR files into an external schema (Spectrum) or a regular table, then create views that expose columns such as line_item_usage_start_date, product_product_name, and usage_cost.

Which columns matter for pricing?

Focus on product_product_name = 'Amazon Redshift', line_item_blended_cost (or net_amortized_cost), and any tag columns that identify the customer, workspace, or feature.

Can I split costs by customer_id?

Yes. Tag Redshift clusters or queries with customer_id. After the CUR is ingested, join the tag column to your Customers(id) primary key and aggregate costs.

How do I calculate price per order?

Allocate the customer’s monthly Redshift cost across their total orders. Divide cost by COUNT(*) from the Orders table for the same period.

Best practices for accurate allocation

1) Enable the hourly CUR. 2) Turn on Redshift usage tags. 3) Schedule a nightly ETL that refreshes the CUR table. 4) Store pricing logic in version-controlled views.

What about concurrency scaling & RA3?

Include Redshift Concurrency Scaling lines in your query. For RA3, add Spectrum and managed-storage charges to avoid surprises.

How do I surface costs in my app?

Create a materialized view that writes per-customer costs to a table your application can read via an API endpoint or GraphQL resolver.

Example automation workflow

1) Copy CUR to aws_cur.redshift_usage. 2) Run the sample query below. 3) Insert results into customer_redshift_costs. 4) Expose the table in your SaaS billing service.

Why How to Calculate Redshift SaaS Pricing in PostgreSQL is important

How to Calculate Redshift SaaS Pricing in PostgreSQL Example Usage


-- Price per order for each customer in September 2023
WITH monthly_cost AS (
    SELECT tag_user_customer_id AS customer_id,
           SUM(line_item_blended_cost) AS cost_usd
    FROM   aws_cur.redshift_usage
    WHERE  product_product_name = 'Amazon Redshift'
      AND  line_item_usage_start_date >= '2023-09-01'
      AND  line_item_usage_start_date <  '2023-10-01'
    GROUP BY tag_user_customer_id
),
orders_in_month AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM   Orders
    WHERE  order_date >= '2023-09-01' AND order_date < '2023-10-01'
    GROUP BY customer_id
)
SELECT c.id,
       c.name,
       mc.cost_usd,
       o.order_count,
       ROUND(mc.cost_usd / NULLIF(o.order_count,0), 4) AS cost_per_order
FROM   Customers c
JOIN   monthly_cost mc ON mc.customer_id = c.id
JOIN   orders_in_month o ON o.customer_id = c.id
ORDER  BY cost_per_order DESC;

How to Calculate Redshift SaaS Pricing in PostgreSQL Syntax


-- Allocate Redshift cost per customer for a given month
WITH base AS (
    SELECT  tag_user_customer_id      AS customer_id,
            SUM(line_item_blended_cost) AS cost_usd
    FROM    aws_cur.redshift_usage
    WHERE   product_product_name = 'Amazon Redshift'
      AND   line_item_usage_start_date >= date_trunc('month', :start_date)
      AND   line_item_usage_start_date <  date_trunc('month', :end_date) + INTERVAL '1 month'
    GROUP BY tag_user_customer_id
)
SELECT  c.id,
        c.name,
        b.cost_usd
FROM    base b
JOIN    Customers c ON c.id = b.customer_id
ORDER BY b.cost_usd DESC;

Common Mistakes

Ignoring Spectrum and managed-storage charges—results in underestimating total cost. Always filter on all Redshift-related product codes.
Using daily CUR granularity—misses intraday spikes from concurrency scaling. Switch to hourly CUR for precision.