Build Claims Analytics Dashboard

Model healthcare claims in ClickHouse and publish a governed Superset dashboard.

Introduction

30 Second Summary

A healthcare claim may be denied before it is corrected. Its resubmission can make the same claim look like two separate outcomes.

In this project, you will use ClickHouse to turn 1,100 fictional claim submissions into 1,000 trusted final claims. Apache Superset will present those claims through an interactive dashboard with filters for service date, payer, facility, and status.

What You'll Build

Picture a published healthcare dashboard where every payer or facility filter updates KPIs backed by a reporting model you proved correct.

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

  • A raw-versus-trusted comparison that shows 1,100 submissions collapse to 1,000 final claims after resubmissions are resolved.
  • A validated claims model that you can query to prove each claim appears once and every quality check passes.
  • A published claims dashboard whose KPI cards and investigation charts respond to filters for service date, payer, facility, or claim status.
  • Secret Mission: Restrict the dashboard to North Clinic for a Gamma user with Row Level Security.

Are there any prerequisites?

You'll need a Windows 10 or Windows 11 computer with virtualization available, approximately 8 GB of RAM, and internet access for container images. Step 1 provides official installation routes for missing tools. Docker Desktop licensing may apply in some organizations.

Before We Start

Before the hands-on work begins, this checkpoint commits you to building a trustworthy claims dashboard for healthcare operations and finance users with Docker Desktop, Git for Windows, plus Visual Studio Code. Raw claim submissions cannot be counted directly because corrected denied claims can create multiple submission rows for one claim_id.

Launch the Local Analytics Stack

Trustworthy claims modeling depends on a repeatable environment. A local Docker Compose stack gives ClickHouse a pinned home for the raw claims data.

The same stack runs Apache Superset for analysis. This keeps cloud accounts out of the way so you can focus on the reporting grain behind every dashboard number.

In this step, get ready to:
  • Verify the required Windows tools.
  • Create the version-pinned Superset workspace and configuration files.
  • Start the local analytics services and sign in to Superset.
Verify the Windows tools

What do these tools do?

  • Docker Desktop runs the Linux containers through WSL 2.
  • Git downloads the pinned Superset source code.
  • Visual Studio Code gives you a workspace for the project files.
  • Press the Windows key to open the search bar.
  • Type PowerShell into the search bar.
  • Press Enter to open PowerShell.
  • Check Docker Desktop and Git by running these commands:
docker version
docker compose version
git --version

What do these checks prove?

  • The first command confirms that the Docker client can reach the running Docker engine.
  • The second command confirms that Docker Compose is available for the multi-container stack.
  • The third command confirms that Git for Windows is available in PowerShell.

✔️ All three commands print version details

Good progress. Your container engine and both command-line tools are responding.

ⓧ Docker is missing or unavailable

  • Download Docker Desktop from the official Windows installation page.
  • Run the downloaded installer.
  • Select the WSL 2 backend when the installer offers the choice.
  • Complete the Docker Desktop installation.
  • Restart PowerShell after installation.
  • Press the Windows key to open the search bar.
  • Type Docker Desktop into the search bar.
  • Press Enter to start Docker Desktop.
  • Recheck Docker after its engine finishes starting by running:
docker version
docker compose version

What should I see?

The output should include Docker client details and server details. The Compose command should print its installed version.

ⓧ Git is missing

  • Download Git for Windows from the official Windows installation page.
  • Run the downloaded installer.
  • Accept the default installation options.
  • Complete the Git for Windows installation.
  • Restart PowerShell after installation.
  • Recheck Git by running:
git --version

What should I see?

Git prints a version number when PowerShell can find the installed command.

  • Press the Windows key to open the search bar.
  • Type Visual Studio Code into the search bar.
  • Press Enter to launch the editor.

✔️ Visual Studio Code opens

Visual Studio Code is ready to hold the Superset workspace and project configuration.

ⓧ Visual Studio Code is missing

  • Download the Windows User setup from the official Visual Studio Code setup page.
  • Run the downloaded installer.
  • Complete the Visual Studio Code installation.
  • Restart PowerShell so the editor command is available.
  • Search for Visual Studio Code from the Windows search bar.
  • Press Enter to confirm that the editor opens.
Create the Superset workspace

The official Superset repository contains the base Compose services. Checking out tags/6.0.0 makes the local environment reproducible.

  • Switch back to PowerShell.
  • Move to your Desktop by running this command:
cd ~/Desktop

What does this command do?

This makes your Desktop the starting location for the repository. You will be able to find the new superset folder without searching your computer.

Cloning the repository can take a few minutes because Git downloads the full Superset project history. A steady stream of progress messages means the download is working.

  • Clone the official repository and check out Superset 6.0.0 by running:
git clone https://github.com/apache/superset
cd superset
git checkout tags/6.0.0

What do these commands do?

  • The first command downloads the Apache Superset repository into a folder named superset.
  • The second command moves PowerShell into that folder.
  • The third command selects the official 6.0.0 source snapshot.

The terminal confirms that Git checked out the requested tag. Your local workspace now starts from the same Superset source as the project configuration.

Did the repository fail to clone?

  • Confirm that your internet connection can reach GitHub.
  • Check that the earlier Git verification printed a version number.
  • Remove any incomplete superset folder from your Desktop before retrying the clone.

Still stuck? Help me troubleshoot cloning the Apache Superset repository on Windows. You can also share the non-sensitive command output with the NextWork community.

  • Open the cloned repository in Visual Studio Code by running:
code .

What does this command do?

The command opens the current superset folder as a Visual Studio Code workspace. The Explorer sidebar gives every project file one shared home.

Did the editor command fail?

  • Restart PowerShell so it picks up the Visual Studio Code PATH update.
  • Use Windows search to open Visual Studio Code if the command remains unavailable.
  • Select the cloned superset folder from the editor's folder-opening screen.

Still stuck? Help me open my cloned Superset folder in Visual Studio Code.

Four local files customize the official repository for this project. They pin the database driver, define the ClickHouse service, and generate the fictional raw claims.

  • Expand the docker folder in the Visual Studio Code Explorer sidebar.
  • Select the new-file control in the Explorer sidebar.
  • Name the file .env-local.
  • Paste this configuration into docker/.env-local.
SUPERSET_LOAD_EXAMPLES=no
SUPERSET_SECRET_KEY=your-superset-secret-key-here
ADMIN_PASSWORD=your-superset-admin-password-here

What does this configuration do?

  • SUPERSET_LOAD_EXAMPLES keeps the sandbox focused on your claims data.
  • SUPERSET_SECRET_KEY stores the private value used by Superset security features.
  • ADMIN_PASSWORD sets the password for the Docker initialization admin account.

Credentials are the fiddly part of this setup because the template must stay shareable while your local copy stays private. Replace every credential placeholder before the containers start.

  • Replace your-superset-secret-key-here with a private random value from your password manager.
  • Replace your-superset-admin-password-here with a private password from your password manager.
  • Save docker/.env-local.

The Explorer sidebar now lists .env-local inside the docker folder.

  • Select the new-file control in the Explorer sidebar.
  • Name the file requirements-local.txt inside the docker folder.
  • Paste this dependency into docker/requirements-local.txt.
clickhouse-connect==1.9.0

Why add this dependency?

The clickhouse-connect driver lets Superset communicate with ClickHouse. Pinning it to 1.9.0 keeps the connector reproducible.

  • Save docker/requirements-local.txt.

