Your Dog Can't Tell You Where It Hurts. MATCH_RECOGNIZE in Snowflake Can.

This is a submission for Weekend Challenge: Dog Days Edition

Target categories: Best use of Snowflake + Best use of Solana.

A diagnosis is a sequence, so make the detector a regular expression

A 2022 study screened 500 dogs with no osteoarthritis diagnosis on file and found 188 of them, 38%, had it. The number that matters more is the next one: fewer than half, 47.2%, had ever been presented for stiffness or lameness (Wright et al.). Most arthritic dogs never showed their owner the sign the owner was watching for, because a visibly weak animal is a target and the instinct outlived the danger (Merck).

So the only witness that does not editorialise is movement – and every consumer tracker destroys it on the way in, compressing a day of movement into a scalar and thresholding it. But a vet does not diagnose from a scalar. A vet diagnoses from an ordered sequence of events: the dog walks, stops, walks, stops again. Sniffs, circles, sniffs again without ever settling. Rises slowly, three separate times, before ten in the morning. Average those and the finding is gone before you look at it. Not weakened, gone, because averaging is precisely the operation that discards ordering, and ordering was the entire signal.

Both dogs walked 56 of their 60 seconds. Every total a step counter can compute is identical, and one of them is limping. The difference only exists at one row per second, which is the only resolution MATCH_RECOGNIZE cares about.

So the question is not “what threshold?” It is: what if the detector were a regular expression over rows instead of a number? That is a real, standardised SQL feature almost nobody reaches for – SQL:2016 row pattern recognition, and Snowflake’s MATCH_RECOGNIZE does exactly it: partition, order, then match a regex whose alphabet is rows.

A differential diagnosis is a sequence. A sequence is a pattern. A pattern is a query.

100 Hz dual-IMU collar --> Snowflake --> Snowflake --> Snowflake --> Solana (devnet)
  45 dogs, 27 breeds,      Dynamic       MATCH_          Cortex       a signed claim
  video-annotated,         Tables DAG    RECOGNIZE       vet notes    a shelter can
  10.6M sensor rows        no cron       6 syndromes     + triage     read without us

Enter fullscreen mode Exit fullscreen mode

TELLTAIL turns a dog collar into a syndrome detector. Not “active minutes”, but six named clinical presentations, each a row pattern, each explainable down to the individual second that satisfied each symbol:

10.6M sensor rows → 198,681 classified epochs → 118 findings across 32 dogs → 5,955 per-second symbol rows → 117 signed attestations. Every arrow is SQL running inside one Snowflake account.

Every dog on two independent deviations and its finding count. Colour is the triage band a Cortex task wrote, so position and colour agreeing is a result rather than a construction.

One thing up front, because it changes how you read every number below

Held-out accuracy is 77.46%, macro F1 0.650, dog-disjoint across 10 unseen animals. That number is low because the protocol is honest, and the protocol is the interesting part. At 100 Hz, two samples 40 ms apart are near-identical; a random row split puts adjacent samples from the same dog on both sides, the model memorises the individual, and the accuracy is a fiction. The published work on this corpus reports single-subject classifiers falling from ~91% to ~70-74% when generalising to unseen dogs. 77.46% sits above that band. The 91% was available and it would have been a lie, so the dashboard prints 77.46% in 44px type.

3.65% of epochs carry a heuristic state. SHAKE and SCRATCH are not first-class labels here, so they are derived and flagged state_source = 'HEURISTIC' – in the data, in a banner on every tab, and in a provenance table.

Two of the six syndromes never fired. S1 and S5 have zero matches, and are still in the catalogue, still swept, still displayed at zero. A detector that ships only the patterns which happened to hit is not a detector, it is a highlight reel.

Full accounting in What’s real and what’s simplified.

The walkthrough

Two minutes: the replayer streaming 100 Hz collar rows in, MATCH_RECOGNIZE catching a syndrome, and the claim landing on devnet.

TELLTAIL

A dog cannot tell you where it hurts. Ten million rows of collar data can.

Every consumer dog tracker computes activity minutes and step counts, because that is what you get from AVG() and SUM(). But a vet does not diagnose from an average. A vet diagnoses from a sequence: this, then that, then this again, in that order, that many times.

TELLTAIL streams 10.6 million rows of real dog collar telemetry into Snowflake classifies every second into a behavioural state, and then hunts for clinical syndromes expressed as regular expressions over rows using MATCH_RECOGNIZE.

Not “scratching is high today.” Instead:

head shake, scratch cluster, head shake, scratch cluster, emerging from rest

