bsky firehose anonymized dec 2025 bluesky posts

Overview

Source: /home/coolhand/datasets/bsky-firehose-anonymized-dec-2025/bluesky_posts.csv

Saturn profiled 101,040 rows across 19 columns. The stats below are deterministic and machine-readable; the prose is a language-model interpretation of those stats (opt-in, added after the fact, never sees raw rows).

text text free_text

Short user-generated posts (likely social media, given bsky.app URLs and hashtag-heavy entries), averaging 14 words and a median of 68 characters. The corpus is predominantly English (3309) but spans 30 detected languages with notable Japanese (656) and Korean (125) presence, and 18.3% of rows contain emoji while 16.9% are flagged all-caps. Watch the 5,105 duplicates (5.05%) and the top entries dominated by emoji spam (224 sheep emojis) and repeated promotional Thai text.

Treatment: Deduplicate, normalize casing/emoji, and apply a multilingual tokenizer or embedding model before downstream NLP.

author_did_hash text foreign_key

This column holds 16-character hexadecimal hashes of author DIDs, with every value exactly 16 chars long and a single token (one_word_rate 1.0). Across 101040 rows there are only 43998 unique authors and a 56.5% duplicate rate, with the top author hash appearing 1016 times — so a small number of authors generate a disproportionate share of records.

Treatment: Treat as an author identifier; group/join on it rather than feeding the raw hash to a model.

uri_hash text identifier

This column holds 16-character single-token strings that look like truncated hex digests of URIs, with 101,039 unique values across 101,040 rows and zero nulls. It is effectively a primary key — duplicate_rate is 0.0 and every entry is exactly 16 characters long. No textual signal is present beyond the hash itself.

Treatment: Use as a row key or join key; drop before modelling.

reply_parent_hash text foreign_key

This column is a 16-character hexadecimal hash pointing at a parent message, populated only when a row is a reply — 57.67% of the 101,040 values are null. Every non-null entry is exactly 16 chars and one token, with 34,738 unique hashes and an 18.78% duplicate rate (8,032 duplicates), indicating popular parents that attract many replies (top hash 04a1db17fbc9ff3a appears 121 times). The flesch_mean of 71.73 is meaningless here since the strings are opaque IDs.

Treatment: Treat as a self-referential foreign key to the message hash; use for thread reconstruction and leave nulls as 'not a reply'.

reply_root_hash text foreign_key

This column holds 16-character hexadecimal hashes identifying the root of a reply thread, with every value exactly 16 chars and a single token. 57.67% of rows are null (consistent with non-reply posts being root-less), and among the 42.33% populated rows there are 21,277 unique hashes with a 50.25% duplicate rate, indicating many replies share common thread roots.

Treatment: left-join on this id to retrieve the root post; do not feature-engineer the hash itself.

sentiment categorical label

This is a three-class sentiment label with values neutral, positive, and negative across 101040 rows and no nulls. The distribution is uneven: neutral dominates at 48.5% (top_rate 0.4847684085510689), positive accounts for 34622, and negative trails at 17437 — roughly half the positive count. Entropy ratio of 0.93 still indicates a fairly balanced spread despite the negative class being underrepresented.

Treatment: Use as classification target; consider class weighting or resampling to offset the underrepresented negative class.

sentiment_score numeric feature

Continuous sentiment polarity bounded in [-0.998, 1.0], consistent with a tool like VADER/TextBlob compound score. Distribution is roughly symmetric (skew 0.019, kurtosis 0.018) but dominated by a neutral spike: 47.8% of rows are exactly 0 and the median is 0, pulling Q1 to 0 as well. Despite the symmetry, 5,763 rows (5.7%) flag as outliers, suggesting heavy tails of strongly polarised text alongside the neutral mass.

Treatment: Treat the zero-mass separately (e.g., add an is_neutral flag) before using the score as a numeric feature.

created_at text timestamp

This is a creation timestamp stored as ISO-8601 strings rather than a native datetime, with lengths from 20 to 35 characters and a single token per value. The format is inconsistent: top values mix offset suffixes like `+00:00` with `Z` and microsecond `.000000Z` variants, which will break naive lexical comparisons. Cardinality is high (96576 unique of 101040) but 4464 duplicates (4.4%) suggest concurrent events sharing timestamps.

Treatment: Parse to a normalized UTC datetime before any time-based join or sort.

timestamp text timestamp

This column holds ISO-8601 timestamps with microsecond precision, stored as text rather than a native datetime type. All 101040 values are unique with zero nulls and a fixed length of 26 characters, and the sampled values cluster on 2025-12-23 and 2025-12-24. The text-profile alerts (near_unique, one_word, allcaps) are artefacts of the string representation, not genuine quality issues.

Treatment: parse to datetime and use for time-based ordering or feature extraction

language categorical feature