The Explorer sidebar now lists both local override files inside the docker folder.

  • Select the superset workspace name at the top of the Explorer sidebar.
  • Select the new-file control in the Explorer sidebar.
  • Name the file docker-compose.project.yml.
  • Paste this configuration into docker-compose.project.yml.
services:
  superset:
    image: apache/superset:6.0.0-dev

  superset-init:
    image: apache/superset:6.0.0-dev

  clickhouse:
    image: clickhouse:26.9.8.3
    container_name: claims_clickhouse
    restart: unless-stopped
    ports:
      - "8123:8123"
    environment:
      CLICKHOUSE_DB: claims
      CLICKHOUSE_USER: claims_user
      CLICKHOUSE_PASSWORD: your-clickhouse-password-here
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: "1"
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - ./clickhouse:/project-clickhouse:ro
      - ./clickhouse/init:/docker-entrypoint-initdb.d:ro

volumes:
  clickhouse_data:

What does this configuration do?

  • The Superset services use the apache/superset:6.0.0-dev image.
  • The ClickHouse service uses the clickhouse:26.9.8.3 image and the container name claims_clickhouse.
  • Port 8123 exposes ClickHouse over local HTTP.
  • The volume mounts preserve the database and expose the project SQL inside the container.
  • Replace your-clickhouse-password-here with a private password from your password manager.
  • Save docker-compose.project.yml.

The Explorer sidebar now lists docker-compose.project.yml at the top level of the superset workspace.

  • Select the superset workspace name in the Explorer sidebar.
  • Select the new-folder control in the Explorer sidebar.
  • Name the folder clickhouse.
  • Select the new-folder control beside the clickhouse folder.
  • Name the nested folder init.
  • Select the new-file control beside the init folder.
  • Name the file 01_raw_claims.sql.
  • Paste this full initialization script into clickhouse/init/01_raw_claims.sql.
CREATE DATABASE IF NOT EXISTS claims;

CREATE TABLE IF NOT EXISTS claims.raw_claims
(
    claim_id UInt32,
    submission_version UInt8,
    service_date Date,
    submission_date Date,
    processed_date Date,
    facility LowCardinality(String),
    payer LowCardinality(String),
    claim_status LowCardinality(String),
    denial_reason LowCardinality(String),
    submitted_amount Decimal(12, 2),
    allowed_amount Decimal(12, 2),
    paid_amount Decimal(12, 2)
)
ENGINE = MergeTree()
ORDER BY (claim_id, submission_version);

INSERT INTO claims.raw_claims
SELECT
    number + 1 AS claim_id,
    1 AS submission_version,
    toDate('2026-01-01') + (number % 180) AS service_date,
    toDate('2026-01-02') + (number % 180) AS submission_date,
    toDate('2026-01-02') + (number % 180) + ((number % 12) + 1) AS processed_date,
    CASE number % 3
        WHEN 0 THEN 'North Clinic'
        WHEN 1 THEN 'Central Hospital'
        ELSE 'Lakeside Center'
    END AS facility,
    CASE number % 4
        WHEN 0 THEN 'HealthFirst'
        WHEN 1 THEN 'CarePlus'
        WHEN 2 THEN 'United Shield'
        ELSE 'Community Health'
    END AS payer,
    CASE
        WHEN number % 5 = 0 THEN 'Denied'
        WHEN number % 5 = 1 THEN 'Pending'
        ELSE 'Paid'
    END AS claim_status,
    CASE
        WHEN number % 5 != 0 THEN ''
        WHEN number % 4 = 0 THEN 'Missing information'
        WHEN number % 4 = 1 THEN 'Authorization required'
        WHEN number % 4 = 2 THEN 'Coding issue'
        ELSE 'Coverage ended'
    END AS denial_reason,
    1000 + (number % 20) * 50 AS submitted_amount,
    900 + (number % 20) * 40 AS allowed_amount,
    CASE
        WHEN number % 5 IN (2, 3, 4) THEN 900 + (number % 20) * 40
        ELSE 0
    END AS paid_amount
FROM numbers(1000);

INSERT INTO claims.raw_claims
SELECT
    (number * 10) + 1 AS claim_id,
    2 AS submission_version,
    toDate('2026-01-01') + ((number * 10) % 180) AS service_date,
    toDate('2026-01-09') + ((number * 10) % 180) AS submission_date,
    toDate('2026-01-12') + ((number * 10) % 180) AS processed_date,
    CASE (number * 10) % 3
        WHEN 0 THEN 'North Clinic'
        WHEN 1 THEN 'Central Hospital'
        ELSE 'Lakeside Center'
    END AS facility,
    CASE (number * 10) % 4
        WHEN 0 THEN 'HealthFirst'
        WHEN 1 THEN 'CarePlus'
        WHEN 2 THEN 'United Shield'
        ELSE 'Community Health'
    END AS payer,
    'Paid' AS claim_status,
    '' AS denial_reason,
    1000 + ((number * 10) % 20) * 50 AS submitted_amount,
    850 + ((number * 10) % 20) * 40 AS allowed_amount,
    850 + ((number * 10) % 20) * 40 AS paid_amount
FROM numbers(100);

What does this SQL create?

  • The script creates the claims database and a MergeTree table named claims.raw_claims.
  • The first insertion generates the original fictional claim submissions.
  • The second insertion adds later versions for selected claim IDs.
  • The version column preserves the resubmission history that you will investigate next.
  • Save clickhouse/init/01_raw_claims.sql.

The Explorer sidebar now shows 01_raw_claims.sql inside clickhouse/init.

Are any files in the wrong folder?

  • Keep docker-compose.project.yml at the top level of the cloned superset folder.
  • Keep both local Superset override files inside the existing docker folder.
  • Keep the SQL script inside clickhouse/init so the database container can run it during first initialization.

Still stuck? Help me check the file paths in my Superset and ClickHouse workspace.

Use the tabs below to confirm that every project file matches the template. Keep your own private values in place when comparing the three credential placeholders.

✔️ Awesome, I've got everything!

Great. Save every file before you start the containers.

ⓧ I'd like to double check the full code

Compare each file with its full template below. Preserve your private values wherever the template shows a credential placeholder.

SUPERSET_LOAD_EXAMPLES=no
SUPERSET_SECRET_KEY=your-superset-secret-key-here
ADMIN_PASSWORD=your-superset-admin-password-here
clickhouse-connect==1.9.0
services:
  superset:
    image: apache/superset:6.0.0-dev

  superset-init:
    image: apache/superset:6.0.0-dev

  clickhouse:
    image: clickhouse:26.9.8.3
    container_name: claims_clickhouse
    restart: unless-stopped
    ports:
      - "8123:8123"
    environment:
      CLICKHOUSE_DB: claims
      CLICKHOUSE_USER: claims_user
      CLICKHOUSE_PASSWORD: your-clickhouse-password-here
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: "1"
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - ./clickhouse:/project-clickhouse:ro
      - ./clickhouse/init:/docker-entrypoint-initdb.d:ro

volumes:
  clickhouse_data:
CREATE DATABASE IF NOT EXISTS claims;

CREATE TABLE IF NOT EXISTS claims.raw_claims
(
    claim_id UInt32,
    submission_version UInt8,
    service_date Date,
    submission_date Date,
    processed_date Date,
    facility LowCardinality(String),
    payer LowCardinality(String),
    claim_status LowCardinality(String),
    denial_reason LowCardinality(String),
    submitted_amount Decimal(12, 2),
    allowed_amount Decimal(12, 2),
    paid_amount Decimal(12, 2)
)
ENGINE = MergeTree()
ORDER BY (claim_id, submission_version);

