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

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.

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.