which is otitis and nothing else.

The thesis. A threshold cannot detect a syndrome. Aggregation tells you a number changed. Only row pattern recognition tells you that events happened in a diagnostic order. Snowflake…

Verify this in 60 seconds — no keys, no clone

1 · The detector is SQL. warehouse/07_syndromes.sql — every syndrome is a PATTERN (...) and a DEFINE, and the dashboard prints the exact clause that produced each finding.

2 · The feature is one function. warehouse/04_staging_dt.sql — CORR(vm_neck, vm_back), and the whole detection argument rests on it.

3 · The findings are on a public chain. One devnet wallet signs every attestation, and each transaction carries the claim in its Memo instruction:

Publishing wallet — ERcokh7aHySS2nLmfT7UjDzTtp8ZT1szwV5x2oKcAFFx
117 confirmed transactions · 31 dogs · 0.000585 SOL in total fees

Open any of these and expand Memo to read the bytes Snowflake produced. The right-hand column is the row pattern that fired, verbatim from REF.SYNDROME_CATALOGUE — the regex on the left is what put the claim on the right on chain:

Finding Devnet transaction The pattern that fired S2 · intermittent lameness 56Ptv79m…SbH2CgLkz stride{3,} halt+ stride2{1,3} halt2+ stride3{1,3} halt3+ S4 · reluctance to rise 4Lz1C7pi…4jZDT44Q settled{10,} lever rise settled2{10,} S6 · GI discomfort 2xyQwPKu…DGTbmBLu8 probe{5,} turn{2,} probe2{5,}

The receipt I care about most

Everything above is a static artefact. This one is a stopwatch. While writing this section I had the collar feed running and the bridge polling, and the warehouse found something on its own:

12:48:23.067   ORACLE.SP_ENQUEUE_ATTESTATIONS stages claim #1314
12:48:29.925   bridge claims it, signs it, submits to devnet
12:48:56.017   confirmed in slot 484450346
               ────────────────────────────────────────────
               32.95 s   warehouse row  ->  public ledger

Enter fullscreen mode Exit fullscreen mode

Three timestamps from one clock, one signature, one slot — transaction 3DMXbMBT…NhWpCg28. I trace that claim back through every layer of the build — down to the fourteen seconds that produced it, and the arithmetic behind its confidence — further down this post.

The warehouse stages the claim; a separate Node process holds the key. This table is the audit trail, not the source of truth. The chain is.

Two sensors, one question

The corpus is Kumpulainen, Vehkaoja et al. (2021): 45 dogs, 27 breeds, video-annotated at one-second resolution, and critically two IMUs, one on the collar and one on a back harness.

Most canine accelerometry uses a single collar device, and it works well. But one sensor has a blind spot no amount of model capacity fixes: a collar alone cannot distinguish a dog walking from a dog shaking its head. Both are vigorous neck motion. The harness resolves it physically rather than statistically – in locomotion the two sensors rise and fall together, and in a head shake the neck moves while the body does not, so their correlation collapses toward zero.

-- warehouse/04_staging_dt.sql — one SQL function, and only possible
-- because this dataset has two sensors.
CORR(vm_neck, vm_back)                       AS neck_back_corr,
STDDEV(vm_neck) / NULLIF(STDDEV(vm_back), 0) AS neck_dominance,

Enter fullscreen mode Exit fullscreen mode

The collar rides the cervical vertebrae, which the neck can swing on its own; the harness rides the thoracic vertebrae, which only move when the whole animal does.

The whole detector rests on this picture, and CORR(vm_neck, vm_back) is the difference between the two placements.

That 100 Hz → 1 Hz collapse is a hard gate, not an optimisation. 10.6M samples become ~106K one-second epochs, because MATCH_RECOGNIZE pointed at the raw table scans ~10.6M rows per partition and never returns. At the epoch layer the same six patterns come back in seconds.

The same minutes, spent differently. Two dogs can log identical walking totals and have completely different bout-length distributions – and that difference is where lameness becomes visible before any syndrome fires.

The feature then has to justify itself, so the dashboard plots that correlation grouped by true annotated label: locomotion should sit high, neck-dominant behaviours should collapse toward zero, and if it came out flat the project would be over. It is the one chart I built expecting to be disappointed by.

The state ladder

A classifier trained on locomotion and posture labels physically cannot emit CIRCLE or PAUSE – those are defined by geometry and context, not by the dataset’s label vocabulary. So every epoch is resolved through a strict precedence ladder, and which rung fired is a column, not a comment:

0  QUALITY   n_samples below the gate            -> UNKNOWN        LOW_QUALITY
1  GEOMETRY  yaw signature the model cannot see  -> CIRCLE, PACE   GEOMETRY
2  NECK      shake/scratch, if unlabelled        -> SHAKE, SCRATCH HEURISTIC
3  CONTEXT   stillness bracketed by locomotion   -> PAUSE          CONTEXT
4  MODEL     whatever the classifier said                          MODEL

Enter fullscreen mode Exit fullscreen mode

That is where the 3.65% comes from. Rung 2 is conditional: V_NECK_LABELS_PRESENT asks the data at refresh time whether SHAKE, SCRATCH and PACE are real annotations here, and derives only what is not observed. The COALESCE around its BOOLOR_AGG is load-bearing – over an empty label map BOOLOR_AGG returns NULL, NOT NULL is NULL, every heuristic branch silently fails to match, and S1 becomes unable to fire at all, on a warehouse that looks perfectly healthy.

The money layer: a diagnosis as a regular expression

Here is S2, intermittent lameness, exactly as it exists in REF.SYNDROME_CATALOGUE:

PATTERN ( stride{3,} halt+ stride2{1,3} halt2+ stride3{1,3} halt3+ )
DEFINE
    stride  AS state = 'WALK',   halt  AS state = 'PAUSE',
    stride2 AS state = 'WALK',   halt2 AS state = 'PAUSE',
    stride3 AS state = 'WALK',   halt3 AS state = 'PAUSE'

Enter fullscreen mode Exit fullscreen mode

Read it aloud: walk at least three seconds, stop, walk a little, stop again, walk a little, stop again. That is a limp, written as a regex – and the catalogue row says why it cannot be a threshold: step count and daily distance are unchanged, but stride interruption frequency is rising. That is “he seems fine, he just stops a lot now”, which is what the other 52.8% of Wright’s undiagnosed arthritic dogs look like. A sequence, which is exactly what a daily average deletes.

S6, GI discomfort, is PATTERN ( probe{5,} turn{2,} probe2{5,} ) over SNIFF/CIRCLE/SNIFF: sniffing is the most common outdoor behaviour and circling precedes every normal elimination, so only the repetition without resolution is abnormal – and repetition is an ordering property. S3, exercise intolerance, fires when total activity minutes are identical and the same minutes have been redistributed into shorter bursts with longer recoveries.

Six syndromes, 118 matches across 32 dogs. And the corpus rejected my first draft of S3: I defined recovery as state = 'REST', it matched nothing, and that was not a bug – TROT -> REST occurs zero times in 106K epochs, because a dog recovering from exertion stands or sits long before it lies down. It matches on activity_class now.

The part that makes it explainable

ONE ROW PER MATCH gives you a finding. ALL ROWS PER MATCH with CLASSIFIER() gives you which pattern variable consumed each individual second:

MATCH_RECOGNIZE (
    PARTITION BY dog_id, test_num  ORDER BY epoch_ts
    MEASURES MATCH_NUMBER() AS match_id,
             CLASSIFIER()   AS symbol,        -- which variable ate this row
             COUNT(*)       AS seq_in_match
    ALL ROWS PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( ... )  DEFINE ... )

Enter fullscreen mode Exit fullscreen mode

5,955 of those rows are materialised, and the dashboard renders them as a ribbon: one coloured block per real second, coloured by the symbol it played. So a generated sentence like “repeated gait interruption” is printed directly beside the seconds that produced it – prose checkable against the rows, which is not a feature most AI-assisted health tools offer.

Each block is one real second, coloured by the symbol the row-pattern engine assigned it.

Linked selection compiled into the Vega spec – no rerun, no callback, not one further query.

Confidence is a UDF, not a vibe

Every finding carries a confidence from one definition used everywhere: MARTS.F_CONFIDENCE, weighting three terms 0.45 / 0.35 / 0.20. evidence is how far a match exceeds its pattern’s minimum length, because a match sitting on the quantifier floor is the weakest kind. quality is mean epoch completeness. purity is the fraction of matched epochs whose state came from the classifier rather than a heuristic rung – so a finding assembled out of derived states is scored down automatically, and the honesty is arithmetic rather than editorial. Hold on to those three terms.

The quantifier is not a magic number

stride{3,}. Why 3? A hand-tuned quantifier is exactly the magic number I spent the opening criticising. So the pattern text is not code – it is rows in REF.SYNDROME_VARIANTS, three strictness settings per syndrome, and SP_SYNDROME_SWEEP() cursors that table, string-builds a MATCH_RECOGNIZE per row and runs it with EXECUTE IMMEDIATE. Eighteen row-pattern queries, one loop, zero hand-written variants, and the sensitivity curve is the output.