INSERT INTO claims.raw_claims
SELECT
    number + 1 AS claim_id,
    1 AS submission_version,
    toDate('2026-01-01') + (number % 180) AS service_date,
    toDate('2026-01-02') + (number % 180) AS submission_date,
    toDate('2026-01-02') + (number % 180) + ((number % 12) + 1) AS processed_date,
    CASE number % 3
        WHEN 0 THEN 'North Clinic'
        WHEN 1 THEN 'Central Hospital'
        ELSE 'Lakeside Center'
    END AS facility,
    CASE number % 4
        WHEN 0 THEN 'HealthFirst'
        WHEN 1 THEN 'CarePlus'
        WHEN 2 THEN 'United Shield'
        ELSE 'Community Health'
    END AS payer,
    CASE
        WHEN number % 5 = 0 THEN 'Denied'
        WHEN number % 5 = 1 THEN 'Pending'
        ELSE 'Paid'
    END AS claim_status,
    CASE
        WHEN number % 5 != 0 THEN ''
        WHEN number % 4 = 0 THEN 'Missing information'
        WHEN number % 4 = 1 THEN 'Authorization required'
        WHEN number % 4 = 2 THEN 'Coding issue'
        ELSE 'Coverage ended'
    END AS denial_reason,
    1000 + (number % 20) * 50 AS submitted_amount,
    900 + (number % 20) * 40 AS allowed_amount,
    CASE
        WHEN number % 5 IN (2, 3, 4) THEN 900 + (number % 20) * 40
        ELSE 0
    END AS paid_amount
FROM numbers(1000);

INSERT INTO claims.raw_claims
SELECT
    (number * 10) + 1 AS claim_id,
    2 AS submission_version,
    toDate('2026-01-01') + ((number * 10) % 180) AS service_date,
    toDate('2026-01-09') + ((number * 10) % 180) AS submission_date,
    toDate('2026-01-12') + ((number * 10) % 180) AS processed_date,
    CASE (number * 10) % 3
        WHEN 0 THEN 'North Clinic'
        WHEN 1 THEN 'Central Hospital'
        ELSE 'Lakeside Center'
    END AS facility,
    CASE (number * 10) % 4
        WHEN 0 THEN 'HealthFirst'
        WHEN 1 THEN 'CarePlus'
        WHEN 2 THEN 'United Shield'
        ELSE 'Community Health'
    END AS payer,
    'Paid' AS claim_status,
    '' AS denial_reason,
    1000 + ((number * 10) % 20) * 50 AS submitted_amount,
    850 + ((number * 10) % 20) * 40 AS allowed_amount,
    850 + ((number * 10) % 20) * 40 AS paid_amount
FROM numbers(100);
Start the analytics stack

The project Compose file extends the official Superset services with ClickHouse. Compose also starts the required PostgreSQL and Redis dependencies.

This environment is a local learning sandbox. The project starts the web dependencies without the optional worker services so it fits more comfortably on the Windows machine.

  • Press Cmd+Shift+F on macOS or Ctrl+Shift+F on Windows to open the Visual Studio Code search panel.
  • Enter your- in the search field.

The search should return no credential placeholders. This confirms that your three private values are in place before startup.

The first startup can take several minutes while Docker downloads the pinned images and initializes the databases. Quiet periods are normal while the containers prepare the local services.

  • Return to PowerShell.
  • Start Superset and ClickHouse from the cloned superset folder by running:
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml up -d superset clickhouse

What does this command start?

  • The repeated Compose file options merge the official Superset definition with your project override.
  • The superset target starts the web application and its required dependencies.
  • The clickhouse target starts the pinned database container and runs the initialization SQL against its new data volume.
  • The detached option leaves the services running after PowerShell returns to the prompt.

Did the stack fail to start?

  • Confirm that Docker Desktop is still running.
  • Check that PowerShell is inside the cloned superset folder.
  • Check that the Visual Studio Code search found no remaining credential placeholders.

Still stuck? Help me troubleshoot the Superset and ClickHouse Docker Compose startup. You can also share the non-sensitive startup output with the NextWork community.

Before you check, which commands do you expect to prove that the local tools and containers are responding?

  • Verify the tools and running containers by running:
docker version
docker compose version
git --version
docker ps

What should I see?

  • Docker prints client and server information.
  • Docker Compose prints its installed version.
  • Git prints its installed version.
  • The container list includes superset_app and claims_clickhouse with running statuses.
  • Open a browser window.
  • Navigate to http://localhost:8088.

You should see the Superset login page. That page confirms that the local web service is accepting requests.

  • Enter admin in the Username field.
  • Enter the private value you placed in ADMIN_PASSWORD in the Password field.
  • Submit the login form.

That is the local stack online. You are signed in to Superset while the ClickHouse claims database runs beside it.

Can you see the login page but not sign in?

  • Use the exact private value saved under ADMIN_PASSWORD in docker/.env-local.
  • Wait another minute if the Superset initialization container is still completing its setup.
  • Confirm that the username is lowercase admin.

Still stuck? Help me troubleshoot signing in to my local Superset 6.0.0 environment.

Your version-pinned analytics environment is running. Next, you will test whether the raw submission rows can be trusted as claim counts.

Expose the Double-Counting Problem

Your local analytics stack is running. Now you can test whether the raw claims table tells the truth about claim volume.

The raw layer in ClickHouse contains claim resubmissions. Counting each submission as a claim inflates the result.

This step makes that failure visible before you build the reporting model.

In this step, get ready to:
  • Compare raw submission rows with distinct claim IDs.
  • Inspect a denied claim that was resubmitted as paid.
  • Explain why raw denied counts cannot represent final outcomes.
Test the raw table's grain

Data grain defines what one row represents. Each row in raw_claims represents one submission version.

A claim can therefore appear more than once. Comparing row count with distinct claim count exposes that mismatch.

  • Return to the PowerShell terminal from the previous step.

Before you run this query, do you expect the two counts to match?

  • Measure raw rows against distinct claim IDs by running this query:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --query "SELECT count() AS raw_rows, count(DISTINCT claim_id) AS unique_claims FROM raw_claims"

What does this query measure?

  • The count() AS raw_rows expression counts every submission record.
  • The count(DISTINCT claim_id) AS unique_claims expression counts each claim ID once.
  • Comparing the values tests whether the table grain matches the business grain.

You'll see raw_rows = 1100 plus unique_claims = 1000.

You found the deliberate gap. The table has 100 more rows than business claims.

Query not returning both counts?

  • Confirm the claims_clickhouse container shows as running in Docker Desktop.
  • Check that the command uses the claims database.
  • Check that the query reads from raw_claims.

Still stuck? Help me troubleshoot the raw-versus-unique ClickHouse query

Connect the grain mismatch to denial outcomes

A resubmission creates another operational event for the same business claim. Looking at one claim makes the duplicate grain concrete.

Before you run this query, do you expect claim 1 to have one row or two?

  • Inspect every stored version of claim 1 by running this query:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --query "SELECT claim_id, submission_version, claim_status, denial_reason, paid_amount FROM raw_claims WHERE claim_id = 1 ORDER BY submission_version"

