Analyze AWS Costs with ClickHouse

Query 500,000 rows of AWS billing data with ClickHouse running in Docker.

Introduction

30 Second Summary

Every company running workloads on AWS gets a monthly bill. That bill can contain millions of rows, one for every hour of every resource across every account.

In this project, you will set up ClickHouse, a columnar database built for fast analytics on large datasets. You will load it with 500,000 rows of realistic billing data and write queries that answer real cost questions.

What You'll Build

You will run a query in DataGrip and instantly see which AWS services cost the most, how costs trend over time, and which accounts are driving the spend.

By the end of this project, you'll have:

  • A local ClickHouse instance running in Docker, connected to DataGrip, where you query half a million billing rows in milliseconds.
  • Cost analysis queries that answer real questions: top services by spend, daily cost trends, per-account breakdowns, and cost by pricing term.
  • A clear picture of how ClickHouse stores and skips data, so you can design faster tables as your data grows.
  • Secret Mission: Build a Materialized View that automatically pre-aggregates daily costs as new data arrives.

Are there any prerequisites?

This project assumes you are comfortable with SQL basics like SELECT, WHERE, and GROUP BY in Postgres. You should also have Docker and DataGrip installed on your machine.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. This step is your chance to commit to the project goal before any hands-on work begins.

Set Up ClickHouse with Docker and DataGrip

You have committed to building an AWS cost analysis environment powered by ClickHouse. Before you can write any SQL, you need a running ClickHouse server and a tool to query it.

In this step, you will start ClickHouse inside a Docker container and connect DataGrip as your query editor. By the end, you will have confirmed the connection by running your first query.

In this step, get ready to:
  • Start a ClickHouse server in Docker with one command.
  • Connect DataGrip to your ClickHouse instance.
  • Run your first query to confirm everything works.
Start ClickHouse in Docker

ClickHouse runs as a server process that listens for connections. Docker lets you start it with a single command instead of installing anything on your machine.

  • Open your terminal by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing Terminal.
  • Pull and start a ClickHouse server by running this command:
docker run -d -p 8123:8123 -p 9000:9000 --name clickhouse-learning --ulimit nofile=262144:262144 clickhouse/clickhouse-server

What does this command do?

  • docker run -d starts a new container in the background so you keep your terminal free.
  • -p 8123:8123 exposes the HTTP port that DataGrip uses to send queries.
  • -p 9000:9000 exposes the native client port for tools that use ClickHouse's binary protocol.
  • --name clickhouse-learning gives the container a friendly name so you can stop or remove it easily later.
  • --ulimit nofile=262144:262144 raises the open-file limit. ClickHouse needs this because it opens many file handles for its columnar storage.
  • Confirm the container is running by running:
docker ps

You should see clickhouse-learning in the list with status Up.

Container not showing up?

  • Make sure Docker Desktop is running. If it is not, open it and wait for the engine to start, then rerun the command.
  • If you see an error about the name clickhouse-learning already being in use, a previous attempt left a stopped container. Remove it with docker rm clickhouse-learning and then rerun the docker run command.
  • If you see a port conflict error, another process is using port 8123 or 9000. Stop that process or change the host port in the command.

Help me troubleshoot my ClickHouse Docker container

Connect DataGrip to ClickHouse

DataGrip does not ship with database drivers to keep its download size small. You will load the ClickHouse driver first, then configure the connection.

  • Open DataGrip by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing DataGrip.
  • Open the Data Sources and Drivers dialog. On the Data Sources tab, click the + icon.
  • Select ClickHouse from the list.

Before filling in the connection details, you need to load the ClickHouse driver.

  • Switch to the Drivers tab in the same dialog.
  • Select ClickHouse from the Complete Support list.
  • Expand the + sign and choose the Latest stable driver from the Provided Driver option.

Now switch back to the Data Sources tab to fill in the connection details.

  • Set Host to localhost.
  • Set Port to 8123.
  • Set User to default.
  • Leave the Password field empty.
  • Set Database to default.
  • Click Test Connection to verify the connection.
  • Click OK to save the data source.

You should see a green checkmark when the test succeeds. Your ClickHouse data source now appears in DataGrip's Database tool window.

Why no password?

The ClickHouse Docker image starts with a user called default and no password. This is fine for a local learning environment. In production, you would set a password with the CLICKHOUSE_PASSWORD environment variable when starting the container.

Test Connection failed?

  • Check that your Docker container is still running with docker ps. If it is not listed, restart it with docker start clickhouse-learning.
  • Make sure the port is 8123 (the HTTP port), not 9000 (the native port). DataGrip connects over HTTP.
  • Confirm you loaded the ClickHouse driver on the Drivers tab. Without the driver, DataGrip cannot speak ClickHouse's protocol.

Help me connect DataGrip to my local ClickHouse instance

Run your first query

The connection is established. Time to send ClickHouse its first query and confirm everything is wired up end to end.

  • In DataGrip, right-click your ClickHouse data source in the Database tool window and select New, then Console to open a new query console.

Before you run this, what do you think ClickHouse will return for a version query?

  • Check which version of ClickHouse is running by executing this query:
SELECT version();

You should see a single row containing the ClickHouse version number. The exact number depends on the latest Docker image.

  • Confirm the query pipeline is fully working by running:
SELECT 'Hello, ClickHouse!';

