bsky firehose dec 2025

Short user-generated posts (likely social/Bluesky given the bsky.app top value and hashtag/emoji patterns), with median 10 words and a 525-character cap suggesting a platform limit. Heavily English-skewed (3309 of 101040) but genuinely multilingual with sizeable Japanese (656) and Korean (125) tails, plus 18.3% emoji rate, 16.9% all-caps lines, and 19% one-word entries. Note 5105 duplicates (5.05%) including spam-like Thai promo and repeated sheep-emoji posts among the top values. Treatment: Deduplicate, language-detect and route per language, then tokenize/embed for modelling. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

95,935

len_min

1

len_max

525

len_mean

97.63

len_median

68

len_p95

290

word_mean

14.23

word_median

10

n_empty

0

n_duplicates

5,105

duplicate_rate

0.05052

vocab_size

77,183

readability_flesch_mean

64.09

emoji_rate

0.1832

url_rate

0.07586

one_word_rate

0.1899

allcaps_rate

0.1691

boilerplate_rate

0.001049

Fixed 16-character single-token hex strings, almost certainly hashed author DIDs acting as a pseudonymous user identifier. Across 101,040 rows there are only 43,998 unique values and a 56.5% duplicate rate, with the top author appearing 1,016 times — so this is a foreign-key-style author handle, not a per-row id. No nulls or empties, and length is constant at 16. Treatment: Treat as a categorical author key; left-join on this to author-level features rather than feeding the raw hash into a model. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

43,998

len_min

16

len_max

16

len_mean

16

len_median

16

len_p95

16

word_mean

1

word_median

1

n_empty

0

n_duplicates

57,042

duplicate_rate

0.5645

vocab_size

13,938

readability_flesch_mean

68.35

emoji_rate

0

url_rate

0

one_word_rate

1

allcaps_rate

0.0003464

boilerplate_rate

0

Fixed 16-character single-token strings with 101,039 unique values across 101,040 rows and zero nulls — almost certainly hex digests (16 hex chars = 64-bit hash) of URIs, used as row identifiers. The column is effectively a primary key: one_word_rate is 1.0, length is exactly 16 at min/median/max, and duplicate_rate is 0.0. No analytic signal lives here beyond identity. Treatment: drop from modelling; retain only as a join key or deduplication handle. high · anthropic:claude-opus-4-7

n

101,040

nulls

1 (0.0%)

unique

101,039

len_min

16

len_max

16

len_mean

16

len_median

16

len_p95

16

word_mean

1

word_median

1

n_empty

0

n_duplicates

0

duplicate_rate

0

vocab_size

20,000

readability_flesch_mean

69.61

emoji_rate

0

url_rate

0

one_word_rate

1

allcaps_rate

0.0005245

boilerplate_rate

0

Every non-null value is a single 16-character token (len_min=len_max=16, one_word_rate=1.0), strongly indicating a hex hash identifier pointing to a parent post. 57.67% of rows are null, consistent with a reply-only field where most posts are top-level rather than replies. Among populated rows, 34,738 unique hashes cover 101,040 entries with an 18.78% duplicate rate, so some parents attract many replies (top hash appears 121 times). Treatment: Treat as a foreign key to the parent post; left-join on this hash and ignore for modelling. high · anthropic:claude-opus-4-7

n

101,040

nulls

58,270 (57.7%)

unique

34,738

len_min

16

len_max

16

len_mean

16

len_median

16

len_p95

16

word_mean

1

word_median

1

n_empty

0

n_duplicates

8,032

duplicate_rate

0.1878

vocab_size

17,415

readability_flesch_mean

71.73

emoji_rate

0

url_rate

0

one_word_rate

1

allcaps_rate

0.0007949

boilerplate_rate

0

This is a 16-character hex hash identifying the root post of a reply thread, with every value being a single token of fixed length 16. About 57.67% of rows are null (likely top-level posts with no reply root) and 50.25% of the non-null values are duplicates across 21,277 unique hashes, consistent with many replies sharing the same thread root. Treatment: left-join on this id to the parent post table; do not feature-encode. high · anthropic:claude-opus-4-7

n

101,040

nulls

58,270 (57.7%)

unique

21,277

len_min

16

len_max

16

len_mean

16

len_median

16

len_p95

16

word_mean

1

word_median

1

n_empty

0

n_duplicates

21,493

duplicate_rate

0.5025

vocab_size

12,498

readability_flesch_mean

77.23

emoji_rate

0

url_rate

0

one_word_rate

1

allcaps_rate

0.0008183

boilerplate_rate

0

A three-class sentiment label across 101040 rows with no nulls and only 3 unique values: neutral, positive, negative. Distribution is uneven — neutral leads at 48.5%, followed by positive (34622) and negative (17437), so negatives are roughly half as common as positives. Entropy ratio of 0.93 indicates the classes are reasonably spread but not balanced. Treatment: Use as a categorical target; consider class weighting to offset the under-represented negative class. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

3

top_value

neutral

top_rate

0.4848

cardinality

3