What does this query reveal?

  • The submission_version column shows the sequence of submissions for one claim.
  • The claim_status column shows how the outcome changed.
  • The paid_amount column confirms whether the payer released payment.

You'll see version 1 marked Denied with a paid amount of 0.00.

You'll also see version 2 marked Paid with a paid amount of 850.00. Claim 1 moved from a denied submission to a paid resubmission.

Claim 1 not showing two versions?

  • Confirm the query contains claim_id = 1.
  • Check that the query reads from the raw_claims table.
  • Confirm the current data volume was initialized from clickhouse/init/01_raw_claims.sql.

Need another pair of eyes? Help me find both versions of claim 1

A raw status count treats every historical submission as a current outcome. The count mixes resolved denials with unresolved denials.

Before you run the next query, do you expect the raw denied count to reflect only final outcomes?

  • Count every denied submission in the raw table by running this query:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --query "SELECT count() AS raw_denied_rows FROM raw_claims WHERE claim_status = 'Denied'"

What does this denial count include?

The query counts rows whose stored claim_status is Denied. It includes denied submissions that later received another version.

You'll see raw_denied_rows = 200.

Of those denied submissions, 100 belong to claims that later received a paid version. The raw count therefore overstates final denials by 100.

You've exposed the reporting risk. A dashboard connected directly to raw_claims would display stale denials as current outcomes.

Denied count not showing 200?

  • Confirm the status value uses the capitalized spelling Denied.
  • Check that the query uses count() against raw_claims.
  • Confirm the initialization SQL was not run manually more than once against the current data volume.

Still seeing a different result? Help me troubleshoot the raw denied submission count

Before the final check, do you expect these read-only queries to have changed either count?

  • Verify the raw-versus-unique result again by running:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --query "SELECT count() AS raw_rows, count(DISTINCT claim_id) AS unique_claims FROM raw_claims"

Why repeat the comparison?

This repeats the original grain check after your inspection queries. Matching counts confirm that the read-only investigation left the raw table unchanged.

You'll still see raw_rows = 1100 plus unique_claims = 1000. The 100-row difference confirms that raw submissions cannot be counted directly as final claims.

Final counts changed?

Read-only queries do not add rows. A changed count usually means another process reran the initialization SQL against the current data volume.

Pause before continuing. Help me restore the expected ClickHouse row counts

You have proven that raw submissions cannot support trustworthy claim-level metrics. Next, you'll build the one-row-per-claim view that resolves the grain.

Build and Validate the Trusted Claims Model

You proved that 1,100 submission rows describe only 1,000 claim IDs. That gap makes the raw table unsafe for final claim reporting.

One-claim questions need a one-row-per-claim source. In this step, a ClickHouse window function selects each latest submission.

In this step, get ready to:
  • Build a reporting view that retains the latest submission for each claim.
  • Validate the reporting grain with reproducible quality checks.
  • Document the model rules in a metric contract.
Create the latest-claim reporting view

A reporting view turns the raw submission history into a reusable query. The ranking restarts for each claim before keeping its highest submission version.

  • In the Visual Studio Code file tree from earlier, locate the clickhouse folder.
  • Right-click the clickhouse folder.
  • Choose the option to create a new folder.
  • Enter reporting as the folder name.
  • Right-click the new reporting folder.
  • Choose the option to create a new file.
  • Name the file 02_reporting_view.sql.
  • Paste this reporting view definition into clickhouse/reporting/02_reporting_view.sql:
CREATE OR REPLACE VIEW claims.claims_reporting AS
SELECT
    claim_id,
    submission_version,
    service_date,
    submission_date,
    processed_date,
    facility,
    payer,
    claim_status,
    denial_reason,
    submitted_amount,
    allowed_amount,
    paid_amount,
    dateDiff('day', submission_date, processed_date) AS processing_days,
    submitted_amount - paid_amount AS outstanding_amount
FROM
(
    SELECT
        *,
        row_number() OVER
        (
            PARTITION BY claim_id
            ORDER BY submission_version DESC
        ) AS version_rank
    FROM claims.raw_claims
)
WHERE version_rank = 1;

How does the reporting view work?

  • The PARTITION BY claim_id clause creates a separate ranking group for each claim.
  • The ORDER BY submission_version DESC clause places the latest submission first.
  • The WHERE version_rank = 1 condition keeps one final row from each group.
  • The derived processing_days column measures the processing interval.
  • The derived outstanding_amount column measures the unpaid portion of each final claim.
  • Save clickhouse/reporting/02_reporting_view.sql.
  • Switch back to the PowerShell terminal in the Superset repository.
  • Create the reporting view in ClickHouse by running:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/reporting/02_reporting_view.sql

What does this command do?

The command executes the saved SQL file inside claims_clickhouse. ClickHouse creates the claims.claims_reporting view from the mounted project file.

PowerShell should return to the prompt without an error. The reporting view is now available for the quality checks.

Reporting view command failed?

  • Confirm that 02_reporting_view.sql is inside the clickhouse/reporting folder.
  • Check that the file contains the final WHERE version_rank = 1 line.
  • Confirm that the claims_clickhouse container is still running.

Still stuck? Help me troubleshoot why ClickHouse cannot create claims.claims_reporting from my mounted SQL file.

✔️ Awesome, I've got everything!

Your reporting view file is saved. The latest submission now defines each final claim.

ⓧ I'd like to double check the full code

CREATE OR REPLACE VIEW claims.claims_reporting AS
SELECT
    claim_id,
    submission_version,
    service_date,
    submission_date,
    processed_date,
    facility,
    payer,
    claim_status,
    denial_reason,
    submitted_amount,
    allowed_amount,
    paid_amount,
    dateDiff('day', submission_date, processed_date) AS processing_days,
    submitted_amount - paid_amount AS outstanding_amount
FROM
(
    SELECT
        *,
        row_number() OVER
        (
            PARTITION BY claim_id
            ORDER BY submission_version DESC
        ) AS version_rank
    FROM claims.raw_claims
)
WHERE version_rank = 1;
Validate the reporting grain

A row count alone can hide incorrect status totals or duplicated claim IDs. A data quality suite tests each rule that the dashboard depends on.

  • Return to the clickhouse folder in the Visual Studio Code file tree.
  • Create a new file named quality_checks.sql.
  • Paste these volume checks into clickhouse/quality_checks.sql:
SELECT
    'raw_vs_unique' AS check_name,
    count() AS raw_rows,
    count(DISTINCT claim_id) AS unique_claims
FROM claims.raw_claims;

SELECT
    'reporting_row_count' AS check_name,
    count() AS reporting_rows
FROM claims.claims_reporting;

SELECT
    claim_status,
    count() AS claim_count
FROM claims.claims_reporting
GROUP BY claim_status
ORDER BY claim_status;

What do these checks prove?

  • The first query preserves the raw-versus-unique comparison from your earlier investigation.
  • The second query confirms that the reporting view contains one row for every unique claim ID.
  • The third query verifies the final status distribution after resubmissions replace their earlier versions.
  • Save clickhouse/quality_checks.sql.

Before you run these checks, how should the reporting total compare with the unique claim count?

  • Execute the current quality checks by running:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/quality_checks.sql

What does this validation command do?

The command runs each complete query in quality_checks.sql against the claims database. Each result exposes one reporting assumption as a visible check.

You should see raw_rows = 1100 beside unique_claims = 1000.

You should also see reporting_rows = 1000. The status results should show Paid = 700, Pending = 200, and Denied = 100.

