04 Shrinking the table

Five concrete changes to the naive schema, run one at a time, each with the compressed-byte count that proves whether it helped.

Outcome

In about 25 minutes you will take bq.events_naive through five changes, one at a time, and measure the compressed size after each one. By the end you will have a table holding the same events in a fraction of the bytes, and you will know exactly which change earned which part of that reduction, because you watched each one happen.

The starting point

bq.events_naive is where module 03 left you: a working, unremarkable migration, every column Nullable, sorted on a raw microsecond timestamp with no relationship to how anyone queries it. Measured on the full 4,295,584-row export, on a ClickHouse Cloud service:

348,759,309 Bcompressed (332.60 MiB)

5,897,273,025 Buncompressed (5,624.08 MiB)

That gap between compressed and uncompressed already shows ClickHouse's default compression doing real work on data nobody has tuned yet. The five steps below beat that number, deliberately, one lever at a time.

About the numbers in this module

Every step below shows the exact bytes and MiB its measurement query returned, on a ClickHouse Cloud service against the full 4,295,584-row export, so you have a concrete figure to check your own run against, not just a percentage you have to reverse into a number. Cloud numbers vary a little run to run and service to service -- if yours lands close but not identical, that is expected. Run every query yourself. That is the only number that actually describes your service.

Step 1: Flatten and right-size the types

Every column in bq.events_naive is Nullable, because that is what BigQuery's Parquet export produces and nothing has corrected it yet. Several are also the wrong shape for what they hold: event_date is a String instead of a Date, event_timestamp is a raw Int64 of microseconds instead of a DateTime64, and device, geo and traffic_source are nested tuples instead of flat columns.

None of that costs anything unusual to store correctly. Nullable adds a mask ClickHouse has to store per column, a String date takes more bytes than a Date, and reading a nested field on every query is friction with no compression benefit at all. This step removes all of it: flatten every nested field into its own column, drop Nullable in favor of an explicit default ('' for strings, 0 for numbers), and give each column the type that actually fits its values.

CREATE TABLE bq.tuned_types
(
  event_date       Date,
  event_time       DateTime64(6),
  event_name       String,
  user_pseudo_id   String,
  user_id          String,
  device_category  String,
  device_os        String,
  device_browser   String,
  geo_country      String,
  geo_city         String,
  geo_continent    String,
  traffic_medium   String,
  traffic_source   String,
  traffic_name     String,
  platform         String,
  stream_id        UInt32,
  ga_session_id    UInt64,
  page_location    String,
  page_title       String,
  engagement_msec  UInt32,
  item_ids         Array(String),
  item_names       Array(String),
  event_params     Map(String, String)
)
ENGINE = MergeTree
ORDER BY event_time;

INSERT INTO bq.tuned_types
SELECT
  toDate(parseDateTimeBestEffort(n.event_date))                                   AS event_date,
  fromUnixTimestamp64Micro(n.event_timestamp, 'UTC')                              AS event_time,
  ifNull(n.event_name, '')                                                        AS event_name,
  ifNull(n.user_pseudo_id, '')                                                    AS user_pseudo_id,
  ifNull(n.user_id, '')                                                           AS user_id,
  ifNull(n.device.category, '')                                                   AS device_category,
  ifNull(n.device.operating_system, '')                                           AS device_os,
  ifNull(n.device.web_info.browser, '')                                           AS device_browser,
  ifNull(n.geo.country, '')                                                       AS geo_country,
  ifNull(n.geo.city, '')                                                          AS geo_city,
  ifNull(n.geo.continent, '')                                                     AS geo_continent,
  ifNull(n.traffic_source.medium, '')                                             AS traffic_medium,
  ifNull(n.traffic_source.source, '')                                             AS traffic_source,
  ifNull(n.traffic_source.name, '')                                               AS traffic_name,
  ifNull(n.platform, '')                                                          AS platform,
  toUInt32(ifNull(n.stream_id, 0))                                                AS stream_id,
  toUInt64(ifNull(arrayFirst(x -> x.key = 'ga_session_id', n.event_params).value.int_value, 0)) AS ga_session_id,
  ifNull(arrayFirst(x -> x.key = 'page_location', n.event_params).value.string_value, '')       AS page_location,
  ifNull(arrayFirst(x -> x.key = 'page_title',    n.event_params).value.string_value, '')       AS page_title,
  toUInt32(ifNull(arrayFirst(x -> x.key = 'engagement_time_msec', n.event_params).value.int_value, 0)) AS engagement_msec,
  arrayMap(i -> ifNull(i.item_id, ''),   n.items)                                 AS item_ids,
  arrayMap(i -> ifNull(i.item_name, ''), n.items)                                 AS item_names,
  mapFromArrays(
    arrayMap(x -> ifNull(x.key, ''), n.event_params),
    arrayMap(x -> coalesce(
      x.value.string_value,
      toString(x.value.int_value),
      toString(x.value.double_value),
      ''), n.event_params)
  )                                                                              AS event_params