The sweep also keeps its own scoreboard, because it had to. Every variant succeeds alone; executing all eighteen inside one procedure intermittently trips a Snowflake internal error (370001), and an unhandled one abandons the sweep while the procedure still reports success. A truncated curve looks exactly like a real curve. So each variant is wrapped in its own BEGIN ... EXCEPTION and writes ran_ok plus the verbatim error to MARTS.SWEEP_STATUS – which is how I know the 229 matches behind the published curve came from 11 completed variants and not eighteen.

The DAG: 12 Dynamic Tables, zero cron, and four things a Dynamic Table cannot do

There is no scheduler in the transform layer. Each table declares its TARGET_LAG and Snowflake resolves the refresh order: raw telemetry into epoch features, into the ML state prediction and the ethogram, then bouts, transitions, baselines and deviations, and finally the syndrome layer that feeds Cortex and the publish queue.

The account itself, not a diagram of it. REF seeds, RAW lands, STAGING derives, MARTS scores, ML fits, ORACLE queues – and nothing flows right to left.

Each bar is an object’s observed refresh lag as a multiple of the TARGET_LAG it declared, so past the dotted line is a table falling behind its own promise.

Why the syndrome layer is a task and not a Dynamic Table. This is the most useful thing I learned this weekend: a Dynamic Table containing MATCH_RECOGNIZE will not refresh incrementally. REFRESH_MODE = INCREMENTAL fails compilation with an unsupported construct, and AUTO silently resolves to FULL – a full rebuild on a one-minute lag is a bill you did not agree to, so that layer is an explicit task on a two-minute schedule. Same at the feature layer, where the sample-level window functions are not incrementally refreshable either and FULL is declared rather than inferred. (Two more, so you do not rediscover them: MATCH_RECOGNIZE cannot appear inside a recursive CTE, and must never be pointed at the raw 100 Hz table.)

So tasks exist for exactly four jobs Dynamic Tables cannot do: row pattern recognition, calling a stored procedure, anything budgeted that must be rate-limited by schedule rather than refresh lag, and snapshotting. They chain with AFTER, so it is one DAG with a single root rather than five schedules racing:

T_ROOT (2 min)
  └─ T_SYNDROMES ────── MATCH_RECOGNIZE, all six patterns
       ├─ T_MATCH_ROWS ─ ALL ROWS PER MATCH symbol tagging
       └─ T_SNAPSHOT ─── per-minute activity history
            └─ T_BOUNDARY ── fix the single train/detect split point
                 ├─ T_FORECAST ── ML.FORECAST
                 │    └─ T_ANOMALY ── ML.ANOMALY_DETECTION
                 │         └─ T_DRIVERS ── TOP_INSIGHTS
                 └─ T_NOTES ───── AI_COMPLETE
                      └─ T_TRIAGE ── AI_CLASSIFY over the cached notes
                           └─ T_BRIEF ── AI_AGG
                                └─ T_ATTEST ── enqueue for the bridge

Enter fullscreen mode Exit fullscreen mode

One procedure per task, deliberately. A multi-statement AS BEGIN ... END; task body is valid Snowflake and also unparseable by any client that splits on semicolons – including my own run_sql.py, which would create the task with only its first CALL and execute the rest as orphaned statements. The task then exists and does a third of its job, silently.

ASOF JOIN for the baseline, and it matters clinically. Every dog is its own control, because a Husky doing forty minutes of galloping is a Tuesday and a twelve-year-old Bulldog doing the same is an emergency:

FROM MARTS.ACTIVITY_EPOCH cur
ASOF JOIN MARTS.ACTIVITY_BASELINE base
     MATCH_CONDITION (cur.epoch_ts >= base.window_end)
     ON cur.dog_id = base.dog_id

Enter fullscreen mode Exit fullscreen mode

ASOF earns its place over LAG(n) because LAG counts ROWS, not TIME. A collar that drops out for ten minutes is not hypothetical, and with LAG that dog is quietly compared against the wrong hour and its deviation becomes fiction. For a health metric, silently wrong is the worst failure mode available: it does not look like a bug, it looks like a finding.

Each dog is its own control – the band is that dog’s own trailing hour, matched on the timestamp condition itself.

The classifier, and two decisions that decide whether the number means anything

