Context

I'm trying to find the best way to represent and aggregate a high-cardinality column in Redshift. The source is event-based and looks something like this:

Where:

• the number of users is very large
• a single user can have very large numbers of events, but is unlikely to have many different event types
• the number of different event_type values is very large, and constantly growing

I want to aggregate this data into a much smaller dataset with a single record (document) per user. These documents will then be exported. The aggregations of interest are things like:

• Number of events
• Most recent event time

But also:

• Number of events for each event_type

It is this latter case that I am finding difficult.

Solutions I've considered

The simple "columnar-DB-friendy" approach to this problem would simply be to have an aggregate column for each event type:

But I don't think this is an appropriate solution here, since the event_type field is dynamic and may have hundreds or thousands of values (and Redshift has a upper limit of 1600 columns). Moreover, there may be multiple types of aggregations on this event_type field (not just count).

A second approach would be to keep the data in its vertical form, where there is not one row per user but rather one row per (user, event_type). However, this really just postpones the issue - at some point the data still needs to be aggregated into a single record per user to achieve the target document structure, and the problem of column explosion still exists.

A much more natural (I think) representation of this data is as a sparse array/document/SUPER:

This also pretty much exactly matches the intended SUPER use case described by the AWS docs:

When you need to store a relatively small set of key-value pairs, you might save space by storing the data in JSON format. Because JSON strings can be stored in a single column, using JSON might be more efficient than storing your data in tabular format. For example, suppose you have a sparse table, where you need to have many columns to fully represent all possible attributes, but most of the column values are NULL for any given row or any given column. By using JSON for storage, you might be able to store the data for a row in key:value pairs in a single JSON string and eliminate the sparsely-populated table columns.

So this is the approach I've been trying to implement. But I haven't quite been able to achieve what I'm hoping to, mostly due to difficulties populating and aggregating the SUPER column. These are described below:

Questions

Q1:

How can I insert into this kind of SUPER column from another SELECT query? All Redshift docs only really discuss SUPER columns in the context of initial data load (e.g. by using json_parse), but never discuss the case where this data is generated from another Redshift query. I understand that this is because the preferred approach is to load SUPER data but convert it to columnar data as soon as possible.

Q2:

How can I re-aggregate this kind of SUPER column, while retaining the SUPER structure? Until now, I've discussed a simplified example which only aggregates by user. In reality, there are other dimensions of aggregation, and some analyses of this table will need to re-aggregate the values shown in the table above. By analogy, the desired output might look something like (aggregating over all users):

I can get close to achieving this re-aggregation with a query like (where the listagg of key-value string pairs is a stand-in for the SUPER type construction that I don't know how to do):

select
  sum(nb_events) nb_events,
  (
      select listagg(s)
      from (
          select
              k::text || ':' || sum(v)::text as s
          from my_aggregated_table inner_query,
              unpivot inner_query.count_by_event_type as v at k
          group by k
      ) a
  ) count_by_event_type
from my_aggregated_table outer_query

But Redshift doesn't support this kind of correlated query:

[0A000] ERROR: This type of correlated subquery pattern is not supported yet

Q3:

Are there any alternative approaches to consider? Normally I'd handle this kind of problem with Spark, which I find much more flexible for these kinds of problems. But if possible it would be great to stick with Redshift, since that's where the source data is.