Handle SUPER type in Redshift
Question
I have a Redshift table with a SUPER column. Inside Redshift everything works: I can multiply payload.amount by 2 and Redshift figures out on its own that the value is a number. But when the same field lands in Holistics, it comes back as text. I can't SUM or AVG it, and the number and date formatting options aren't there. What's going on, and how do I fix it?
Answer
Why the field arrives as text
This isn't something Holistics does to the column. It happens in the database driver, before Holistics sees any data. AWS's own data type conversion table maps SUPER to SQL_LONGVARCHAR (a Java String), so every client that reads a SUPER column over JDBC or ODBC receives text.
The fix: cast to a primitive type
Give the column a concrete type in SQL, before the result set leaves Redshift. Holistics then maps it to a number, date, or text field, and aggregations, measures, and formatting all behave normally.
The examples below use an orders table with a SUPER column named payload:
{"amount": 129.50, "quantity": 3, "paid_at": "2026-04-18", "channel": "web"}
Option A: model dimension. Good when you need one or two fields and want the cast to live next to the rest of the model:
dimension amount {
label: "Amount"
type: "number"
definition: @sql {{ #SOURCE.payload }}.amount::decimal(12, 2) ;;
}
dimension paid_at {
label: "Paid At"
type: "date"
definition: @sql {{ #SOURCE.payload }}.paid_at::date ;;
}
Make sure type matches what you cast to. If the SQL returns a number but the dimension is still declared as text, Holistics keeps treating the field as text and you're back where you started.
Option B: query model. Good when you need several fields out of the same SUPER column, or when you also have arrays to flatten. Do all the casting in one query model:
SELECT
o.id AS order_id,
// highlight-start
o.payload.channel::varchar AS channel,
o.payload.quantity::int AS quantity,
o.payload.amount::decimal(12, 2) AS amount,
o.payload.paid_at::date AS paid_at
// highlight-end
FROM {{ #orders }} o
Option C: shred it in Redshift. Create a view or materialized view in Redshift that exposes the cast columns, then model that object in Holistics as a normal table. This is AWS's own recommendation for BI tools that expect a conventional schema.
Which option should you pick?
The cast expression itself is cheap. The cost is in reading the SUPER column: Redshift stores each object as a single binary value, so a query that needs one attribute still reads the whole object, and it loses the columnar advantage it would have on a plain typed column.
Where you put the cast decides how often you pay that:
| Approach | When the cast runs | Good for |
|---|---|---|
| Model dimension | Every query (inlined into the SQL Holistics sends) | A couple of fields, moderate data volumes |
| Query model, no persistence | Every query (compiled into a CTE) | Many fields or arrays, still exploratory |
| Query model with persistence, or a Redshift materialized view | On a schedule | Fields your team queries often, or large tables |
So a query model is a good way to keep the logic in one place, but on its own it doesn't make anything faster: without persistence it compiles into a CTE and Redshift re-reads and re-parses the SUPER column on every query.
Arrays: flatten instead of cast
If the SUPER value is an array, there's no single scalar to cast. Unnest it in a query model so each element becomes its own row, using PartiQL's array iteration in the FROM clause:
SELECT
o.id AS order_id,
// highlight-start
i.sku::varchar AS sku,
i.qty::int AS quantity
FROM {{ #orders }} o, o.payload.items i
// highlight-end
The overall pattern (extract objects inline, flatten arrays into their own table, then join it back) is the same one covered in Handle JSON type in MySQL, if you want a fuller worked example with the target schema laid out.
Things to watch out for
Invalid casts go silently null. Redshift uses lax semantics for SUPER, so casting a string like 'unknown' to a number returns null instead of raising an error. If one attribute holds different shapes across rows, check the type first so you can tell real nulls from failed casts:
CASE WHEN JSON_TYPEOF(o.payload.amount) = 'number'
THEN o.payload.amount::decimal(12, 2)
END AS amount
Casting an object or array to varchar returns null. If you want to keep the raw JSON as a text field, use JSON_SERIALIZE(o.payload) instead of o.payload::varchar.
Mixed-case attribute names need a session setting. A path like payload.paidAt won't resolve unless enable_case_sensitive_super_attribute is turned on. AWS recommends enabling it whenever you work with SUPER data.
The cast only goes one way. You can cast a date or timestamp out of SUPER, but not into it. '2026-04-18'::date::super raises an error.