You should see a single row with the text Hello, ClickHouse! in the results panel.

Query not returning results?

  • Make sure you are running the query against the ClickHouse console, not a different data source. Check the data source selector in the top toolbar of the console tab.
  • To execute, click the green play button or press Ctrl+Enter (Windows) or Cmd+Enter (macOS).

Help me run my first ClickHouse query in DataGrip

Your ClickHouse server is running and DataGrip is connected. Next up, you will learn how ClickHouse SQL differs from the Postgres SQL you already know.

Learn ClickHouse SQL Basics

Your ClickHouse server is running and DataGrip is ready to go. Now it's time to write your first real queries.

ClickHouse SQL looks familiar if you know Postgres, but the differences matter. Every table requires an ENGINE and an ORDER BY clause that determine how data is physically stored on disk. You will learn these differences hands-on with a small practice table before building the real billing dataset.

In this step, get ready to:
  • Create a database and your first table with MergeTree and ORDER BY.
  • Insert data and run SELECT, WHERE, and GROUP BY queries.
  • Explore the data types you will use in the billing table.
Create a database and table

In Postgres, you create a table and the database handles storage details for you. In ClickHouse, every table must declare an ENGINE (how it stores data) and an ORDER BY (how it physically arranges rows on disk).

  • In your DataGrip console, create the aws_costs database and a regions table by running this SQL:
-- Create a database (same as Postgres)
CREATE DATABASE IF NOT EXISTS aws_costs;

