{ 01 · analytics engineering exercise }

Fernando Carlini Guimarães

Lead Analytics Engineer

LinkedIn GitHub Start

{ 02 · the problem }

The problem we are solving

What makes a Stack Overflow question get answered, and get an accepted answer? Three questions on the public dataset, answered with a dbt project, guarded by tests written from acceptance criteria, and computed again on this page by SQL you can edit.

  1. 1 · tags

    What tags on a Stack Overflow question lead to the most answers and the highest rate of approved answers for the current year? What tags lead to the least? How about combinations of tags?

  2. 2 · python vs dbt

    For posts which are tagged with only ‘python’ or ‘dbt’, what is the year over year change of question-to-answer ratio for the last 10 years? How about the rate of approved answers on questions for the same time period? How do posts tagged with only ‘python’ compare to posts only tagged with ‘dbt’?

  3. 3 · beyond tags

    Other than tags, what qualities on a post correlate with the highest rate of answer and approved answer?

{ 03 · the data }

Frozen at 2022-09-25, and full of traps

A snapshot that stopped on 2022-09-25 at 05:56 UTC, with 23,020,127 questions since 2008, so "this year" means 2022, a partial year. All three prompts rank, one way or another, answers against questions (answers per question, answer rate, accepted rate), so knowing what makes that ratio spin is the key point. Four things in this data can spin it without the question being any better or worse. Here they are as problems; the next slide is what was done about them.

  • 1 · Small groups

    2

    questions at most behind every tag in the raw 2022 top and bottom 10, all at exactly 100% or 0% accepted. The median tag has 6 questions, so one more accepted answer moves its accepted rate by 17 points. An accepted rate from a handful of questions is mostly luck. Ranked raw, P1 would just list the luckiest and unluckiest tiny tags.

  • 2 · Edits and leakage

    45%

    of 2022 questions were edited at some point: 9% had their title or body first edited after the first answer, 4% their tags. The table keeps only the latest version, so the answer can leave its mark on the question: someone answers with dplyr, then adds the dplyr tag. That is leakage, the outcome seeping into what should explain it.

  • 3 · Censoring

    73.5%

    answer rate of 2022 python-only questions, down from 84.8% in 2021. Most of that is not a collapse: a question from September 2022 had days to be answered, not months. That is censoring, an outcome cut off before it had time to happen, and it makes the newest year look the worst.

  • 4 · Survivorship

    up

    is the direction of the bias. Only questions that survived are in the data, and Stack Overflow deletes mostly unanswered, low-score ones, so every rate reads high, and older years (more time to be cleaned up) more. This data can't say by how much.

The good news: the keys are clean. answer_count matches the joined answers for every question, no answer is orphaned, and only 292 of 11.8M accepted-answer ids (all from 2008–2012) don't resolve; a warning test keeps listing them.