FROM bq.events_naive AS n;

Read the new size the same way you read the starting point's:

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_types';

303,794,384 Bcompressed, Cloud (289.72 MiB)

~13%smaller than the naive table

Flattening and right-sizing types alone, with no LowCardinality, no codecs, and the same plain sort key, already cuts a meaningful slice off the table. Every step from here compounds on top of this one.

Step 2: `LowCardinality` for columns that repeat themselves

LowCardinality stores each distinct value once, in a dictionary, and every row as a small integer index into it. It earns its keep when a column is drawn from a small vocabulary relative to the row count. This dataset's own measured cardinalities make the case directly: funnel.event_names counts 17 distinct event_name values, funnel.countries counts 109 distinct geo_country values, funnel.item_names counts 431 distinct item names, all several orders of magnitude below 4,295,584 rows.

Wrap every column like that. user_pseudo_id and user_id are the opposite case (close to one distinct value per user) and stay plain String.

CREATE TABLE bq.tuned_lowcard
(
  event_date       Date,
  event_time       DateTime64(6),
  event_name       LowCardinality(String),
  user_pseudo_id   String,
  user_id          String,
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64,
  page_location    String,
  page_title       LowCardinality(String),
  engagement_msec  UInt32,
  item_ids         Array(String),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String)
)
ENGINE = MergeTree
ORDER BY event_time;

INSERT INTO bq.tuned_lowcard SELECT * FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_lowcard';

183,994,516 Bcompressed, Cloud (175.47 MiB)

~39%smaller than Step 1

The single biggest drop so far, from wrapping thirteen columns that were already the right type, in the right shape, and holding the right values. Nothing about the data changed.

Step 3: Codecs for columns that change predictably

A column compression codec runs before the general-purpose compressor sees the bytes, reshaping values into something more compressible first. Delta stores the difference from the previous value instead of the value itself, which is a small number when a column moves in one direction. That only helps a column that is monotonic, or close to it, in storage order: event_date and event_time are exactly that, since this table is still sorted by event_time.

CREATE TABLE bq.tuned_codecs
(
  event_date       Date          CODEC(Delta, ZSTD(1)),
  event_time       DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name       LowCardinality(String),
  user_pseudo_id   String        CODEC(ZSTD(1)),
  user_id          String        CODEC(ZSTD(1)),
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64        CODEC(ZSTD(1)),
  page_location    String        CODEC(ZSTD(3)),
  page_title       LowCardinality(String),
  engagement_msec  UInt32        CODEC(ZSTD(1)),
  item_ids         Array(String) CODEC(ZSTD(1)),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String) CODEC(ZSTD(6))
)
ENGINE = MergeTree
ORDER BY event_time;

INSERT INTO bq.tuned_codecs SELECT * FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_codecs';

176,934,547 Bcompressed, Cloud (168.74 MiB)

~4%smaller than Step 2

A small, real cut on top of what LowCardinality already saved -- much smaller than the roughly-48%-on-48% drop a clickhouse local run of this same step showed while this module was being written. On Cloud, LowCardinality already captured most of what these codecs add: Delta on the two monotonic columns, plus a general ZSTD codec on the higher-entropy string columns, is still doing real work, there is just less room left for it to matter once LowCardinality has already run. No other column in this schema is monotonic in sort order, which is why Delta appears nowhere else: applying it to a column with no ordering relationship between adjacent rows does not shrink anything.

Step 4: Choose a sort key, and watch what happens

A MergeTree's sort key decides the physical order rows are written in. So far this table has been sorted by event_time, which is convenient but arbitrary. The rest of this workshop needs rows sorted by (event_date, event_name, user_pseudo_id) instead, because module 05's lookup filters on user_pseudo_id and a sort key can only prune on its leading columns.

CREATE TABLE bq.tuned_sorted
(
  event_date       Date          CODEC(Delta, ZSTD(1)),
  event_time       DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name       LowCardinality(String),
  user_pseudo_id   String        CODEC(ZSTD(1)),
  user_id          String        CODEC(ZSTD(1)),
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64        CODEC(ZSTD(1)),
  page_location    String        CODEC(ZSTD(3)),
  page_title       LowCardinality(String),
  engagement_msec  UInt32        CODEC(ZSTD(1)),
  item_ids         Array(String) CODEC(ZSTD(1)),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String) CODEC(ZSTD(6))
)
ENGINE = MergeTree
ORDER BY (event_date, event_name, user_pseudo_id);