Unexpected reporting counts?

  • Confirm that the reporting view orders submission_version in descending order.
  • Confirm that the reporting view filters to version_rank = 1.
  • Run the reporting-view command again after saving any correction.

Need another pair of eyes? Help me compare my reporting-view SQL with the unexpected ClickHouse counts.

Correct totals can still conceal duplicate IDs or impossible values. The next checks test those model invariants directly.

  • Append these integrity checks below the status query in clickhouse/quality_checks.sql:
SELECT
    'duplicate_claim_ids' AS check_name,
    count() AS duplicate_claim_ids
FROM
(
    SELECT claim_id
    FROM claims.claims_reporting
    GROUP BY claim_id
    HAVING count() > 1
);

SELECT
    'invalid_paid_amounts' AS check_name,
    count() AS invalid_paid_amounts
FROM claims.claims_reporting
WHERE paid_amount < 0;

SELECT
    'invalid_processing_dates' AS check_name,
    count() AS invalid_processing_dates
FROM claims.claims_reporting
WHERE processed_date < submission_date;

What do the integrity checks cover?

  • The duplicate check groups the reporting rows by claim_id. It counts any identifier that appears more than once.
  • The paid-amount check finds values below zero.
  • The processing-date check finds claims processed before their submission date.
  • Save clickhouse/quality_checks.sql.
  • Run the expanded quality suite by running:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/quality_checks.sql

Why rerun the full file?

Rerunning the same file confirms that the earlier totals still pass after the new checks are added. It also makes the complete validation sequence reproducible.

You should now see duplicate_claim_ids = 0. You should also see invalid_paid_amounts = 0.

The final integrity result should show invalid_processing_dates = 0.

Integrity check returned a nonzero value?

  • Inspect the reporting-view filter for a missing version_rank = 1 condition if duplicate IDs appear.
  • Compare the invalid-value query with the saved version if an expected zero changes.
  • Save the corrected file before rerunning the quality suite.

Need help tracing the failed rule? Help me diagnose which ClickHouse reporting invariant is failing.

A reconciliation check confirms that regrouping paid amounts by facility preserves the overall total. This protects the dashboard from aggregation drift.

  • Append this reconciliation query to the end of clickhouse/quality_checks.sql:
SELECT
    total_paid,
    regrouped_paid,
    total_paid = regrouped_paid AS totals_reconcile
FROM
(
    SELECT sum(paid_amount) AS total_paid
    FROM claims.claims_reporting
)
CROSS JOIN
(
    SELECT sum(facility_paid) AS regrouped_paid
    FROM
    (
        SELECT
            facility,
            sum(paid_amount) AS facility_paid
        FROM claims.claims_reporting
        GROUP BY facility
    )
);

How does reconciliation work?

  • The first subquery calculates one overall paid amount.
  • The second subquery groups paid amounts by facility before summing those groups.
  • The totals_reconcile comparison proves that both aggregation paths produce the same result.
  • Save clickhouse/quality_checks.sql.

Before you rerun the suite, should the overall paid amount equal the sum of the facility totals?

  • Run the completed quality suite by running:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/quality_checks.sql

What does the completed suite establish?

The completed suite tests volume, grain, status distribution, invalid values, and reconciliation. These checks convert the reporting view from an assumption into a verified model.

The final result should show matching paid totals. You should see totals_reconcile = true.

That is the core modeling problem solved. Your 1,100 operational submissions now produce 1,000 trusted final claims.

Paid totals do not reconcile?

  • Confirm that both sides of the comparison read from claims.claims_reporting.
  • Check that the grouped subquery calculates sum(paid_amount) for each facility.
  • Check that the outer query calculates sum(facility_paid).

Still seeing different totals? Help me debug the paid-amount reconciliation query in ClickHouse.

✔️ Awesome, I've got everything!

Your completed quality suite verifies the reporting grain and the dashboard totals.

ⓧ I'd like to double check the full code

SELECT
    'raw_vs_unique' AS check_name,
    count() AS raw_rows,
    count(DISTINCT claim_id) AS unique_claims
FROM claims.raw_claims;

SELECT
    'reporting_row_count' AS check_name,
    count() AS reporting_rows
FROM claims.claims_reporting;

SELECT
    claim_status,
    count() AS claim_count
FROM claims.claims_reporting
GROUP BY claim_status
ORDER BY claim_status;

SELECT
    'duplicate_claim_ids' AS check_name,
    count() AS duplicate_claim_ids
FROM
(
    SELECT claim_id
    FROM claims.claims_reporting
    GROUP BY claim_id
    HAVING count() > 1
);

SELECT
    'invalid_paid_amounts' AS check_name,
    count() AS invalid_paid_amounts
FROM claims.claims_reporting
WHERE paid_amount < 0;

SELECT
    'invalid_processing_dates' AS check_name,
    count() AS invalid_processing_dates
FROM claims.claims_reporting
WHERE processed_date < submission_date;

SELECT
    total_paid,
    regrouped_paid,
    total_paid = regrouped_paid AS totals_reconcile
FROM
(
    SELECT sum(paid_amount) AS total_paid
    FROM claims.claims_reporting
)
CROSS JOIN
(
    SELECT sum(facility_paid) AS regrouped_paid
    FROM
    (
        SELECT
            facility,
            sum(paid_amount) AS facility_paid
        FROM claims.claims_reporting
        GROUP BY facility
    )
);
Document the reporting grain and metric contract

A correct model can still be misused when its grain or formulas remain implicit. A metric contract gives every dashboard author the same definitions.

  • Return to the top level of the Superset workspace in the Visual Studio Code file tree.
  • Create a new file named README.md.
  • Paste this project context into README.md:
# Healthcare Claims Analytics with ClickHouse and Apache Superset

This project uses generated fictional records to demonstrate how an analytics engineer turns claim submissions into trusted reporting metrics.

## Business problem

The raw table contains 1,100 submission rows for 1,000 claim IDs. One hundred denied claims were corrected and resubmitted as paid claims. Counting raw rows as final claims therefore overstates claim volume and denials.

## Architecture

1. ClickHouse stores the raw claim submissions.
2. A window-ranked view selects the latest submission for each claim ID.
3. SQL quality checks validate the reporting grain and totals.
4. Apache Superset exposes reusable metrics, charts, filters, and dashboard access.

## Reporting grain

`claims.claims_reporting` contains exactly one latest submission for each `claim_id`.

What does this documentation establish?

  • The business-problem section records why raw submission rows cannot represent final claims.
  • The architecture section traces the path from raw data to the dashboard.
  • The reporting-grain section defines one latest submission for each claim ID.
  • Save README.md.
  • Select the Markdown preview control in the editor header.

You should see the business problem, architecture, and reporting grain rendered as separate sections.

Markdown preview looks incomplete?

  • Confirm that README.md is saved at the top level of the Superset workspace.
  • Check that each section heading starts with the same number of hash characters shown in the reference.

Need help checking the structure? Help me diagnose why my README Markdown sections are not rendering correctly.

The metric contract separates reusable business definitions from individual charts. Each formula operates on the trusted one-row-per-claim view.

  • Append this metric contract and expected-results section to README.md:
## Metric contract

