Tracking Half a Billion TikTok Sounds

Overview

Every TikTok video is attached to a sound, and every sound carries a count of the videos using it. That count is the input. The output is the day over day difference, which is what a song breaking looks like a week before it is obvious.

Six Go programs write seven ClickHouse tables.

Scale

Everything below was measured on one machine over two days in September 2026. The numbers matter mostly for what they rule out: at this size there is no distributed cluster, no queue, and no second box.

The read path that makes this affordable is a bulk one. This article does not name it or describe how it works.

Scrape cadence

62% of sounds have fewer than ten videos on them, and the median sound has one. Reading everything daily spends the budget on audio nobody will use again.

Cadence is derived from the row at query time rather than stored:

retired   miss_streak >= 3
daily     user_count >= 10
weekly    user_count under 10, read recently
monthly   user_count under 10, long dormant
new       never successfully read

Storing a tier column would freeze the scheduler's thresholds into the store of record and buy nothing, since the pass full-scans this table to build its id list anyway.

Sounds get demoted rather than dropped. A sound that stops being queried cannot be observed breaking out, and no data would ever reveal the filter was wrong.

Reaching the API

TikTok's answer to a request it dislikes is HTTP 200 with an empty body. Four things have to be right before anything below matters:

• A registered device, which is an app install rather than a login.
• An activation call after registration, or some endpoints return empty forever.
• The X-Argus and X-Ladon headers, which cover the query string as a literal.
• A matching TLS fingerprint. A stock Go client gets an empty 200 with perfect headers.

All four are covered in Scraping TikTok's Mobile API. The rest of this article assumes they work and deals with what happens when you run them a few hundred million times a day.

Device lifetime

I assumed devices got rate limited and recovered, the way an IP does. Budget for the throttle, back off, come back later. That model is wrong, and building on it wastes most of your proxy spend.

A device holds a flat success rate for a fixed number of requests and then stops working. Permanently.

• A burned pool read 2.6% after a 45 minute rest. A fresh slice read 50.4% at the same instant through the same ports.
• Death is global rather than per endpoint: 0.28% against 50.0% in a side by side.
• Retire at the knee. Running past it adds a handful of records and wastes half the proxy capacity.

Batch size and cost

If a device is worth a fixed number of requests, the next question decides whether the whole project is affordable: is it a budget of requests, or a budget of records? Nothing in the responses says which.

Three arms ran at once on adjacent slices of one pool, at different batch sizes:

A confirming run held requests constant and varied objects by 1,500x. A device that had consumed 4,453 objects probed the same as an unused one.

So a device is worth its request budget multiplied by the objects each request returns. At one record per request, half a billion records a day needs about 2.05M devices and roughly nine residential plans, which is what kills that design.

Single-record throughput, for reference:

Throughput is concurrency divided by RTT at every point, and RTT inflates with concurrency. Splitting across processes gains nothing: one process did 524.7 ok/s against two processes at 514.0 with the same total in flight.

Generating devices

Devices being consumable means the factory matters as much as the pool. Most of what follows was learned by watching the factory quietly produce nothing.

• Registration has to go through a residential exit. Direct and datacenter give 0% survival every time.
• 34 devices in 38 seconds at concurrency 32. 155 devices in about 86 seconds.
• Keep generation concurrency at or below 40 per process. At 128 survival goes to zero, quietly. Scale with more processes.
• Sustained rate is about 2.65 devices/s per plan, with survival decaying from 84% to 19% as it runs. The likely cause is exhausting fresh exit IPs across the sticky ports. Inferred, not proven.

Proving devices

Registration succeeding does not mean TikTok will answer the device, so generation ends with a live read and failures are discarded rather than retried later.

Prove against the endpoint the run actually uses. In one window the profile probe returned 0 of 111 while the endpoint the loader needed answered around 50% on the same devices inside the same requests. Proving on the wrong endpoint throws away every good device the factory makes.

Probe on the client that registered the device: 27 of 31 succeeded on the registration's own proxy client against 0 of 37 from a different exit. The mechanism is unproven and honouring it costs nothing.

Transport

The working rule on this project was that many small requests go through residential exits and a handful of large ones go direct. It is correct for bulk reads and it is badly wrong for the creator post endpoint, which is how a loader ended up reporting 88% failure against an endpoint that was working fine.