Identifiers are not features. dog_id, test_num and epoch_ts are carried through the training view for the split and for joining back, and dropped before the model sees a row. Leave dog_id in the vector and the model learns “dog 23 mostly trots”, which invalidates the entire holdout protocol while making the accuracy go up.

The holdout is breed-spread, and the obvious way to build it collapses. Take FLOOR(n_breed * fraction) per breed with a floor of one so none is skipped, and on this roster – 26 breeds across 45 dogs – GREATEST(1, ...) fires for nearly every breed and the “22% holdout” becomes 26 of 45 dogs: more than half the corpus. What works is to rank dogs within their breed, interleave breeds globally, and take the first N – exact count, spread preserved. That seed table is also CREATE TABLE IF NOT EXISTS on purpose: with CREATE OR REPLACE, retuning an unrelated threshold empties the holdout as a side effect, ML.V_TRAIN quietly widens to all 45 dogs, and the next reported accuracy is a row-split fiction wearing a dog-disjoint label.

And 7.9% of the corpus needed rescuing first. The dataset carries up to three simultaneous annotations and the posture is not always in the first: Panting is primary for 836K rows and is respiratory, not a posture – the actual posture sits in Behavior_2 (48% Sitting, 48% Standing, 4% Lying). Mapping it to one posture is wrong about half the time; dropping those rows throws away the same 7.9%. A COALESCE across the two columns recovers it, and label_source records which one answered.

Cortex, and the rule that the LLM never produces a number

SQL and fitted models compute every number. The LLM only writes prose. Nothing an LLM says reaches a state, a match, a confidence, or the chain.

Three AI functions, batched into tables by tasks: AI_COMPLETE writes a SOAP-format vet handoff note per finding, AI_CLASSIFY assigns a triage band over that note, AI_AGG produces one pack brief. Every number in every prompt was computed upstream in SQL – the prompt is handed the evidence VARIANT verbatim and instructed, in the system text, to not diagnose. It is a handoff document for a clinician, not a verdict.

Two decisions worth stealing. AI_CLASSIFY classifies the note, not the raw evidence, so the triage badge and the document a human reads cannot drift apart. And every AI procedure catches its own exception, logs it to AI.USAGE_LOG, and returns normally, so a Cortex hiccup degrades one batch instead of taking the task DAG down. The render path never calls Cortex at all: a trial account is capped at roughly ten credits of AI Function usage per day, and a dashboard that bills on page load dies mid-demo.

Position is measured; colour is generated. Because colour is not one of the three axes, the two agreeing is a result rather than a construction.

The time-series gotcha that cost an hour: ANOMALY_DETECTION rejects a call whose training and detection windows overlap. The fix is not to guess a gap – compute the boundary T once, into a table, then strict < on the train side and >= on the detect side. It has its own task, T_BOUNDARY, upstream of forecast, anomaly and drivers so all three inherit the same number.

The honest substitution: every model-introspection accessor on this account raises Computation Error in function __SHOW_* while PREDICT works fine, so SHOW_FEATURE_IMPORTANCE is permanently empty. Rather than ship an empty panel or quietly relabel something else, the dashboard computes a one-way ANOVA F-ratio per feature in SQL, says in the caption that it answers a different question from a tree model’s split gain, and prints the account error verbatim beneath it.

Publishing the claim, not the data

A dog arriving at a shelter arrives with no history. Vets, shelters, kennels and adopters share no database and commercially never will, and a health record living in one vendor’s cloud dies the moment the dog changes hands – which is precisely when it matters most. So findings at severity >= 2 are staged into a queue and attested on Solana.

{"conf":0.996,"finding":"S4","model":"state-v3+match-v1","oracle":"TELLTAIL",
 "severity":2,"source":"Snowflake MARTS.SYNDROME_MATCHES",
 "subject":"b80a39d78c5fb38f","window":"2026-08-15T02:18:23Z/PT47S",
 "authority":"ERcokh7aHySS2nLmfT7UjDzTtp8ZT1szwV5x2oKcAFFx"}

Enter fullscreen mode Exit fullscreen mode

Snowflake output on a block explorer: 117 confirmed transactions, 31 dogs, 0.000585 SOL in total fees, and not one needed a smart contract. subject is a salted hash of the dog id – no telemetry, no breed, no owner, no location, only that a finding of this code, severity and confidence occurred in this window. Publish the claim, never the data.