| Metric | Superset SQL | Meaning |
| --- | --- | --- |
| Total Claims | `COUNT(*)` | Final claims at the one-row-per-claim grain |
| Submitted Amount | `SUM(submitted_amount)` | Amount requested on final claim versions |
| Paid Amount | `SUM(paid_amount)` | Amount paid on final claim versions |
| Denial Rate | `100.0 * SUM(CASE WHEN claim_status = 'Denied' THEN 1 ELSE 0 END) / COUNT(*)` | Percentage of final claims still denied |
| Collection Rate | `100.0 * SUM(paid_amount) / SUM(submitted_amount)` | Paid amount as a percentage of submitted amount |
| Average Processing Days | `AVG(processing_days)` | Average days from submission to processing |

## Expected quality results

- Raw rows: 1,100
- Unique claim IDs: 1,000
- Reporting rows: 1,000
- Paid claims: 700
- Pending claims: 200
- Denied claims: 100
- Duplicate reporting claim IDs: 0
- Negative paid amounts: 0
- Processing dates before submission dates: 0
- Facility totals reconcile to the overall paid amount: true

Why record the metric formulas?

  • The table gives each metric one shared name.
  • The SQL column gives dashboard authors one reusable formula.
  • The meaning column connects each formula to its business interpretation.
  • The expected-results list provides a baseline for future model changes.
  • Save README.md.
  • Return to the open Markdown preview.

You should see a six-row metric table. You should also see the complete list of expected quality results.

Metric table is not rendering?

  • Confirm that every metric row begins and ends with a vertical bar.
  • Check that the separator row remains directly below the table headings.
  • Save README.md before refreshing the preview.

Still seeing plain table text? Help me fix the Markdown metric table in my README.

The final documentation section records how to start, validate, and remove the local sandbox. These commands make the workflow repeatable.

  • Append these operational instructions to the end of README.md:
## Start

```powershell
git clone https://github.com/apache/superset
cd superset
git checkout tags/6.0.0
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml up -d superset clickhouse
```

Open `http://localhost:8088`.

## Validate

```powershell
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/reporting/02_reporting_view.sql
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/quality_checks.sql
```

## Cleanup

```powershell
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml down -v
```

This local Docker Compose environment is a learning sandbox, not a production deployment.

What do the operational sections provide?

  • The start section records how to reproduce the version-pinned local stack.
  • The validate section records how to rebuild the view and rerun the quality suite.
  • The cleanup section records how to remove the local containers and volumes.
  • The final statement defines the environment as a learning sandbox.
  • Save the completed README.md.
  • Return to the open Markdown preview.

You should see Start, Validate, and Cleanup sections below the expected quality results. The file now documents the complete analytics workflow.

Code fences are not rendering?

  • Confirm that each PowerShell section has one opening code fence.
  • Confirm that each PowerShell section has one closing code fence.
  • Check that the final sandbox statement sits below the Cleanup code fence.

Need help locating the Markdown issue? Help me fix the fenced PowerShell blocks in my README.

✔️ Awesome, I've got everything!

Your README now defines the business problem, reporting grain, metric contract, quality baseline, and operating commands.

ⓧ I'd like to double check the full code

# Healthcare Claims Analytics with ClickHouse and Apache Superset

This project uses generated fictional records to demonstrate how an analytics engineer turns claim submissions into trusted reporting metrics.

## Business problem

The raw table contains 1,100 submission rows for 1,000 claim IDs. One hundred denied claims were corrected and resubmitted as paid claims. Counting raw rows as final claims therefore overstates claim volume and denials.

## Architecture

1. ClickHouse stores the raw claim submissions.
2. A window-ranked view selects the latest submission for each claim ID.
3. SQL quality checks validate the reporting grain and totals.
4. Apache Superset exposes reusable metrics, charts, filters, and dashboard access.

## Reporting grain

`claims.claims_reporting` contains exactly one latest submission for each `claim_id`.

## Metric contract

| Metric | Superset SQL | Meaning |
| --- | --- | --- |
| Total Claims | `COUNT(*)` | Final claims at the one-row-per-claim grain |
| Submitted Amount | `SUM(submitted_amount)` | Amount requested on final claim versions |
| Paid Amount | `SUM(paid_amount)` | Amount paid on final claim versions |
| Denial Rate | `100.0 * SUM(CASE WHEN claim_status = 'Denied' THEN 1 ELSE 0 END) / COUNT(*)` | Percentage of final claims still denied |
| Collection Rate | `100.0 * SUM(paid_amount) / SUM(submitted_amount)` | Paid amount as a percentage of submitted amount |
| Average Processing Days | `AVG(processing_days)` | Average days from submission to processing |

## Expected quality results

- Raw rows: 1,100
- Unique claim IDs: 1,000
- Reporting rows: 1,000
- Paid claims: 700
- Pending claims: 200
- Denied claims: 100
- Duplicate reporting claim IDs: 0
- Negative paid amounts: 0
- Processing dates before submission dates: 0
- Facility totals reconcile to the overall paid amount: true

## Start

```powershell
git clone https://github.com/apache/superset
cd superset
git checkout tags/6.0.0
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml up -d superset clickhouse
```

Open `http://localhost:8088`.

## Validate

```powershell
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/reporting/02_reporting_view.sql
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/quality_checks.sql
```

## Cleanup

```powershell
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml down -v
```

This local Docker Compose environment is a learning sandbox, not a production deployment.

Before the final run, which results would prove that the latest-version model is safe for dashboard reporting?

  • Switch back to the PowerShell terminal from earlier.
  • Run the documented quality suite one final time by running:
docker exec claims_clickhouse clickhouse-client --user claims_user --database claims --queries-file /project-clickhouse/quality_checks.sql

What does this final run verify?

The final run checks the deployed reporting view against every documented expectation. Matching results prove that the SQL model and its metric contract describe the same trusted grain.

You should see reporting_rows = 1000 with no duplicate claim IDs.

You should see Paid = 700, Pending = 200, and Denied = 100.

You should see zero invalid paid amounts. You should also see zero invalid processing dates.

The final reconciliation result should be true.

Strong work. Your reporting layer now answers one-claim questions with validated totals.

Final validation does not match?

  • Compare clickhouse/reporting/02_reporting_view.sql with its full-code reference.
  • Compare clickhouse/quality_checks.sql with its full-code reference.
  • Execute the reporting-view file again before rerunning the quality suite.

Need help isolating the mismatch? Help me compare my final ClickHouse quality results with the expected reporting model.

Your trusted claims model is ready for dashboard authors. Next, you will connect Apache Superset to the validated view and turn the metric contract into reusable KPIs.

Connect Superset and Create Governed Metrics

Your ClickHouse reporting view now returns 1,000 validated claims at the correct data grain. The raw resubmissions no longer distort final status counts.

Correct SQL can still drift when dashboard authors rebuild formulas themselves. Apache Superset's semantic layer stores shared virtual metrics for every chart.

In this step, get ready to:
  • Connect Apache Superset to ClickHouse.
  • Register the one-row-per-claim reporting view as a dataset.
  • Create six governed metrics with a verified Big Number KPI.
Connect Superset to ClickHouse

A database connection gives Superset a reusable route to the validated reporting view. The connection uses the local ClickHouse service that is already running.

  • Switch back to the Superset browser tab from earlier.
  • Click the + menu in the top-right corner.
  • Select Data.
  • Select Connect Database.

Why Does the Host Use a Service Name?

Containers in the same Docker Compose project can reach each other by service name. Superset therefore uses clickhouse as the database host.