Seven arms against the same 65 device pool and the same 24 creators, run simultaneously, one attempt each:

A residential exit takes this endpoint from about 40% to about 2%. Cursor, secondary user id, page size and regional host move it by noise next to that.

The obvious explanation is that a device is bound to the exit it registered from. The same pool file answers the following-list endpoint through a residential exit at 86%, which rules that out. Mechanism unknown.

Hosts

An inherited endpoint catalogue had one endpoint pointed at the wrong regional host. Measured with a simultaneous control, so the pool was provably healthy:

A second endpoint was misrouted the same way. Zero videos reads as a dead endpoint, which is why it survived.

Source addresses

TikTok throttles per source address at roughly 87 requests/s on a datacenter IP, so the run spreads across the addresses the box already holds and skips the proxy entirely.

The address list is a config file rather than a fact about the machine, and it was wrong twice:

• Not assigned. 7 of 37 entries were on no interface. Requests through them failed instantly with a bind error, which shows up as a huge failure rate at an impossible requests/second.
• Assigned but not routed. 29 of 30 addresses in one block timed out silently for months. The routing table was correct and no rule selected it, so packets left with the wrong source and were dropped upstream.

Binding catches the first case instantly. The second needs a real dial. Both run at startup and cost a couple of seconds against a 19 hour job.

A single exit sustains about 29 following-list reads/s at concurrency 48, then degrades into soft blocks rather than errors. Each exit carries a decaying failure ratio, gets benched when it crosses a threshold, and the bench lengthens each time it trips.

Six loaders

The pipeline is six separate binaries rather than one program with subcommands, and the split is not stylistic. Each table has exactly one writer, for a reason covered two sections down, and the cleanest way to enforce that is for the writers to be different programs.

Four writers, seven tables, no table with two writers. The reason is in whole-row replacement.

# daily cycle
GOMEMLIMIT=350GiB ./bin/soundscrape          # ~19 h, 447M ids
./bin/trendfilter -horizon 1 -top 5000       # seconds
./bin/enrich                                 # minutes
./bin/postscrape                             # continuous
./bin/discover                               # continuous

• The unit of work is one partition, a 64th of the universe, about 12 minutes. A crash repeats at most one partition.
• snapshot_date is pinned when the pass starts and does not roll at midnight, because the delta query keys on one date per pass.
• Without GOMEMLIMIT the heap target grows past physical memory and the kernel kills the process hours in.

Discovery inbox

New sounds come from scraping creators. postscrape reads the creator table, asks promising accounts for recent videos, and writes the distinct sound ids: 400 creators in 3 seconds, 82% answering, 8,300 ids, about 25 distinct sounds per creator.

It writes ids and nothing else. A two column stub landing on a hydrated sound row would erase the other 48 columns, so discovery hands ids to an inbox table and soundscrape drains it.

One row per distinct id costs about 12 bytes, so 1.75B ids is roughly 21 GB. The design this replaced kept one row per video: 5B rows and 80 GB to extract a column nothing else read, with stats captured once at an arbitrary crawl moment that could never show velocity.

Retirement

Around 26.6% of requested ids never come back. No placeholder, no error, absent from the response.

Five rounds over the same id set, control at 10/10 every round:

Same ids every time. 72% of them resolve on the single-record path, and TikTok gives the reason in a field most clients ignore: "The copyright owner hasn't made this sound available in your country", on 81 of 86 sampled. A numeric marker in the response matches on 82 of 86 omitted sounds and on none of 115 returned ones.

These are licensed commercial tracks with a median video count of 23,197 against 1 for the sounds that do resolve. They are already large before this system could see them, and for finding unsigned artists a copyright owner is the negative signal. Chasing them individually would cost about 480,000 devices a day.

Three consecutive misses retires an id, which is conclusive at 0.3% flakiness and takes the daily job from 609M to 447M.

• Deleting the row means rediscovering the id tomorrow and paying for it again.
• Writing a stub row to mark it retired triggers the wipe described below. Retirement is a full-row rewrite with the counter incremented.

Whole-row replacement

ReplacingMergeTree replaces the entire row. A process writing a partial row erases every column it did not set, with no error and no marker in the data.

It compounds. The stripped row reads a video count of zero, so the scheduler classes the sound as inert and stops asking. The wiped Enum resolves to its first declared member rather than null, so the row also lands in a valid looking wrong state.