entropy

1.473

entropy_ratio

0.9295

This is a bounded sentiment polarity score in [-0.998, 1.0], typical of lexicon- or model-based sentiment scoring. Distribution is roughly symmetric (skew 0.019, kurtosis 0.018) but heavily zero-inflated: 47.8% of rows are exactly 0 and the median is 0, suggesting many neutral or unscoreable texts. Despite the symmetry, 5,763 rows (5.7%) are flagged as outliers, indicating fat tails of strong polarity at both ends. Treatment: Treat zeros as a separate 'neutral' indicator and use the raw score as a feature; no transform needed given symmetric bounded range. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

1,928

min

-0.998

max

1

mean

0.1074

median

0

std

0.4104

q1

0

q3

0.402

iqr

0.402

skew

0.01861

kurtosis

0.01774

n_outliers

5,763

outlier_rate

0.05704

zero_rate

0.478

This is a creation timestamp column stored as ISO 8601 strings (lengths 20-35, single-token), not parsed datetimes. Values are near-unique (96576/101040) yet 4464 duplicates exist and the top values cluster tightly around 2025-12-24T05:00, suggesting a narrow ingestion window or batch insert. Format is inconsistent across rows, mixing '+00:00', '.000Z', and microsecond-precision 'Z' suffixes, which will break naive string sorting. Treatment: Parse to a normalized UTC datetime before any temporal analysis or joins. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

96,576

len_min

20

len_max

35

len_mean

24.34

len_median

24

len_p95

27

word_mean

1

word_median

1

n_empty

0

n_duplicates

4,464

duplicate_rate

0.04418

vocab_size

19,720

readability_flesch_mean

121.2

emoji_rate

0

url_rate

0

one_word_rate

1

allcaps_rate

1

boilerplate_rate

0

This is an ISO-8601 timestamp column stored as text, with every one of the 101040 values unique and exactly 26 characters long. Sampled values cluster on 2025-12-23 and 2025-12-24, suggesting a narrow capture window rather than a broad historical range. The 'allcaps' and 'one_word' alerts are artefacts of the ISO format (the literal 'T' separator and no whitespace), not a data quality issue. Treatment: Parse to datetime and derive features (hour, day, delta) instead of using as text. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

101,040

len_min

26

len_max

26

len_mean

26

len_median

26

len_p95

26

word_mean

1

word_median

1

n_empty

0

n_duplicates

0

duplicate_rate

0

vocab_size

20,000

readability_flesch_mean

121.2

emoji_rate

0

url_rate

0

one_word_rate

1

allcaps_rate

1

boilerplate_rate

0

Language tag of each record, using ISO-style codes across 90 distinct values with no nulls. English dominates at 60.8% of rows, followed by Japanese (12,607) and a sizeable 'unknown' bucket (11,481) that signals missing-data leakage into the category itself. Note the inconsistent granularity: bare 'en' coexists with locale-specific 'en-US' (3,617), so codes need normalisation before grouping. Treatment: Normalise locale codes (collapse en-US into en), treat 'unknown' as missing, then one-hot or target-encode. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

90

top_value

en

top_rate

0.6084

cardinality

90

entropy

2.178

entropy_ratio

0.3356

This is almost certainly a per-row character count of some text field, ranging from 1 to 525 with a median of 68 and mean of 97.6. The distribution is right-skewed (skew 1.02) with a wide IQR of 113, but only 0.29% of values flag as outliers and there are no nulls or zeros. With just 341 unique integer values across 101,040 rows, the field is discrete and well-behaved. Treatment: Consider a log or sqrt transform before regression to tame the right skew. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

341

min

1

max

525

mean

97.63

median

68

std

86.05

q1

30

q3

143

iqr

113

skew

1.018

kurtosis

-0.05733

n_outliers

289

outlier_rate

0.00286

zero_rate

0

This is a per-row word count, ranging from 0 to 83 with a median of 10 and mean of 14.67, indicating most entries are short snippets rather than long documents. The distribution is right-skewed (skew 1.21) with a wide IQR of 19 and ~2.85% outliers, suggesting a long tail of unusually verbose rows. Only 79 unique values across 101,040 rows and a near-zero zero_rate (0.06%) confirm it's a bounded discrete count with virtually no empty texts. Treatment: Log-transform or bin before modelling to dampen the right skew. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

79

min

0

max

83

mean

14.67

median

10

std

14.22

q1

3

q3

22

iqr

19

skew

1.209

kurtosis

0.699

n_outliers

2,882

outlier_rate

0.02852

zero_rate

0.0006037

This is a binary indicator (only 2 unique values, min 0, max 1) flagging whether a record has images. It's heavily imbalanced: 86.4% zeros and a mean of 0.136, meaning only ~13.6% of rows have images. The 'outliers' alert simply reflects the minority class rather than anomalous values. Treatment: Treat as a boolean flag; no transformation needed, but watch class imbalance if used as a target. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

2

min

0

max

1

mean

0.1363

median

0

std

0.3431

q1

0

q3

0

iqr