-- Create a table (different from Postgres: needs ENGINE and ORDER BY)
CREATE TABLE aws_costs.regions
(
    region_code String,
    region_name String,
    continent LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY region_code;

What makes this different from Postgres?

  • ENGINE = MergeTree tells ClickHouse how to store data. MergeTree is the most common engine and the foundation for analytical workloads.
  • ORDER BY region_code is not just for sorting query results. It determines how ClickHouse physically arranges rows on disk, which directly affects which queries can skip large chunks of data.
  • LowCardinality(String) is ClickHouse's way to optimize columns that have few distinct values (like continents or service names). Think of it as dictionary encoding built into the type system.
  • Expand the aws_costs database in DataGrip's Database Explorer panel on the left.
  • You should see the regions table listed under Tables.

Seeing 'Table already exists'?

  • If you ran the CREATE TABLE statement more than once, ClickHouse will reject duplicates. Drop the table first by running DROP TABLE aws_costs.regions and then re-run the CREATE TABLE statement.
  • Make sure you are connected to the default database on localhost:8123. Check the connection details in the top bar of DataGrip's console.

Still stuck? Ask for help.

Insert and query data

With the table created, you can insert rows and query them. The INSERT and SELECT syntax is the same as Postgres, so this part will feel familiar.

  • Insert five AWS region rows and view them by running this SQL:
-- Insert data (same syntax as Postgres)
INSERT INTO aws_costs.regions VALUES
    ('us-east-1', 'US East (N. Virginia)', 'North America'),
    ('us-west-2', 'US West (Oregon)', 'North America'),
    ('eu-west-1', 'Europe (Ireland)', 'Europe'),
    ('ap-southeast-1', 'Asia Pacific (Singapore)', 'Asia'),
    ('sa-east-1', 'South America (Sao Paulo)', 'South America');

-- Basic SELECT (same as Postgres)
SELECT * FROM aws_costs.regions;

-- WHERE clause (same as Postgres)
SELECT region_code, region_name
FROM aws_costs.regions
WHERE continent = 'North America';

What does this code do?

  • INSERT INTO ... VALUES adds five rows. The syntax is identical to Postgres.
  • SELECT * shows all five rows so you can confirm the data landed.
  • The WHERE clause filters to North American regions only. This also works exactly like Postgres.
  • After running the SELECT *, you should see all five region rows in the results pane.
  • After running the WHERE query, you should see only two rows: us-east-1 and us-west-2.

Now for a query that introduces a ClickHouse-specific idiom. Before you run it, how many distinct continents do you think the data covers?

  • Run this GROUP BY query to count regions per continent:
-- GROUP BY with count() - ClickHouse idiom uses count() not count(*)
SELECT
    continent,
    count() AS region_count
FROM aws_costs.regions
GROUP BY continent
ORDER BY region_count DESC;

What's different here?

Notice count() instead of count(*). Both work, but count() is the idiomatic ClickHouse way. You will see this pattern throughout ClickHouse documentation and examples.

  • You should see four rows: North America with 2 regions, and Europe, Asia, and South America with 1 each.

What else is different from Postgres?

Beyond the syntax you have already seen, ClickHouse has fundamental differences in how it handles writes.

  • No transactions. Every INSERT is immediate and append-only. There is no ROLLBACK.
  • No UPDATE or DELETE. ClickHouse uses lightweight mutations for row changes, but they are slow background operations. ClickHouse is designed for append-heavy analytical workloads, not row-level edits.
  • No auto-incrementing primary keys. ClickHouse identifies data by its ORDER BY columns, not by a serial ID.

These trade-offs are exactly why ClickHouse is fast for analytical queries. It does not spend resources maintaining transaction logs or row-level locks.

Query returning zero rows?

  • Make sure you ran the INSERT statement before the SELECT. ClickHouse will not return an error for an empty table. It just returns no rows.
  • Check that the table name matches exactly: aws_costs.regions (with the database prefix).

Need help debugging your query?

Explore ClickHouse-specific data types

The regions table only used String and LowCardinality(String). The billing table you will build in the next step uses a wider range of types. Here is a preview of the key ones.

ClickHouse types vs Postgres types

  • Date and DateTime for time columns. Similar to Postgres date and timestamp, but ClickHouse stores them more compactly.
  • Float64 for usage amounts. Equivalent to Postgres double precision.
  • Decimal64(4) for cost values where precision matters. Similar to Postgres numeric(p, s).
  • LowCardinality(String) for columns with few distinct values (services, regions, pricing terms). No direct Postgres equivalent.
  • String for high-cardinality columns (resource IDs, usage types). Similar to Postgres text or varchar, but ClickHouse does not require a length limit.

The key takeaway is that ClickHouse gives you fine-grained control over storage. Choosing LowCardinality(String) over plain String on the right columns can dramatically reduce memory usage and speed up queries.

✔️ Awesome, I've got everything!

Great. Double check you have saved your SQL file if you would like to keep your work for reference.

ⓧ I'd like to double check the full code

Scroll up and confirm you have each of these three code blocks in order, all inside a file called 01_basics.sql:

  • CREATE DATABASE and CREATE TABLE with ENGINE = MergeTree and ORDER BY region_code.
  • INSERT INTO with five AWS region rows, followed by SELECT * and a WHERE query.
  • GROUP BY with count() grouped by continent, ordered by region_count DESC.

You now have a working foundation in ClickHouse SQL. Next up, you will build a half-million-row billing table modeled after real AWS Cost and Usage Reports and see where ClickHouse's design choices really pay off.

Build and Load the Cost Analysis Table

You have a working ClickHouse connection and you know the SQL basics. But a 5-row regions table does not show you what ClickHouse is built for.

In this step, you will build a table modeled after the AWS Cost and Usage Report (CUR) schema and fill it with half a million rows of realistic billing data. That is enough data for columnar storage and smart ORDER BY choices to make a visible difference.

In this step, get ready to:
  • Design a CUR-style billing table with proper ClickHouse types, partitioning, and a query-optimized ORDER BY.
  • Generate 500,000 rows of sample billing data using pure SQL.
Create the billing table

AWS CUR reports follow a fixed schema with columns for billing periods, account IDs, service codes, usage amounts, and costs. Your table will mirror that schema using ClickHouse-specific types and storage settings.

  • Open a new console tab in DataGrip by right-clicking your ClickHouse data source in the Database Explorer and selecting New, then Console.
  • Create the billing table by running this query:
-- Create a CUR-style billing table with proper ClickHouse types
CREATE TABLE aws_costs.billing
(
    bill_billing_period_start_date Date,
    line_item_usage_account_id String,
    line_item_usage_start_date DateTime,
    line_item_usage_end_date DateTime,
    line_item_product_code LowCardinality(String),
    line_item_usage_type String,
    line_item_operation LowCardinality(String),
    line_item_availability_zone LowCardinality(String),
    line_item_resource_id String,
    line_item_usage_amount Float64,
    line_item_unblended_cost Float64,
    line_item_blended_cost Float64,
    line_item_line_item_type LowCardinality(String),
    product_product_name LowCardinality(String),
    product_region LowCardinality(String),
    pricing_term LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(bill_billing_period_start_date)
ORDER BY (line_item_product_code, line_item_usage_account_id, line_item_usage_start_date);

Why this ORDER BY and PARTITION BY?

The most common cost analysis questions are: "How much did service X cost?" then "Which account drove that spend?" then "When did it happen?" The ORDER BY (line_item_product_code, line_item_usage_account_id, line_item_usage_start_date) matches that query pattern exactly, so ClickHouse can skip irrelevant data.

PARTITION BY toYYYYMM(bill_billing_period_start_date) splits data into monthly chunks, matching how AWS delivers CUR reports. ClickHouse docs explicitly recommend partitioning no more granularly than by month.

Columns like line_item_product_code and pricing_term use LowCardinality(String) because they have only a handful of distinct values. ClickHouse stores these as dictionary-encoded integers internally, saving space and speeding up filters.

  • Run the query by clicking the green play button or pressing Ctrl+Enter (Windows) or Cmd+Enter (macOS).
  • You should see a success message in the output panel confirming the table was created.

Seeing a "table already exists" error?

If you ran this query before, the table already exists. Run DROP TABLE aws_costs.billing first, then re-run the CREATE TABLE query.

Also confirm you created the aws_costs database in Step 2. If not, run CREATE DATABASE IF NOT EXISTS aws_costs first.

Help me debug my CREATE TABLE error

Generate half a million rows of sample data

There is no public AWS CUR sample dataset to download. Instead, you will generate realistic billing data directly in SQL using ClickHouse's numbers() table function with rand() and arrayElement(). No external files needed.

The INSERT query is one large statement. It is split across three code blocks below for readability. Paste each block directly below the previous one in the same console, then run the complete query once all three are in place.

  • Start building the INSERT query by pasting this first section below your CREATE TABLE statement:
-- Generate 500,000 rows of realistic billing data
INSERT INTO aws_costs.billing
SELECT
    toDate(toStartOfMonth(usage_start)) AS bill_billing_period_start_date,
    toString(arrayElement(
        [111122223333, 444455556666, 777788889999, 123456789012],
        toUInt8((rand(number + 1) % 4) + 1)
    )) AS line_item_usage_account_id,
    usage_start AS line_item_usage_start_date,
    usage_start + toIntervalHour(1) AS line_item_usage_end_date,
    arrayElement(
        ['AmazonEC2', 'AmazonS3', 'AmazonRDS', 'AWSLambda', 'AmazonDynamoDB',
         'AmazonCloudWatch', 'AmazonSNS', 'AmazonSQS', 'AWSDataTransfer', 'AmazonElastiCache'],
        toUInt8((rand(number + 2) % 10) + 1)
    ) AS line_item_product_code,

What does this section do?

  • INSERT INTO ... SELECT generates rows from a query instead of listing values by hand.
  • arrayElement() picks a random item from a list. Combined with rand(number + offset), each row and column gets a different random selection.
  • The four account IDs, ten service codes, and five regions are realistic AWS values that give the data enough variety for meaningful cost analysis.
  • Continue building the query by pasting this second section directly below the previous code (right after the trailing comma on line_item_product_code):
    concat(
        arrayElement(['USE1', 'USE2', 'USW2', 'EUW1', 'APS1'], toUInt8((rand(number + 3) % 5) + 1)),
        '-',
        arrayElement(
            ['BoxUsage:t3.micro', 'BoxUsage:m5.large', 'Requests-Tier1',
             'TimedStorage-ByteHrs', 'Lambda-GB-Second', 'DataTransfer-Out-Bytes'],
            toUInt8((rand(number + 4) % 6) + 1)
        )
    ) AS line_item_usage_type,
    arrayElement(
        ['RunInstances', 'GetObject', 'PutObject', 'CreateDBInstance', 'Invoke', 'PutMetricData'],
        toUInt8((rand(number + 5) % 6) + 1)
    ) AS line_item_operation,
    arrayElement(
        ['us-east-1a', 'us-east-1b', 'us-west-2a', 'eu-west-1a', 'ap-southeast-1a', ''],
        toUInt8((rand(number + 6) % 6) + 1)
    ) AS line_item_availability_zone,
    concat(
        'arn:aws:',
        arrayElement(['ec2', 's3', 'rds', 'lambda'], toUInt8((rand(number + 7) % 4) + 1)),
        ':us-east-1:',
        toString(arrayElement(
            [111122223333, 444455556666, 777788889999, 123456789012],
            toUInt8((rand(number + 1) % 4) + 1)
        )),
        ':resource-',
        toString(number)
    ) AS line_item_resource_id,

What does this section do?

  • line_item_usage_type uses concat() to combine a region prefix (like USE1) with a usage type (like BoxUsage:t3.micro), mirroring how real CUR usage types look.
  • line_item_resource_id builds a fake ARN string for each row, using the row number to make each resource ID unique.
  • The empty string '' in the availability zone array represents rows with no AZ, which is common in real CUR data for global or regional services.
  • Finish the query by pasting this final section directly below the previous code (right after the trailing comma on line_item_resource_id):
    round((rand(number + 8) % 100000) / 100.0, 6) AS line_item_usage_amount,
    round((rand(number + 9) % 5000) / 100.0, 2) AS line_item_unblended_cost,
    round((rand(number + 10) % 5000) / 100.0, 2) AS line_item_blended_cost,
    arrayElement(
        ['Usage', 'Usage', 'Usage', 'Usage', 'Tax', 'Fee', 'Discount', 'Credit'],
        toUInt8((rand(number + 11) % 8) + 1)
    ) AS line_item_line_item_type,
    arrayElement(
        ['Amazon Elastic Compute Cloud', 'Amazon Simple Storage Service',
         'Amazon Relational Database Service', 'AWS Lambda', 'Amazon DynamoDB',
         'Amazon CloudWatch', 'Amazon Simple Notification Service',
         'Amazon Simple Queue Service', 'AWS Data Transfer', 'Amazon ElastiCache'],
        toUInt8((rand(number + 2) % 10) + 1)
    ) AS product_product_name,
    arrayElement(
        ['us-east-1', 'us-west-2', 'eu-west-1', 'ap-southeast-1', 'sa-east-1'],
        toUInt8((rand(number + 12) % 5) + 1)
    ) AS product_region,
    arrayElement(
        ['OnDemand', 'OnDemand', 'OnDemand', 'Reserved', 'Reserved', ''],
        toUInt8((rand(number + 13) % 6) + 1)
    ) AS pricing_term
FROM (
    SELECT
        number,
        toDateTime('2024-01-01 00:00:00') + toIntervalSecond(rand(number) % 15724800) AS usage_start
    FROM numbers(500000)
);

What does this section do?

  • Cost columns use round(rand(...) % 5000 / 100.0, 2) to generate random dollar amounts between $0.00 and $49.99 per line item.
  • line_item_line_item_type is weighted toward Usage (appears four times in the array), so roughly half of all rows are usage records. The rest are Tax, Fee, Discount, or Credit.
  • The FROM numbers(500000) subquery generates 500,000 row numbers. Each row number seeds the random values, giving every row a unique combination of account, service, cost, and timestamp spanning January through June 2024.

Before you run this, how many rows do you expect it to insert?

  • Select only the INSERT statement (from INSERT INTO down to the closing );) and run it with Ctrl+Enter (Windows) or Cmd+Enter (macOS).

The query should complete in a few seconds. ClickHouse is fast at bulk inserts.

  • Verify the row count by running this query:
-- Verify the data loaded
SELECT count() FROM aws_costs.billing;

You should see 500000 in the result. That is half a million rows generated from pure SQL.

  • Preview a few rows to see what the data looks like by running:
-- Quick preview
SELECT * FROM aws_costs.billing LIMIT 5 FORMAT Vertical;

Each row should show a mix of AWS services, accounts, timestamps, and cost values. The vertical format makes it easy to scan all 16 columns without horizontal scrolling.

Seeing 0 rows or a syntax error?

  • If the count returns 0, the INSERT ran but matched no data. Check that you pasted all three code blocks in order with no gaps or overlapping lines.
  • If you see a syntax error, check that the trailing comma at the end of each block's last line connects cleanly to the next block. Each continuation starts at the same indentation level.
  • Make sure you selected only the INSERT statement before running. If you selected the CREATE TABLE too, it will fail because the table already exists.

Help me debug my INSERT query

Now let's confirm the data is shaped correctly for cost analysis. Before you run the next query, which service do you think will have the highest total cost?

  • Run this query to see the top 5 services by total cost:
-- Top 5 services by total unblended cost
SELECT
    line_item_product_code,
    round(sum(line_item_unblended_cost), 2) AS total
FROM aws_costs.billing
GROUP BY line_item_product_code
ORDER BY total DESC
LIMIT 5;

You should see five service codes (like AmazonEC2, AmazonS3, etc.) ranked by total cost. The exact numbers will vary because the data is randomly generated, but all ten services should have roughly similar totals since the random distribution is uniform.

Why is this fast?

That query just grouped and summed half a million rows. ClickHouse's columnar storage means it only read the line_item_product_code and line_item_unblended_cost columns. The other 14 columns were never touched. In Postgres, every row would be read in full even though you only need two columns.

✔️ Awesome, I've got everything!

Double check you have saved your console as 02_create_billing_table.sql.

ⓧ I'd like to double check the full code

Scroll up through your console and confirm each section is present in this order:

  • The CREATE TABLE aws_costs.billing statement with all 16 columns, ENGINE = MergeTree, PARTITION BY, and the three-column ORDER BY.
  • The INSERT INTO aws_costs.billing SELECT ... FROM (SELECT number, ... FROM numbers(500000)) statement, ending with );.
  • The verification query SELECT count() FROM aws_costs.billing;.
  • The preview query SELECT * FROM aws_costs.billing LIMIT 5 FORMAT Vertical;.

