# Handle SUPER type in Redshift

> Why Redshift SUPER columns arrive in Holistics as text, and how casting them to a primitive type restores aggregations and formatting.

## 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](https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-data-type-mapping.html) 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`:

```json
{"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:

```aml
dimension amount {
  label: "Amount"
  type: "number"
  // highlight-next-line
  definition: @sql {{ #SOURCE.payload }}.amount::decimal(12, 2) ;;
}

dimension paid_at {
  label: "Paid At"
  type: "date"
  // highlight-next-line
  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](/docs/query-models):

```sql
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](https://docs.aws.amazon.com/redshift/latest/dg/super-overview.html#super-best-practices) 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](/docs/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:

```sql
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](/docs/connect/faqs/handle-json-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](https://docs.aws.amazon.com/redshift/latest/dg/query-super.html#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:

```sql
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`](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_super_attribute.html) 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.

## Related resources

- [Query Model](/docs/query-models) and [Query Model Persistence](/docs/persistence)
- [Dataset Dimension](/docs/dimensions-in-datasets)
- [Handle JSON type in MySQL](/docs/connect/faqs/handle-json-in-mysql)
- [Handle RECORD type in BigQuery](/docs/connect/faqs/handle-record-type-in-bigquery)
- [SUPER data type limitations](https://docs.aws.amazon.com/redshift/latest/dg/limitations-super.html) (AWS)