The security property worth checking: the keypair never touches Snowflake. The warehouse stages claims in ORACLE.PUBLISH_QUEUE and does nothing else; a small Node bridge holds the key, signs, submits, and writes the signature back. Grep the SQL codebase for key material and there is nothing to find, because Snowflake has no code path that can sign anything. The state machine itself is SQL (SP_CLAIM_BATCH, SP_MARK_CONFIRMED, SP_MARK_FAILED), so two bridges polling the same queue cannot double-publish: claiming flips rows to SENT before anything is signed. It is outbound polling rather than Snowflake calling out because External Access Integrations are unsupported on trial accounts (error 509009, confirmed the hard way).

One finding, all the way down

Every architecture diagram in every hackathon post is a row of boxes with arrows between them. Here is the alternative: one real finding, and the actual value at every layer it passed through. Claim #1314, detected while I was writing the section above.

# Layer The actual value 1 RAW.COLLAR_TELEMETRY ~1,400 samples, 6 channels, dog 23, session 2 2 STAGING.EPOCH_FEATURES 14 epochs, neck_back_corr computed per second 3 MARTS.EPOCH_STATES SNIFF SNIFF SNIFF SNIFF SNIFF CIRCLE CIRCLE SNIFF SNIFF SNIFF SNIFF SNIFF SNIFF SNIFF 4 MATCH_RECOGNIZE PROBE{5} TURN{2} PROBE2{7} – cast, circle, cast, unresolved 5 MARTS.SYNDROME_MATCHES S6 GI discomfort · 13 s · conf 0.7321 · purity 0.7857 · quality 1.0 6 evidence (VARIANT) {"cast_epochs":[5,7],"circle_epochs":2,"unresolved":true} 7 AI.VET_NOTES (AI_COMPLETE) “repetitive cast-circle-cast sequence without resolution (5 cast epochs, 2 circle epochs, 7 cast epochs)” 8 AI.TRIAGE (AI_CLASSIFY) schedule appointment, severity 2 9 ORACLE.PUBLISH_QUEUE subject 33e35a39e17b15ec – a salted hash, no dog id 10 Solana devnet slot 484450346, 3DMXbMBT…WpCg28

Read row 3 and row 7 next to each other. The LLM wrote “5 cast epochs, 2 circle epochs, 7 cast epochs” – and rows 3 and 4 are those exact epochs, in that exact order, with the symbol that consumed each one. The model did not count them. CLASSIFIER() counted them in SQL, the counts went into the prompt as an evidence object, and the note is constrained to narrate numbers it was handed. That is the difference between an explainable finding and a fluent one.

Now decompose the number that went on-chain

Row 5 says conf 0.7321 – the 7.32e-01 in the memo instruction of a transaction anyone can open. It is not a score a model emitted. Take F_CONFIDENCE and the match’s own facts: 14 epochs against S6-tuned’s min_epochs of 12, quality 1.0, purity 0.7857.

evidence  0.4 + 0.6 * ((14 - 12) / 12)  = 0.5000  ->  x 0.45 = 0.22500
quality                                   1.0000  ->  x 0.35 = 0.35000
purity                                    0.7857  ->  x 0.20 = 0.15714
                                                            ─────────
                                                    ROUND(,4)  0.7321

Enter fullscreen mode Exit fullscreen mode

That is the published digit, reached by hand. And purity 0.7857 is not a coincidence – it is exactly 11/14: eleven of the fourteen matched epochs came from the classifier and three from a heuristic rung of the state ladder, so the finding was scored down for its own provenance, automatically, in SQL, before anything was signed.

Then read row 9 against rows 1-8. Everything that makes the finding useful to a vet stays in the warehouse. Everything that reaches a public ledger is a code, a severity, a confidence and a window against a salted hash. A shelter can verify the claim without the previous owner, the vendor, or me – and still cannot tell you which dog it is.

Row 7 and row 3 in one screenshot: the note on top, the seconds that produced it underneath.

The honest caveat on this particular finding – click to expand

This finding came off the replayer: ingest/replay.py streams the published corpus into the live landing table with a fresh timestamp anchor. The sensor data, feature, classifier, pattern match, Cortex call and Solana transaction all genuinely happened while I was writing this post – but these are not fourteen dog-seconds never observed before, since the same samples also exist in the bulk path under their original timestamps. So 118 findings is not a claim about 118 distinct clinical events in the world.

The dashboard runs inside the warehouse

Streamlit in Snowflake. Eleven pages, 45 figures, zero external hosting – the app, the data, the ML and the LLM all in the same account. Three renderers, each for the one thing it is unmatched at: plotly for 3D, Altair for linked selection compiled into the Vega spec, Bokeh for a shared x_range scrubber over a 100 Hz waveform.