You have half a million rows of CUR-shaped data sitting in a properly designed MergeTree table. Next up, you will write the cost analysis queries that answer real questions: where is the money going, which accounts are driving spend, and how costs trend over time.

Run Cost Analysis Queries

In the last step, you loaded 500,000 rows of realistic billing data into your aws_costs.billing table. The data is there, but raw rows are not useful on their own.

In this step, you will write the kind of analytical queries that answer real cost questions. Which services eat the most budget? Which accounts drive spending? How do costs trend over time? Along the way, you will pick up ClickHouse-specific functions for formatting numbers, grouping by date, and drawing inline sparklines right in the query output.

In this step, get ready to:
  • Break down costs by service and account.
  • Analyze cost trends over daily and monthly time periods.
  • Run deep-dive queries on pricing terms, availability zones, and individual line items.
Break down costs by service and account

The most common question in any cost review is "which services cost the most?" Start with service-level totals, then slice by account.

  • In DataGrip, open a new console connected to your ClickHouse data source.
  • Run this query to see total cost broken down by AWS service:
-- Total cost by service
SELECT
    line_item_product_code AS service,
    round(sum(line_item_unblended_cost), 2) AS total_cost,
    count() AS line_items,
    formatReadableQuantity(count()) AS line_items_readable
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
GROUP BY service
ORDER BY total_cost DESC;

