Creating Calculated Columns in Apache Superset: Splitting Customer Names Using SQL Expressions

  

In this post, we explore how to create calculated columns in Apache Superset using the Sample orders dataset. Calculated columns help transform existing row-level data into new meaningful fields, unlike metrics which operate on aggregated data.

 

We focus on a practical use case—splitting a full customer name into first name and last name using the split_part SQL function. This demonstrates how Superset allows data engineers and analysts to enhance datasets directly within the BI layer without modifying the source data.

 

You’ll learn:

 

·      The difference between calculated columns and custom metrics

·      How to navigate to dataset editing in Apache Superset

·      Creating calculated columns using SQL expressions

·      Using split_part to extract structured text fields

·      Applying transformations like first name and last name extraction

·      Reusing calculated columns across charts and dashboards

 

By the end, you’ll understand how calculated columns improve data flexibility and enable more granular analysis in Superset dashboards.

 

1. Calculated columns

Calculated columns in tools like Apache Superset exist mainly to solve a very practical problem: your raw data is rarely in the shape you actually need for analysis or visualization. At the core, a calculated column is a row-level transformation done using SQL expressions inside the BI tool, without changing the underlying table in the database. That makes it powerful, but also something to use carefully.

 

1.1 Why calculated columns are needed?

In real-world datasets, especially something like an orders table, data is usually stored in a “normalized or raw” format. For example:

 

·      Full names stored as one field (Customer Name)

·      Dates stored in timestamps

·      Codes or IDs that need parsing

·      Text fields that contain multiple pieces of information

 

But for analysis, you often need:

·      First name and last name separately

·      Month, year, or day extracted from timestamps

·      Categorized labels derived from raw values

·      Cleaned or standardized text fields

 

This is exactly where calculated columns help.

 

1.2 Advantages of Calculated Columns

Data transformation at query time: Instead of modifying the database or creating ETL pipelines, you can transform data directly in Superset using SQL expressions.

 

Faster analysis without backend changes: Analysts can create new fields instantly (like splitting names, formatting strings, or deriving flags) without waiting for engineering changes.

 

Reusability across charts: Once created, calculated columns behave like normal columns and can be reused across multiple visualizations and dashboards.

 

Simplifies complex data preparation: Common transformations like:

 

·      split_part() for names

·      date_trunc() for time grouping

·      case when for categorization

can all be handled inside Superset.

 

Keeps source data intact: Since transformations happen in the BI layer, the original dataset remains unchanged, reducing risk and dependency on upstream systems.

 

2. Calculated columns Vs custom metrics

Calculated Columns create new columns per row using existing columns, whereas custom metrics are aggregations computed over multiple rows.

 

For example, let’s take following data.

id	customer_name	amount	quantity	order_date
1	John Doe	500	2	2026-01-01
2	Jane Smith	300	1	2026-01-02

 

Suppose we want to split the customer_name into first_name and last_name, we can use split_part transformation to achieve this.

SELECT 
    id,
    customer_name,
    split_part(customer_name, ' ', 1) AS first_name,
    split_part(customer_name, ' ', 2) AS last_name
FROM orders;

 

id	customer_name	first_name	last_name
1	John Doe	John	Doe
2	Jane Smith	Jane	Smith

 

Custom metrics are the aggregations computed over multiple rows.

 

Example 1: total revenue

SUM(amount), it returns 800.

 

Example 2: Total orders

COUNT(id), it returns 2

 

Example 3: Average order value

SUM(amount) / COUNT(id) returns 400

 

Following table summarizes the difference between calculated columns and custom metrics.

 

Feature	Calculated Column	Metric
Level	Row level	Aggregate level
Output	Adds a column per row	Produces summay value
Changes row count	No	Yes (reduces to aggregates_
Example	amount * quantity	SUM(amount)
Purpose	Data Shaping	Data Analysis

 

3. Create Calculated Columns

Let’s use following csv file to demo Calculated columns.

 

orders.csv  

 

id,customer_name,amount,quantity,order_date
1,John Doe,500,2,2026-01-01
2,Jane Smith,300,1,2026-01-02
3,Alice Johnson,200,3,2026-01-03
4,Bob Brown,150,4,2026-01-04
5,Charlie Green,800,1,2026-01-05

   

Follow below step-by-step procedure to create dataset, and add calculated columns.

 

Step 1: Create a dataset from orders.csv file

 

Data -> Upload CSV to database

 

 

Provide following details

·      Upload orders.csv file

·      Select database and schema where you want to keep this data

·      Give table name as my_orders

 

Expand Columns section,

·      Select all the columns to read

·      Supply following json to ‘Column data types’ section: {"id": "int64", "customer_name": "string", "amount" : "float64", "quantity" : "int32", "order_date" : "datetime64[ns]"}

 

 

Click on Upload button to create the dataset.

 

Navigate to datasets listing page, you can see my_orders dataset is created.

 

Step 2: Create Calculated Columns

Click on Edit action against my_orders table.

 

It opens 'Edit Dataset my_orders' popup.

 

Click on Calculated columns tab.

 

   

Click on Add item button.

 

·      Give the new column name as 'first_name',

·      Add SQL Expression as SPLIT_PART(customer_name, ' ', 1), this function splits the customer_name field using space as the delimeter and returns the first part.

·      Select the Data type as STRING

 

Click on Save button.

 

Click on my_orders dataset from the listing page. You can able to see the column first_name is added there. fx prefix before the column specifies, it is a calculated column.

 

Now drag and drop id, first_name columns you can see the table like below.

 

You can even edit the dataset and update calculated columns from the same page.

 

Click on three vertical dots against the dataset -> Edit dataset.

 

Navigate to ‘Calculated columns’ tab and experiment.

 

 

 

Previous                                                    Next                                                    Home