0

skew

2.12

kurtosis

2.497

n_outliers

13,768

outlier_rate

0.1363

zero_rate

0.8637

Binary flag indicating whether a record has an associated video, stored as 0/1 with no nulls across 101040 rows. The positive class is rare: 98.67% are zero and only 1.33% are one, producing extreme skew (8.50) and kurtosis (70.19). The 1344 ones are flagged as outliers purely because of the imbalance, not because they are anomalous. Treatment: Treat as a boolean indicator; expect minimal signal given 98.67% zeros and consider class-imbalance handling if used as a target. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

2

min

0

max

1

mean

0.0133

median

0

std

0.1146

q1

0

q3

0

iqr

0

skew

8.497

kurtosis

70.19

n_outliers

1,344

outlier_rate

0.0133

zero_rate

0.9867

Binary 0/1 flag indicating whether a record contains a link, with 82.0% zeros and a mean of 0.180 across 101,040 rows. The 18,140 'outliers' are simply the minority positive class, not anomalies — IQR-based outlier detection misfires on binary data. No nulls, exactly 2 unique values. Treatment: Use directly as a boolean feature; ignore the outlier alert. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

2

min

0

max

1

mean

0.1795

median

0

std

0.3838

q1

0

q3

0

iqr

0

skew

1.67

kurtosis

0.7888

n_outliers

18,140

outlier_rate

0.1795

zero_rate

0.8205

This column tags the embed type attached to a Bluesky post, with 5 distinct AT Protocol lexicon values like app.bsky.embed.external and app.bsky.embed.images. 61.15% of rows are null, consistent with most posts having no embed, and among the populated rows external links (46.2%) and images dominate while video and recordWithMedia are rare. Entropy ratio of 0.74 indicates a moderately concentrated but not degenerate distribution. Treatment: Treat nulls as a 'no embed' category and one-hot encode the 5 levels. high · anthropic:claude-opus-4-7

n

101,040

nulls

61,791 (61.2%)

unique

5

top_value

app.bsky.embed.external

top_rate

0.4622

cardinality

5

entropy

1.717

entropy_ratio

0.7394

This column stores serialized JSON arrays of hashtags extracted from social posts, with 87,318 of 101,040 rows holding an empty list `[]` and only 10,103 distinct values overall (duplicate_rate 0.90). When hashtags are present they are short — word_mean 1.38 and len_median 2 — and span multiple scripts (Thai, Japanese, German, English), so any text processing must be Unicode-aware. The mix of `#NowPlaying` (115) and `#nowplaying` (85) shows case is not normalized. Treatment: Parse the JSON list, lowercase, and one-hot or count-encode the most frequent tags; treat `[]` as an explicit no-tag category. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

10,103

len_min

2

len_max

1,122

len_mean

10.38

len_median

2

len_p95

63

word_mean

1.384

word_median

1

n_empty

0

n_duplicates

90,937

duplicate_rate

0.9

vocab_size

7,036

readability_flesch_mean

2.752

emoji_rate

0

url_rate

0

one_word_rate

0.902

allcaps_rate

0.005701

boilerplate_rate

0

This column stores a serialized JSON array of mention IDs (hex tokens) attached to each record, but 98,659 of 101,040 rows hold the empty list `[]`, giving a 0.98 duplicate rate and 0.996 one-word rate. When mentions do appear, they are almost always single-element arrays referencing 16-character hex IDs, with the most-cited ID occurring only 16 times. Effectively a sparse foreign-key list dominated by absence. Treatment: Parse the JSON array and explode to a mention-id join table, or collapse to a binary has_mentions flag given the 98% empty rate. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

1,921

len_min

2

len_max

420

len_mean

2.67

len_median

2

len_p95

2

word_mean

1.012

word_median

1

n_empty

0

n_duplicates

99,119

duplicate_rate

0.981

vocab_size

660

readability_flesch_mean

0.702

emoji_rate

0

url_rate

0

one_word_rate

0.9962

allcaps_rate

3.959e-05

boilerplate_rate

0

This column stores JSON-encoded arrays of URLs, but 96,211 of 101,040 rows hold the empty literal "[]", driving a 96.3% duplicate rate and a median length of 2 characters. Only 3,771 unique values exist and just 4.8% contain a URL, so the field is overwhelmingly absent rather than informative. When populated, entries point to radio/streaming sites (BBC, Radio France, shoutcast), suggesting a sparse list-of-links attribute. Treatment: Parse the JSON arrays, derive a has_links boolean and link count, and skip the raw string for modelling. high · anthropic:claude-opus-4-7

n

101,040

nulls

0 (0.0%)

unique

3,771

len_min

2

len_max

266

len_mean

4.95

len_median

2

len_p95

2

word_mean

1.003

word_median

1

n_empty

0

n_duplicates

97,269

duplicate_rate

0.9627

vocab_size

904

readability_flesch_mean

-20.11

emoji_rate

0

url_rate

0.04779

one_word_rate

0.9978

allcaps_rate

0

boilerplate_rate

0