What does this query do?

  • WHERE line_item_line_item_type = 'Usage' filters out taxes, fees, credits, and discounts so you only see actual usage charges.
  • formatReadableQuantity(count()) turns raw numbers like 50123 into human-readable strings like 50.12 thousand. This is a ClickHouse-specific function with no Postgres equivalent.

You should see 10 rows, one per AWS service, sorted by total cost. The line_items_readable column shows each count in a human-friendly format.

Seeing zero results?

  • Make sure your console is connected to the ClickHouse data source, not a different database.
  • Check that you ran the data generation INSERT in Step 3. Run SELECT count() FROM aws_costs.billing to confirm you have 500,000 rows.

Why is my ClickHouse cost query returning zero results?

  • Run this query to see how costs break down by account:
-- Cost by account
SELECT
    line_item_usage_account_id AS account,
    round(sum(line_item_unblended_cost), 2) AS total_cost,
    count() AS line_items
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
GROUP BY account
ORDER BY total_cost DESC;

What does this query do?

This groups by line_item_usage_account_id instead of service. You should see 4 rows, one per AWS account, showing total spend per account.

  • Run this cross-tabulation query to see cost per service per account:
-- Cost per service per account (cross-tabulation)
SELECT
    line_item_usage_account_id AS account,
    line_item_product_code AS service,
    round(sum(line_item_unblended_cost), 2) AS total_cost
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
GROUP BY account, service
ORDER BY account, total_cost DESC;

