DEV Community: Samir Adhikari

380,000 shelter records: what it costs a dog to just look like a pit bull

Samir Adhikari — Sat, 15 Aug 2026 19:48:04 +0000

This is a submission for Weekend Challenge: Dog Days Edition

What I Built

I kept seeing the same story come up. A dog attack, usually a bad one, and then people underneath saying pit bulls should be banned, or muzzled, or just put down as a breed. It shows up every few months, sometimes a local news clip, sometimes somebody's thread that got picked up.

I don't have a dog and I don't really have a side in it. But it's the kind of thing that sounds like it should be checkable, and most of what gets posted on both sides is people repeating a number without saying where it came from. So when this challenge showed up in my email and the theme was dogs, that's what I wanted to look at.

I didn't manage to answer it. I'll get to why, it took me a while to work out, and it's most of the reason this ended up being about something else.

What I could get at was what happens to these dogs once they're in a shelter. So I loaded every intake and outcome Austin Animal Center has published since 2013, around 380,000 rows, plus the live feed of what's in the building this morning, and built an app on top of it.

Somewhere in there I ran into black dog syndrome, which shelters have been warning about for years, the idea that black dogs get passed over and sit longer. It doesn't hold up. Black dogs are the biggest group in the whole dataset, 24,546 of them, they get adopted more often than white dogs, 52.2% against 46.9%, and they come eighth out of seventeen colours on wait time.

The colours that were slow were blue, fawn, and the two brindles, which are the words people use when they're describing a pit bull. Blue dogs turn out to be 81.8% pit bull.

So I grouped the dogs by how much they look the part instead, and the wait doubles at every step. An ordinary dog goes home in 8 days. One that just looks like a bully breed takes 14. An actual pit bull takes 28.

There are about 600 dogs in that shelter right now. Pit bull types are 19% of everything that's ever come through the door, and about 40% of what's standing in there today.

Demo

Live app

The explorer in the middle lets you re-cut the data yourself, by breed group, colour, age, condition on arrival, or how they got there. The search box takes plain English, something like "a young pit bull who's been waiting more than three months", and gives you dogs actually in the shelter now. The last section lists the eight who've been waiting longest today, by name.

Every chart is a live query, so the numbers move.

Code

thopl0 / dog-adoption-analytics

The pit bull penalty

Thirteen years of Austin Animal Center records in Snowflake, and a Streamlit app on top of them, asking what it costs a dog to look like a pit bull.

Live app · Write-up

The finding

The question this started from was whether the case for banning pit bulls holds up. It can't be settled from the numbers people quote at each other, because breed on a bite report is whoever filled the form in guessing by eye. In Olson and Levy's study, 16 shelter staff identified 120 dogs and then the dogs were DNA tested. DNA found 25 pit bull types. The staff called 62.

What Austin's records can measure is what that guessing costs.

Black dog syndrome does not show up at all. Black dogs are the largest group in the data, 24,546 of them, adopted more often than white dogs, and eighth of…

View on GitHub

How I Built It

The first thing I went looking for was bite data by breed. What I wanted was attacks per breed against how many of that breed there are, because a raw count doesn't tell you anything if you don't know the denominator.

I couldn't get there, and it took me a while to see why. People do publish bite data. But when a dog bites someone, whoever writes the report puts down what they think the dog was, going off how it looked.

There's a study on this. Olson and Levy, in The Veterinary Journal, took 120 shelter dogs, had 16 staff members identify them, four of them veterinarians, then ran DNA on all 120. The DNA said 25 of them were pit bull type. The staff said 62. Depending on who was doing the looking, accuracy ran between 33% and 75%, and the assessors often didn't agree with each other either.

These are people who handle dogs every day. So when a statistic says pit bulls are some percentage of attacks, what it's counting is dogs somebody looked at and called a pit bull. I don't think more data fixes that, and both sides of the argument are quoting numbers built the same way. Attacks are real and I'm not saying otherwise, I just couldn't find a version of the breed number that meant anything.

That left me with something I could actually check. If people are identifying dogs by eye, how much does looking a certain way cost a dog?

The first thing that went wrong was counting a shelter stay. I assumed animal_id was unique and joined intakes to outcomes on it, and the numbers came out obviously wrong. Animals come back. About one visit in five is a returning dog, and one animal has been through eleven times. Joining on the id alone gives you every arrival crossed with every departure, so a dog with two stays turns into four rows and three of them never happened.

