This is a submission for "Weekend Challenge: Dog Days Edition" (https://dev.to/challenges/weekend-2026-08-13).
What I Built
I started with two CSV files.
173,813 intake records.
173,775 outcome records.
At first, they looked like ordinary rows: animal IDs, timestamps, ages, breeds and outcome types.
But every intake represented the moment an animal entered a shelter. Every outcome represented the moment that particular wait ended.
Between those two moments was something the original files did not show clearly:
Time.
How many mornings passed? How many nights? Which dogs left quickly, and which ones remained long after the median suggested they should have gone?
That question became Still Waiting—an interactive shelter-data story that asks:
«Which dogs wait longest for a way home?»
The application lets visitors explore shelter waiting times by:
- Dog age group
- Intake category
- Intake year
- Median and average waiting time
- Number of matched shelter stays
It also explains exactly how the records were matched so that every displayed number can be traced back to the Snowflake analysis.
After cleaning and matching the records, I analyzed 89,723 complete-year dog stays from 2014–2024.
The overall median stay was 6 days.
But the average was 21.2 days.
That distance between six and 21.2 is the part that stayed with me. It means that while many dogs left quickly, others waited long enough to pull the average much higher.
They are the long tail hidden behind one reassuring median.
They are why the project is called Still Waiting.
Verify the Snowflake Work in 60 Seconds
1.Raw data: 173,813 intakes + 173,775 outcomes.
2.Matching logic: LEAD() identifies the next intake, while ROW_NUMBER() selects the earliest valid outcome before that next visit.
3.Result: 93,792 matched dog stays and 89,723 stays from complete years 2014–2024.
4.Interactive dataset: GROUPING SETS creates the age × intake type × year aggregate cube used by the dashboard.
5.Privacy: Only aggregate results reach the browser—no Snowflake credentials or animal-level records.
Demo
🐾 Explore the live application:
https://still-waiting-public-ahmadameen7471-4889s-projects.vercel.app
Try selecting different age groups and intake types. The headline metric, age comparison and yearly trend will update using the verified Snowflake aggregates.
Code
The complete source code, Snowflake queries, methodology and validated aggregate dataset are available on GitHub:
(https://github.com/ahmardchain/still-waiting)
How I Built It
From two disconnected files to one shelter stay
The project uses two official City of Austin Open Data datasets:
- "Austin Animal Center Intakes" (https://data.austintexas.gov/Health-and-Community-Services/Austin-Animal-Center-Intakes-10-01-2013-to-05-05-2/wter-evkm)
- "Austin Animal Center Outcomes" (https://data.austintexas.gov/Health-and-Community-Services/Austin-Animal-Center-Outcomes-10-01-2013-to-05-05-/9t4d-g238)
An intake record tells us when a dog arrived.
An outcome record tells us when something happened afterward—such as an adoption, transfer or return to an owner.
The difficult part was connecting those moments honestly.
A dog can enter the shelter more than once. Joining only by "ANIMAL_ID" could connect an intake to the wrong outcome and create a stay that never actually happened.
Inside Snowflake, I used window functions to:
- Order every animal’s intake history
- Find its next intake with "LEAD()"
- Match the current intake to the earliest valid outcome
- Ensure that outcome happened before a later intake
- Keep one match using "ROW_NUMBER()"
- Calculate elapsed time with "DATEDIFF()"
This produced 93,792 matched dog stays.
I then restricted the analysis to the 11 complete calendar years from 2014 through 2024, leaving 89,723 stays for the final story.
Handling imperfect real-world data
Public data is rarely perfectly clean.
I used "TRY_TO_TIMESTAMP_NTZ()" to safely parse timestamps without allowing one malformed value to stop the full workflow.
The transformation also excludes:
- Non-dog records
- Missing animal IDs
- Invalid timestamps
- Outcomes before an intake
- Negative waiting intervals
Age descriptions such as “2 years,” “7 months” and “3 weeks” were converted into approximate days and grouped into Puppy, Young, Adult and Senior categories.
Building the interactive dataset
Snowflake does more than store the CSV files in this project.
It performs the cleaning, sequencing, matching, date calculations, quality checks and final aggregation.
I used "GROUPING SETS" to produce a filterable cube containing combinations of:
- Intake year
- Age group
- Intake category
For each combination, Snowflake calculates:
- Matched shelter stays
- Median waiting time
- Average waiting time
This gives the frontend everything it needs to respond instantly when a visitor changes a filter.
Protecting the underlying records
The website does not expose Snowflake credentials or send animal-level shelter records to the browser.
Instead, the production interface uses a validated aggregate export generated by Snowflake.
This was a deliberate decision: the analytical work remains reproducible, while the public application receives only the non-sensitive summary results required for its charts.
The repository contains the complete workflow:
- "01_setup.sql" — creates the Snowflake resources and raw tables
- "02_analysis.sql" — cleans and matches shelter stays
- "03_results.sql" — produces the findings and dashboard cube
Turning analysis into a story
I built the interface with:
- Next.js
- React
- TypeScript
- Responsive custom CSS
- Accessible SVG visualizations
- Vercel
I did not want Still Waiting to feel like a corporate analytics dashboard.
The muted colors, editorial layout and restrained visualizations are intentional. The interface should make the findings understandable without allowing the design to overpower what the records represent.
What the Data Revealed
Six days does not tell the whole story
Across 89,723 analyzed stays, the median wait was 6 days, while the average reached 21.2 days.
The median describes the typical stay.
The average reveals the dogs who waited far longer.
Waiting increased sharply in 2023
The shelter-wide median increased from 5.3 days in 2019 to 13.9 days in 2023.
It improved to 10.8 days in 2024, but remained considerably higher than the pre-2020 figure.
The way a dog entered mattered
Median stays differed by intake path:
- Stray: 5.3 days
- Owner surrender: 6.9 days
- Other intake paths: 8.3 days
Behind those categories are very different beginnings—but they all involve waiting for the next outcome.
Prize Categories
Best Use of Snowflake
Snowflake is not a decorative integration in Still Waiting. It is the analytical foundation of the entire project.
Snowflake is responsible for:
- Storing both raw shelter datasets
- Parsing inconsistent timestamps
- Sequencing repeat shelter visits
- Pairing intakes with valid outcomes
- Calculating the duration of each stay
- Creating age and intake categories
- Filtering complete calendar years
- Running quality checks
- Calculating median and average waits
- Producing the interactive aggregate cube
Without that processing, the frontend would have no reliable story to tell.
Snowflake transformed two disconnected public datasets into a clear measure of something that is normally invisible:
How long a dog waited.
Final Thoughts
Data does not bark.
It does not press its face against a kennel door or look up when footsteps approach.
But when it is handled carefully, data can reveal who may be getting overlooked.
Still Waiting is not meant to rank dogs or reduce their lives to statistics. Its purpose is to make invisible time visible—and to remind us what every row represents.
Every row was a shelter stay.
Every number was a day.
And behind every number was a dog still waiting.
Top comments (0)