{ 04 · decisions: how to deal with our data's traps }

One answer per trap

Traps 1 and 2 had a setting to choose, and an EDA settled each one before any dbt model. Traps 3 and 4 have none: one is checked, the other stated as a limit.

  1. D1 · trap 1, small groups

    How do we keep luck from ranking small groups?

    Ranked by raw accepted rate, tiny tags land at 100% or 0% by sheer luck. Each question is a yes/no draw (like a coin flip), so a tag's accepted rate measured again lands within ±1.96 standard errors of its true accepted rate 95% of the time, and that band narrows as the tag grows. How many questions until it is ±5 points at worst?

    Decided: a 385-question floor, (1.96 × 0.5 / 0.05)² = 384.2; lists sort by the Wilson bound, not the raw accepted rate.

    Same floor at 99% or 99.7%: the lists barely move.

  2. D4 · trap 2, edits and leakage

    Which version of an edited question do we measure?

    Only today's version is kept, and 9% of 2022 questions had their title or body first edited after the first answer. Dropping edited questions is biased: they do better (accepted rate 34% vs 28%). The original is in post_history, one 106 GiB read away.

    Decided: P3 reads the text as first posted; P1 and P2 keep today's tags, as the prompts ask, and are rebuilt on the original ones; every row shows its exposure, the share of its questions first edited after the first answer.

    Retagging made the #1 tag pair; single tags hold.

  3. Trap 3, censoring

    Is the 2022 drop real?

    Mostly not. From 2021 to 2022 the answer rate fell from 84.8% to 73.5%, while answers per answered question barely moved (1.79 → 1.71): about three quarters of the drop is questions not answered yet when the data stops.

    Checked: every list is rebuilt counting only answers within 30 days, on questions that had those 30 days; P2 flags 2022 as partial, and P3 makes no claim on it.

    30-day lists: 9 of the top 10 and 6 of the bottom 10 tags stay.

  4. Trap 4, survivorship

    What about deleted questions?

    They are not in the data, and Stack Overflow deletes mostly unanswered, low-score ones. Every rate reads high, and older years, with more time to be cleaned up, read higher.

    Stated as a limit: the direction is known, the size is not. The real gap between top and bottom tags is likely a bit wider, and P2's long decline a bit less steep, than shown.

    This snapshot has no trace of what was deleted.

Every decision and its why: the decisions log in PLAN.md.

D1 · EDA

How the floor was measured

Left: funnel plot of 2022 tags, accepted rate against questions per tag on a log scale, with the pooled accepted rate, its 95% and 99.8% limits, the median tag (6 questions) and the 385-question floor; small tags scatter inside the funnel, big ones fall outside it. Right: the worst-case 95% margin of an accepted rate falling with tag size, ±40 points at 6 questions, ±5 at 385.
Left: each blue dot is a 2022 tag. Across all tags, 30.2% of questions get an accepted answer (white horizontal line). The orange lines show how far luck alone can move a tag with that accepted rate: dashed for 95% of the time, dotted for 99.8%. Half of all tags have 6 questions or fewer (dark green line, the median tag). Left of that line the dots look few, but each one stacks hundreds or thousands of tags: a 1-question tag can only sit at 0% or 100% (the bars below show how many). At 6 questions, luck can put an accepted rate anywhere from 0% to 67%; at the pink line, 385 questions, only about ±5 points from 30.2% (25.6% to 34.8%). Right: the worst-case 95% margin of an accepted rate shrinks as the tag grows and reaches ±5 points at 385 questions.
Two stacked bars of 2022 tags by size (1–6, 7–29, 30–99, 100–499, 500–1,999 and 2,000+ questions). Share of tags: 53.8%, 26.8%, 11.3%, 6.0%, 1.5%, 0.6%. Share of question–tag links: 1.5%, 4.2%, 6.7%, 14.3%, 15.9%, 57.3%.
A question–tag link is one question counted under one of its tags: a question tagged python, pandas and dataframe makes 3 links, one in each tag. So a tag's share of links is its share of everything asked under tags. Tag sizes are very uneven: half of all tags have 6 questions or fewer and hold 1.5% of question–tag links, while the 0.6% with 2,000+ hold 57%. That unevenness doesn't set the floor (precision does), but it's why the floor matters: most tags are too small to measure, so the question becomes how many questions a tag needs before its accepted rate can be trusted. The answer, 385, ranks only 2.7% of tags, but they cover 76% of what was asked in 2022.
Question
How many questions before a tag's accepted rate is mostly signal, not luck?
Data
2022 questions from posts_questions, metadata only (tags, dates, answer_count, accepted answer id), never title or body. One row per tag: 42,627 tags; tag pairs as a frequency table (934K pairs).
Method
Each question either gets an accepted answer or doesn't, like a coin flip weighted by the tag's true accepted rate. With few questions, luck can push the measured accepted rate far from the true one; the standard error, √(p(1 − p) / n), measures how far, and it shrinks as the tag gets more questions. A simulation checks it: 100,000 runs of a 385-question tag (385 weighted coin flips each) put the measured accepted rate within ±1.96 standard errors of the true one 94.8% of the time, against the 95% the formula promises. The funnel plot then asks whether a tag's true accepted rate is above or below the all-tag 30.2%. It shows, for every tag size, where a tag whose true accepted rate is 30.2% would land by luck alone: inside the dashed lines 95% of the time, inside the dotted lines 99.8%. So even if every tag's true accepted rate were exactly 30.2%, about 5% of tags would fall outside the dashed lines just by luck. Under 30 questions, 6.7% do: only 1.7 points more than luck alone. For almost none of these tags can we tell a true accepted rate above or below 30.2% from a 30.2% tag that got lucky or unlucky. The share outside grows with size (38% at 30–99 questions, 60% at 100–499, 80% at 500–1,999). With 2,000+ questions, 82% fall outside even the dotted lines, where luck explains only 0.2%: their true accepted rates really are above or below 30.2%. The floor follows from a target: ±5 points at worst, 95% of the time. Luck moves an accepted rate most when it is 50%, so the smallest n with 1.96 × √(0.5 × 0.5 / n) ≤ 0.05 is (1.96 × 0.5 / 0.05)² = 384.2, rounded up to 385, the textbook poll size. The lists then rank by the Wilson bound, each tag's 95% range for its true accepted rate: highest lists sort by its low end, lowest lists by its high end. Is 95% too lax? Ranking the same tags with 99% or 99.7% ranges keeps all of the top 10 and 9 and 8 of the bottom 10. The floor is what moves the lists: a 99% target needs 664 questions, and at 664, 9 of the bottom 10 drop out, because they have fewer (median 518). Two other ways of ranking the same tags agree: the raw accepted rate and empirical Bayes (each accepted rate pulled toward 30.2%, harder the fewer questions the tag has) share 9 or 10 names with every top and bottom 10 (how each range and check works: next row).
How the ranges work
Left: for a measured accepted rate of 10%, the plain ± range and the Wilson range at 10, 30, 100 and 385 questions. At 10 questions the plain range runs from −8.6% to 28.6%, below 0%; Wilson's from 1.8% to 40.4%. At 385 questions the two are almost the same. Right: the 2022 top five tags by the Wilson low end, each with its measured accepted rate, its range and its low end: jq (824 questions), awk (1,266), data.table (696), dplyr (4,093), tidyr (412).
Left: a group whose measured accepted rate is 10%, at four sizes. Orange is the plain ± range, blue is Wilson's. At 10 questions the plain range runs below 0%, which no accepted rate can; Wilson's stays inside. At 385 questions the two lines are almost the same. Right: the 2022 top five, each with its measured accepted rate (dot), its Wilson range (line) and the low end the list sorts by (pink). dplyr has a lower accepted rate than tidyr but ten times the questions, so its range is narrower and its low end higher: #4 against #5.
  • What a 95% range says. A tag's measured accepted rate is its true accepted rate plus luck. The 95% range is the set of true accepted rates that could easily have produced what we measured; built this way, it holds the true accepted rate 95% of the time.
  • The plain ±, and where it breaks. The measured accepted rate p ± 1.96 standard errors, with the standard error √(p(1 − p) / n) taken at the measured p. Near 0% and 100% that fails. 1 accepted of 10 gives −8.6% to 28.6%, below what is possible. 0 of 10 gives a standard error of 0, so “0% to 0%”, as if 10 questions proved that no question in the tag ever gets an accepted answer.
  • How Wilson works. It turns the question around. Instead of taking the spread at the accepted rate we measured, it tries every possible true accepted rate t and keeps t if the measured p is within 1.96 standard errors of it, with the standard error taken at t: |p − t| ≤ 1.96 × √(t(1 − t) / n). Solving that for t gives the two ends, (p + z²/2n ± z × √(p(1 − p)/n + z²/4n²)) ÷ (1 + z²/n), with z = 1.96. Since the spread is taken at t, a true accepted rate near 0% has a small spread and one near 50% a large one, so the range can't leave 0–100% and never has zero width: 1 of 10 gives 1.8% to 40.4%, and 0 of 10 gives 0% to 27.8%. The z²/2n term pulls a small group's range toward 50%: the middle of the 1-of-10 range is 21%, not 10%. As n grows, that term fades and Wilson meets the plain ±.
  • Why the low end ranks the highest list. Sorting by the measured accepted rate rewards small, lucky tags. Sorting by the low end ranks a tag by the lowest accepted rate it plausibly has, so it ranks high only if it is high and well measured. data.table and tidyr both measure 66.5%; with 696 questions against 412, data.table's low end is 62.9% and tidyr's 61.8%, so they rank #3 and #5. The lowest list mirrors it with the high end: a tag ranks low only if even its highest plausible accepted rate is low.
  • Why Wilson. It stays close to 95% even for small groups, where the plain ± often misses; it is the standard recommendation in its place for a share. It is one SQL expression, so dbt computes it in BigQuery for every tag and pair, and a test checks it against reference values (9 of 10, 850 of 1,000, 1 of 1, 0 of 1). The exact interval (Clopper–Pearson) guarantees at least 95% but is wider than it needs to be, and it needs a beta-distribution function that BigQuery SQL doesn't have. One honest limit: at 385+ questions, Wilson and the plain ± differ by at most 0.4 points and give the same top 10 and bottom 10, so in P1 the floor does most of the work. Wilson is the rule that stays right at any size, so it needs no second look for a pair or a tag near the floor.
  • Check 1, no range at all: the raw accepted rate. Same floor, each tag sorted by its measured accepted rate alone. Precision is what goes: every place is shown to a tenth of a point as if it were exact, when at 385 questions a tag's accepted rate can be off by up to ±5 points, and nothing tells a tag measured on 7,394 questions from one measured on 471. The lists barely move at the floor: the bottom 10 keeps all ten, and the top 10 swaps one, kql (60.3% on 471 questions) in for regex (58.5% on 7,394), though regex's low end is higher (57.4% against 55.8%). What the ranges add is how sure each place is: the top five's ranges all overlap, so the data says they belong in the list, not in what order. Without the floor as well, the lists fill with 1- and 2-question tags at 100% and 0% (executive impact, below).
  • Check 2, empirical Bayes. Each tag starts with m imaginary questions at the all-tag accepted rate (30.2%), then adds its real ones: (accepted + m × 30.2%) ÷ (questions + m). m comes from the data: how much tags' accepted rates really differ, beyond what luck alone would make, fitted on every tag with 30+ questions. Here m = 18, a weak pull, because real differences between tags are large. A 9-of-10 tag would drop from 90% to 51.6%; jq, with 824 questions, barely moves (67.6% to 66.8%).
  • For answers per question, the bootstrap. Answers per question is an average of counts (0, 1, 2 answers…), so its range is the mean ± 1.96 standard errors, which assumes the average behaves like a bell curve. The bootstrap checks that without the assumption: it redraws a tag's questions at random, with replacement, 2,000 times, recomputes answers per question each time, and keeps the middle 95% of the results. Its bounds sit a median 0.002 answers per question from the formula's, and the lists match (10 of 10 at the top, 9 of 10 at the bottom). It ran once, in Python on a cached table: in dbt it would mean 5.8 billion rows for the tags alone.
  • Result. Both accepted-rate checks share 9 or 10 of every 10 names with the Wilson lists, so the lists don't hang on the choice of interval.
Impact
P1: 1,162 of 42,627 tags ranked in 2022 (2.7% of tags, but 76% of question–tag links), plus 1,026 tag pairs. P2: nothing is ranked, so no floor; the same ±1.96 standard-error intervals show the dbt years overlap, so no dbt trend is claimed. P3: groups under 385 questions are not reported.
Trade-offs
  • Small tags are left out, even good ones. A tag needs 385 questions in the year to be ranked. google-query-language had 277 questions in 2022 and 89.9% of them got an accepted answer, more than any ranked tag, but it is under the floor, so it doesn't appear. The lists answer "which tags do best among those measured to ±5 points", not "which tags do best".
  • Big tags get the benefit of the doubt. Sorting by the low end of the range pulls a well-measured tag above a smaller one with a higher accepted rate: dplyr (64.2%, 4,093 questions) ranks #4, above tidyr (66.5%, 412). The lists answer "which tags are surely high (or low)", so a small tag has to clear a higher bar. At the floor that costs little: sorting by the accepted rate alone would change one name in the top 10 and none in the bottom 10.
  • The bottom 10 depends on where the floor sits. The tags with the lowest accepted rates tend to be smaller: 9 of the bottom 10 have fewer than 664 questions (median 518). Raising the floor to 664, the 99% version of the ±5-point target, would drop those 9 and bring in 9 others. Changing only the confidence level, with the floor kept at 385, barely moves the lists (the top 10 stays the same, the bottom 10 keeps 8 or 9). So the floor, not the confidence level, decides who is in the bottom 10.
  • A tag can leave the list just by shrinking. The floor is 385 every year. purrr and stringr were #1 and #3 in 2021; in 2022 they had 368 and 270 questions, so they aren't ranked, even though 68.5% and 69.3% of their questions got an accepted answer, above every ranked 2022 tag. Comparing the lists across years means checking whether a tag left because of its accepted rate or because of its size.
  • About 1 range in 20 is wrong. Each tag's 95% range holds its true accepted rate 95% of the time, so across 1,162 ranked tags about 58 ranges miss, and we can't tell which. A tag at the edge of the top 10 could truly belong just outside it. That's why the lists were also built two other ways (raw accepted rate and empirical Bayes): they share 9 or 10 of every 10 names, so the lists hold even if a single position might swap.
Tools
BigQuery SQL: 3 queries in run_eda.py, about 3.4 GB in total, each dry-run first and capped in bytes; results cached as CSV. Everything after that reads the cache, never BigQuery: floor_precision.py (simulation, floors, tag sizes, tags left out) and presentation/eda_charts.py (the charts in this dialog). Python with pandas, NumPy and matplotlib.
GitHub repo

Executive impact of the decision

“Why is P1 ruling out tags with fewer than 385 questions?”

Because below that size, a tag's accepted rate is mostly luck. If we rank all tags raw, the whole top 10 and bottom 10 are tags with one or two questions, at exactly 100% or 0%. Half of all tags have six questions or fewer, and at six, a single accepted answer moves the accepted rate by 17 points.

385 is the same sample size a political poll uses: it's the smallest number of questions where a tag's accepted rate is right to within ±5 points, 95% of the time.

What it costs: we rank only 1,162 of 42,627 tags, but those cover 76% of everything asked in 2022.

  • “So we ignore small tags?” We can't rank them reliably. If one matters to the business, we can track it over several years, or group it with related tags until it reaches 385 questions.
  • “Why ±5 and 95%?” It's the standard polling target. We also tested stricter settings (99% and 99.7% confidence): the top 10 didn't change, and the bottom 10 kept 8 or 9 of its tags.
  • “Why not just sort by the accepted rate?” Because two tags with the same accepted rate aren't equally sure. We rank each tag by the lowest accepted rate it plausibly has, so a well-measured tag beats a small one that may have been lucky. Above 385 questions it changes one name in the top 10, but it tells us how far to trust each place: the top five are a group, not a podium.
  • “Is 385 the right number?” A stricter target (±5 points at 99%) needs 664 questions. That mostly changes the bottom 10, because the tags with the lowest accepted rates also tend to be smaller.

D4 · EDA

How edits and leakage were measured

Left: two stacked bars over all 2022 questions. Tags: 4.4% first edited after the first answer, 9.0% edited earlier or never answered, 86.5% never edited. Title or body: 8.9%, 30.8% and 60.2%. Right: edited questions have an answer rate of 66.9% against 56.8% for never-edited ones, and an accepted rate of 34.0% against 27.6%.
Left: every 2022 question, by what was edited. Orange: the first edit of that kind came after the first answer, so the answer could have caused it: 4.4% of questions for tags, 8.9% for the title or body. Blue: edited, but the first edit came earlier, or the question was never answered. Right: the 45% of questions edited at some point do better than the 55% never edited: answer rate 66.9% vs 56.8%, accepted rate 34.0% vs 27.6%. Keeping only never-edited questions would drop almost half the year and keep its weaker part.
Scatter of the 1,162 ranked 2022 tags: accepted rate against tag exposure on a log scale, rank correlation 0.67. The top 10 by accepted rate sit around or above 10% exposure, jq at 19%; the bottom 10 sit at or under 3.1%; webdriverwait reaches 62%.
Each dot is a 2022 tag with 385+ questions, the 1,162 that P1 ranks. Tag exposure is the share of a tag's questions whose tags were first edited after the first answer (log scale). Green: the top 10 by accepted rate; orange: the bottom 10. Above the dashed line, over 10% of a tag's questions are exposed: 8 of the top 10 are, while the bottom 10 stay at or under 3.1%. webdriverwait (white) reaches 62%: a tag mostly added by the people answering.
live SQL · DuckDB-WASM · P1's result table, with its exposure columnloading engine…

The bars are each tag's exposure. For the bottom 10, order by accepted_rate_upper ascending ( ranks the lowest by the upper bound): all at or under 3.1%. Order by tag_exposure DESC to find webdriverwait.

Dumbbell chart of P3's text features, 2018–2021: for each feature level, its effect on the accepted rate measured on the original text and on today's text. The two dots nearly touch for every level; the widest gap is an image, +4.7 points on the original text and +2.3 on today's.
P3, every question of 2018–2021. Each row is one level of a text feature against its reference level (for example, has a code block against has none). Orange: its effect on the accepted rate measured on the text as first posted, the headline; white: the same effect on today's text. Features not read from the text (the asker's history, the posting time) are the same in both and left out. The widest gap is an image (+4.7 points on the original text, +2.3 on today's); a code block goes from +11.8 to +11.3.
Question
How often are questions edited, does an edit come after the answer, is a never-edited filter safe, and do edits change the three answers?
Data
post_history for 2022 questions, and for python-only and dbt-only questions of 2013–2022: the kind of each edit (title, body or tags, edited or rolled back), its time and its editor, never its text column; joined to the time of each question's first answer. For P1, the 1,162 tags with 385+ questions ().
Method
The table keeps only today's title, body and tags. post_history logs every revision with its kind and time, so each question's first edit of each kind can be set against its first answer, the earliest moment an answer can shape an edit (when an answer was accepted is not in the data). Any editor counts, the asker included. In 2022, 45% of questions were edited: 13.5% their tags, 40% their title or body. For 4.4% the first tag edit, and for 8.9% the first title or body edit, came after the first answer. Why not keep only never-edited questions? Edited questions do better (answer rate 67% vs 57%, accepted rate 34% vs 28%), so that filter would drop 45% of the year and keep its weaker part. The gap isn't even stable: among python-only questions, the edited ones had the lower accepted rate every year from 2013 to 2020 (62.7% vs 68.3% in 2013). The risk is measured as exposure: the share of a group's questions whose relevant field was first edited after the first answer (tags for P1 and P2, title or body for P3). Across the 1,162 ranked tags, tag exposure has a median of 3.7%, 1 tag in 10 is above 10%, and webdriverwait reaches 62%. It rises with the accepted rate: a rank correlation of 0.67 (sort the tags by each measure and compare the two orders; 1 is the same order, 0 no relation). Part of that is built in, since an unanswered question can't be edited after an answer, but counting only answered questions it is still 0.49. So the possible leakage sits where P1's headline is. Exposure says what could leak, not whether it moved anything, so each answer is also rebuilt on the version the asker first posted (the original tags for P1 and P2, the original title and body for P3, all from one read of post_history.text), and a warn test lists every result that moves. What they found is under Impact.
Impact
  • P1. Every row carries its tag exposure. Rebuilt on the original tags, a warn test flags 46 tags and 65 pairs (a top or bottom 10 changes, or an accepted rate moves more than 2.5 points, half of 's ±5-point target). 9 of 10 tags stay in each accepted-rate list: tidyr (#5) falls under 385 questions on its original tags and java-stream comes in; at the bottom, usb gives way to video-streaming. Pairs keep 7 of the top 10 and 8 of the bottom 10. The #1 pair, arrays + google-sheets (exposure 64%), falls under 385: it is mostly made by retagging after an answer, so it isn't quoted as a finding.
  • P2. Tag exposure is 3.6–5.1% of python-only questions a year and 0–3.2% of dbt-only ones. The original-tags check flags 3 rows (python 2019 and 2021, dbt 2022), none of which changes a conclusion, and dropping every question whose tags were ever edited moves python 2022 from 1.26 to 1.25 answers per question.
  • P3. Every feature is measured on the text as first posted. Today's text would have given 5.1–7.4% of each year's questions a different "has a code block" value (beyond the format noise between the two texts), yet the effects barely move (third chart), and the warn test raises one warning: five or more tags, on the accepted rate among answered questions (−0.3 points on the original version, +1.5 on today's).
Trade-offs
  • P1 and P2 still carry some leakage. The prompts ask how a question is tagged, and today's tags are what a reader sees, so the lists keep them; the original-tags rebuild says where that matters. tidyr is #5 on today's tags, but on the tags askers first chose it has fewer than 385 questions (exposure 14.6%): part of the tag was added after answers.
  • Exposure is a lower bound. It counts a question only when the first edit of that kind came after the first answer, so a question whose tags were edited before and again after its first answer counts as not exposed. The rebuild compares the original version with today's, so it catches those; the exposure column alone doesn't.
  • Exposure is partly answer rate. An unanswered question can't be exposed, so tags whose questions get answered score higher: the 0.67 rank correlation with the accepted rate drops to 0.49 counting only answered questions.
  • The line is the first answer, not the accepted one. When an answer was accepted isn't in the data, so an edit after the first answer but before the accepted one counts as exposed, and an edit prompted by a comment before any answer doesn't (it can't be the answer's doing).
  • The original text costs a 106 GiB read. On a free tier of 1 TB a month, that read became a decision of its own: how it was priced, proved worth it, run once and guarded while an AI agent wrote the queries is D5, on slide 7.
  • Never-edited is a check, not the answer. It would drop 45% of questions, and the better part of them, so it is one of P3's sensitivity versions (a code block: +12.0 on never-edited questions, +11.8 on the original text) rather than a filter.
Tools
BigQuery SQL: 2 queries in run_eda.py (6.9 GB and 5.2 GB), each dry-run first and capped at 10 GB, results cached as CSV; they read revision metadata (ids, kinds, times, editors), never the text column. The checks under Impact run in dbt: exposure in int_question_exposure (metadata only), the original versions from the one-time post_history.text read (106 GiB; its budget is D5), and three warn tests. The charts in this dialog come from presentation/eda_charts.py, which reads the cached CSVs and the exported P3 mart, never BigQuery. Python with pandas and matplotlib.
GitHub repo

Executive impact of the decision

“Can we trust the top tags, if tags get added after the answer?”

Yes, and we checked rather than assumed. Stack Overflow keeps only today's version of a question, and 9% of 2022 questions had their text changed after someone answered (4% their tags), sometimes by the person answering. Left alone, the lists would credit tags that answerers add: webdriverwait has 62% of its questions retagged after an answer.

So every list is rebuilt on the tags the asker first chose. 9 of the top 10 and 9 of the bottom 10 tags stay. The top tag pair doesn't (most of it is retagging), so we don't quote it. P3, which is about the question itself, reads the text as first posted.

What it costs: one 106 GiB read of the revision history, planned against a free monthly budget (D5).

  • “Why not just drop the edited questions?” Almost half the questions were edited, and they don't behave like the rest (in 2022 they do better: accepted rate 34% vs 28%), so dropping them would bias every rate.
  • “Why not rank on the original tags, then?” The prompt asks how questions are tagged, and today's tags are what people see and search by. The rebuild shows the answer barely changes either way.
  • “How do we know the answer caused the edit?” For any single question, we don't. Exposure counts what could have leaked; the rebuild measures how much the results actually move, and that's what we report.

{ 05 · decisions: definitions }

What the prompts left open

Two phrases in the prompts can be read two ways. Each reading was computed on the data before choosing.

  1. D2 · definition, P1 and P2

    Accepted out of what?

    "Rate of approved answers" has two readings: out of all questions, or out of the answered ones. The top 10 barely cares (8 of 10 tags shared). The bottom 10 does (5 of 10), because the worst tags are the ones nobody answers.

    Decided: all questions as the headline, with answer rate and accepted-among-answered beside it on every row; the two multiply back to it.

    magento2: 41% answered, 9% accepted.

  2. D3 · definition, P2

    Which way up is the ratio?

    Questions per answer and answers per question carry the same information, but not the same headline: python-only 2021 → 2022 is −17% one way and +21% the other.

    Decided: answers per question: higher reads as more answers, and P1 uses the same metric. Questions per answer is its inverse (1 ÷ answers per question), shown once.

    answer_count matches the joined answers exactly.

D2 · EDA

How the two denominators were compared

Scatter of 2022 tags with at least 385 questions: accepted out of all questions against accepted out of answered questions, with the two bottom-10 lists marked.
Each dot is a 2022 tag with ≥ 385 questions. Orange: in the bottom 10 by A only; lilac: by B only; pink: in both (5 of 10). A's lowest tags are the ones few people answer; B's are answered but rarely accepted.
Question
Should the accepted rate divide by all questions (A) or by answered questions (B)? A = answer rate × B.
Data
The 2022 tag table from (reused), plus questions tagged exactly python or exactly dbt by year, 2013–2022.
Method
A and B with Wilson bounds; the top and bottom 10 under each, with the 385 floor counted on questions, then on answered questions (0 of 10 shared at the bottom: the floor alone drops A's lowest tags). For P2, A and B year by year, to separate censoring from behaviour.
Tools
BigQuery SQL (1 query, about 1.1 GB); Python with pandas and matplotlib.
GitHub repo

D3 · EDA

How the two orientations were compared

Two line charts for python-only questions, 2013 to 2022: answers per question falling, questions per answer rising.
Same data, two headlines: from 2021 to 2022, python-only answers per question fall 17% while questions per answer rise 21%.
Question
Which way up should the question-to-answer ratio go, and does the choice change the story?
Data
Questions tagged exactly python or exactly dbt, 2013–2022: answer_count, its square (for the standard error) and the answers joined from posts_answers.
Method
Both orientations per year with 95% intervals (mean ± 1.96 × SE of answer_count); YoY change, absolute and relative; answers per question split into answer rate × answers per answered question; answer_count checked against the joined answers (gap 0).
Tools
BigQuery SQL (1 query, about 1.5 GB); Python with pandas and matplotlib.
GitHub repo

{ 06 · the stack }

How we solved the problem

One warehouse, one dbt project, a few Python scripts and three ways to reach language models, delivered as a public repo and this page. What each piece did, and what it cost:

  • warehouse

    BigQuery sandbox

    The public Stack Overflow tables, read in place, never copied. The free tier: 1 TB of queries and 10 GB of storage a month, no billing account.

    511 GiB billed, US$ 0 ()

  • transform and test

    dbt Core 1.12 · MetricFlow

    58 models on dbt-bigquery, from four staging views to one mart per prompt; tests named after acceptance criteria; a semantic layer that defines each metric once and reproduces the README's numbers.

    every everyday query capped at 20 GiB

  • python

    pandas · matplotlib · BigQuery client

    The EDA behind every decision (each query dry-run first, stopped over 10 GB), the builder of queries/, the mart export for this page, the labeling scripts.

    EDA, exports and checks: 188 GiB

  • language models

    Anthropic · Vercel AI Gateway · TypeSafe

    For P3's quality labels: Claude proposed them, Gemini and Grok drafted a reference a person reviewed, and TypeSafe Jev, the best and cheapest of three candidates, labeled 30,000 questions.

    US$ 10.65 for every call ()

  • delivery

    GitHub · Actions · Pages

    CI on every push without warehouse credentials (dbt parse, MetricFlow configs, Python checks); Pages hosts this page, where DuckDB-WASM runs the SQL beside each answer on CSV exports of the marts.

    this page reads 0 bytes

  • how it was built

    Specs first · Claude Code

    Definitions and before any SQL; an AI coding agent wrote the code inside byte caps, a graph test and a shell gate that asks a person before anything that could cost.

    every big read approved by a person

Two of these bill by use: BigQuery by the bytes a query reads, the language models by the tokens they read and write. Each got a decision of its own, on the next slide.

{ 07 · decisions: spending on a budget }

What could cost money, and what it cost

Each metered service got the same treatment: priced before it ran, capped while it ran, measured after. Neither cost a dollar it wasn't meant to.

  1. D5 · budget, P1–P3

    We're on a free tier: how do we not overspend?

    The project runs in BigQuery's free sandbox: 1 TB of queries a month, 10 GB of storage. needs the original text, and one read of post_history.text bills 106 GiB whatever the filter: BigQuery bills whole columns, and this table has no partition. And an AI coding agent writes and runs the queries.

    Decided: price every query before it runs; prove the read is worth it on metadata first (12 GB); read once, on its own dbt target capped at 120 GiB, keeping all that later steps need; cap everything else at 20 GiB. The agent works inside caps, tests and a gate that asks a person before it spends.

    511 GiB billed in all: 45% of October's free quota, US$ 0.

  2. D6 · language models, P3

    Which language model labels the questions, and at what price?

    P3's rules count what is easy to count, like code blocks. How a question is written (a worked example, a broad ask) needs a reader, and nobody reads 30,000 questions by hand. Model prices differ about 100× per token, and no model can be scored against its own labels.

    Decided: a written rubric; two models from other companies (Gemini, Grok), called through the Vercel AI Gateway with one key, draft a reference and a person reviews it; three cheap candidates are scored on it, and the best, TypeSafe Jev, is also the cheapest; 30,000 questions, sized to a US$ 4 budget; every paid run priced for free first and capped.

    Jev: kappa 0.80 at US$ 0.00013 a question.

D5 · EDA

How the budget was planned, guarded and spent

Left: GiB billed so far in each month against the free quota of 1 TB a month. October climbs slowly to about 50 GiB, jumps to about 330 GiB on October 31 with the three full text reads, and ends at 460 GiB, 45% of the quota; November ends at 50 GiB, 5%. Right: the 511 GiB by kind of work: post_history.text full reads 212 GiB in 2 jobs, posts_questions.body full read 35 GiB in 1 job, 2% samples of both 8 GiB in 6 jobs, the rest of dbt on the dev target 67 GiB in 339 jobs, EDA, exports and checks 188 GiB in 337 jobs.
Left: every BigQuery query the project ran, 685 jobs from October 29 to November 1, as GiB billed so far in the month, against the sandbox's free 1 TB a month (dashed line). October ended at 460 GiB (45%) and November at 50 GiB (5%). The jump on October 31 is the three full text reads, 12 minutes apart: post_history.text (106 GiB), posts_questions.body (35 GiB), and post_history.text again (106 GiB) after a rule fix. Right: the 511 GiB by kind of work. The full text reads are 247 GiB, about half of everything; the 2% samples that tested the rules first cost 8 GiB; every other dbt query together, 67 GiB; everything not sent by dbt (the EDA, the mart exports, the query checks), 188 GiB.
Question
Can a free sandbox pay for the original text needs, and how do we keep an AI coding agent from spending the month's quota by accident?
Data
BigQuery's own job log for the project (INFORMATION_SCHEMA.JOBS_BY_PROJECT): 685 query jobs from October 29 to November 1, with the bytes each one billed and, for dbt's queries, the target and the model (dbt writes both into every query it sends); never the query text. Plus the free dry runs that priced each big read before it ran.
Method
The sandbox gives 1 TB of queries a month and 10 GB of storage, at no cost; past the quota, queries stop until the next month. A dry run is free and returns what a query would bill, so the two text columns were priced first: post_history.text 109 GB and posts_questions.body 36 GB (the models that read them, with a few more columns, billed 106 GiB and 35 GiB). A filter doesn't make them cheaper: BigQuery bills every column a query touches, in full, and these tables have no partition, so keeping only the three revision kinds needs still reads all 106 GiB. Most other queries read 1–7 GB. Then, in order. (1) Prove the read is worth it with metadata: 's two EDA queries (6.9 + 5.2 GB, about a tenth of the read) showed edits are common, biased, and sit where P1's headline is. (2) Design it once, in the spec, before paying: one read with its size stated (acceptance criterion ), storing the features, the original titles and tags (P1 and P2 need them too) and a 5% sample of the raw text, about 3 GB of the 10 GB of storage. The body is a separate read, because together the two would bill about 145 GB, over the cap. (3) Test cheap: 2% samples of both models on the everyday dev target (6 jobs, 8 GiB) found two rule bugs. (4) Read: on October 31, on the full_scan target, post_history.text and then posts_questions.body. Comparing the stored original and current text question by question then found three more bugs in the rules for the original text, so post_history.text was read a second time. (5) Measure: the job log shows 511 GiB billed in all, 460 in October (45% of the month) and 50 in November (5%). The full_scan target ran 3 queries in the project's life.
With an AI agent
The code was written with an AI coding agent (Claude Code), so the limits had to hold without anyone reading every query first. They sit in five layers, from the spec to the shell:
  • Spec first. The cost rules are acceptance criteria, written before any SQL: the body is read once into a feature table and nothing downstream reads it (); the original text is read once and its size is stated (). The test assert_text_columns_read_once checks them on dbt's project graph, without reading a table: if anyone, agent or person, adds a model that reads a text column, the build fails before a byte is billed.
  • Caps that BigQuery enforces. Each dbt target carries maximum_bytes_billed (dev 20 GiB, full_scan 120 GiB per query): over it, BigQuery refuses the query, whoever wrote it. The default build skips the two scan models (selectors.yml); every EDA script dry-runs first and stops over 10 GB unless run with --allow-gb; the export script caps each query at 1 GiB; CI runs with no credentials at all.
  • A gate in the agent's shell. A local hook (machine-specific, kept out of the repo) checks every shell command before the agent runs it: it dry-runs a bq query and asks above 10 GB; it always asks before a dbt run, an mf query, an EDA run with --allow-gb or inline Python that touches BigQuery; when it can't estimate, it asks. Only a person can answer it. All three full reads were approved there, one at a time. The hook was tested with a 13-check script, including prompts declined in a live agent session.
  • A hook, not an instruction. A line in the agent's instructions, or a skill, is advice the agent can misread or skip; the hook runs before every command, so it holds even when the agent gets it wrong. Instructions still carry the why: AGENTS.md tells any agent working on a clone to dry-run first, keep the default build and never read body or post_history.text outside the two scan models.
  • The same rules for language-model spend. The P3 labeling script counts tokens for free and prints the expected and worst-case cost before any paid run, refuses to run without --max-usd, and stops starting calls at that cap; no run was allowed more than US$ 10. The 30,000 labeled questions were sized to a US$ 4 budget, and which model labeled them is a decision of its own (, beside this one). Total: US$ 10.65, logged call by call.
Impact
  • P1–P3. One read feeds all three prompts: the original tags behind P1's and P2's rebuild, and the text P3 measures.
  • Everyday work stays cheap. Everything except the two scan models rebuilds on the dev target, where no query has billed more than 3.9 GiB; queries/q1.sql bills about 9 GB and q2.sql about 7 GB.
  • The budget held. 511 GiB in all, never more than 45% of a month's free quota, and nothing paid.
Trade-offs
  • The original text was read twice. Matching original and current text question by question needs both full tables (a sample takes whole storage blocks, so two samples can't be matched), so the last three rule bugs only showed after the first full read: 212 GiB instead of 106.
  • Only 5% of the raw text is kept. It fits the 10 GB of storage, but every later step that needs the raw text, such as P3's quality labels, is limited to that sample.
  • dbt has no dry run. A dbt query can't be priced before it runs, so the cap is the real guard there, and the gate asks before every dbt run, however small: many prompts for cheap builds.
  • The gate protects only this machine. A clone gets the caps, the default build, the graph test and AGENTS.md, but not the approval prompt.
  • Rebuilding pays again. Sandbox tables expire after 60 days, and anyone rebuilding P3 from scratch pays the 141 GiB once.
  • Two months helped. The work crossed from October into November; inside one month it would have used half the quota (511 of 1,024 GiB): still under, with less room for a third read.
Tools
One BigQuery query in eda/d5_budget/run_eda.py, on the project's job log (a metadata query, billed the 10 MB minimum), dry-run first and capped at 1 GB, cached as CSV; the summary and this dialog's charts (presentation/eda_charts.py) read only the cache. The guards: the dbt profile's caps (profiles.example.yml), selectors.yml, the graph test, each EDA script's dry run and limit, the labeling script's cost estimate and cap; the shell hook stays local.
GitHub repo

Executive impact of the decision

“What did the data cost, and could the AI agent have run up a bill?”

Nothing, and no. The project ran on BigQuery's free tier, 1 TB of queries a month. Over four days it billed 511 GiB, never more than 45% of a month. Half of that was one decision: reading the original text of every question (), at 106 GiB a read.

We proved that read was worth it with 12 GB of metadata first, designed it to run once, and ran it on purpose: three big queries in the project's life, each approved by a person.

The agent couldn't overspend by accident: BigQuery refuses any query over its cap, the default build skips the expensive models, a test fails any new model that touches the text, and a gate in the agent's shell asks a person before anything that could cost.

  • “What would it cost on a paid project?” About US$ 3 at BigQuery's on-demand list price (US$ 6.25 per TiB); the text reads are US$ 1.50 of it. The habits matter more than the bill: the same caps and gates scale to a warehouse where a careless query costs real money.
  • “Why not work on a sample?” We did, to test the rules (2%, 8 GiB). But P3's claims are about every question, and P1 and P2's rebuild needs every question's original tags, so the final read had to be full.
  • “What stops the next person's agent?” The caps, the default build and the graph test travel with the repo, and AGENTS.md tells any agent the rules. The approval gate is local; on a team, the next step is BigQuery's own custom quotas per user and per day.

D6 · EDA

How the labeler was chosen, and what it cost

Left: five models by mean Cohen's kappa against the reviewed gold set and measured cost per question on a log scale. Candidates: TypeSafe Jev 0.80 at US$ 0.00013, GPT-6 Luna 0.72 at US$ 0.00031, Claude Haiku 0.68 at US$ 0.00077. References, hollow: Gemini 4 Argon 0.91 at US$ 0.0094, Grok 4.7 0.85 at US$ 0.015. Right: the US$ 10.65 of model calls by stage: discovery 3.35, practice round 0.94, reference labels 2.43, candidate scoring 0.12, analysis set 3.82.
Left: every model that labeled the 100 scoring questions, by its mean Cohen's kappa against the reviewed gold set (1 = always agrees, 0 = no better than chance) and its measured cost per question (log scale). Filled dots are the three candidates; hollow ones are the two reference models, whose labels the gold set started from, so their scores are inflated and they were not in the contest. Colour is the route the call took. Jev agrees most and costs least: US$ 0.00013 a question, 2.4× less than GPT-6 Luna and 6× less than Claude Haiku. Right: every paid model call in the project, by stage and route. The discovery pass (Claude Opus reading 200 question pairs) cost almost as much as labeling 30,000 questions.
Question
Which model should label 30,000 questions for P3, how do we know its labels are any good, and how is the spend kept in hand?
Data
Every paid model call, logged with its tokens and cost (labels/spend_log.csv, 30,572 calls); the 20 practice and 100 scoring gold questions with the hand labels, the reviewed labels and each model's labels; the price list the labeling script used. The questions come from the 5% text sample of the one-time read (), original text only (), and no model ever sees whether a question was answered.
Method
P3's rules see the surface of a question: a code block, its length, a link. How it is written needs a reader, so six qualities were defined first, in a written rubric: a worked example of the desired output, the scope of the ask, the failure description, code focus, dependence on the asker's own setup, and evidence only in an image. They came from a discovery pass: Claude Opus read 200 pairs of questions, one accepted and one unanswered, without being told which was which, guessed right 72% of the time, and then, shown the outcomes, proposed the candidate labels. Then the labeler is chosen like any vendor: on a test it didn't write, at a price measured per question. (1) Practice: 20 questions labeled by hand and by Claude Opus, blind to each other. They agreed little (kappa 0.14 to 0.75 by label), and in most disagreements the rubric's wording sided with the model, so the wording was tightened and frozen. (2) Reference: two models from companies whose models were not candidates, Google Gemini 4 Argon and xAI Grok 4.7, labeled the 100 scoring questions with a reason per label; a person kept or changed each label both chose and broke every tie (0 of the 567 agreed labels changed; the 33 ties went 18 to Gemini, 15 to Grok). (3) Contest: three cheap candidates labeled the same 100 questions and were scored by Cohen's kappa per label (agreement corrected for what chance alone would give): TypeSafe Jev 0.80 on average, GPT-6 Luna 0.72, Claude Haiku 0.68, for US$ 0.12 together. Jev was best on 3 of the 6 labels and the cheapest. (4) Size: the analysis set was sized to a US$ 4 budget, 30,000 questions at Jev's US$ 0.00013 each, enough to give the rarest label (image-only evidence, about 2%) several hundred positives. (5) Run: 30,000 questions, 8 calls in parallel, about 17 minutes, US$ 3.82.
Routing
Three ways to reach the models, each for what it gave:
  • Vercel AI Gateway (Gemini 4 Argon, Grok 4.7, GPT-6 Luna; US$ 2.96). One key and one OpenAI-compatible client for three other companies, so the reference could come from companies whose models weren't candidates without three accounts. Each response carries what the gateway billed, which goes straight into the spend log, and its model list gave the prices for planning, Jev's included.
  • Anthropic API (Claude Opus, Claude Haiku; US$ 3.86, from promotional credits). Its free token count gives the exact input of a run before paying for it.
  • TypeSafe API (Jev; US$ 3.83). A decisions API: each label is a choice question, answered with one level and its probabilities, which is the shape P3 counts.
  • One spend log for all three. Every call appends its tokens and cost to labels/spend_log.csv, so the totals here are summed, not estimated.
Spend rules
The same habit as BigQuery's (): label_questions.py count prints the expected and worst-case cost of a run for free; run refuses to start without --max-usd and stops starting calls once that much is spent; no run was allowed more than US$ 10, and each cap was approved by a person before the run.
Impact
  • P3. Six quality labels on 30,000 questions (25,451 from 2018–2021), measured with the same rules as the rule-based features: the 385 floor, the same sign in every year, the topic control. The strongest: a worked example goes with +17.2 points of accepted rate, a question about the asker's own setup with −14.9, and both hold among questions with the same code and length.
  • P1 and P2. None: the labels are only P3's.
  • The next labels are cheap. At Jev's measured price, the whole 5% sample (about 422,000 questions) would cost about US$ 54.
Trade-offs
  • The scores are optimistic. The gold set is the reference models' labels with a human check on the ties (0 of 567 changed), so a candidate that labels like Gemini and Grok scores well. The only independent check is the blind practice round, 20 questions, and it scored Claude Opus, not Jev.
  • Every label is one model's reading. A wrong label mixes some "yes" questions into the "no" group and the other way round, which pulls every gap towards zero: the effects are probably understated, not overstated.
  • 30,000 is a sample of a sample. Rare levels (dump code, multiple asks, image-only evidence) reach 385 questions only with the four years pooled, so their stability can't be checked. The first estimate for the whole sample, US$ 400–550, priced Haiku and Luna at an assumed 1,500 output tokens per question; measured, Jev would label all of it for about US$ 54.
  • Jev gives no reasons. It returns a level and its probabilities, not why: enough for labels that are only counted, not for auditing a single question.
  • Nobody sees the images. The image label is judged from the text, by the models and the person alike; 46% of its "yes" labels have no image in the text, so P3 doesn't lean on it.
  • Bigger models went untested. Claude Sonnet or Opus might agree more, but priced on Haiku's tokens Sonnet costs 120–190× Jev per question (about US$ 460–730 for the same 30,000): it would have needed a reason the scores didn't give.
Tools
No BigQuery and no model call behind this dialog: eda/d6_labelers/run_eda.py reads the spend log, the gold labels and the price list, and presentation/eda_charts.py draws the chart from its output. The labeling itself: labels/discover_labels.py (discovery), labels/gold_page.py (the local page for the hand labels and the review), labels/label_questions.py (count, then run --max-usd), labels/agreement.py (kappa per label, every disagreement listed). Python with the anthropic, openai (for the gateway) and typesafe-sdk clients; keys only in a gitignored .env.
GitHub repo

Executive impact of the decision

“Why trust a model's labels, and what would it cost at scale?”

Because the model was picked on a test it didn't write. Two models from other companies drafted a reference set, a person checked it, and three cheap candidates were scored against it. The winner, TypeSafe Jev, agreed best and cost least: 30,000 questions labeled for US$ 3.82, about 13 cents per thousand.

Every call took a priced, capped and logged path: a free estimate before each run, a hard dollar cap during it, a line in the spend log after. All the model calls in the project cost US$ 10.65.

What it costs: the scores are generous, because the reference started from the models' own labels, and 30,000 questions leave the rarest labels thin.

  • “Why not the biggest model?” Claude Sonnet would cost 120–190× more per question, about US$ 460–730 for the same 30,000, to beat an agreement that was already 0.80. A bigger model earns its price where the cheap one fails the test; here it didn't.
  • “Why a gateway?” One key and one client for three companies' models, and every call comes back with what it was billed. Swapping a model is a one-line change, which is what made a fair contest cheap to run. On a team, the next step is a key per project with a spending limit at the gateway.
  • “Can we label everything?” Yes: about 422,000 questions for about US$ 54 at Jev's measured price. The 30,000 were a budget choice; the next check worth paying for is a blind gold set, labeled by hand without the models' suggestions.

{ 08 · tests and safeguards }

From the public dataset to one number

One public dataset goes in; the three answers, the runnable queries and this page's numbers come out. Pick a guard to see where it attaches.Tests are dbt tests (generic, singular and unit); every in specs/ has one. dbt build runs all but those that need the text scan.BigQuery enforces the caps, one per dbt target; each script caps its own queries; a local agent gate asks a person first ().Leakage checks run in dbt: a model computes each question's exposure, and warn tests rebuild the rankings on the original version ().

  1. 01 · source

    bigquery-public-data.stackoverflow

    4 tables (questions, answers, post history, users), frozen at 2022-09-25.

    • snapshot unchanged (warn)

  2. 02 · staging

    4 views, one per table

    Without body and post_history.text, the two columns that cost 36 GB and 109 GB a read.

    • unique keys, every answer has its question
    • answer_count = joined answers; 292 legacy ids (warn)
    • views store nothing; text left out

  3. 03 · the hub

    int_questions

    One row per question, partitioned by year: a 2022 query reads 0.05 GB, not 0.84. Tags, pairs, the 30-day window and exposure sit on top.

    • dev target: 20 GiB per query
    • exposure: edited after the first answer

  4. 03b · read once

    Text scans and quality labels

    Original text and tags (106 GiB) and current body (35 GiB), each read once (). labels/ sends a 30,000-question sample to a language model under a written rubric ().

    • full_scan target: 120 GiB; skipped by the default build
    • graph test: no other model reads the text

  5. 04 · marts

    One prompt each

    P1 tags and pairs, P2 python / dbt, P3 rates and effects, with the floor, intervals and ranks; plus robustness, 30-day and original-version checks.

    • floor, bounds, sort order, Wilson values, unit tests
    • warn: 46 tags, 65 pairs and 3 P2 rows move on the original tags

  6. 05 · semantic layer

    MetricFlow

    Each metric defined once, at question grain, never as a ratio of rates that were already divided.

    • mf query reproduces the README numbers
    • CI: dbt parse, mf validate-configs

  7. 06 · outputs

    Queries, README, this page

    queries/ is generated from the models and inlined down to the public tables. This page reads CSV exports of the marts with DuckDB-WASM.

    • q1 about 9 GB, q2 about 7 GB; this page 0 bytes

Every in specs/ points to its test; the map is TESTING.md. The same diagram is in the README; its keys and schedule are on the next slide.

{ 09 · architecture: tables and pipeline }

How the tables join, and how they would run

The same models as the last slide, one level down: which key joins them, and how an orchestrator would run them if the source were live.

Every join is on a key; the marts group the question key away, and what is left is the grain each prompt asks about. Pick a key to follow it.

01 · source

  • posts_questions

    one row per question

    idaccepted_answer_idowner_user_idtags

  • posts_answers

    one row per answer

    idparent_idowner_user_id

  • post_history

    one row per revision

    idpost_iduser_id

  • users

    one row per user

    id

02 · staging: keys renamed

  • stg_…__questions

    one row per question, no body

    question_idaccepted_answer_idowner_user_idtags

  • stg_…__answers

    one row per answer

    answer_idquestion_idowner_user_id

  • stg_…__post_history

    one row per revision, no text

    post_history_idpost_iduser_id

  • stg_…__users

    one row per user

    user_id

03 · intermediate: the hub

  • int_questions

    one row per question, partitioned by year

    question_idaccepted answerowner_user_id

  • int_question_tags · _tag_pairs

    one row per question and tag (or pair)

    question_idtag

  • int_question_exposure

    one row per question: its exposure ()

    question_id

  • int_post_history_scan

    read once: original title, tags, text features

    question_idoriginal tags

  • int_question_posting_context

    the asker's earlier questions, posting time

    question_idowner_user_id

04 · marts: the grain is the key

  • mart_p1_tags · mart_p1_pairs

    P1: one row per year and tag (or pair)

    creation_yeartag

  • mart_p2_python_dbt

    P2: one row per tag and year

    tagcreation_year

  • int_p3_questions → P3 marts

    P3: features per question, then one row per version, feature, level and years

    question_idfeature · bucket · cohort

  • quality_labels_analysis (seed)

    's 30,000 labels, joined on the question

    question_id

Filled: the table's key (its grain); outlined: a key that joins to another table. Not shown, with the same grain as their mart: the 30-day, original-tag and robustness variants, the semantic views and the label-set views (58 models in all).

A design, not built: the data is a frozen snapshot, and an orchestrator stays out of scope. If the source were live, each box would be an asset in an orchestrator such as Dagster, the dbt models with their tests attached as checks.

The models as an orchestrated pipeline Three lanes. Every day: a sensor on the source tables starts staging, the question hub in daily partitions, the tag models, the P1 and P2 marts with their tests as checks, and the exports that deploy this page. On demand, after a person approves the bytes: the text scans, the original versions with warn checks, and the P3 marts. On demand, under a dollar cap: the quality labels, which feed the P3 marts. every day,on a schedule on demand,approved in GiB on demand,capped in US$ source tablessensor: new data staging4 views int_questionsdaily partitions tag modelspairs, exposure, 30-day P1 · P2 martstests = checks exportsCSV → Pages text scans106 + 35 GiB (D5) original versionswarn checks (D4) P3 martsrates, effects quality labelsPython, --max-usd (D6)
  • Every day. A sensor sees new data land and runs only the new day's partitions down to the P1 and P2 marts; a freshness check replaces today's snapshot test, and every dbt test runs as a check on its asset.
  • Approved in GiB. The text scans never run on a schedule: they wait for a person, as the shell gate does today (); the original versions and P3 follow them.
  • Capped in US$. The labels are a Python asset with a dollar cap per run (); new questions get labeled in small, priced batches.

{ 10 · prompt 1 }

Which tags get answers?

“Which tags get answered and accepted in 2022, and which don't?” The ten highest, text processing and data wrangling, each get an accepted answer on 58.5% to 67.6% of their 2022 questions, against 30.5% for all 2022 questions; the ten lowest, mostly platforms and integrations, on 8.6% to 11.2%. My reading of the tag names, not yet measured: problems anyone can reproduce from the question get solved; problems tied to the asker's own setup don't (an association, not a cause).

  • Highest accepted rate jq 67.6%, awk 66.0%, data.table 66.5%
  • Most answers per question awk 2.45, sed 2.19, grep 1.79
  • Lowest and fewest magento2 8.6%; facebook-graph-api 0.31 per question
  • Pairs the same story, jq + json on top, android + webview at the bottom; the #1 is left out, made by

Accepted rate = accepted ÷ all questions (); most answers = answers per question (); only tags with 385+ questions, ranked by 95% interval ().

{ HOW WE KNOW }

live SQL · DuckDB-WASMloading engine…

P1 · how we know

How the tag lists were built and checked

Question
“What tags on a Stack Overflow question lead to the most answers and the highest rate of approved answers for the current year? What tags lead to the least? How about combinations of tags?”
Data
Questions created in 2022, the latest year in the snapshot (2022-01-01 to 2022-09-25, a partial year): 1,268,788 questions, metadata only (tags, creation date, answer_count, accepted answer id), never title or body. 2021 is the check year. Tags are the ones each question has today. A question counts once under each of its tags, a : one tagged r, dplyr and tidyverse makes three links, and three unordered pairs (dplyr + r, dplyr + tidyverse, r + tidyverse). The links and pairs are int_question_tags and int_question_tag_pairs; the lists are mart_p1_tags and mart_p1_pairs (data model).
Method
Three rates per tag (): accepted rate = questions with an accepted answer ÷ all questions, the ranking metric for “the highest rate of approved answers”; answer rate = answered ÷ all questions; accepted among answered = accepted ÷ answered. They multiply back (accepted rate = answer rate × accepted among answered), so each row shows whether a tag loses at getting answers or at getting them accepted. “Most answers” is read as answers per question = answers ÷ questions (); a plain total of answers mostly measures how big a tag is. Only tags with 385+ questions in the year are ranked (): the smallest n whose worst-case 95% margin is ±5 points, (1.96 × 0.5 ÷ 0.05)² = 384.2, rounded up. That leaves 1,162 of 42,627 tags (2.7% of tags, 76% of question–tag links) and 1,026 pairs. Each group then gets a 95% interval: Wilson for the two rates, mean ± 1.96 standard errors for answers per question. “Highest” lists sort by the interval's lower end, “lowest” lists by its upper end, so a tag makes a list only if it is high (or low) and well measured: jq (67.6%, 824 questions) ranks above tidyr (66.5%, 412). How the Wilson range is computed, why the lists sort by its ends and why Wilson rather than the plain ±: . Every row also carries its tag exposure (): the share of its questions whose tags were first edited after the first answer arrived. The 30.5% baseline is all 2022 questions: 386,683 accepted of 1,268,788.
Evidence
  • Two other rankings agree. The raw accepted rate (same floor) and empirical Bayes (each accepted rate pulled toward the average, harder the fewer questions a tag has) share 9 of 10 names at the top and 10 of 10 at the bottom with the tag lists; for pairs, 9–10 at the top and 8 at the bottom. For answers per question, a bootstrap interval changed nothing. How each check works: .
  • 2021 as a check year. 8 of the 2022 top 10 tags were in 2021's top 10, and 4 of the bottom 10 in its bottom 10 (pairs: 3 and 6, since a pair near the floor drifts in and out). No tag or pair jumps from a top 10 to a bottom 10. purrr and stringr, 2021's #1 and #3, are missing in 2022 because they fell under the floor, not because their accepted rate dropped.
  • Time to collect answers. A question from late September 2022 had only days to get answered before the snapshot (trap 3), so it reads as unanswered just from timing. The question this check answers: would the lists change if every question had the same time? int_question_window30 marks each 2021–2022 question that had a full 30 days before the snapshot and counts only the answers, and the accepted answer, that came within them; mart_p1_tags_30d and mart_p1_pairs_30d rerun the same floor, ranges and ranks on those questions (data model). 1,020 of the 1,162 tags still reach 385 questions; 9 of the top 10 and 6 of the bottom 10 by accepted rate stay (answers per question: 9 and 7). So the top is not a timing effect; the bottom moves more because its tags are smaller and sit nearer the floor.
  • Interval shape. The answers-per-question interval (mean ± 1.96 standard errors) assumes the average behaves like a bell curve, which a skewed count can break: one viral question with 37 answers drags the mean of ios + xcode. Cochran's rule (n > 25 × skewness²) flags where that may happen: 6 of 1,162 tags and 8 of 1,026 pairs in 2022. For those, the interval may be a little too narrow (in a simulation they still covered 92–94%, against 95%), so their place is a bit less sure than it looks. The consequence for the answer is none: not one is in a top or bottom 10, and the bootstrap, which makes no bell-curve assumption, gives the same lists (). The flag stays on every row so a reader can see it.
  • Tag edits. Every 2022 ranking was rebuilt on the original tags, as the asker posted them. 46 tags and 65 pairs enter or leave a list or move more than 2.5 points. Tags: 9 of 10 stay in both accepted-rate lists (tidyr and usb fall under the floor). Pairs: 7 of 10 at the top, 8 at the bottom; arrays + google-sheets (exposure 64%) falls under the floor, so it is not quoted.
Guard
The ten acceptance criteria of specs/001, each checked by a dbt test; each in plain words with its test: . Where the tests run: architecture.
  • , , : only 2021 and 2022; every ranked row has 385+ questions (also in the 30-day and original-tags versions); rates between 0 and 1, the count beside each.
  • : the 2021 ranking is built and its rank changes in the top and bottom lists are listed (mart_p1_rank_flips); the 2021 check under Evidence reads it.
  • , : on every row, accepted rate = answer rate × accepted among answered.
  • , : the Wilson bounds match reference values (9/10, 850/1,000, 1/1, 0/1); highest lists sort by the lower bound and lowest by the upper; lower ≤ rate ≤ upper.
  • , : the raw-rate and empirical-Bayes rankings and their overlap; Cochran's flag on every answers-per-question row, and a unit test on two hand-computed tags (3 ± 1.96 = 1.04 to 4.96, skewness 1.5, flagged).
  • : tag exposure on every row, and the original-tags ranking as a warn test that lists the 46 tags and 65 pairs above without failing the build.
  • Semantic layer: MetricFlow defines the metrics once, and a check script reproduces the README's numbers from it (1,162 tags, 1,026 pairs, 30.5%).
Cost
queries/q1.sql, the whole answer in one script for the BigQuery console, bills about 9 GB (8.92 GiB in a fresh sandbox): metadata columns only, never body. The dbt models run on the everyday target, each query capped at 20 GiB. The original-tags check reads a stored table from the one-time 106 GiB read of post_history.text, shared with P3 (). This page reads 0 bytes from BigQuery: the two marts were exported to CSV (the tags on 2026-11-01; the pairs on 2026-11-04, for 10 MiB, BigQuery's minimum per query), and DuckDB-WASM runs the SQL in the browser (stack).
Limitations
  • Association, not cause. A tag labels a kind of problem; adding jq to a question would not get it answered.
  • Lists overlap. A question counts under each of its tags, so most dplyr questions are also r questions, and dplyr + r is close to dplyr.
  • 2022 is partial (to 09-25), and its newest questions are censored; the 30-day check above sizes it.
  • Survivorship. Deleted questions are not in the data, and Stack Overflow deletes mostly unanswered, low-score ones, so every accepted rate reads high, probably more at the bottom (the traps).
  • Today's tags, because the prompt asks how a question is tagged; exposure and the original-tags check measure the edits, they don't remove them.
  • Pairs only. Triples and exact tag sets rarely reach 385 questions. The rest of the limits: limits slide.
GitHub repo

{ 11 · prompt 2 }

Python-only vs dbt-only, year over year

“How did the answer ratio and accepted rate of python-only and dbt-only questions change, 2013 to 2022, and how do they compare?” python-only questions from 2013 got 2.31 answers each, those from 2021 got 1.52 (0.10 fewer a year); their accepted rate fell from 65.2% to 43.0% (2.8 points a year). dbt-only questions, from 2020 on, got fewer answers in 2021 and 2022 (1.06 vs python's 1.26 per 2022 question). The pattern: a slow, steady python decline, with dbt below it wherever the data can tell.

  • Ratio python-only 2.31 → 1.52 answers per question, 2013 → 2021
  • Accepted rate python-only 65.2% → 43.0%, 2013 → 2021
  • python vs dbt dbt-only got fewer answers in 2021 and 2022
  • 2022, to 2022-09-25 1.26 and 35.4%, mostly questions not answered yet

Ratio = answers per question, questions per answer = 1 ÷ it (); accepted rate = accepted ÷ all questions (); “only” = that one tag; 95% intervals, no floor ().

{ HOW WE KNOW }

live SQL · DuckDB-WASMloading engine…

P2 · how we know

How the python and dbt years were built and checked

Question
“For posts which are tagged with only 'python' or 'dbt', what is the year over year change of question-to-answer ratio for the last 10 years? How about the rate of approved answers on questions for the same time period? How do posts tagged with only 'python' compare to posts only tagged with 'dbt'?”
Data
Questions created 2013 to 2022 (2022 runs to 2022-09-25, a partial year), metadata only (tags, creation date, answer_count, accepted answer id), never title or body. “Only” is read literally: the question's tags today are exactly python or exactly dbt, one tag and nothing else, so python|pandas and python-3.x are out. That leaves 7,819 to 16,256 python-only questions a year and 31, 58 and 79 dbt-only ones (2020 to 2022; none before), 168 in total. Each answer counts in its question's creation year. The questions come from int_questions; the answer is mart_p2_python_dbt, one row per tag and year, 20 rows (data model).
Method
The question-to-answer ratio is shown as answers per question = answers ÷ questions (), for three reasons: higher reads as more answers, it is the same metric as P1's “most answers”, and it splits like the accepted rate does (answer rate × answers per answered question). The literal ratio is its inverse: questions per answer = 1 ÷ answers per question (python-only 0.43 in 2013, 0.79 in 2022). Each year gets a 95% interval, mean ± 1.96 standard errors. The accepted rate = questions with an accepted answer ÷ all questions (), with a Wilson interval, and the answer rate and accepted among answered beside it, so a fall can be read as “fewer answered” or “fewer accepted”. Year over year: answers per question as an absolute change (−0.26 for python 2021 → 2022), the relative change beside it; relative changes don't survive the inversion (the same step is −17% in answers per question and +21% in questions per answer). Accepted-rate changes are in percentage points. Nothing is ranked, so 's 385-question floor does not apply, but every year carries the same kind of 95% interval, and a difference is claimed only where the intervals don't touch (). Every row also carries its tag exposure (), the share of its questions whose tags were first edited after the first answer: python-only 3.6–5.1% a year, dbt-only 0–3.2%. One macro, p2_python_dbt, builds the mart and its three check versions, so their rules cannot drift apart.
Evidence
  • Censoring in 2022. A question from late September 2022 had only days to collect answers before the snapshot (trap 3). The question: is the 2021 → 2022 drop a real change? Answers per question = answer rate × answers per answered question, both on mart_p2_python_dbt: for python-only the answer rate fell from 84.8% to 73.5% while answers per answered question barely moved, 1.79 → 1.71, so about three quarters of the drop is questions not answered yet. Same on acceptance: the accepted rate fell 7.6 points, accepted among answered only 2.6 (50.7% → 48.1%) (eda/d3_ratio, eda/d2_denominator). So the last yearly step is mostly timing, not a trend, and the slide quotes 2013 → 2021 for the decline.
  • Survivorship. Deleted questions are not in the data, Stack Overflow deletes mostly unanswered ones, and older years have had longer to be cleaned up (the traps). So 2013's 2.31 and 65.2% are inflated more than 2021's. The direction holds: 7 of the 9 yearly steps go down with intervals that don't overlap, and 2018 and 2019 are flat. The size of the decline is less sure than its direction, and this data cannot size the bias.
  • dbt among other tags. With 31 to 79 questions a year, dbt-only's accepted-rate intervals are 19 to 33 points wide. The question: is the small sample a fluke? mart_p2_dbt_any_position applies the same rules to every question tagged dbt among other tags (96, 224 and 351 questions): 1.46 → 1.17 → 0.93 answers per question and 49.0% → 41.5% → 32.5% accepted, the same direction. So the dbt-only numbers are not an accident of size, but this is a different population and does not replace the literal answer.
  • Tag edits. The question: would the answer change on the tags as first posted? mart_p2_python_dbt_original rebuilds both groups on the original tags, and a warn test lists 3 rows that move: python 2019 (a near-zero yearly change, +0.01 vs −0.02, switches sign), python 2021 (1.52 vs 1.50; 17,104 questions instead of 15,811, posted as python-only and retagged later), dbt 2022 (accepted change +2.0 vs −0.6 points, inside its interval either way). mart_p2_python_dbt_no_tag_edits drops every question whose tags were ever edited: python 2022 reads 1.25 instead of 1.26 and 35.2% instead of 35.4%. So no conclusion on the slide changes.
Guard
The six acceptance criteria of specs/002, each checked by a dbt test or the check script; each in plain words with its test: . Where the tests run: architecture.
  • : only questions tagged exactly python or dbt count; a unit test, mart_p2_exact_tag_match, feeds in python, python|pandas, python-3.x, dbt and dbt|sql and expects only the first and the fourth.
  • : one row per tag and year, 2013 to 2022, with the empty dbt years kept as rows, on the mart and its three versions.
  • : every dbt rate comes with its question count and an interval.
  • : 2022 is marked partial, on the mart and its three versions.
  • : answers per question is the charted ratio and questions per answer is exactly 1 ÷ it; the check script asks the semantic layer (MetricFlow) for the README's numbers and compares them.
  • : tag exposure on every row, and the original-tags check as a warn test that lists the 3 rows above without failing the build.
Cost
queries/q2.sql, the whole answer in one script for the BigQuery console, bills about 7 GB: metadata columns only, never body. In dbt the mart reads the question table and billed 1.15 GiB, on the everyday target, where each query is capped at 20 GiB. The original-tags version reads a stored table from the one-time 106 GiB read of post_history.text, shared with P1 and P3 (). This page reads 0 bytes from BigQuery: the mart was exported to CSV on 2026-11-01 and DuckDB-WASM runs the SQL in the browser (stack).
Limitations
  • “Only” read literally. Most Python questions carry more than one tag, so python-only is a small, particular group; “python among other tags” would answer a different question.
  • 2022 is partial (to 09-25) and its newest questions are censored, so its yearly step is mostly not a trend (Evidence).
  • Survivorship inflates older years more than recent ones; the decline's size is less sure than its direction.
  • Today's tags, because the prompt asks how a post is tagged; exposure and the original-tags check measure the edits, they don't remove them.
  • dbt has 2.7 years, not ten, on 168 questions in total: read its intervals, not its single values; no dbt trend is claimed.
  • A trend, not a cause. The data shows that python-only questions get fewer answers each year, not why. The rest of the limits: limits slide.
GitHub repo

{ 12 · prompt 3 }

What else goes with an answer?

“Other than tags, what qualities of a question go with an answer and an accepted answer?” A worked example (input and wanted output, both written out): 60.0% of the labeled 2018–2021 questions with one got an accepted answer, against 42.8% of those without. One about the asker's own setup (installs, versions): 32.2%, against 47.1%. The pattern is P1's, without the tags: problems anyone can reproduce from the question get solved; problems tied to the asker's own setup don't (an association, not a cause).

  • Highest accepted rate worked example +17.2 points, 3+ code blocks +14.5
  • Lowest own setup −14.9, a broad ask −9.5, bloated code −7.1
  • Answer rate moves less worked example +9.4, own setup −10.8
  • Barely matter posting hour, weekday, “thanks in advance”: within 1.8

Accepted rate = accepted ÷ all questions (); points = a group's accepted rate minus its reference group's; text as first posted (); groups of 385+ (); labels: one model, 25,451 questions ().

{ HOW WE KNOW }

live SQL · DuckDB-WASMloading engine…

P3 · how we know

How the qualities were measured and checked

Question
“Other than tags, what qualities on a post correlate with the highest rate of answer and approved answer? Feel free to get creative.”
Data
Every question created from 2018 to 2021, four full years: 7,157,196 questions. 2022 is computed too, but it is partial (to 2022-09-25) and censored, so no claim rests on it. Every text feature is measured on the text as the asker first posted it, from post_history, not on today's text (): one read of post_history.text into int_post_history_scan, plus one of today's body into int_question_body_scan for the comparison. Everything else is known at posting time: when it was posted, and the asker's account age and earlier questions. Score, views, comments and later edits are out, because they partly happen because of the answer. The labels cover 30,000 questions drawn at random from a 5% text sample, 25,451 of them from 2018–2021. Rules: int_p3_questions → mart_p3_feature_rates and mart_p3_feature_effects; labels: a seed → int_p3_label_questions → mart_p3_label_rates, mart_p3_label_effects, mart_p3_label_vs_rules, mart_p3_label_incremental (data model).
Method
Two kinds of quality. Rules count something on every question: code blocks, inline code, length, links, images, error text, a greeting, “what I tried” phrases, the title, the number of tags, the hour and weekday (UTC), the asker's account age and earlier questions. Labels are what only a reader can judge, defined in a written rubric: worked example, scope of the ask, failure description, code focus, setup dependence, and key evidence only in an image. One model, TypeSafe Jev, read the 30,000 questions under that rubric; how it was picked over two other models, and its cost: . Each quality is cut into fixed buckets, and the first is the reference (no, zero, shortest, a first question, Monday, 00–05 UTC). The gap is a bucket's accepted rate (or answer rate) minus the reference's, in points, with its 95% margin. Three measures, as in P1 (): accepted rate = accepted ÷ all questions, answer rate = answered ÷ all questions, and accepted among answered, so each gap shows whether a quality helps a question get an answer or get one accepted. For comparison, all 2018–2021 questions: 44.3% accepted (3,170,421 of 7,157,196), 80.7% answered. Same 385-question floor as P1 for every bucket (). Stable = the same sign in each of 2018, 2019, 2020 and 2021 on its own. Two controls: inside tags, the gap within each of the 20 most common primary tags (plus “other”), averaged by the bucket's questions; and for labels, same rules, the gap within groups with the same “has a code block” and body-length bucket, which shows what a label adds beyond what the rules already count.
Evidence
  • Every year. The question: does a gap hold in each year, or only when the four are pooled? mart_p3_feature_effects and mart_p3_label_effects recompute every gap per year. 37 of the 43 rule gaps keep their sign in all four; the other 6 are within 0.2 points of zero (Tuesday to Thursday, 40–59-character titles, three tags, 4,000+-character bodies). Worked example, own setup and partial code are stable in all four years. A level needs about 6% of the questions to reach 385 in a single year, so a broad ask reached the floor in 2 of 4 (negative both times), and dump code, multiple asks and image-only evidence only on the four years pooled: their stability is not assessable, which is not the same as “flips”. So no claim on the slide rests on a gap that changes direction.
  • Is it the tags in disguise? The question: is “has code” just “is a Python question”? The same marts carry each gap inside the 20 largest primary tags. Every gap keeps its sign and most of its size: the rules move at most 1.2 points, the labels at most 2.0 (image-only evidence, −6.5 → −8.5, the label not to lean on). Worked example +17.2 → +16.6, own setup −14.9 → −13.3, a code block +11.8 → +11.3. So the qualities are not the tags under another name, which is what the prompt asks for.
  • Beyond the rules. The question: does a label add anything a rule doesn't already count? mart_p3_label_vs_rules sets each label beside its closest rule, and mart_p3_label_incremental recomputes its gap among questions with the same code-block flag and body length. 82% of worked examples have a code block, but only 12% of questions with a code block are worked examples, and it keeps 90% of its gap (+15.5). Own setup keeps 83% (−12.3; only 27% of those questions have error text), partial code 81%, a broad ask 63%, multiple asks 50%. Image-only evidence keeps 26% (−6.5 → −1.7), and 46% of its “yes” questions have no image at all in the original text, which the rubric doesn't allow. So the two headline labels are mostly new information; image-only evidence is the one label not to lean on.
  • Did edits plant them? The question: did an edit after the answer add the quality (someone asks “please show your code”, the asker pastes it)? Every gap is also computed on today's text, on never-edited questions and on questions not edited after their first answer (mart_p3_leakage measures how often a bucket changed). On never-edited questions every quality keeps its sign and moves at most 2.4 points (a worked example grows to +19.6). A code block is +11.8 on the original text and +11.3 on today's, where about one question in 15 changed bucket; the biggest move among the rules is an image, +4.7 → +2.3. The warn test raises one warning: five or more tags on accepted among answered (−0.3 original, +1.5 today). So reading the original text changed little here, but it is the version that can't be the answer's doing ().
  • How good are the labels? Jev was scored on 100 reference questions labeled by two other models, with my review of every label: I changed 0 of the 567 labels they agreed on and broke the 33 ties. That is the models' labels with a human check on the ties, not independent hand labels, so Jev's kappa (0.71 to 0.98 by label, mean 0.80) is optimistic. The one blind human check is a 20-question practice round labeled by hand before any model, which checked the rubric, not Jev. Jev never sees whether a question was answered, so a wrong label mixes the groups and pulls the gap towards zero: the +17.2 and −14.9 are probably understated ().
  • And the answers? Answers are posts too: among the 423,192 answers to the sample's 2018–2021 questions, by the answer's own original text, inline code goes with +13.0 points of acceptance and 4,000+ characters against under 500 with +16.2. That mart (mart_p3_answer_rates) is not exported to this page, so it is stated here, not queryable.
Guard
The eight acceptance criteria of specs/003, each checked by a test; each in plain words with its test: . Where the tests run: architecture.
  • : every feature is known at posting time: account age and earlier questions come from posting-time metadata (int_question_posting_context), with a warn range test on the account age.
  • : the text columns are read once: assert_text_columns_read_once reads dbt's project graph and fails if any model other than the staging view and the one-time scans reads them.
  • : a gap that doesn't point the same way every full year is labeled unstable: the stability flags on both effects marts, and two unit tests (a level with only 3 full years at the floor reads “not stable”).
  • : text features come from the original text, read once, its size stated: the two scan models behind the full_scan tag, never in a default build, and two tests with hand-written examples of every rule.
  • : every gap is also reported on today's text, never-edited and not-edited-after-the-answer questions: the four versions on mart_p3_feature_rates and mart_p3_label_rates, a flips-vs-original column, and the check script.
  • : text exposure and the share of changed values are reported, and a direction change warns: mart_p3_leakage and the warn test assert_p3_original_text_stable (the one warning above).
  • : each label is set beside its closest rule, and one that doesn't line up is flagged: mart_p3_label_vs_rules (image-only evidence).
  • : each label level's gap is reported overall and inside the rules' groups, and rare levels say how many full years reached the floor: mart_p3_label_incremental, full_years_with_effect, a unit test, and the check script (worked example +17.2, rebuilt through the semantic layer).
  • The labels themselves: the seed covers exactly the 30,000-question analysis set, every level is one the rubric allows, and the label layer accounts for every labeled question.
Cost
The original text came from one 106 GiB read of post_history.text, plus 35 GiB for today's body, each run once into a stored table on its own target, capped at 120 GiB a query; every other model runs on the everyday target, capped at 20 GiB (). queries/q3.sql reads those stored tables and the label seed, about 38 GiB for its eight results, so it is not as-is like q1 and q2: the tables have to be built first (steps in its header). Labeling 30,000 questions with Jev cost US$ 3.82, about US$ 10.65 for every model call in the project (). This page reads 0 bytes from BigQuery: the six marts were exported to CSV on 2026-11-01, and DuckDB-WASM runs the SQL in the browser (stack).
Limitations
  • Association, not cause. This is observational data: nobody assigned the qualities at random, and the askers who write a worked example also differ in other ways (more experience, more care, a problem that is easier to show). The 95% margins say a gap is not noise, and with millions of questions nearly every gap clears that bar; they say nothing about whether the quality causes the acceptance. So writing out the expected output would not by itself get a question accepted; the questions that do it get accepted more. What it would take to get closer to cause: limits slide, “A causality study”.
  • One quality at a time. A worked example and an asker with 20 earlier questions can be the same question, and nothing here nets them out against each other. A logistic regression would (optional in specs/003, not built), but its result would still be an association.
  • Asker experience is about the person, not how the question is written; it is known at posting time, so it is in.
  • Survivorship. Deleted questions are not in the data, and the worst-written ones are the most likely to be gone, so the gaps are probably smaller than the real ones (the traps).
  • Hours are UTC, not the asker's local time; 2022 is out of the claims; each gap is inside one year, which mostly cancels censoring, so P3 has no 30-day version.
  • Labels: a random 30,000, original text only, no images seen (neither by Jev nor in the review), not stratified, so rare levels stay rare; the answers were not labeled. The rest of the limits: limits slide.
GitHub repo

{ 13 · quality and cost }

Tests, caps, and what it cost

Quality

  • 218

    dbt tests: 197 generic, 16 singular and 5 unit tests on hand-written rows. 74 name the they check; the rest guard keys, nulls and ranges on the models in between. TESTING.md maps every criterion to its test and says where a gap is.

  • warn

    is the severity of the leakage impact checks: they never fail the build, they list what moves on the original version (P1: 46 tags and 65 pairs out of 1,162 tags and 1,026 pairs; P2: 3 rows out of 20 rows), and the README discusses each.

  • 0 credentials

    in CI. GitHub Actions runs dbt parse, mf validate-configs --skip-dw and the Python checks on every push, without touching the warehouse.

Cost

  • 3 layers

    kept BigQuery in check. A byte cap per dbt target (dev 20 GiB, full_scan 120 GiB per query), a 10 GB dry-run limit in every EDA script, and a local coding-agent hook (kept out of the repo) that dry-runs each query, asks before a big one or any dbt run, and asks whenever it can't estimate.

  • 511 GiB

    billed in BigQuery for the whole project, by its own job log: 45% of October's free 1 TB, US$ 0. The text reads are 247 GiB of it (post_history.text twice, after a rule fix; posts_questions.body once); every other query reads metadata only. How it was planned: , slide 7.

  • US$ 10.65

    of language-model calls, logged call by call: US$ 3.86 Anthropic (out of US$ 100 of promotional credits), US$ 2.96 Vercel AI Gateway, US$ 3.83 TypeSafe Jev (from the logged tokens; each call rounds to almost nothing in the log). How the labeler was chosen: , slide 7.

{ 14 · limits }

What this does not show

Limits

  • Association, not cause. A tag or a quality goes with answers; nothing here shows that adding it to a question would help (what it would take: next column).
  • 2022 is partial and censored. The 30-day window and accepted among answered are the checks, not a fix.
  • Survivorship. Deleted questions are missing, so every rate reads high, older years more, by an amount this data can't size.
  • Literal populations. "Only python" means exactly one tag, and dbt-only is 168 questions in 2.7 years: read its intervals, not its points.
  • Edits. Exposure and the original-version checks measure the leakage; they don't remove it.
  • Model labels. P3's quality labels come from a language model, scored against a gold set I reviewed starting from two other models' labels, so the agreement is optimistic (slide 12, ).

What I'd do next

  • A causality study. A logistic regression with tag, year and asker experience as controls would net the qualities against each other, but not what isn't measured. Comparing questions from the same asker is stronger; only a randomized test, which only Stack Overflow could run, shows cause.
  • CI and orchestration. dbt build on every push needs a cloud identity on the runner and a byte budget per run (left out on purpose); Dagster once the source is live, with source freshness in place of the snapshot test.
  • A blind gold set. Hand labels made without the models' suggestions, as the independent check on the labeler.
  • Labels on the whole text sample. About 422,000 questions instead of 30,000: about US$ 54 at Jev's measured price ().
  • Qualities per tag. With those labels, test P1's reading: do its top tags have more worked examples, and its bottom ones more questions about the asker's setup?
  • Hosted dbt docs. The lineage graph online; it needs a warehouse connection for the catalog.

{ 15 · how it was built }

Built with an AI assistant

I wrote the first and built the rest with Claude Code as a pair programmer, which is why every commit carries a co-author line. The choices are mine, and the decisions log shows each one next to the EDA that backed it.

The language-model calls of P3 cost about US$ 10.65 in all, logged call by call in labels/spend_log.csv: US$ 3.86 on Anthropic (out of US$ 100 of promotional credits), US$ 2.96 on the Vercel AI Gateway and US$ 3.83 on TypeSafe Jev (worked out from the logged tokens, because each Jev call rounds to almost nothing in the log). BigQuery ran in the free sandbox, behind the byte caps of slide 13.

Stack Overflow content: (c) Stack Exchange Inc. and its contributors, licensed CC BY-SA 4.0.

specs · acceptance criteria

Every acceptance criterion and how it is checked

What
Each prompt got a spec before any SQL: its definitions, then numbered acceptance criteria (AC-x.y), the rules a result must meet before it is quoted. Four specs hold 35 of them: 000 for the definitions all three prompts share, then one per prompt. TESTING.md maps each criterion to its test. 34 are checked by a dbt test or a check script; was measured once in an EDA instead. The specs: , , , .
Kinds
  • generic a dbt check declared on a column in a model's YAML (unique, not null, a range, an expression that must hold on every row).
  • singular a SQL file in tests/ that returns the rows that break the rule; no rows, pass.
  • unit hand-written input rows and the rows the model must return; reads no table, costs nothing.
  • check script tests/semantic/check_semantic_layer.py asks MetricFlow for the README's numbers and compares them.
  • page check presentation/check_numbers.py recomputes this page's headline numbers from the committed files, in CI and before every deploy.
  • model the criterion is a table the project builds (a list, a column), checked by the tests beside it.
  • EDA measured once in an eda/ script, not a dbt test.
  • warn flags rows for the README to discuss; never fails the build. after the scan reads the one-time text scan, so it runs on its own: dbt build --select tag:needs_full_scan.
Size
218 dbt tests on 58 models, about 3.8 a model, on the baseline dbt Labs recommends: every model that changes what one row means has a key test (unique, never empty), and tests sit on the columns a model creates, not on columns it passes through. 74 name the acceptance criterion they prove; the rest are those structural checks. Unit tests (5) only where the logic is easy to get wrong, as dbt advises, and 5 warn tests, few enough that a warning gets read. The semantic views have none: each reads one keyed model and joins one to one, and the check script compares their numbers. dbt Labs' guidance: which tests, where they go.
000 · shared

Shared definitions and data profile · its tests

The three prompts use the same words: accepted rate (), answers per question (), an edited question. A word defined three times drifts into three numbers. This spec fixes those words once and profiles the 2022-09 snapshot with its known gaps. It also defines exposure (), the measure of how far later edits could reach a result. Its criteria check the data itself: one id per question, no orphan answers, and accepted rate splitting exactly into answer rate × accepted rate among answered.

  1. AC-0.1

    Every question has one id, never empty.

    generic unique and not null on the questions view (staging YAML).

  2. AC-0.2

    Every answer points to a question that exists.

    generic not null and a relationship test on the answers view (same YAML). No orphan answer.

  3. AC-0.3

    Every accepted answer belongs to its own question, and any exception is reported.

    singularwarn assert_accepted_answer_belongs_to_question: warns on the 292 legacy ids from 2008–2012, fails above that.

  4. AC-0.4

    The stored answer count matches the answers actually joined, so a gap can't change a result unseen.

    singular assert_answer_count_matches_joined_answers. No gap.

  5. AC-0.5

    The three rates multiply back on every row (): accepted rate = answer rate × accepted rate among answered.

    genericcheck script an expression test on the P1 and P2 marts (also the 30-day marts and P3's rates); the check script recomputes it.

  6. AC-0.6

    Answers per question is total answers ÷ total questions; questions per answer is its inverse ().

    genericcheck script an expression test on the P1 and P2 marts; the check script, on every python and dbt year.

  7. AC-0.7

    “Never edited”, read from the last-edit date, agrees with the edit history ().

    EDA not a dbt test: measured once in eda/d4_edits, 32 differences out of 637,996 edited questions.

  8. AC-0.8

    Every result row shows its exposure: the share of its questions edited after the first answer arrived ().

    modelgeneric built once in int_question_exposure, a column on every P1 and P2 row and a metric in the semantic layer; not null on the P1 marts (0–1 on the tags mart) and on the per-question flags. The P2 mart's column has no test.

  9. AC-0.9

    Exposure counts an edit only if it changed the field the prompt reads and came after the first answer ().

    unit exposure_counts_only_edits_after_the_first_answer: an edit before the first answer, a tag edit for P3 and a never-answered question are not exposed; a rollback after the first answer is.

  10. AC-0.10

    Each leakage impact check is a warn test, and the README lists every warning ().

    singularwarnafter the scan assert_p1_original_tags_stable and assert_p2_original_tags_stable; P3's is under .

  11. AC-0.11

    Every headline number on this page equals its source, recomputed from the committed files.

    page check check_numbers.py reads each tagged number's source (the exported marts, the EDA results, the job and spend logs, dbt's manifest) and fails CI and the deploy on a mismatch; it also lists, as a warning, any other number found in no source and not in the README.

001 · P1

Tag answerability · its tests

“Which tags lead to the highest accepted rate” has no answer until two things are decided (): how small a tag can be and still be ranked, and how to rank an accepted rate () measured on few questions. This spec fixes the population (2022 questions, with 2021 as the check year), the 385-question floor, the ranges that order the highest and lowest lists, and two other rankings to compare against. Its criteria make sure every listed tag or pair followed those rules.

  1. AC-1.1

    Only 2022 questions are ranked (2021 is the check year), and the cutoff date is printed with the result.

    generic creation year is 2021 or 2022 on both marts; queries/q1.sql prints the newest question's date.

  2. AC-1.2

    Every ranked tag or pair has 385+ questions in its year ().

    genericcheck script the count is at least 385 on both marts and their 30-day and original-tags versions; the check script finds 1,162 tags and 1,026 pairs in 2022.

  3. AC-1.3

    Every rate is between 0 and 1, with its question count beside it.

    generic a 0–1 range on the rate columns, not null on the count.

  4. AC-1.4

    A 2021 ranking is built with the same floor (), and the rank changes in the top and bottom lists are listed.

    modelgeneric the list is the model mart_p1_rank_flips; a test checks its metric names.

  5. AC-1.5

    The Wilson ranges () match hand-worked reference values (9/10, 850/1,000, 1/1, 0/1).

    singular assert_wilson_reference_values; reads no table.

  6. AC-1.6

    “Highest” lists sort by the low end of the range, “lowest” lists by the high end ().

    singulargeneric assert_p1_rank_order; low end ≤ rate ≤ high end on both marts.

  7. AC-1.7

    Two other rankings (the raw accepted rate and empirical Bayes) are built (), and their overlap with the lists is reported.

    generic expression tests on the two robustness marts and the overlap table.

  8. AC-1.8

    Every accepted-rate row also shows the answer rate and the accepted rate among answered ().

    generic the same test as .

  9. AC-1.9

    Every ranked row shows its tag exposure, and the ranking is rebuilt on the original tags ().

    modelsingularwarnafter the scan the exposure column; assert_p1_original_tags_stable lists the 46 tags and 65 pairs that move.

  10. AC-1.10

    Answers-per-question lists sort by the ends of mean ± 1.96 standard errors (), the same rule as the accepted-rate lists (), and every row carries Cochran's flag for a skewed count.

    unitgeneric mart_p1_answers_interval: a tag with 2, 2, 2, 2 and 7 answers must come out as 3 ± 1.96 = 1.04 to 4.96, skewness 1.5, flagged; an even tag at 2.0 to 2.0 ranks first in both answers lists despite its lower mean. The ends are also checked by 's test, and the flag matches eda/d1_answers_ranking (6 tags and 8 pairs flagged, none in a list).

002 · P2

python-only vs dbt-only, year over year · its tests

“Tagged only python or dbt” and “question-to-answer ratio” can each be read two ways, and dbt has few questions a year. This spec settles that: an exact tag match, one row per tag and year from 2013 to 2022 with 2022 marked partial, and answers per question as the ratio (). Every dbt number carries a range (), so a small year can't pass for a trend.

  1. AC-2.1

    Only questions tagged exactly python or exactly dbt count; python|pandas, python-3.x and dbt|sql don't.

    unit mart_p2_exact_tag_match, with those five tag strings as input.

  2. AC-2.2

    One row per tag and year, 2013–2022, years with no question kept.

    genericunit one row per (tag, year) on mart_p2_python_dbt and its three sensitivity versions; the same unit test.

  3. AC-2.3

    Every dbt rate comes with its question count and a range: Wilson for the accepted rate (), mean ± 1.96 standard errors for answers per question ().

    generic not null on the count, an expression test on the range.

  4. AC-2.4

    2022 is marked partial.

    generic an expression test on the partial-year flag, on the mart and its three versions.

  5. AC-2.5

    Answers per question is the charted ratio; questions per answer is 1 ÷ it ().

    genericcheck script an expression test on the mart; the check script, as in . The sentence itself is README text.

  6. AC-2.6

    Every row shows its tag exposure, and both populations are rebuilt on the original tags ().

    modelsingularwarnafter the scan the exposure column; assert_p2_original_tags_stable lists the 3 rows that move.

003 · P3

Non-tag qualities of a post · its tests

“Other qualities” is open-ended, and the easy features are known only after the fact. An edited title or an answer already in are examples. This spec allows only what is known when the question is posted, read from the text as first posted (), in one priced read (). It also sets how an effect is measured, when it counts as stable (the same direction in every full year from 2018 to 2021), and how the language-model quality labels () are checked against the rule-based features.

  1. AC-3.1

    Every feature is known at the moment the question is posted ().

    modelgenericwarn the features with no text come from posting-time data in int_question_posting_context; account age is never negative (warn). The text features: .

  2. AC-3.2

    The question text is read once; no other model reads it ().

    singular assert_text_columns_read_once reads dbt's project graph and fails if any other model reads the text tables; reads no table.

  3. AC-3.3

    An effect that doesn't point the same way in every full year, 2018 to 2021, is labeled unstable; a year below the 385 floor doesn't count ().

    genericunit the stability flags on the effects mart; two unit tests (a level with only 3 full years above the floor is “not stable”).

  4. AC-3.4

    Title and body features come from the text as first posted (), read once, and the size of that read is stated ().

    modelsingular the two scan models, outside the default build; assert_text_feature_rules and assert_p3_bucket_rules hold hand-written examples of every rule and read no table. The size is README text.

  5. AC-3.5

    Each effect is also reported on the current text, on never-edited questions and on questions not edited after the first answer ().

    genericcheck script the four versions are the only allowed values on the rates marts; a flip column on both effects marts; the check script rebuilds a few counts.

  6. AC-3.6

    How many questions had their text edited, and per feature the share whose value changed; a change of direction warns ().

    genericsingularwarnafter the scan not null on both shares of mart_p3_leakage; assert_p3_original_text_stable.

  7. AC-3.7

    Each quality label () is set beside the rule-based feature closest to it, and a label that doesn't line up is flagged.

    modelgeneric mart_p3_label_vs_rules, one row per label, level and rule value, both shares between 0 and 1. The flag is README text read from it.

  8. AC-3.8

    Each label's effect () is reported overall and inside groups of the closest rule-based features, so the README can say which labels add something; rare levels say so ().

    modelgenericunitcheck script mart_p3_label_incremental; full years with the effect between 0 and 4; a unit test; the check script rebuilds one worked example (+17.2 points).

Outside the list: the labels' own checks (the label file covers exactly the 30,000-question sample, every level is an allowed value) and a warn test that the public snapshot is still the one the numbers come from.

GitHub repo