Querying

Replace <catalog> and <namespace> (or <project> and <dataset> on BigQuery) with the names Autumn sent you. Examples on the other v2.6 pages are written as plain SQL against <namespace>.v2_6_<object>; this page shows how each engine spells them.

Addressing tables

ClickHouse
BigQuery

The Glue catalog mounts as a database, and the whole <namespace>.v2_6_<object> is one table name with a literal dot in it. Backtick both parts:

SELECT * FROM `<catalog>`.`<namespace>.v2_6_customers` LIMIT 10;

`<catalog>.<namespace>`.`v2_6_customers` does not resolve, and neither does the single-quoted form.

Tables are ordinary project.dataset.table names:

SELECT * FROM `<project>.<dataset>.v2_6_customers` LIMIT 10;

Types

Every v2.6 column is one of five types. There are no array, map or struct columns.

Type in the docs	ClickHouse	BigQuery
`string`	`Nullable(String)`	`STRING`
`string (JSON)`	`Nullable(String)`	`STRING`
`boolean`	`Nullable(Bool)`	`BOOL`
`long`, `long (epoch ms)`	`Nullable(Int64)`	`INT64`
`decimal`	`Nullable(Decimal(38, 10))`	`BIGNUMERIC`

events.timestamp is the one native timestamp.

Timestamps

Columns typed long (epoch ms) (created_at, started_at, expires_at, current_period_end, as_of and the like) count milliseconds since 1970-01-01 UTC. Convert them before you display or compare with dates:

ClickHouse
BigQuery

SELECT customer_id, fromUnixTimestamp64Milli(created_at) AS created
FROM `<catalog>`.`<namespace>.v2_6_customers`;

-- "now" in epoch ms
SELECT toUnixTimestamp64Milli(now64());

SELECT customer_id, TIMESTAMP_MILLIS(created_at) AS created
FROM `<project>.<dataset>.v2_6_customers`;

-- "now" in epoch ms
SELECT UNIX_MILLIS(CURRENT_TIMESTAMP());

JSON columns

Objects and arrays are stored as JSON text in string columns, in the same shape the API returns. Columns typed string (JSON) in the Schema Reference hold either:

an object, such as v2_6_customers.metadata = '{"region":"eu"}' or v2_6_features.display = '{"singular":"message","plural":"messages"}', or
an array, such as v2_6_features.event_names = '["message_sent","message_edited"]', v2_6_invoices.plan_ids = '["pro","seats"]', v2_6_plan_items.price_tiers or events.deductions.

Each engine below shows the same four operations: read a field of an object, check whether an array contains a value, turn an array into rows, and read fields from an array of objects.

ClickHouse
BigQuery

-- Read a field of an object
SELECT customer_id, JSONExtractString(metadata, 'region') AS region
FROM `<catalog>`.`<namespace>.v2_6_customers`;

-- Does an array contain a value?
SELECT feature_id
FROM `<catalog>`.`<namespace>.v2_6_features`
WHERE has(JSONExtract(ifNull(event_names, '[]'), 'Array(String)'), 'message_sent');

-- One row per array element
SELECT invoice_id, arrayJoin(JSONExtract(ifNull(plan_ids, '[]'), 'Array(String)')) AS plan_id
FROM `<catalog>`.`<namespace>.v2_6_invoices`;

-- Fields from an array of objects: what each event deducted
SELECT
  e.id,
  JSONExtractString(d, 'balance_id') AS balance_id,
  JSONExtractString(d, 'feature_id') AS feature_id,
  JSONExtractFloat(d, 'value')       AS value
FROM `<catalog>`.`<namespace>.events` AS e
ARRAY JOIN JSONExtractArrayRaw(ifNull(e.deductions, '[]')) AS d;

The columns are Nullable(String), and ClickHouse won’t build an array from a nullable value, so wrap them in ifNull(col, '[]') before JSONExtract(..., 'Array(...)') or JSONExtractArrayRaw.

-- Read a field of an object
SELECT customer_id, JSON_VALUE(metadata, '$.region') AS region
FROM `<project>.<dataset>.v2_6_customers`;