INSERT INTO bq.tuned_sorted SELECT * FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_sorted';

194,809,477 Bcompressed, Cloud (185.78 MiB)

~10%LARGER than Step 3

That is not a typo, and it is not a mistake in the DDL above: this step makes compression measurably worse, by more on Cloud than the roughly-6% a clickhouse local run of this step shows. event_time was compressing well precisely because rows were physically ordered by event_time, which made Delta's row-to-row differences small. Sorting by (event_date, event_name, user_pseudo_id) instead scrambles that fine-grained time order inside every group, so Delta on event_time now sees larger, less predictable jumps.

This trade-off is the point, not a wrong turn

A sort key is not chosen for compression. It is chosen for how a query reads the table, and module 05 is entirely about that trade-off: the roughly-10% this step gives up here buys a lookup that reads hundreds of times fewer rows once you filter on user_pseudo_id. Keep this sort key. The next step recovers the loss and then some.

Step 5: De-duplicate `event_params`

event_params still holds every key from the source, including the four already extracted into their own columns two steps ago: page_location, page_title, ga_session_id and engagement_time_msec. Every event pays to store those four values twice: once typed, in a dedicated column, and once again as text, inside the generic map.

mapFilter removes specific keys from a Map using a predicate over each key/value pair. Filter the four duplicated keys out of the residual map, on top of everything Step 4 already built. This step produces bq.events_tuned, the table the rest of this workshop uses:

CREATE TABLE bq.events_tuned
(
  event_date       Date          CODEC(Delta, ZSTD(1)),
  event_time       DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name       LowCardinality(String),
  user_pseudo_id   String        CODEC(ZSTD(1)),
  user_id          String        CODEC(ZSTD(1)),
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64        CODEC(ZSTD(1)),
  page_location    String        CODEC(ZSTD(3)),
  page_title       LowCardinality(String),
  engagement_msec  UInt32        CODEC(ZSTD(1)),
  item_ids         Array(String) CODEC(ZSTD(1)),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String) CODEC(ZSTD(6))
)
ENGINE = MergeTree
ORDER BY (event_date, event_name, user_pseudo_id);

INSERT INTO bq.events_tuned
SELECT
  event_date, event_time, event_name, user_pseudo_id, user_id,
  device_category, device_os, device_browser,
  geo_country, geo_city, geo_continent,
  traffic_medium, traffic_source, traffic_name,
  platform, stream_id, ga_session_id, page_location, page_title, engagement_msec,
  item_ids, item_names,
  mapFilter(
    (k, v) -> k NOT IN ('page_location', 'page_title', 'ga_session_id', 'engagement_time_msec'),
    event_params
  ) AS event_params
FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'events_tuned';

140,637,179 Bcompressed, Cloud (134.12 MiB)

~28%smaller than Step 4

The single biggest lever in this whole module, bigger than LowCardinality or codecs on their own. event_params is the highest-entropy column in this schema: free-text URLs, search terms, campaign identifiers. Storing that same high-entropy data twice, once inside the map and once in a dedicated column, means paying its compression cost twice. Removing the duplicate is what the byte count actually responds to.

What the residual map costs you

Filtering event_params recovers every extracted key's value for free from its dedicated column, but it does not preserve presence. If a source event never carried a page_title param at all, the dedicated page_title column holds '', the same value it would hold if the event carried page_title and it happened to be empty. Once the key is gone from the map, nothing in this schema can tell those two cases apart. That is a real, deliberate cost of this design, not an oversight.

Confirm the row count matches what you started with, now that you have rebuilt the table twice more since module 03:

SELECT count() FROM bq.events_tuned;

Expect 4,295,584, same as bq.events_naive.

What five steps were worth

The two numbers below are the real bookends, both measured on ClickHouse Cloud, against the same 4,295,584-row export, start to finish.

348,759,309 Bnaive (Cloud, module 03)

140,637,179 Bevents_tuned (Cloud)

2.48xsmaller, measured on Cloud

Two steps did almost all of the work: LowCardinality and de-duplicating event_params. Types mattered too, but codecs alone barely moved the byte count once LowCardinality had already run. The sort key step made compression measurably worse by itself, on purpose, because it was never being chosen for compression: it is what module 05 needs to turn a full-table scan into a lookup that reads a few thousand rows instead of millions.

Done when

bq.events_tuned holds 4,295,584 rows, and you can say in one sentence what each of the five steps changed, including why Step 4 made the table larger rather than smaller. Continue to 05 The sort key when you are ready.