Inside the Superset container, localhost points back to Superset. The service name sends the request to the ClickHouse container.

  • Choose ClickHouse Connect as the database type.
  • Enter clickhouse in the Host field.
  • Enter 8123 in the Port field.
  • Enter claims in the Database field.
  • Enter claims_user in the Username field.

Keep the private database password off screenshots during this local connection.

  • Switch back to docker-compose.project.yml from earlier.
  • Copy the private value assigned to CLICKHOUSE_PASSWORD.
  • Return to the Superset connection form.
  • Paste the value into the Password field.
  • Switch SSL off.

Before you test the connection, predict whether Superset reaches the database through the clickhouse service name.

  • Click Test Connection.

You should see a success confirmation showing that Superset can reach ClickHouse with the supplied settings.

  • Click Connect to save the database connection.

The connection is live. Superset can now query your validated claims data without duplicating it.

Connection Test Failing?

  • Confirm that the host is clickhouse and the port is 8123.
  • Check that the password matches the private CLICKHOUSE_PASSWORD value in docker-compose.project.yml.
  • Confirm that the ClickHouse container is running in Docker Desktop.

Still stuck? Help me troubleshoot the local Superset connection to my ClickHouse container.

Register the reporting dataset

A Superset dataset exposes a database table or view to the Explore workflow. You will register the validated reporting view so chart authors start from one row per claim.

  • Select Data from the top menu.
  • Select Datasets.
  • Click + Dataset in the top-right corner.
  • Select the ClickHouse connection you just created in the Database field.
  • Select claims in the Schema field.
  • Select claims_reporting in the Table field.
  • Click Add.

You should see claims_reporting in the datasets list.

What Does the Dataset Add?

The dataset points to claims.claims_reporting and makes its columns available in Explore.

Queries continue to run against the existing 1,000-row view. The dataset becomes the governed entry point for chart authors.

  • Open the dataset editor for claims_reporting using the edit control on its row.
  • Select the Columns section.
  • Find the service_date row.
  • Mark service_date as temporal.
  • Click Save.
  • Select Data from the top menu.
  • Select Datasets.
  • Click the claims_reporting dataset name to launch Explore.

In the left-hand dataset panel, you should see fields such as service_date, facility, payer, and claim_status. You should also see processing_days and outstanding_amount.

Your trusted reporting columns are ready for analysis. Every chart built from this dataset starts with the corrected claim grain.

Create governed metrics and your first KPI

Governed metrics turn the formulas in README.md into reusable dataset definitions. Dashboard authors can select a metric by name instead of rewriting its business logic.

  • Select Data from the top menu.
  • Select Datasets.
  • Open the dataset editor for claims_reporting using the edit control on its row.
  • Select the Metrics section.

How the Metric Contract Works

Each metric pairs a readable name with an aggregate SQL expression. Superset stores these definitions with the dataset so charts reuse the same formulas.

  • Use Total Claims with COUNT(*).
  • Use Submitted Amount with SUM(submitted_amount).
  • Use Paid Amount with SUM(paid_amount).
  • Use Denial Rate with 100.0 * SUM(CASE WHEN claim_status = 'Denied' THEN 1 ELSE 0 END) / COUNT(*).
  • Use Collection Rate with 100.0 * SUM(paid_amount) / SUM(submitted_amount).
  • Use Average Processing Days with AVG(processing_days).
  • Create six metric rows with the names shown in the metric contract.
  • Paste each matching SQL expression into its metric row.

The metrics table should now show six populated definitions.

  • Click Save to store the metric definitions.
  • Select Data from the top menu.
  • Select Datasets.
  • Click the claims_reporting dataset name to return to Explore.
  • Select Big Number as the visualization type.
  • Set Time Range to No filter.
  • Select Total Claims as the metric.

Before you run the chart, predict the Total Claims value produced by the one-row-per-claim dataset.

  • Click Run.

You should see a Big Number result of 1,000.

That is the metric layer doing its job. You have turned a validated data grain into a reusable KPI.

Big Number Not Showing 1,000?

  • Confirm that Time Range is set to No filter.
  • Confirm that Explore is using claims_reporting because raw_claims contains 1,100 rows.
  • Check that Total Claims uses the exact expression COUNT(*).

Still stuck? Help me troubleshoot why my Superset Total Claims Big Number does not show 1,000.

  • Click Save in Explore.
  • Enter Total Claims as the chart name.
  • Choose Add To Dashboard.
  • Enter Healthcare Claims Performance as the new dashboard name.
  • Click Save & Go To Dashboard.

You should see the saved Total Claims chart on the Healthcare Claims Performance dashboard.

Your governed KPI is live on its first dashboard. Next, you will add the remaining claims KPIs and the investigation charts that explain what drives them.

Publish the Claims Performance Dashboard

Your governed dataset in Apache Superset now returns a trusted Total Claims KPI of 1,000. One headline number cannot show which payer, facility, or service period drives performance.

This step expands that result into a published dashboard with investigation charts. Native filters make every scoped view respond to the same question.

In this step, get ready to:
  • Complete the six governed KPI cards.
  • Build the four investigation charts.
  • Publish the interactive dashboard.
Build the KPI and investigation charts

KPI cards expose the headline measures. Investigation charts reveal the operational dimensions behind those measures.

KPI Chart Recipes

  • The Submitted Amount card uses the Big Number chart type with the Submitted Amount metric. Its chart title is Submitted Amount.
  • The Paid Amount card uses the Big Number chart type with the Paid Amount metric. Its chart title is Paid Amount.
  • The Denial Rate card uses the Big Number chart type with the Denial Rate metric. Its chart title is Denial Rate.
  • The Collection Rate card uses the Big Number chart type with the Collection Rate metric. Its chart title is Collection Rate.
  • The Average Processing Days card uses the Big Number chart type with the Average Processing Days metric. Its chart title is Average Processing Days.
  • Return to the existing Healthcare Claims Performance dashboard in your Superset session.
  • Keep the saved Total Claims card as the first KPI.
  • Create the five missing Big Number cards from the KPI recipes above.
  • Click Run after configuring each card.
  • Save each rendered card to the existing Healthcare Claims Performance dashboard.
  • Return to the dashboard after saving the fifth new card.

Good progress. Your dashboard now has six governed KPI cards ready for the executive summary.

Investigation Chart Recipes

  • The service-date time series uses service_date as its time column. It uses Total Claims as its metric.
  • The payer bar chart uses payer as its category. It uses Denial Rate as its metric.
  • The facility bar chart uses facility as its category. It uses Paid Amount as its metric.
  • The denial-reason table uses denial_reason as its grouping column. It uses Total Claims as its metric.
  • The denial-reason table applies claim_status = 'Denied' so blank reasons from other statuses do not dominate the result.
  • Create the service-date time series from claims.claims_reporting using its chart recipe.
  • Create the payer denial-rate bar chart using its chart recipe.
  • Click Run to render both charts.
  • Save both rendered charts to the existing dashboard.
  • Create the facility paid-amount bar chart using its chart recipe.
  • Create the denial-reason table using its chart recipe.
  • Click Run to render both charts.
  • Save both rendered charts to the existing dashboard.

You should now have ten charts assigned to the dashboard. The set includes six KPI cards plus four investigation views.

Missing Data in a Chart?

  • Confirm the chart uses the claims.claims_reporting dataset.
  • Check that the chart uses the governed metric named in its recipe.
  • Remove any accidental chart-level filters that exclude all rows.

