Source

Query the historical Twitter archive with SQL

A historical archive of public Twitter/X posts — about 43 billion unique posts from 235 million authors (measured 2026-08-26), with engagement counts, reply and quote links, and author profiles — queryable with read-only SQL by text, author, and date.

Surface

  • Recordsposts
  • Canonicaltwitter.tweets
  • Freshness—
  • Extentbucket_date 2006-01-01 to 2026-08-27
  • Cadencehistorical archive; the extent published on the schema entry is the only coverage statement
  • MethodHistorical archive of public posts and author profiles; source-native records with observation time.
  • StatsLast stats snapshot: 2026-08-27T22:31:10Z

Query surfaces

  • twitter.tweets
  • twitter.token_search
  • twitter.author_timeline
  • twitter.users
  • twitter.vector_search
  • embeddings.tweets

Historical archive of public posts and author profiles; source-native records with observation time.

Best for

one author’s whole public posting history through twitter.author_timeline
token search across the archive through twitter.token_search, or column-level token predicates on twitter.tweets
author discovery by bio — twitter.users indexes the lowercase bio text

Fields

  • tweet_id
  • author_id
  • author_handle
  • author_display_name
  • author_followers
  • is_blue_verified
  • user_verified
  • original_timestamp
  • bucket_date
  • text
  • lang
  • is_quote_status
  • quoted_tweet_id
  • in_reply_to_tweet_id
  • quote_count
  • reply_count
  • retweet_count
  • like_count
  • view_count
  • bookmark_count
  • requested_user_id
  • observed_at
  • source_file
  • text_is_complete
  • article_id
  • article_title
  • article_text
  • article_preview_text
  • article_content_state
  • article_published_at
  • article_modified_at
  • loaded_at
  • replacement_version
  • search_text_lc

Read live from the schema registry for twitter.tweets — 34 columns. Every relation's full contract is served by /v1/scry/schema.

Indexed predicates

  • PruneshasToken(search_text_lc, '<lowercase-token>')
  • PruneshasAllTokens(search_text_lc, ['<t1>','<t2>'])
  • PruneshasAnyTokens(search_text_lc, ['<t1>','<t2>'])

Queries

An author’s timeline, newest first

The parameterized per-author path; rows are observations, so collapse on tweet_id.

SELECT tweet_id, original_timestamp, like_count, text
FROM (
  SELECT * FROM twitter.author_timeline(handle = 'karpathy', limit = 200)
  ORDER BY loaded_at DESC
  LIMIT 1 BY tweet_id
)
ORDER BY original_timestamp DESC
LIMIT 20;

Recent posts matching two tokens

Scope the date, match the indexed lowercase tokens, drop retweets, collapse revisions.

SELECT tweet_id, author_handle, original_timestamp, like_count, text
FROM (
  SELECT * FROM twitter.tweets
  WHERE bucket_date >= today() - 90
    AND hasAllTokens(search_text_lc, ['clickhouse', 'benchmark'])
    AND NOT startsWith(text, 'RT @')
  ORDER BY (text_is_complete, observed_at) DESC
  LIMIT 1 BY tweet_id
)
ORDER BY original_timestamp DESC
LIMIT 20;

Find authors by what their bio says

Profiles converge to one latest row per author; aggregate to be exact.

SELECT author_id, argMax(handle, version) AS latest_handle, max(followers) AS followers, argMax(bio, version) AS latest_bio
FROM twitter.users
WHERE hasAllTokens(bio_lc, ['rust', 'compiler'])
GROUP BY author_id
ORDER BY followers DESC
LIMIT 20;

Gaps

  • HolesNone declared on the live schema entry for twitter.tweets (declared holes are curation, not a measurement of completeness).
  • Notetwitter.tweets holds one row per archived revision, not per post: collapse on tweet_id inside a subquery (ORDER BY (text_is_complete, observed_at) DESC LIMIT 1 BY tweet_id) before counting or ranking
  • Notescope bucket_date before a broad token predicate; the archive is tens of billions of rows
  • Notetwitter.token_search(term = …) and twitter.author_timeline(handle = …, limit = …) are parameterized — a bare FROM fails; the term does the case-insensitive token match itself
  • Noteengagement counts are the newest archived revision, not a live read; retweets start with RT @ and are usually excluded