That sandbox has no outbound network, so <img src="https://…"> is a broken icon no matter how reachable the host and breed photographs live base64 in a table. Those photographs are of the breed, never of the study animal – stated in a column and repeated under every image, because a photograph implying it is the animal being diagnosed would be the most misleading thing this project could put on screen.

Three SiS hazards that reproduce nowhere else:

Hazard Symptom Fix to_pandas() returns numerics as object-dtype Decimal plotly treats them as categories; every line chart is the same straight diagonal convert element-wise with float() SiS ships Altair 4 selection_point / .add_params are Altair 5 API – an AttributeError took the entire Syndromes tab down resolve the spelling at call time The plotly version is unknowable from here fillgradient, barcornerradius, griddash each raise ValueError at figure construction on an older build probe each at import, with a fallback

The Altair one is the nastiest bug in the build, because every development machine on earth has Altair 5 or 6: I wrote the modern spelling, watched it work locally, and watched Snowflake render the Syndromes tab heading followed by a red traceback where the cross-filter should have been. The money tab, dead, in the one environment I could not reproduce. So every effect is capability-probed now, and tests/test_chart_layer.py builds one of everything twice – natively, and with all probes forced False – because the degraded path only ever runs in production.

Three axes, and the separation is either there or it is not. This is the picture that decides whether CORR(vm_neck, vm_back) was a good idea, which is why it is rotatable rather than a projection I flattened for you.

Everything Snowflake is actually doing here

The test I held myself to: could this be swapped for a Postgres box and a cron job without losing the argument? If yes, it did not belong in the build.

Feature Where Why it is not decoration MATCH_RECOGNIZE 07_syndromes.sql the entire thesis: six diagnoses as PATTERN + DEFINE CLASSIFIER() + ALL ROWS PER MATCH 07_syndromes.sql turns a finding into an explanation, second by second EXECUTE IMMEDIATE over a pattern table 07_syndromes.sql 18 row-pattern queries from one loop, so the sweep is a result SQL UDF F_CONFIDENCE 07_syndromes.sql one confidence definition, decomposable by hand 12 Dynamic Tables + TARGET_LAG 04–06 declarative freshness, no cron in the transform layer ASOF JOIN 06_marts_dt.sql a feed gap degrades the baseline instead of silently shifting it SNOWFLAKE.ML.CLASSIFICATION 05_ml_classification.sql dog-disjoint classifier, 77.46%, above the published cross-dog band FORECAST / ANOMALY_DETECTION / TOP_INSIGHTS 08_ml_timeseries.sql projection, deviation, drivers – one shared split boundary AI_COMPLETE / AI_CLASSIFY / AI_AGG 09_ai_layer.sql notes, triage, brief – batched by tasks, never on render Task graph with AFTER 11_tasks.sql one root, twelve tasks, one procedure each Streamlit in Snowflake streamlit_app.py the app runs inside the account with the data, no egress

What it cost, read out of the account rather than estimated: 53.34 of a 400-credit trial grant over 7 days, 118 AI_COMPLETE + 118 AI_CLASSIFY + 540 AI_AGG with 0 failed batches, 0.000585 SOL in fees, and zero Cortex calls per page render, by construction – so a trial account cannot be drained by someone refreshing a tab during judging.

Where this ends up

The last tab is allowed to be quiet, because the context around it is not. American shelters have been at or above capacity for years, with dogs in shelters up roughly 22% since 2021 and non-live outcomes up 27% in a single year. Big dogs wait longest. Waiting is itself the harm.

Austin publishes a decade of intake records, and behaviour is a named outcome reason in them. The categories TELLTAIL detects on a collar, at home, from movement alone are the same categories written down at intake, after the relationship has already broken down. Two independent counts, deliberately on their own scales – a shared axis would invite a comparison the numbers do not support.

The behaviour-linked series is a subset of the total, so it is drawn inside it rather than beside it.

What’s real and what’s simplified

Real and running: everything in the Snowflake table above, against 3.1M live plus 10.6M bulk rows, 198,681 classified epochs, 118 matches across 32 dogs, 50,000 Austin records and 117 confirmed devnet attestations. python tests/run_all.py is green with no account and no network: 13 pattern tests, 9 demo-signal tests, the SiS compatibility suite, the chart layer native and degraded, and a parse of all twelve warehouse scripts.

