Migrating BigQuery to ObsessionDB

Why move?

ObsessionDB (our ClickHouse-based warehouse) gives us properties that BigQuery couldn’t:

• Real-time freshness. Materialized views derive src_events and src_messages directly from raw block-results — no batch lag.
• Predictable cost. You pay for the cluster, not for every byte scanned. Exploration stops being a billing event.
• Re-derivable schema. Raw block payloads are retained, so new derived columns can be built without re-indexing the chain.
• Native JSON. Decode message bodies and event attributes inline with JSON_VALUE / JSONExtractArrayRaw — no pre-materialization step.

Conceptual change

BigQuery had 7 raw tables per chain, each pre-parsing a slice of the block payload. ObsessionDB consolidates this into 3 tables per chain, with the parsing pushed into ClickHouse JSON functions you call at query time. The unified shape works across every Cosmos SDK chain.

Table-by-table mapping

The table below walks you through every BigQuery raw table and its ObsessionDB equivalent. Where a BigQuery table maps to a column inside an ObsessionDB table rather than a top-level table, the How to query column shows you the relevant ClickHouse JSON function.

Query translation

A few side-by-side examples to make the shift concrete. Assume we’re working with Cosmos Hub — BigQuery dataset numia-data.cosmoshub, ObsessionDB database cosmoshub_4.

Count blocks per day

SELECT
    DATE(block_timestamp) AS day,
    COUNT(*) AS blocks
FROM `numia-data.cosmoshub.cosmoshub_blocks`
WHERE block_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY day
ORDER BY day DESC

SELECT
    toDate(block_timestamp) AS day,
    count() AS blocks
FROM cosmoshub_4.raw_block_results
WHERE block_timestamp >= now() - INTERVAL 7 DAY
GROUP BY day
ORDER BY day DESC

Top message types over the last day

SELECT
    message_type,
    COUNT(*) AS msgs
FROM `numia-data.cosmoshub.cosmoshub_tx_messages`
WHERE block_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY message_type
ORDER BY msgs DESC
LIMIT 20

SELECT
    message_type,
    count() AS msgs
FROM cosmoshub_4.src_messages
WHERE block_timestamp >= now() - INTERVAL 1 DAY
GROUP BY message_type
ORDER BY msgs DESC
LIMIT 20

transfer events with sender / recipient

SELECT
    e.block_height,
    MAX(IF(a.key = 'sender',    a.value, NULL)) AS sender,
    MAX(IF(a.key = 'recipient', a.value, NULL)) AS recipient,
    MAX(IF(a.key = 'amount',    a.value, NULL)) AS amount
FROM `numia-data.cosmoshub.cosmoshub_message_events`      AS e
JOIN `numia-data.cosmoshub.cosmoshub_message_event_attributes` AS a
  USING (block_height, tx_id, msg_index, event_index)
WHERE e.event_type = 'transfer'
  AND e.block_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY e.block_height, e.tx_id, e.msg_index, e.event_index

SELECT
    block_height,
    JSONExtractString(arrayFirst(x -> JSON_VALUE(x, '$.key') = 'sender',    JSONExtractArrayRaw(event_attributes)), 'value') AS sender,
    JSONExtractString(arrayFirst(x -> JSON_VALUE(x, '$.key') = 'recipient', JSONExtractArrayRaw(event_attributes)), 'value') AS recipient,
    JSONExtractString(arrayFirst(x -> JSON_VALUE(x, '$.key') = 'amount',    JSONExtractArrayRaw(event_attributes)), 'value') AS amount
FROM cosmoshub_4.src_events
WHERE event_type = 'transfer'
  AND block_section_index = 1
  AND block_timestamp >= now() - INTERVAL 1 DAY

Migration checklist

1. Request ObsessionDB access — see Getting Started.
2. Identify the BigQuery tables your workload reads. The mapping above tells you the ObsessionDB equivalent for each one.
3. Translate your queries. SQL is mostly portable; the main rewrites are JSON access (JSON_VALUE / JSONExtractArrayRaw), date arithmetic (INTERVAL), and the block_section_index filter for event tables.
4. Backfill your dashboards / pipelines against ObsessionDB and compare row counts vs. BigQuery as a sanity check.
5. Cut over reads when you’re satisfied. We keep BigQuery live during the transition — no hard deadline.

Need help with the cutover?