The visits don't overlap though. A dog arrives, leaves, arrives again. So I numbered each side chronologically per animal and joined on the animal plus the visit number.

WITH i AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY animal_id ORDER BY intake_ts) AS visit_no
  FROM shelter.analytics.intakes
),
o AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY animal_id ORDER BY outcome_ts) AS visit_no
  FROM shelter.analytics.outcomes
)
SELECT ..., DATEDIFF('day', i.intake_ts, o.outcome_ts) AS days_to_outcome
FROM i LEFT JOIN o ON i.animal_id = o.animal_id AND i.visit_no = o.visit_no

It has to be a left join. Dogs that were still in the shelter when the data ends have an arrival and no departure, and an inner join drops them.

I didn't trust any of it yet, so I checked it against something I already knew the answer to. Return to owner should be much faster than adoption, since an owner comes and reclaims a stray within a day or two while an adopter takes weeks. Return to owner came out at a 1 day median and adoption at 9, so the two hadn't blurred into each other.

Then black dog syndrome fell over, and the colour list pointed at breed, so I built a 2x2, bully or not against bully-coded colour or not. The cell I cared about was dogs that look the part but aren't. It came out around 5 points of a 28 point penalty. I nearly wrote that down as the answer.

What stopped me was that I was picking out bully breeds with four ILIKE patterns. Boxers are fawn and brindle by definition and blocky enough to read as a pit bull to someone walking past a kennel, and so are cane corsos and bullmastiffs. All of them were sitting in my "doesn't look like a pit bull" group. More patterns wouldn't have fixed it either, there are 2,655 distinct breed strings in this data, all hand-typed, things like Border Terrier/Border Collie and Chihuahua - Smooth.

Sorting 2,655 breed strings is the kind of job you'd normally hand to a model, and Snowflake puts that inside SQL itself. AI_CLASSIFY is a function you call in a SELECT, so the classification runs in the same query as everything else, against the column where it already sits, with no API round trip and nothing leaving the warehouse. Unfortunately I was on a trial account, which doesn't include those functions, so I wrote the rules out in SQL instead, three groups. This is an argument about whether a bias effect is real, and a rule list is something a reader can go through and disagree with line by line.

Running the same comparison against real groups, the number I nearly published was off by more than double. The appearance-only penalty isn't around 5 points of 28, it's 12.7 of 27.8.

	dogs	adopted	median days	over 30 days
Pit Bull type	17,831	46.0%	28	48.9%
Bully adjacent	5,198	45.7%	14	33.8%
Other	70,936	51.5%	8	21.1%

The middle row is the one that got me back to the question I started with. Those 5,198 dogs aren't pit bulls and nobody wrote pit bull on their paperwork. They just look like one, and they wait nearly twice as long as the baseline for it. It's the same eyeballing that fills in the breed box on a bite report.

A share of each group never leaves at all, and it's bigger for the pit bulls.

Then I wanted to know whether I'd just fitted a story to one dataset. Austin swapped its record system in May 2025, so everything after that is a different pipeline with a different schema and different names for the outcomes, and none of my analysis had ever touched it. I normalised the new names back onto the old ones and ran the same thing over 6,052 completed stays. 23 days, 38.5 days, 54 days.

The numbers being so much bigger bothered me, since the easy explanation is that the new system just counts differently. So I looked at every outcome type across both eras instead of only adoption. Transfers take five days now and took five days before. Return to owner barely moved. Adoption went from 9 days to 28, and adoption is the one that needs a member of the public to pick a dog.

The last thing I tried was the economy. Adoption times tripled, so maybe people just can't afford a dog any more. I attached the free Snowflake Public Data listing off the Marketplace, one click and no storage on my side, and joined BLS unemployment for the Austin metro onto my monthly figures. Across 123 months the correlation with the gap between breed groups is -0.02. The one stretch where the economy clearly moved the shelter was April 2020, and those couple of months on their own were enough to push the correlation up to 0.43 until I dropped the pandemic out of it.

One thing I can't explain and I'm just leaving it there. Pit bull types and bully adjacent dogs get adopted at almost the same rate, 46.0% and 45.7%, but the second group waits half as long. Looking blocky seems to hurt a dog's chances about as much as the label does and hurt its waiting time much less, and I don't have a reason for that.

On the app side, Gemini does one job, it turns a sentence into JSON. It never writes SQL. Python whitelists every field against known values, coerces the numbers, and passes free text through as a bound parameter. The page shows you the JSON and the SQL it generated in an expander.

Prize Categories