Four structural decisions come out of this:

• The discovery inbox exists so postscrape never touches Music.
• Retirement is a full-row rewrite.
• MusicAuthor is filled from two endpoints, so both fetches run in one process.
• A planned trending table was dropped because three enrichment branches would each have set their own watermark column on one row.

Name the single writer before adding a table. When two stages want to write one table, add a second table.

Stale predicates

A ReplacingMergeTree keeps several physical copies of a row until a merge runs, and WHERE is evaluated per physical row. Old copies carry old values.

Measured on the live table:

The fix filters on the deduplicated value in a subquery:

SELECT ... FROM sounds.Music
WHERE cityHash64(music_id) % 64 = {p}
  AND music_id IN (
    SELECT music_id FROM sounds.Music
    WHERE cityHash64(music_id) % 64 = {p}
    GROUP BY music_id HAVING argMax(miss_streak, updated_at) < 3
  )

Miss rate on the same partitions went from 15.3% to 1.3%. Retirement had been silently disabled since the day it shipped.

countIf used as a presence guard has the same problem, since it counts physical rows. A day scraped twice yields 2 until a merge. Test > 0 rather than = 1.

Partitioning

An earlier version of this schema claimed IN with exact dates reads four days of a column where BETWEEN reads thirty. Measured on 124M rows across three monthly partitions:

snapshot_date is the second key column, behind music_id, so every granule holds every date and a date predicate prunes nothing.

• Partition on a hash of the id. TikTok ids skew badly, so a modulo of the raw value gives lumpy partitions.
• Snapshot tables partition by month. By day measured 27x more expensive.
• FINAL is cheap on a single partition range read and ruinous on a full scan of a 609M row table. Where an aggregate already collapses versions it buys nothing.

Codecs

"DoubleDelta for timestamps" holds only when the column correlates with the sort order. Both tables below are the same 31M rows.

A popularity rank, uncorrelated with the sorting key:

A creation timestamp, where snowflake ids make the sort order roughly chronological:

On fully uncorrelated ids DoubleDelta expands the column, ratio 0.93, compressed larger than raw.

Settled set: Delta on sorted keys, DoubleDelta on monotone dates, T64 on counters, ZSTD(3) on text. On the snapshot table the ORDER BY choice is worth 12.8x and the codecs another 2.3x over plain LZ4.

Enums, TTL, units

Declare 'unknown' = 0 on every Enum

• A RowBinary writer sending 0x00 for an Enum with no zero member inserts with no error. Every later read of that column, and any GROUP BY on it, throws UNKNOWN_ELEMENT_OF_ENUM for the whole table. Go's zero value for a byte field is 0, so a forgotten assignment does exactly this.
• Omitting the column from a named insert yields the first declared member, silently.

A column TTL can fire at insert

A signed image URL carried TTL cover_expires + toIntervalHour(1). With cover_expires unset, the default of 1970 puts the expiry in the past:

(1,'https://signed-and-valid',  now()+3600)   -> kept
(2,'https://loader-forgot-it',  0)           -> stored as '' immediately

A parse miss on one column destroyed a different column, indistinguishably from the API sending nothing. Guard the sentinel if you need a column TTL: TTL if(expires = 0, toDateTime('2106-01-01'), expires + toIntervalHour(1)).

Both columns were then deleted. The signed URL expires ~22 hours out and the table is rewritten daily, so the TTL could never usefully fire. It was merge work across 64 partitions of 447M rows, daily, for nothing.

Units

music.duration is seconds. video.duration on the post endpoints is milliseconds. Same name, same JSON shape.

A UInt16 overflows at 65,535, which is 65.5 seconds. On 236 real posts the longest was 479,080 ms and 26 of 236 would have wrapped to a plausible wrong number. p99 video length is 224 s. The schema now carries duration_ms as UInt32 so the unit is in the name.

Read the string id

TikTok emits ids above 2⁵³ as JSON numbers as well as strings, already rounded by its own serialiser. On one real post list, 134 of 135 sound ids differed from their own string form.

Absent is not false

One sound field is a genuine tri-state. Decoding into a Go bool cannot represent "TikTok did not say" and writes false for every sound missing the key. The byte-walking parser records only keys it finds, and the column is a three-valued Enum.

The delta query