-- Does an array contain a value?
SELECT feature_id
FROM `<project>.<dataset>.v2_6_features`
WHERE 'message_sent' IN UNNEST(JSON_VALUE_ARRAY(event_names));

-- One row per array element
SELECT invoice_id, plan_id
FROM `<project>.<dataset>.v2_6_invoices`,
  UNNEST(JSON_VALUE_ARRAY(plan_ids)) AS plan_id;

-- Fields from an array of objects: what each event deducted
SELECT
  e.id,
  JSON_VALUE(d, '$.balance_id')                  AS balance_id,
  JSON_VALUE(d, '$.feature_id')                  AS feature_id,
  CAST(JSON_VALUE(d, '$.value') AS FLOAT64)      AS value
FROM `<project>.<dataset>.events` AS e,
  UNNEST(JSON_QUERY_ARRAY(e.deductions)) AS d;

Price tiers end with "to": "inf" (a string) on the last tier, and a number on the others. Read to as text, then convert the numeric ones.

Nulls in ClickHouse

On Iceberg tables, ClickHouse can raise NOT_FOUND_COLUMN_IN_BLOCK for WHERE col IS NULL. If you hit it, write coalesce(col, '') = '' for strings and coalesce(col, 0) = 0 for numbers instead. BigQuery handles IS NULL normally.

Rebuild a customer

Each part of POST /v1/customers.get comes from one table. Resolve the customer’s internal id once, then read each part with it. Join on org_id and env as well when you query more than one environment.

Part of the customer	Table and filter
Top-level fields	`v2_6_customers`
`subscriptions[]`	`v2_6_subscriptions`, customer-level, not seats, live statuses (details), `ORDER BY position`
`purchases[]`	`v2_6_purchases`, same filter, `ORDER BY position`
`balances{}`	`v2_6_balances` with `scope = 'customer'`
`balances{}.breakdown[]`	`v2_6_breakdowns` on `balance_id`, ordered by `position`
`balances{}.rollovers[]`	`v2_6_rollovers` on `balance_id`
`flags{}`	`v2_6_flags` with `scope = 'customer'`
`licenses[]`	`v2_6_licenses`
`entities[]`	`v2_6_entities`
`invoices[]`	`v2_6_invoices`, newest 10
`referrals[]`	`v2_6_referrals`

An entity (POST /v1/entities.get) works the same way with the entity’s view: scope = 'entity' and its internal_entity_id. The balances part, in each engine:

ClickHouse
BigQuery

SELECT b.feature_id, b.granted, b.remaining, b.usage, b.unlimited, b.next_reset_at
FROM `<catalog>`.`<namespace>.v2_6_balances` AS b
INNER JOIN `<catalog>`.`<namespace>.v2_6_customers` AS c
  ON c.org_id = b.org_id AND c.env = b.env
 AND c.internal_customer_id = b.internal_customer_id
WHERE b.env = 'live' AND c.customer_id = 'cus_123' AND b.scope = 'customer';

SELECT b.feature_id, b.granted, b.remaining, b.usage, b.unlimited, b.next_reset_at
FROM `<project>.<dataset>.v2_6_balances` AS b
JOIN `<project>.<dataset>.v2_6_customers` AS c
  ON c.org_id = b.org_id AND c.env = b.env
 AND c.internal_customer_id = b.internal_customer_id
WHERE b.env = 'live' AND c.customer_id = 'cus_123' AND b.scope = 'customer';

Trials a customer has used

trials_used[] has no table of its own. A trial was used when a subscription had a trial and did not stay pending. The API also counts trials used by other customers with the same fingerprint:

SELECT s.plan_id, c.customer_id, c.fingerprint
FROM <namespace>.v2_6_subscriptions s
JOIN <namespace>.v2_6_customers c
  ON c.org_id = s.org_id AND c.env = s.env AND c.internal_customer_id = s.internal_customer_id