Best Use of Snowflake. A raw and analytics schema split, the visit pairing above, a UDF so the historical and live sides can't drift apart, a GENERATOR cross join for the survival curves, and a Marketplace dataset attached and joined straight against my own tables to rule out an explanation.

The Marketplace one is the part I couldn't have got elsewhere. Somebody else's data turning up as a table you can join to, nothing to export and nothing to keep running. AI_CLASSIFY is the same idea for models, a classifier you call inside a SELECT instead of shipping 2,655 rows out to an API and waiting. It's off on trial accounts so I wrote the breed rules by hand, but that's the shape the job wanted.

Nothing on the page is precomputed either, every chart is a query.

Best Use of Google AI. Gemini turns a plain English question into structured JSON. Python validates every field of it against known values before a query gets built, and free text goes to Snowflake as a bound parameter. The model never sees or writes SQL, so a prompt injection has nothing to write into. The page shows you the JSON it produced and the query that came out of it.

Where this could be wrong

Austin's breed column is shelter staff writing down what they think a dog is, which is the same instrument I spent four paragraphs complaining about. My Pit Bull type group is dogs that people called pit bulls, and going by Olson's numbers a fair share of them probably aren't. I don't think it sinks the finding, since I was never measuring genetics. I was measuring what happens to dogs that get seen and labelled a certain way. But if you came in expecting a claim about the breed itself, this isn't one.

None of this says anything about whether pit bulls are dangerous. I went looking, decided the data couldn't answer it honestly, and stopped there. People who've been hurt by a dog aren't wrong to be afraid of that dog.

Putting Rottweilers in the middle group is a call about how people react to a dog rather than a taxonomic one. A Rottweiler isn't a bully breed by any kennel club definition. I put it there anyway.

Anatolian Shepherds and Great Pyrenees are the two I could most easily defend moving, big intimidating livestock guardians, and I left both in Other. Moving them would have nudged the result my way.

The standing population numbers are length biased. They're dogs who haven't left yet, so a snapshot over-samples long stays. Compare the groups against each other rather than against the historical medians.

The gap has widened in raw days over thirteen years, but so has everything else, so I used the ratio instead of the difference. And I only tested unemployment in the same month, not lagged.

89 rows out of 93,965 have a departure recorded before the arrival. Hand-entered municipal data does that. I filtered them out and nothing moved.

Where I ended up

The thing I actually found wasn't about pit bulls. It was about what a label costs on its own.

There are 5,198 dogs in this data that nobody ever called a pit bull. They're boxers, cane corsos, bullmastiffs. They wait nearly twice as long as an ordinary dog, and whatever anyone believes about pit bulls, none of it is about them. They only look the part.

That's also why the argument I came in for is so hard to have. Sixteen people looked at the same 120 dogs and couldn't agree on which ones were pit bulls, and the DNA disagreed with most of them. People are asking for a category to be banned, and the category is a guess.

I don't know whether pit bulls bite more, and this doesn't show they don't. It shows that some of the cost is being paid by dogs that aren't pit bulls at all, and they're sitting in a building in Texas while the argument goes on.

The longest waiting dog in there today is Pancho, a Cairn Terrier, 449 days. Six of the next seven are pit bulls.

The app runs on live data, so that list will have changed by the time you read this. Some of them will have hopefully gone home.

Building a B+ Tree Storage Engine (Part 1): Why Order Stops Making Sense on Disk

Samir Adhikari — Sat, 04 Jul 2026 18:00:27 +0000

I had been doing web dev for most of my coding career. I'd take breaks from it now and then, building games, some small project that looked interesting, or just learning something new. But this time I wanted to go a little deeper.

So I looked around for something interesting and built a simple in-memory key-value store in C. But it felt too simple. It was nothing like the technologies I actually used in web dev, like Redis and databases such as MySQL and Postgres. So I searched around and found the B-tree. I implemented a bit of an in-memory B-tree, then found out a B-tree isn't really what those technologies use either, and found the B+ tree. I finished an in-memory B+ tree. It was messy, but the concept itself was what real databases actually use. So I went looking for how real databases use B+ trees, and that's where I ran into pages, WAL, and so on.

Then I thought about how to add disk saving to it. In the key-value store I had done disk persistence before, just with a CSV file. This time I realized I had to write the disk version from scratch. That wasn't really a problem, I had the general idea of how things worked, I just had to replace the memory pointers with bytes and pages.