Language code of the record, dominated by English ('en' at 60.8% of 101,040 rows) with Japanese ('ja') a distant second at 12,607. Notably, 11,481 rows are literally 'unknown' and the codes mix bare ISO ('en') with locale variants ('en-US', 3,617), so the 90 distinct values overstate true language diversity. No nulls, but entropy ratio of 0.34 confirms heavy concentration at the top.

Treatment: Normalize locale variants to base codes and treat 'unknown' as missing before one-hot or target encoding.

char_count numeric feature

This is almost certainly a character-count feature for some text field, with all 101040 rows populated and only 341 distinct integer values between 1 and 525. The distribution is right-skewed (skew 1.02) with median 68 well below the mean of 97.6 and an IQR of 30-143, indicating most texts are short but a tail stretches out. Outliers are minimal (0.29%) and there are no zero-length entries.

Treatment: Consider a log or sqrt transform before modelling to tame the right skew.

word_count numeric feature

This is a numeric feature counting words per record, ranging from 0 to 83 with a median of 10 and mean of 14.67. The distribution is right-skewed (skew 1.21) with an IQR of 19 and about 2.85% of values flagged as outliers, suggesting a long tail of unusually long entries. Only 79 unique values across 101,040 rows and a near-zero zero_rate (0.06%) indicate counts cluster in a narrow integer range.

Treatment: Consider a log or sqrt transform before modelling to tame the right skew.

has_images numeric feature

Binary flag indicating whether a record has images, encoded as 0/1 with only 2 unique values across 101040 rows and no nulls. The positive class is rare at mean 0.136 (zero_rate 0.864), which is why the profiler flags high skew (2.12) and treats the 13768 ones as 'outliers'.

Treatment: Treat as a boolean feature; ignore the outlier flag since it just reflects class imbalance.

has_video numeric feature

This is a binary flag indicating whether a record has a video, stored numerically with only 2 unique values (0 and 1) and no nulls across 101040 rows. The positive class is rare: 98.67% are zero and only 1.33% are ones, producing the flagged high skew (8.50) and extreme kurtosis (70.19).

Treatment: Cast to boolean and keep as-is, but expect class imbalance when using it as a predictor or target.

has_link numeric feature

This is a binary 0/1 flag indicating whether a record contains a link, with no nulls across 101,040 rows. About 17.95% are 1s and 82.05% are 0s, which is why the IQR is 0 and the minority class gets labelled as 18,140 'outliers' — that's a quirk of applying numeric outlier logic to a Bernoulli variable, not a data issue.

Treatment: Cast to boolean and use directly as a binary feature; ignore the outlier flag.

embed_type categorical feature

This column tags the embed type attached to Bluesky posts, with five AT Protocol values like app.bsky.embed.external and app.bsky.embed.images. Note the 61.15% null rate — most posts carry no embed at all — and among posts that do, external links dominate at 46.22%, with video and recordWithMedia being rare tails. Entropy ratio of 0.74 shows reasonable spread across the five categories when present.

Treatment: One-hot encode with an explicit "none" category for nulls.

hashtags text feature

This column stores per-row hashtag lists serialised as JSON arrays, with `[]` (empty list) appearing 87,318 times — the dominant value in a 101,040-row column. Duplicates are extreme (duplicate_rate 0.90, n_unique 10,103) and 90% of values are effectively one token, consistent with most rows having zero or one hashtag (word_median 1, len_median 2). The non-empty examples mix scripts (Thai, Japanese, German, English) including bot-like patterns such as `#NowPlaying` and repeated `#dpdi`, so any text treatment must be multilingual.

Treatment: Parse the JSON list, derive features like hashtag_count and presence flags, and treat empty lists as a distinct category.

mentions text foreign_key

This column stores serialized JSON arrays of mentioned entity IDs (16-hex tokens), but the overwhelming majority are empty: '[]' appears 98,659 of 101,040 times and the duplicate rate is 0.981. When non-empty, arrays almost always hold a single ID (word_mean 1.01, len_p95 2.0), though len_max reaches 420 indicating occasional multi-mention rows. Vocabulary is small (660) across 1,921 unique values, so signal is sparse and ID-like rather than textual.

Treatment: Parse the JSON array and explode to a mentions bridge table keyed by these hex IDs; treat empty as no-mention rather than null.

links text metadata

This column holds JSON-encoded arrays of URLs, but 96,211 of 101,040 rows are the empty array '[]', driving a 96.3% duplicate rate and a median length of 2 characters. Only 3,771 unique values exist across 101,040 rows, and just 4.8% of rows actually contain a URL. The non-empty entries point to streaming radio and news endpoints (BBC, Radio France, shoutcast streams).

Treatment: Parse the JSON array and convert to a has_link boolean or explode for per-URL analysis; the column is too sparse to use as-is.