What does this query do?

This combines both dimensions into a single result set. Each row shows one service within one account. The ORDER BY account, total_cost DESC groups the output by account first, then sorts services by cost within each account.

You should see rows showing every service-account combination, sorted by account and then by cost within each account.

Analyze costs over time

Knowing which services cost the most is only half the picture. You also need to see how costs change over time to spot trends and spikes.

  • Run this query to see a daily cost trend with inline sparkline bars:
-- Daily cost trend
SELECT
    toStartOfDay(line_item_usage_start_date) AS day,
    round(sum(line_item_unblended_cost), 2) AS daily_cost,
    bar(sum(line_item_unblended_cost), 0, 50000, 40) AS cost_bar
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
GROUP BY day
ORDER BY day;

What does this query do?

  • toStartOfDay() truncates each timestamp to midnight, grouping all usage within the same day together. ClickHouse has similar functions like toStartOfWeek() and toStartOfMonth() for coarser time buckets.
  • bar(value, min, max, width) draws a text-based bar chart directly in the query output. The bar scales between 0 and 50000 across 40 characters. This is a ClickHouse-only function that gives you instant inline visualizations without any charting tool.

You should see a result set with dates spanning January through June 2024. Each row shows the date, the total daily cost, and a text-based bar that visualizes the relative spend.

  • Run this query to see a monthly cost summary:
-- Monthly cost summary
SELECT
    toYYYYMM(line_item_usage_start_date) AS month,
    round(sum(line_item_unblended_cost), 2) AS monthly_cost,
    count() AS line_items
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
GROUP BY month
ORDER BY month;

What does this query do?

toYYYYMM() converts a date into an integer like 202401 for January 2024. This is handy for grouping by billing period because AWS CUR data is organized by month. You should see 6 rows, one per month from January through June.

Run deep-dive queries

Service and time breakdowns show the big picture. Now drill into individual line items, pricing terms, and availability zones to find where the real outliers are.

  • Run this query to find the 10 most expensive individual line items:
-- Top 10 most expensive individual line items
SELECT
    line_item_resource_id,
    line_item_product_code AS service,
    line_item_usage_type,
    round(line_item_unblended_cost, 2) AS cost
FROM aws_costs.billing
ORDER BY line_item_unblended_cost DESC
LIMIT 10;

What does this query do?

This skips GROUP BY entirely and just sorts every individual row by cost. LIMIT 10 returns only the top 10 results. Notice there is no WHERE filter on line item type here. You want to see the single most expensive rows regardless of type.

You should see 10 rows showing the resource ARN, service, usage type, and individual cost for each top-spending line item.

  • Run this query to break down costs by pricing term (OnDemand vs Reserved):
-- Cost by pricing term
SELECT
    pricing_term,
    round(sum(line_item_unblended_cost), 2) AS total_cost,
    count() AS line_items,
    round(sum(line_item_unblended_cost) / (SELECT sum(line_item_unblended_cost) FROM aws_costs.billing WHERE line_item_line_item_type = 'Usage') * 100, 1) AS pct
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
GROUP BY pricing_term
ORDER BY total_cost DESC;

What does this query do?

The pct column uses a scalar subquery to calculate each pricing term's share of total usage cost. The subquery (SELECT sum(line_item_unblended_cost) FROM aws_costs.billing WHERE ...) computes the overall total, and each row divides its own sum by that total.

This is the same subquery pattern you would use in Postgres. You should see rows for OnDemand, Reserved, and an empty string (line items without a pricing term).

  • Run this query to see costs grouped by availability zone:
-- Cost by availability zone
SELECT
    line_item_availability_zone AS az,
    round(sum(line_item_unblended_cost), 2) AS total_cost,
    count() AS line_items
FROM aws_costs.billing
WHERE line_item_line_item_type = 'Usage'
  AND line_item_availability_zone != ''
GROUP BY az
ORDER BY total_cost DESC;

What does this query do?

The AND line_item_availability_zone != '' filter excludes rows with no AZ (some AWS services are regional, not zonal). You should see 5 rows, one per availability zone.

Notice that line_item_availability_zone is not in your table's ORDER BY key. At this data size, you will not feel a performance difference. In Step 5, you will see exactly how ClickHouse treats queries on ORDER BY columns versus non-ORDER BY columns under the hood.

  • Run this final query to filter on columns that ARE in your ORDER BY (service plus date range):
-- Filtered query on ORDER BY columns (service + date range)
SELECT
    toStartOfDay(line_item_usage_start_date) AS day,
    round(sum(line_item_unblended_cost), 2) AS daily_ec2_cost
FROM aws_costs.billing
WHERE line_item_product_code = 'AmazonEC2'
  AND line_item_usage_start_date >= '2024-03-01'
  AND line_item_usage_start_date < '2024-04-01'
GROUP BY day
ORDER BY day;

Why this query matters

This query filters on line_item_product_code (the first column in your ORDER BY) and line_item_usage_start_date (the third column). Because these columns define how data is physically stored on disk, ClickHouse can skip entire blocks of data that do not match.

Compare this to the AZ query above, which filters on a column NOT in the ORDER BY. Both return results fast at 500,000 rows. In the next step, you will use EXPLAIN to see the difference in how many rows ClickHouse actually reads.

You should see daily EC2 cost totals for each day in March 2024.