WHERE s.env = 'live'
  AND (c.internal_customer_id = '<internal_customer_id>'
       OR c.fingerprint = (SELECT fingerprint FROM <namespace>.v2_6_customers
                           WHERE env = 'live' AND internal_customer_id = '<internal_customer_id>'))
  AND s.trial_ends_at IS NOT NULL
  AND s.status <> 'pending'
  AND c.customer_id IS NOT NULL;

What the lakehouse doesn’t have

Three parts of the customer are fetched from Stripe or computed when you call the API, so they are in no lakehouse version: rewards.discounts[], invoice_previews[], and the payment method and billing details.

Examples

Active subscriptions with their plan

SELECT s.customer_id, s.plan_id, p.name, p.price_amount, p.price_interval, s.current_period_end
FROM <namespace>.v2_6_subscriptions s
JOIN <namespace>.v2_6_plans p
  ON p.org_id = s.org_id AND p.env = s.env AND p.internal_plan_id = s.internal_plan_id
WHERE s.env = 'live' AND s.status IN ('active', 'past_due');

Customers close to running out

SELECT customer_id, feature_id, granted, remaining,
       remaining / nullif(granted, 0) AS share_left
FROM <namespace>.v2_6_balances
WHERE env = 'live' AND scope = 'customer' AND NOT unlimited AND granted > 0
ORDER BY share_left
LIMIT 50;

Usage per day, by plan

events.deductions records which plan each unit of usage came from:

ClickHouse
BigQuery

SELECT
  toDate(e.timestamp)                         AS day,
  JSONExtractString(d, 'plan_id')             AS plan_id,
  sum(JSONExtractFloat(d, 'value'))           AS usage
FROM `<catalog>`.`<namespace>.events` AS e
ARRAY JOIN JSONExtractArrayRaw(ifNull(e.deductions, '[]')) AS d
WHERE e.env = 'live' AND JSONExtractString(d, 'feature_id') = 'messages'
GROUP BY day, plan_id
ORDER BY day;

SELECT
  DATE(e.timestamp)                                AS day,
  JSON_VALUE(d, '$.plan_id')                       AS plan_id,
  SUM(CAST(JSON_VALUE(d, '$.value') AS FLOAT64))   AS usage
FROM `<project>.<dataset>.events` AS e,
  UNNEST(JSON_QUERY_ARRAY(e.deductions)) AS d
WHERE e.env = 'live' AND JSON_VALUE(d, '$.feature_id') = 'messages'
GROUP BY day, plan_id
ORDER BY day;

Cross-database joins

Join lakehouse tables against your own data in the same engine by qualifying both sides. Join on your own customer id (customer_id), which is the id you share with Autumn, and filter early by env and time range on large tables such as events.

-- ClickHouse
SELECT c.customer_id, c.email, u.signup_source
FROM `<catalog>`.`<namespace>.v2_6_customers` AS c
INNER JOIN my_db.users AS u ON u.autumn_customer_id = c.customer_id
WHERE c.env = 'live';

Important notes

Join on internal ids. Inside the lakehouse, join on internal_* ids, customer_plan_id and balance_id, never on customer_id, plan_id or entity_id. Your own ids can be renamed, can be null (customer_id), are shared by every version of a plan (plan_id), or are unique only within one customer (entity_id).
Filter env. live and sandbox rows sit in the same tables.
Pick a balance view. See Working with balances.

Lakehouse

Guides

Addressing tables

Types

Timestamps

JSON columns

Nulls in ClickHouse

Rebuild a customer

Trials a customer has used

What the lakehouse doesn’t have

Examples

Active subscriptions with their plan

Customers close to running out

Usage per day, by plan

Cross-database joins

Important notes

​Addressing tables

​Types

​Timestamps

​JSON columns

​Nulls in ClickHouse

​Rebuild a customer

​Trials a customer has used

​What the lakehouse doesn’t have

​Examples

​Active subscriptions with their plan

​Customers close to running out

​Usage per day, by plan

​Cross-database joins

​Important notes

Addressing tables

Types

Timestamps

JSON columns

Nulls in ClickHouse

Rebuild a customer

Trials a customer has used

What the lakehouse doesn’t have

Examples

Active subscriptions with their plan

Customers close to running out

Usage per day, by plan

Cross-database joins

Important notes