This is the query the whole system exists to run: for every sound, how much did it move in the last day, week and month. It runs once for all three horizons, because running it per horizon is four scans doing one scan's work.

SELECT music_id,
       argMaxIf(user_count, updated_at, snapshot_date = {d0})      AS c0,
       argMaxIf(user_count, updated_at, snapshot_date = {d0} - 1)  AS c1,
       argMaxIf(user_count, updated_at, snapshot_date = {d0} - 7)  AS c7,
       argMaxIf(user_count, updated_at, snapshot_date = {d0} - 30) AS c30,
       countIf(snapshot_date = {d0} - 1)                           AS has_c1
FROM sounds.MusicSnapshot
WHERE snapshot_date IN ({d0}, {d0}-1, {d0}-7, {d0}-30)
GROUP BY music_id

argMaxIf rather than maxIf

A sound's video count falls when videos are deleted, so max keeps the larger reading rather than the newest and the delta clamps to 0. Three writes of one sound on one date, 100 then 900 then 250:

max returns a different answer depending on whether a background merge has run.

Guard the missing row

Aggregates over no rows return 0 rather than null, so c0 - c1 with a missing yesterday becomes c0 - 0. Every sound that missed a day looks like it gained its entire audience overnight, and with roughly half of requests soft-blocked the gaps are routine.

TikTok also reports fake zeros

Three passes over the same 5,000 sounds returned 91, 62 and 43 zero counts, and the sets only partly overlapped: 27 zero on two consecutive passes, 64 on one, 35 on the other. One sound read 0 and 40,641 four minutes apart.

The zero is stored, because the snapshot records what TikTok said. Ranking drops it, because the product should not report a fabricated breakout.

Percentage deltas stay out of the store for the same reason: 1 video to 100 is +9,900% and outranks 50,000 to 200,000, so the floor belongs where the ranking happens.

observed_at

The daily pass takes about 19 hours, so two rows sharing a snapshot_date can be 19 hours apart. Where a sound lands in the order shifts daily as retirements and discoveries reshuffle the id list.

Nothing downstream can detect or correct that, and no later scrape can reconstruct when a count was read.

observed_at is a plain column, deliberately separate from updated_at. The version column decides which duplicate wins and a backfill overwrites it, which would destroy the observation record.

Storage

The table below holds one row per sound per day, forever, and it is the thing I was most worried about when sizing this. It turned out to be the cheapest part of the system by a wide margin.