Not seeing March dates?

  • Check that the WHERE clause uses single quotes around the date strings. ClickHouse parses date literals from strings, so missing quotes cause a syntax error.
  • Make sure the date range uses >= '2024-03-01' and < '2024-04-01'. Using <= '2024-03-31' can miss rows on the last day because the column is a DateTime, not a Date.

Why is my ClickHouse date range filter returning no rows?

Before you scroll up to confirm, think about this: how many distinct dates do you expect the daily cost trend query to return for data spanning January through June 2024?

Scroll back up to your daily cost trend results. You should see a result set with dates and daily totals spanning January through June 2024. The cost_bar column draws a text-based sparkline for each day. Since the sample data is randomly generated, daily costs should be roughly similar across the period.

✔️ Awesome, I've got everything!

Great. Make sure you have all 9 queries saved in your DataGrip console for reference.

ⓧ I'd like to double check the full code

Scroll through your console and confirm each of these query sections is present, in order:

  • Total cost by service (with formatReadableQuantity).
  • Cost by account.
  • Cost per service per account (cross-tabulation).
  • Daily cost trend (with bar).
  • Monthly cost summary (with toYYYYMM).
  • Top 10 most expensive individual line items.
  • Cost by pricing term (with percentage subquery).
  • Cost by availability zone.
  • Filtered query on ORDER BY columns (EC2 in March 2024).

You now have a full suite of cost analysis queries covering services, accounts, time trends, pricing terms, and availability zones. Your queries work, but you do not yet know why ClickHouse runs them so fast. Next up, you will peek inside ClickHouse's internals to see how data parts, partitions, and sparse indexes make all of this possible.

Peek Under the Hood

In the last step, you wrote cost analysis queries that broke down spend by service, account, and time period. The queries ran fast, but right now ClickHouse is a black box.

In this step, you will open up that black box. You will query ClickHouse's internal metadata tables to see how your billing data is physically stored on disk, then use EXPLAIN to see exactly which queries can skip data and which have to scan everything.

In this step, get ready to:
  • Query system.parts to inspect data parts and monthly partitions.
  • Use EXPLAIN indexes = 1 to see how ClickHouse skips granules for ORDER BY-aligned queries.
Inspect data parts and partitions

When you inserted 500,000 rows, ClickHouse did not store them as one big file. MergeTree writes data as immutable chunks called data parts. These parts get merged in the background into larger, sorted pieces.

  • In DataGrip, open a new console tab to keep your internals queries separate from your cost analysis queries.
  • Query system.parts to see every data part for the billing table by running this query:
-- Inspect data parts for the billing table
SELECT
    partition,
    name,
    rows,
    formatReadableSize(bytes_on_disk) AS size_on_disk,
    active
FROM system.parts
WHERE database = 'aws_costs'
  AND table = 'billing'
  AND active = 1
ORDER BY partition;

What does this query do?

system.parts is a built-in ClickHouse table that tracks every data part across all databases. Each row represents one data part with its partition, name, row count, and size on disk.

The active = 1 filter shows only current parts, excluding parts that ClickHouse is in the process of merging away.

  • Run the query in DataGrip.
  • You should see a result set with one or more rows per partition, showing how ClickHouse split your 500,000 rows into parts grouped by month.

Seeing zero rows?

Make sure you are querying database aws_costs and table billing. Check for typos in the WHERE clause.

Why is my system.parts query returning no rows for the billing table?

Now let's zoom out and see how your PARTITION BY toYYYYMM(bill_billing_period_start_date) split the data into monthly chunks.

  • See the partition-level summary by running this query:
-- See how PARTITION BY split data into monthly chunks
SELECT
    partition,
    count() AS part_count,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS total_size
FROM system.parts
WHERE database = 'aws_costs'
  AND table = 'billing'
  AND active = 1
GROUP BY partition
ORDER BY partition;

What does this show?

  • Each row represents one monthly partition (e.g., 202401 for January 2024, 202402 for February).
  • part_count tells you how many data parts exist within that partition. ClickHouse merges parts over time, so this number can decrease.
  • total_rows confirms how many rows landed in each month.
  • Run the query.
  • You should see 6 rows, one for each month from January through June 2024 (202401 through 202406). The row counts should add up to 500,000.

Seeing fewer than 6 partitions?

Your billing data spans January through June 2024. If you see fewer partitions, the data insertion in Step 3 may not have completed fully. Run SELECT count() FROM aws_costs.billing to verify you have 500,000 rows.

Why am I seeing fewer than 6 partitions in my billing table?

See the sparse index in action

ClickHouse builds a sparse primary index for each data part. Instead of indexing every single row (like a Postgres B-tree), it stores one index entry per granule. A granule is roughly 8,192 rows by default.

When a query filters on the leading columns of the ORDER BY key, ClickHouse uses the sparse index to skip entire granules that cannot contain matching rows. Let's see this in action with EXPLAIN indexes = 1.

Before you run this next query, think about the billing table's ORDER BY: (line_item_product_code, line_item_usage_account_id, line_item_usage_start_date). If you filter on line_item_product_code = 'AmazonEC2', do you think ClickHouse will need to read all 500,000 rows?

  • Run the following EXPLAIN query to see how ClickHouse plans to execute a filter on the first ORDER BY column:
-- EXPLAIN with indexes: query on ORDER BY column (skips granules)
EXPLAIN indexes = 1
SELECT
    toStartOfDay(line_item_usage_start_date) AS day,
    sum(line_item_unblended_cost) AS cost
FROM aws_costs.billing
WHERE line_item_product_code = 'AmazonEC2'
GROUP BY day
ORDER BY day;

What to look for in the output

Look for Parts and Granules in the output. ClickHouse shows how many parts and granules it plans to read.

Because line_item_product_code is the first column in your ORDER BY, the sparse index can identify which granules definitely do not contain AmazonEC2 rows and skip them entirely. You should see fewer granules selected than the total number in the table.

  • Run the query in DataGrip.
  • In the output, look for the granules line. ClickHouse reads only a fraction of the total granules because it skipped the ones that do not contain EC2 data.

Now for the comparison. Before you run the next query, think about what happens when you filter on line_item_availability_zone. That column is not in the ORDER BY. Do you think ClickHouse can still skip granules?

  • Run this EXPLAIN query, which filters on a column not in the ORDER BY:
-- EXPLAIN with indexes: query NOT on ORDER BY column (scans all granules)
EXPLAIN indexes = 1
SELECT
    line_item_availability_zone AS az,
    sum(line_item_unblended_cost) AS cost
FROM aws_costs.billing
WHERE line_item_availability_zone = 'us-east-1a'
GROUP BY az;

What does this show?

This time, ClickHouse reads ALL granules. Because line_item_availability_zone is not part of the ORDER BY, the sparse index has no information about where us-east-1a values live. ClickHouse must scan every granule to find matching rows.

  • Compare the granules selected in this query to the previous EXPLAIN. This query reads every granule in the table, while the EC2 query skipped the majority.

Why ORDER BY is a physical design decision

ORDER BY in ClickHouse is not about sorting query results. It determines how data is physically laid out on disk.

Queries that filter on ORDER BY columns can skip granules and read less data. Queries that filter on other columns must scan everything. This is fundamentally different from a Postgres B-tree index, which is a separate structure you can add to any column.

When designing a ClickHouse table, put the columns you filter on most often first in the ORDER BY. That single decision controls which queries are fast and which are not.

EXPLAIN output not showing granule information?

Make sure you include indexes = 1 after EXPLAIN. Without it, ClickHouse shows a different, less detailed plan that does not include granule-level information.

Why does my EXPLAIN output not show granule skipping details?

✔️ Awesome, I've got everything!

Great. You have run all four internals queries and seen the key difference between ORDER BY-aligned and non-aligned filters.

ⓧ I'd like to double check the full code

Scroll up and confirm you have run each of these four query blocks in order:

  • The system.parts detail query (SELECT partition, name, rows, etc.).
  • The partition summary query (GROUP BY partition with part count and total rows).
  • The EXPLAIN on line_item_product_code = 'AmazonEC2' (should show granule skipping).
  • The EXPLAIN on line_item_availability_zone = 'us-east-1a' (should read all granules).

Secret mission

Build a Materialized View for Auto-Aggregated Costs

Your cost queries work, but they scan the full billing table every time. What if costs pre-aggregated themselves the moment new data arrived? Build a Materialized View that automatically summarizes daily costs, with no manual refresh needed.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • A Docker container named clickhouse-learning running the ClickHouse server locally.
  • A DataGrip data source configured to connect to the local ClickHouse instance.

Keep everything running

No action needed. Choose this if you are still actively experimenting with ClickHouse queries or want to continue building on the project.

  • The clickhouse-learning container stays running and all your data persists inside it.
  • Your DataGrip data source stays configured and ready for querying.

Pause - I'll come back to this later

Stop the container to free up system resources. Your data is preserved and you can resume later.

  • Stop the ClickHouse container by running this command in your terminal:
docker stop clickhouse-learning
  • When you are ready to resume, start it again by running:
docker start clickhouse-learning
  • All your databases, tables, and data are preserved inside the stopped container.
  • Your DataGrip data source will reconnect automatically once the container is running again.

Delete - I don't want to use this again

Remove the container and all data inside it. This is a clean slate.

  • Stop and remove the ClickHouse container by running this command in your terminal:
docker stop clickhouse-learning && docker rm clickhouse-learning
  • This removes the container along with the aws_costs database and all tables inside it.
  • Remove the DataGrip data source by right-clicking the ClickHouse entry in the Database Explorer panel, then selecting Remove Data Source.

Nice Work!

Nice Work!

Nice work! You just built a full AWS cost analysis environment powered by ClickHouse, loaded it with half a million rows of billing data, and wrote the queries that answer real cost questions.

You learned how to:

  • Built a CUR-style billing table with 500,000 rows in ClickHouse and ran analytical queries to break down AWS costs by service, account, time period, and pricing term.
  • Learned how ClickHouse SQL differs from Postgres: every table needs an ENGINE and ORDER BY that determines physical data layout, columns use types like LowCardinality(String), and writes are append-only with no traditional updates.
  • Inspected ClickHouse internals by querying system.parts to see data parts and partitions, then used EXPLAIN indexes = 1 to watch the sparse index skip granules on ORDER BY columns and scan everything on non-ORDER BY columns.
  • Secret Mission: Built a Materialized View with SummingMergeTree for automatic incremental pre-aggregation of daily costs.

Ready to quiz yourself?