Simplification Why Honest label SHAKE and SCRATCH are heuristic, not model states not first-class labels in this corpus state_source = 'HEURISTIC', 3.65% of epochs, banner on every tab, and purity scores those findings down The “live” feed is a replayer over the real corpus there is no live dog wearing this collar ingest/replay.py, real rows and timestamps; only 12 of 45 dogs have a waveform, and the picker says so 11 of 18 sweep variants completed on the published run Snowflake internal error 370001 on long EXECUTE IMMEDIATE loops MARTS.SWEEP_STATUS records ran_ok and the verbatim error per variant Breed photos are reference images the corpus ships no per-dog photographs stated in a column, under every image, and in the rail Model introspection unavailable on this account Computation Error in function __SHOW_* ANOVA F-ratio in SQL, labelled as a different question Attestations go to devnet, bridge is centralised an attestation bridge, not a decentralised oracle network stated plainly on the On Chain tab S6 accounts for 109 of 118 matches sniff, circle, sniff is genuinely the most common outdoor pattern per-syndrome breakdown on screen, the two zero syndromes shown at zero

What this unlocks

The dog is the costume. Underneath is a primitive worth stealing: MATCH_RECOGNIZE turns “a sequence of events that means something” into a query, and almost every domain currently reaching for a threshold actually wants a sequence.

Fraud is a sequence. Churn is a sequence. Equipment failure is a sequence. Sepsis is famously a sequence. Every one is presently detected by someone thresholding a rolling average and then hand-writing an application to recover the ordering the average destroyed. They are all a PATTERN (...), a DEFINE, and a TARGET_LAG – plus, if you want anyone to act on the output, a confidence you can decompose and an evidence object the prose is not allowed to exceed.

One last thing, since it is nearly the 26th

International Dog Day is August 26th, and the date is not a marketing pick. Colleen Paige chose it because that is the day her family brought her first dog home from a shelter, when she was ten. So the holiday is not really about dogs. It is about the gap between a dog sitting in a shelter and a dog sitting in a house, and how many animals never cross it.

I am not going to claim a collar closes that gap. Most surrenders are housing and money, and no amount of SQL touches either. But some fraction of that intake is a behaviour that was a treatable medical problem eighteen months earlier, and that fraction is what this project is aimed at. A dog that stops rising easily is not being difficult – it hurts, it hurt quietly, and by the time it was legible as behaviour it had stopped being legible as pain.

Somewhere in that corpus is a dog that was showing a pattern for eleven days before a human noticed. The warehouse noticed on day two. That is not a claim about SQL – it is a claim about a gap a dog has to live inside while we catch up. A collar cannot close it. But “he seems fine, he just stops a lot now” is a sequence, and sequences are queryable, and it turns out we have had the right SQL clause since 2016.

Give your dog a scratch on the 26th. Then watch how it gets up.

Run it yourself

git clone https://github.com/SoumyaEXE/TellTail
cp .env.example .env                 # Snowflake creds, Kaggle token, devnet keypair
pip install -r requirements.txt && npm install

python scripts/profile_dataset.py    # Gate A: read the real CSV header first
python scripts/load_raw.py           # 10.6M rows
python scripts/run_sql.py --all      # schemas, DAG, ML, syndromes, AI, tasks
python ingest/replay.py --speed 60 --dogs 12   # the live feed
python scripts/austin_sync.py        # shelter data
npm run bridge:once                  # queue -> signed devnet attestations
python scripts/deploy_streamlit.py   # the dashboard, into the warehouse

Enter fullscreen mode Exit fullscreen mode

python tests/run_all.py runs every offline check with no account and no network: the real PATTERN and DEFINE clauses compiled out of the SQL and exercised against fixtures, synthesised IMU signal through the real feature math, the SiS compatibility suite, and a parse of every warehouse script.

References

1. Wright, A. et al. (2022). Identification of canine osteoarthritis using an owner-reported questionnaire. Journal of Small Animal Practice.
2. Merck Veterinary Manual. Recognizing and Assessing Pain in Animals.
3. Kumpulainen, P., Vehkaoja, A. et al. (2021). Dog behaviour classification with movement sensors placed on the harness and the collar. Applied Animal Behaviour Science 241, 105393. Data: Mendeley, CC BY 4.0.
4. SQL:2016, ISO/IEC TR 19075-5:2016, Row Pattern Recognition.
5. TIME (8 Jan 2024), Surge in Unwanted Dogs Fuels ‘Crisis’ Across U.S. Animal Shelters · City of Austin, Animal Center intakes and outcomes.

Built with @dronzer2code within the challenge window, with AI pair-programming. Breed photographs: Stanford Dogs (Khosla et al., FGVC 2011). TELLTAIL is not a diagnostic device and nothing in it substitutes for a veterinarian.