CREATE TABLE sounds.MusicSnapshot
(
    music_id      UInt64   CODEC(Delta(8), ZSTD(1)),
    snapshot_date Date     CODEC(DoubleDelta, ZSTD(1)),
    user_count    UInt32   CODEC(Delta(4), ZSTD(1)),
    observed_at   DateTime CODEC(DoubleDelta, ZSTD(1)),
    updated_at    DateTime DEFAULT now() CODEC(DoubleDelta, ZSTD(1))
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(snapshot_date)
ORDER BY (music_id, snapshot_date);

Benchmarked on 3.1B real rows, linear in rows, 0.2 s at 310M. The full 500M sound recalculation lands around 16 seconds.

The metadata table is rewritten daily

114.3 bytes per row compressed, an upper bound since the sizing run randomised all 17 string columns. At 447M ids that is ~51 GB of inserts a day, about 2.2 minutes at the measured 3.44M rows/s. Average requirement is ~5,800 rows/s, so ingest has 590x headroom.

TikTok backfills the matched-song block and streaming links weeks after a sound appears, once its fingerprinting catches up, so a write-once table would permanently miss the fields that answer "who made this and is it already signed".

A corollary: "keep this column, it cannot be backfilled" is false for anything sourced from the API, since adding a column back costs one day's pass. Only the accumulated columns, first_seen and miss_streak, are unrecoverable.

Bandwidth

The app counted 299 GB of response bodies during the soak. The NIC saw 23 GB. Bulk JSON with repeated object shapes compresses about 13:1, which is 644 wire bytes per sound, so 500M/day is ~322 GB or ~30 Mbit/s.

Ratios vary by endpoint, from 2.49:1 on a single-record read up to ~13:1 on the bulk path, with one endpoint still contested between three careful measurements. Measure at the network card, per endpoint.

Two tables that were not built

• The follow graph. The following-list scraper reads edges to discover profiles, writes the new creator ids, and discards the list. In a sibling database that edge table is 2.07B rows with no reader.
• A wide creator table. Each following-list response carries the full profile of up to 200 accounts, ~120 fields each, for free. The sibling's wide author table is 97 GiB for 944M rows, so this one keeps only what picks the next request.

Schema conventions

What follows is the full column reference, which is the part I most wanted to exist when I started. Seven tables, one .sql file each, applied by a script that checks the exit status of every statement. A few conventions run through all of them.

• ReplacingMergeTree(updated_at) with updated_at DateTime DEFAULT now(). Without the default, a writer that omits the column stamps every row with 1970 and dedup cannot order anything.
• PARTITION BY cityHash64(id) % N, never a bare id % N. TikTok ids skew badly.
• Snapshot tables partition by month. Everything else partitions by hashed id, 32 or 64 ways.
• Delta on sorted keys, DoubleDelta on monotone dates, T64 on counters, ZSTD(3) on text.
• CDN URLs expire. Where a stable identifier exists it is stored instead of the signed URL.
• No views, no scores, no ranking heuristics. Those change without the data changing.

Populate rates below come from live samples: 50 sounds, 141 profiles, 236 posts. They are listed because several fields the upstream catalogue documents are not in the responses at all.

Music

ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY cityHash64(music_id) % 64
ORDER BY music_id
INDEX idx_owner owner_id TYPE bloom_filter(0.01) GRANULARITY 4

The bloom filter exists because artist expansion and the region join both filter on owner_id, which is not in the sorting key. Without it each is a 447M row scan.

Identity

Owner, on original sounds

Present on 42 of 50 sampled. Artist identity arrives with no second request.

Flags and status

Discovery signals

A numeric encoding of theme_tags was dropped after it turned out to be a positional mapping of the tags stored beside it, which is a static lookup table kept 447M times.

Commercial match

For finding unsigned artists these invert: a match means the track is already released and owned.

An Apple Music developer token arrives alongside these and is not stored. It is a shared expiring credential rather than per-sound data.

Grouping and audio

Media

Scrape state

There is no tier column. Every value it held is derivable from this row, and storing it would freeze the scheduler's thresholds into the store of record.

MusicSnapshot

The deliverable. One row per sound per day, 0.447 bytes per row.

Every sound that resolves gets a row, with no threshold. An earlier design stored only sounds above ten videos, which saved 23 MB a day and lost the breakout day for every sound crossing the line.

MusicDiscovered

The inbox. One row per distinct sound id ever seen, about 12 bytes each.

No video stats here. They arrive free on the same response and were dropped deliberately, since a write-once stat captured at an arbitrary crawl moment can never show velocity.

Author

Backend routing table, never served to a frontend. Its job is deciding which creators are worth a post scrape.

Every following-list response carries about 120 fields per account for free. Storing them is not free: the sibling wide table is 97 GiB for 944M rows, against these eleven columns.

MusicAuthor

Frontend facing, so deliberately wide. Assembled from two endpoints by one process. Percentages are populate rates on 141 profiles.

Fields absent from the profile endpoint entirely: region, language, create_time, bio email, is_private, shop entry, ad flags. There is no link in bio field on this surface, measured 0 of 141 and 0 of 579 in an earlier session.

Removed after measuring 0 of 141 with no reader: legacy Twitter fields, short id, a music usage counter that stayed 0 even where music_count was 82, enterprise verify reason, commerce level, room id, effect artist, star and live commerce flags.

MusicContent

Videos using a sound, from three routes, which is why source is a column.

MusicAuthorContent

A trending sound owner's own posts. Populate rates measured on 236 live posts. Stats belong here, unlike the bulk tables, because this is a small set that gets rescraped and can therefore show velocity.

Forward count, repost count and the loss counters measured 0 of 236 and are not stored. share_url is not stored either, since it is deterministic from the handle and the video id.

Field signal strength

A sound object carries about 50 fields. Measured lift against a population median video count of 1:

The strongest signal is that the creator named the sound. It is also the one that must not be a stored column: the auto-generated title pattern is locale dependent, so the test is a string comparison whose pattern list grows every time somebody checks another language. A stored boolean goes wrong for every historical row when that list changes. It is computed at query time from two columns already present.

For finding unsigned artists the commercial-match fields invert. A matched sound already exists on a streaming service and already has an owner. Of 49 custom-titled originals, 19 were matched and 30 were not, and the 30 are the candidates.

Original sounds carry their owner inline on 42 of 50 sampled, so artist identity costs no second request. The owner id arrives as a JSON string at the top level and as a number inside a nested blob, and that blob is itself a JSON-encoded string needing a second parse.

Columns that were removed:

• A second pair of commercial-rights booleans, identical to the kept pair on 20 of 20 rows.
• Two display strings concatenated from three columns already in the row.
• The signed cover URL, for the TTL reason above. The stable path stays, at the cost that original-sound covers are signed on the path and have to be downloaded inline with the pass.

TikTok groups identical audio under several ids and exposes its own grouping ids. Any aggregate that must not double-count a song has to go through them.

Artist records

There is no bulk profile endpoint on the logged-out surface. That is a measured negative: a 768-combination matrix sweep with a verified positive control and a nonsense-path discriminator on every cell returned zero hits.

One process makes both calls, because neither endpoint answers the whole question:

profile/other   ->  nickname, counts, category, verification, musician flags
aweme/post      ->  region, instagram + youtube handles, recent posts

                    # one worker per artist, one row written
                    # split them and the second write wipes the first

The profile endpoint returns no region at all, verified against the full response rather than the user object. Video-level region is 100% populated on the post list.

Region is the mode of the last several videos. 12 of 13 sampled creators were fully consistent, and the thirteenth had 18 videos from one country and one each from two others, with the single most recent video being an outlier.

There is no link-in-bio field on this surface: 0 of 141, and an earlier session measured 0 of 579. That negative is now a comment where somebody would expect the column.

One field measuring 0 of 141 was kept: the private-account flag, since the sample was defined as public accounts. Recording which zeros mean "dead field" and which mean "wrong sample" is most of the value of measuring.

Fetching an artist's own posts also harvests their other sounds. 236 posts carried 222 distinct sound ids, which feed back into Music at no extra request.

The run prints a count of artists written without a region, since a profile that answered alongside a post list that did not produces a complete-looking row with one blank field.

Measurement mistakes

Fourteen confident conclusions on this project were wrong, and every one was an artifact of the measurement. Several were produced while building the loaders rather than while researching them.

A discarded error faked four server limits

rb, _ := io.ReadAll(resp.Body)

A truncated body reads as a short success. That line produced a fake id ceiling on a batch read, two fake gateway size caps, and a fake response-trimming parameter that appeared to shrink payloads 3x. All four were the same truncation.

Check order that replaced it: read error, then that the JSON parses, then the in-body status code, then that the object count is sane. Successful responses are chunked with no content length, so comparing lengths detects nothing.

Repeated ids make a batch endpoint look enormous

The server charges by distinct id, so filling a batch with repeats is nearly free and a corpus smaller than the batch measures nothing. On the video batch endpoint, 97 real ids produced successive "limits" of 800, then 2,000, then 5,000 videos per request. The real limit with unique ids is 100.

An exhausted pool looks like a broken endpoint

Misdiagnosed on separate occasions as a dead endpoint, a network fault, a server-side size limit and a concurrency effect.

creators asked 400   never answered 377   94% failure   367 creators/second

Two bugs underneath. The pool library evicted a device after 12 consecutive failures, which on an endpoint refusing half of well-formed requests is a coin landing tails 12 times: a fresh 65 device pool emptied in about one second. And nothing noticed the pool was gone, so the run continued and reported nonsense. The threshold is 40 now, and an empty pool ends the run.

The control has to be in the same batch

CONTROL (recipe that had measured 135 videos)   ok=1  failed=23
every other arm                                 ok=0-1

Every arm looked dead, which reads as a dead endpoint. It was a burned pool. A fresh pool ran the identical matrix and separated the arms cleanly: control 9 of 24, best arm 12 of 24, residential at zero. The first matrix was not a wrong answer, it was no answer.

Endpoints also go globally bad for windows at a time. One scored 317 of 376 early in a session and 0 of 39 an hour later on random ports.

Zero is not a ceiling

At a 50% base rate, a 0-of-6 cell happens by luck 1.6% of the time. Two "hard walls" were retracted by running more repetitions. Nothing below about 10 attempts establishes a ceiling on this API, and writing 0/6 instead of 0% makes that visible.

An empty 200 does not prove a path exists

Some prefixes swallow unknown suffixes, so a made-up path answers exactly like a real one that refused you. Probing a nonsense sibling alongside the real path is the discriminator.

One step further: check that the ids you got back are the ids you sent. One endpoint accepts a list of video ids, returns full video objects, and ignores the list, because it is a recommendation feed.

One axis at a time misses two-variable endpoints

The video batch endpoint needed a specific regional host and a specific id encoding at the same time. Every other combination returned an identical empty 200, so a one-axis sweep wrote it off as dead. The later bulk-profile sweep was built as a full matrix with a positive control, since a harness that reports zero is worthless until it has been shown to report a hit.

Refuting a hypothesis with the wrong column

Region gating was the hypothesis for the missing 26.6%. It was checked against the country column on the video corpus: 87.8% US for missing sounds, 85.4% US for returned ones, so region was written off.

That column records where the video was posted, not where the sound is licensed. US creators use sounds unlicensed in the US, so it could only ever have said US for both groups. A near-identical distribution across two groups suggests the column is blind to the split.

Decoded bytes are not wire bytes

Go's transport gzips transparently, so body length is the decoded size. On one endpoint, three careful measurements of wire cost per video gave 12 to 14 KB, 23.8 KB and 78 KB. That is recorded as unresolved rather than averaged.

Schema drift tests

The DDL and the loader's column list are two lists nothing keeps in sync, and a mismatch is invisible until a run finishes. On this box a column with DEFAULT 1 sat in a status table while the writer omitted it, so a full run reported "1 attempt" for every row and the column meant to find failing creators was dead.

RowBinary carries no column names, so a list that is merely out of order inserts cleanly and puts every value after the disagreement in the wrong column.

The test builds a scratch table from the same .sql files, so it gets the real engine, sorting key, codecs and defaults. Every table a loader writes has two:

• Compare the Go column list against system.columns, position by position.
• Write one fully populated row, read it back by column name, compare field by field.

Both were verified to fail when two adjacent same-typed columns are swapped. That verification is the part people skip.

CREATE TABLE IF NOT EXISTS cannot migrate. Adding a column and re-running the apply script prints "ok" and changes nothing, so alterations are manual and verified against system.columns. The apply script checks the exit status of every statement, after a wipe script on the same box once reported success when the database had refused the operation.

Parsing and encoding

A following-list page is about 1.8 MB carrying all 120 fields of every account, and the run keeps the ids. A standard decode visits every field by reflection before discarding it, which costs more CPU than reading the bytes off the wire.

The parser walks the bytes and skips what it does not want. Skipping is a bounds-checked scan for the matching brace, so an ignored field costs its length rather than its shape.

• The walk is structural. A field is taken only from a key at the top level of an entry, so an identically named key nested in a sub-object cannot be mistaken for the account's own.
• It doubles as the "is this JSON at all" check, which separates a real answer from a soft block. TikTok answers "this creator hides their list" with a 200 and a status code in the body.

The RowBinary encoder and decoder live in one file, since every loader reads its previous state and writes new state back and the two halves have to agree on column order. Binary rather than tab-separated because the payloads are mostly 64-bit ids, and because a sound title can contain a tab or a newline, where one unescaped byte shifts every later column on that row.

What I would tell myself at the start

The scraping was the part that looked hard and had the most prior art. It took about a week. The schema took longer, and the measurements took longest of all, because a wrong measurement does not announce itself.

Four things I would want to know on day one:

• Devices are a consumable with a request budget, not a rate limit. That single fact decides whether the daily job costs a hundred devices or two million, and nothing in the API tells you which model applies.
• A predicate on a mutable column of a ReplacingMergeTree reads history. It looks correct, it runs fast, and it quietly doubled the cost of the product for weeks.
• Store what the API said, guard it where you rank. TikTok intermittently reports zero for a sound with a real count. Suppressing that at write time loses the evidence; ignoring it at read time invents a viral hit.
• Run the control in the same batch as the experiment. Half the wrong conclusions on this project came from a control that was fine an hour earlier and rotten by the time it mattered.

If you are building something similar, the measurement section is the part worth reading twice. The signing side is covered separately in Scraping TikTok's Mobile API, and what happens to this data downstream is in Keeping ClickHouse and Elasticsearch in Sync.