Still stuck? Help me troubleshoot an empty Superset chart built from claims.claims_reporting.

Arrange the dashboard and add filters

An executive summary answers the first question quickly. The investigation section gives viewers somewhere to look when a KPI needs explanation.

Native Filter Recipes

  • The service_date filter uses the temporal column. Its scope includes every relevant chart.
  • The payer filter uses the payer column. Its scope includes every relevant chart.
  • The facility filter uses the facility column. Its scope includes every relevant chart.
  • The claim_status filter uses the claim status column. Its scope includes every relevant chart.
  • Switch the Healthcare Claims Performance dashboard into edit mode.
  • Arrange the six KPI cards in an executive summary row.
  • Place the service-date time series with the payer and facility bar charts in the investigation section.
  • Place the denial-reason table beneath the comparison charts.
  • Create the four native filters from the recipes above.
  • Position the native filter controls above the executive summary.
  • Save the dashboard layout.

Before you test the filters, make a quick prediction about which cards and charts will change.

  • Select one payer in the payer filter.
  • Select one facility in the facility filter.
  • Apply both filter selections from the native filter panel.

Every scoped KPI and chart should update. The Total Claims value should stay consistent with the filtered records.

  • Clear the payer selection.
  • Clear the facility selection.
  • Narrow the service_date range.
  • Select Denied in the claim_status filter.

The scoped charts should respond to the date range and denied status. The denial-reason table should continue to show denied claims only.

  • Clear every native filter after the scope test.

The unfiltered Total Claims card should return to 1,000. That confirms the dashboard can move between focused analysis and the complete reporting population.

A Chart Is Ignoring the Filters?

  • Return to the native filter configuration for the filter that did not affect the chart.
  • Confirm the chart is included in that filter's scope.
  • Check that the chart still uses the claims.claims_reporting dataset.

Need another pair of eyes? Help me troubleshoot native filter scope on my Superset dashboard.

Publish and capture the finished dashboard

A dashboard remains private to its draft workflow until you publish it. Publishing turns the finished layout into the view your audience opens.

  • Save the latest dashboard changes.
  • Click Draft next to the dashboard title to publish it.

The dashboard should leave its draft state. Your published view now contains the complete executive summary and investigation section.

  • Capture the full unfiltered dashboard with the Windows screenshot tool.
  • Save the capture as a PNG inside the Apache Superset repository folder using a filename that identifies the unfiltered dashboard.

Before the final filter test, predict how the Total Claims card will respond when one payer and one facility are selected.

  • Select one payer in the published dashboard.
  • Select one facility in the published dashboard.
  • Apply both selections from the native filter panel.

Every scoped KPI and chart should change. Total Claims should match the records allowed by both filter selections.

  • Capture the full filtered dashboard with the Windows screenshot tool.
  • Save the capture as a PNG inside the Apache Superset repository folder using a filename that identifies the filtered dashboard.
  • Return to the Explorer sidebar in Visual Studio Code.

You should see one unfiltered dashboard PNG and one filtered dashboard PNG inside the repository.

Strong finish. Your published dashboard now keeps six KPIs, four investigation views, and four filters aligned to one trusted claims grain.

Secret mission

Restrict the Dashboard to North Clinic

Your published claims dashboard works for administrators. Now enforce a North Clinic boundary so a restricted viewer receives filtered query results across every KPI and chart.

Clean Up Your Resources

Clean Up Your Resources

This stack runs locally, so it creates no cloud or API charges. Docker Desktop licensing may depend on your organization, so confirm your eligibility when choosing whether to keep, pause, or delete the stack.

Resources you used:

  • Local Apache Superset, PostgreSQL, Redis, and ClickHouse containers managed by the merged Docker Compose project.
  • Local Compose networks plus named Docker volumes containing Superset metadata and ClickHouse data.
  • Local superset folder containing the SQL models, quality checks, configuration, documentation, and private local values.

Keep everything running

No action is needed. Choose this option if you want the dashboard and its supporting services ready for more analytics work.

  • Leave the local containers active so the published Healthcare Claims Performance dashboard remains available.
  • Keep the superset folder so your reporting model and metric contract remain available.
  • Keep the credentials in docker/.env-local and docker-compose.project.yml private.
  • Close the private Gamma browser session when you finish testing the North Clinic access rule.

Pause - I'll come back to this later

Shut down the running services to free local memory. Your containers, volumes, dashboard metadata, and project files remain available for your return.

  • Return to the PowerShell terminal at the superset repository root.
  • Stop the local stack by running this command:
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml stop

What does this command do?

The command stops the services in the merged Compose project. It preserves the named volumes containing Superset metadata and ClickHouse data.

  • Wait for PowerShell to return to a prompt.
  • Confirm the terminal reports each project service as stopped.

Your local memory is now free while the full dashboard environment stays ready to resume.

Services Still Running?

Confirm the terminal is at the superset repository root. Make sure Docker Desktop is responsive before trying the command again.

Help me diagnose why my local Compose services did not stop.

Delete - I don't want to use this again

Removing this sandbox is permanent, so use this option only when you no longer need the dashboard. The first command deletes the local containers, networks, Superset metadata, and ClickHouse data.

  • Return to the PowerShell terminal at the superset repository root.
  • Remove the Compose resources and named volumes by running this command:
docker compose -f docker-compose-image-tag.yml -f docker-compose.project.yml down -v

What does this command remove?

The command removes the project containers and Compose networks. The -v flag also removes the named volumes holding Superset metadata and ClickHouse data.

  • Wait for PowerShell to return to a prompt.
  • Confirm the terminal reports the project containers, networks, and volumes as removed.

Compose Resources Not Removed?

Confirm Docker Desktop is running. Check that PowerShell is still at the superset repository root before retrying the command.

Help me diagnose why my Compose resources were not removed.

The containers and stored dashboard data are now gone. One local folder remains on your Windows computer.

  • In Visual Studio Code, record the parent folder that contains superset: path to the folder containing superset.
  • Close Visual Studio Code.
  • Press the Windows key to open search.
  • Type File Explorer into search.
  • Press Enter to launch File Explorer.

File Explorer can now remove the repository without leaving the terminal or editor attached to it.

  • Navigate to path to the folder containing superset.
  • Select the superset folder.
  • Press Shift+Delete to remove the folder permanently.
  • Confirm the permanent deletion when Windows asks.

Cleanup complete. Your local claims analytics containers, stored data, private configuration, and repository files are removed.

Folder Still Present?

Close any terminal whose current location is inside superset. Retry the folder deletion after every project window is closed.

Help me remove a local project folder that Windows says is still in use.

Nice Work!

Nice Work!

You made it! You built a published healthcare claims dashboard in Apache Superset from a validated ClickHouse reporting model.

You've learned how to:

  • Launch a version-pinned local analytics stack through Docker Desktop. Generate 1,100 fictional claim submissions inside ClickHouse.
  • Expose the 100-row resubmission gap between raw submissions and unique claims. Build a one-row-per-claim reporting view with a window function that selects the latest submission. Prove its 1,000-row reporting grain with reproducible quality checks.
  • Create six governed metrics in Apache Superset. Publish the Healthcare Claims Performance dashboard with KPI cards. Use investigation charts to trace performance drivers. Scope the results with native filters.
  • Secret Mission: Create a North Clinic access boundary with Row Level Security for a restricted Gamma user. Verify the filter in a private session with a lower Total Claims result.

Ready to quiz yourself?