At first I just copied the layout from the memory tree. I looked up how people usually save these bytes and found things like magic, version, and so on. I defined an order macro at the top and used fixed size records, same as the memory tree.

// Inital implementation of the tree header where order was present
struct Header
{
    uint8_t magic;
    uint8_t version;
    uint16_t root_page_id;
    uint16_t order;
    uint16_t page_count;
    uint16_t page_size;
};

But then I thought, this doesn't feel real. I mean, it's not like I was building software for people to use, but I at least wanted to implement what real databases used. Fixed size records weren't it. So I scrapped them and decided to do variable sized records.

So I started trying to store variable sized records, and right away I hit a problem: how do I even find a record now? With fixed size records it was easy, they were all the same length, so I could jump straight to the one I wanted. But once records could be any size, I had no idea where one ended and the next one started.

My first idea was to put some kind of delimiter between them. That fell apart pretty fast, because a key is just bytes, so the delimiter could show up inside a key and I'd have no way to tell it apart. And even then, I'd still have to read through the node from the start to find anything.

So then I thought, what if I put a little metadata in front of each key, like its length? At least then I'd know where each key ended. That helped a bit, I could skip past the keys that didn't match by jumping over their bytes. But it still felt off. To actually find keys fast you want them sorted, so you can binary search instead of scanning the whole node. And with the metadata sitting inline next to each record, keeping the node sorted meant moving those variable sized records around every time I inserted one, which got convoluted fast. And if I didn't keep them sorted, I was back to walking the node one key at a time, worst case all the way to the end, and with small records that was a lot of jumps.

I asked some chatbots about it, and that's where I found the slot design. Instead of keeping that metadata inline with each record, you put it in a little directory at the front of the page and store the actual records from the back. The slots are all the same size, so keeping them sorted just meant moving the slots around instead of the actual records. And sorted, same sized slots are exactly what a binary search needs. I'm still scanning them for now, but the setup is there to do it properly later. Ok, that part was done.

struct LeafSlot
{
    uint16_t pl_offset;
    uint16_t pl_size;
};

But once the slots were working, I ran into a bigger problem. What about order? In the memory tree, every node except the root had a minimum and a maximum number of keys, and that's what order gave me: order 4 meant at most 4. Splitting and merging were based on that count. When there were order-1 keys the node was full and I split it, and when it dropped below (order/2)-1 it was too empty and I merged it.

But with variable sized records, that count stopped making sense. If I had a bunch of small records, order would cap me at 4 of them and the rest of the page would just go to waste, even though there was tons of room left. And the other way around, a page could be under (order/2)-1 records and still be basically full, if the records were big. The number of keys wasn't telling me anything about how full the page actually was anymore.

This bugged me for days. I kept feeling like order and page size were doing the same job, but I couldn't quite put it together. I searched for it and didn't really find anything, most of what came up was about the in-memory tree with fixed size records. So I explained my idea to a chatbot again, and it told me my thinking was right: order was basically an abstraction for page size. It was there to make the algorithm easier to explain and simpler to implement, without changing the actual idea underneath.

So I swapped the counts for sizes. For the max, instead of a max number of keys it's a max number of bytes, and when the next record doesn't fit, it splits. The min side would be the same idea in reverse, a min number of bytes instead of a min number of keys, and merge when the page drops under it. Same two rules, just measured in bytes instead of keys.

// in bt_insert: does the new record still fit in the page?
if (total_pl_size + sizeof(LeafSlot) + ph->cur_size > t->header->page_size)
{
    bt__leaf_handle_overflow(t, ph, key, value, k_len, v_len);
    return;
}

When I look back, the thing I actually figured out wasn't really about order, it was about the page. In the memory tree a node is just a bag of keys, and I never thought about where it lived. On disk a node is a page, a fixed block of bytes, and once it's a physical thing like that, everything else bends around it. The pointers between nodes become page ids. Variable sized records need slots. Full and empty stop being counts and become bytes. And order, the first thing you get taught, is really just the memory tree's way of never having to mention the page at all.

But none of that actually changed the tree. It's the same tree, the same algorithm as the one I wrote in memory. You still split a node when it's full and push the middle key up to the parent. You still keep the keys sorted. You still start at the root and walk down to find where something goes. The core concept didn't change at all. What changed was only how it's stored, memory turned into disk, pointers turned into page ids, and a node turned into a page. The concept stayed exactly the same, only the implementation moved underneath it.

So that's what a B+ tree is under the hood. Now, I have just deletion and searching left to implement.

Originally published on samir-adhikari.com.np.