Ad-Hoc SQL and Client Debugging with Athena: The Queries You Didn't Plan For

I wrote six SQL views that answer 90% of my product questions — funnels, cohorts, churn signals, feature engagement. They run on a dashboard I check every morning.

But last week a user reported that their document generation “just stopped working.” The dashboard said activation rates were fine. Feature engagement was steady. Nothing looked wrong.

The problem wasn’t in the aggregates. It was in the detail. I needed to reconstruct exactly what this user did, in what order, and where things went silent. That’s not a dashboard question — it’s an ad-hoc investigation.

The same event lake that powers your predefined views is also the most powerful debugging tool you have. You just need to know how to query it.

When Predefined Views Aren’t Enough

Predefined views answer the questions you anticipated. They tell you what’s happening in general:

Funnel: 62% of signups start a session, 38% generate a doc
Cohorts: this week’s activation rate is up 3% from last week
Churn: 4 users went silent in the last 14 days

But they can’t tell you:

What happened to this specific user? — their full event timeline, in order
Why did this feature break last Tuesday? — error events correlated with a deployment
Which users hit this edge case? — a pattern you didn’t know to track
Is this a real trend or an anomaly? — drilling into the data behind a dashboard number

These are ad-hoc questions — they arise from bug reports, customer conversations, support tickets, or just a number that looks off. You can’t pre-build views for questions you haven’t thought of yet.

The Ad-Hoc Workflow

Ad-hoc investigation on Athena follows a consistent pattern:

graph LR
    A[Signal] --> B[Scope]
    B --> C[Explore]
    C --> D[Narrow]
    D --> E[Explain]
    E -->|"Worth keeping?"| F[Promote to View]

    style A fill:#ef4444,color:#fff
    style F fill:#2563eb,color:#fff

Signal — something triggers the investigation (bug report, anomaly, question from a user)
Scope — constrain by time range and partition to control cost
Explore — broad query to see what events exist for this user/feature/period
Narrow — filter to the relevant events, order chronologically
Explain — find the root cause or answer the question
Promote — if you’ll ask this again, turn it into a saved query or view

Deep Client Debugging: Reconstructing a User’s Journey

The most common ad-hoc investigation: a user reports a problem, and you need to see exactly what happened from their perspective.

Step 1: Find the User’s Event Timeline

SELECT
    time,
    detail_type,
    json_extract_scalar(detail, '$.data.enrichment.userEmail') AS email,
    json_extract_scalar(detail, '$.source') AS source,
    json_extract_scalar(detail, '$.subject') AS subject
FROM pweb_enriched.all_events
WHERE json_extract_scalar(detail, '$.data.enrichment.userEmail') = 'user@example.com'
  AND year = '2026' AND month = '06'
ORDER BY time DESC
LIMIT 100;

This gives you the full chronological story: signed up, started a session, generated a document, started another session, then… silence. No more events after June 18th. That’s your first clue.

Step 2: Zoom Into the Last Session

SELECT
    time,
    detail_type,
    json_extract_scalar(detail, '$.subject') AS subject,
    json_extract_scalar(detail, '$.data') AS payload
FROM pweb_enriched.all_events
WHERE json_extract_scalar(detail, '$.data.enrichment.userId') = 'abc-123-def'
  AND detail_type LIKE 'streaming.%'
  AND year = '2026' AND month = '06' AND day = '18'
ORDER BY time ASC;

Now you can see: the user started a streaming session, the recording began, audio chunks were captured… but no streaming.session.completed event. The session started but never finished. That narrows the investigation to the streaming backend.

Step 3: Correlate with Error Events

SELECT
    time,
    detail_type,
    json_extract_scalar(detail, '$.data.errorCode') AS error_code,
    json_extract_scalar(detail, '$.data.errorMessage') AS error_message,
    json_extract_scalar(detail, '$.data.enrichment.userId') AS user_id
FROM pweb_enriched.all_events
WHERE detail_type LIKE '%.error%'
  AND year = '2026' AND month = '06' AND day = '18'
  AND json_extract_scalar(detail, '$.data.enrichment.userId') = 'abc-123-def'
ORDER BY time ASC;

If there’s an error event, you’ve found your root cause. If there isn’t — and the session just stops — that’s a different kind of bug: the client disconnected without the backend emitting a failure event. Either way, the event timeline told you something the dashboard never could.

Cohort Deep-Dives: When the Number Looks Wrong

Your cohort view shows that the June 16 cohort had a 42% activation rate — down from 44% the week before. Is that noise, or is something broken?

Break the Cohort Apart

WITH cohort AS (
    SELECT
        user_id,
        email,
        plan,
        signed_up_at,
        first_session_at,
        first_doc_at,
        CASE
            WHEN first_doc_at IS NOT NULL THEN 'activated'
            WHEN first_session_at IS NOT NULL THEN 'tried'
            ELSE 'signed_up_only'
        END AS stage
    FROM user_milestones
    WHERE DATE_TRUNC('week', signed_up_at) = DATE '2026-06-16'
)
SELECT stage, COUNT(*) AS users, ARRAY_AGG(email) AS who
FROM cohort
GROUP BY stage;

Now you can see the actual people who signed up but never activated. Are they real users or spam signups? Are they on a specific plan? Did they all sign up at the same time (bot attack)?

Find Where They Dropped Off

SELECT
    c.email,
    c.signed_up_at,
    MAX(e.time) AS last_event_time,
    COUNT(*) AS total_events,
    ARRAY_AGG(DISTINCT e.detail_type) AS event_types
FROM cohort c
LEFT JOIN pweb_enriched.all_events e
    ON json_extract_scalar(e.detail, '$.data.enrichment.userId') = c.user_id
WHERE c.stage = 'signed_up_only'
  AND e.year = '2026' AND e.month = '06'
GROUP BY c.email, c.signed_up_at
ORDER BY total_events DESC;

If “signed_up_only” users have zero events after signup, they never came back. If they have events but no document — the feature is discoverable but something blocks completion.

Funnel Debugging: Finding the Leak

The predefined funnel view says 38% of users who start a session generate a document. But where in the session do the other 62% drop off?

SELECT
    has_recording,
    has_transcript,
    has_generation_started,
    has_document,
    COUNT(*) AS sessions
FROM (
    SELECT
        session_id,
        MAX(CASE WHEN detail_type = 'streaming.recording.started' THEN 1 ELSE 0 END) AS has_recording,
        MAX(CASE WHEN detail_type = 'streaming.transcript.received' THEN 1 ELSE 0 END) AS has_transcript,
        MAX(CASE WHEN detail_type = 'document.generation.started' THEN 1 ELSE 0 END) AS has_generation_started,
        MAX(CASE WHEN detail_type = 'document.generated' THEN 1 ELSE 0 END) AS has_document
    FROM pweb_enriched.all_events
    WHERE detail_type LIKE 'streaming.%' OR detail_type LIKE 'document.%'
      AND year = '2026' AND month = '06'
    GROUP BY session_id
)
GROUP BY has_recording, has_transcript, has_generation_started, has_document
ORDER BY sessions DESC;

Recording	Transcript	Gen Started	Document	Sessions
1	1	1	1	145
1	1	0	0	89
1	0	0	0	52
0	0	0	0	31

Now you know: 89 sessions got a transcript but never triggered generation. That’s the biggest leak. Is the “Generate” button hard to find? Is there an error? That’s your next investigation.

Ad-Hoc SQL for Security Investigations

When your security events view shows a spike in blocked requests, ad-hoc queries help you understand the attack pattern:

SELECT
    DATE_TRUNC('hour', time) AS hour,
    json_extract_scalar(detail, '$.data.sourceIp') AS source_ip,
    json_extract_scalar(detail, '$.data.field') AS target_field,
    json_extract_scalar(detail, '$.data.reason') AS block_reason,
    COUNT(*) AS attempts
FROM pweb_enriched.all_events
WHERE detail_type LIKE '%.spam_blocked%'
  AND year = '2026' AND month = '06' AND day >= '20'
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) > 5
ORDER BY attempts DESC;

Is it one IP hammering the contact form? Multiple IPs targeting the same field? A sudden burst or a slow drip? The aggregate “blocked: 47” in the dashboard doesn’t tell you — the ad-hoc query does.

Controlling Athena Costs

Athena charges £4 per TB scanned. Without care, ad-hoc queries can get expensive. Three rules:

1. Always Filter by Partition

Your data is partitioned by year/month/day. Every query should include partition filters:

-- Cheap: scans one day
WHERE year = '2026' AND month = '06' AND day = '24'

-- Expensive: scans everything
WHERE time > TIMESTAMP '2026-06-24 00:00:00'

The time filter looks equivalent but scans all partitions and then filters rows. The partition filter tells Athena to skip the files entirely.

2. Select Only the Columns You Need

Parquet is columnar — Athena only reads the columns you reference. SELECT * reads every column; SELECT time, detail_type reads two.

-- Reads ~200 bytes per row
SELECT time, detail_type, json_extract_scalar(detail, '$.subject')
FROM pweb_enriched.all_events
WHERE year = '2026' AND month = '06' AND day = '24';

-- Reads ~2 KB per row (every column in the table)
SELECT *
FROM pweb_enriched.all_events
WHERE year = '2026' AND month = '06' AND day = '24';

3. Use Workgroup Cost Controls

Set a per-query scan limit on your Athena workgroup. If an ad-hoc query accidentally scans too much data, it fails fast instead of racking up charges:

AthenaWorkgroup:
  Type: AWS::Athena::WorkGroup
  Properties:
    Name: ad-hoc-analytics
    WorkGroupConfiguration:
      BytesScannedCutoffPerQuery: 1073741824  # 1 GB limit
      EnforceWorkGroupConfiguration: true
      ResultConfiguration:
        OutputLocation: !Sub "s3://${QueryResultsBucket}/ad-hoc/"

With a 1 GB scan limit, the worst-case cost per query is about £0.004. You can run thousands of ad-hoc queries before spending £20.

Practical Athena Tips for Ad-Hoc Work

Extracting Nested JSON

Events stored in Parquet often have a detail column containing the full CloudEvents payload. Use json_extract_scalar for leaf values and json_extract for nested objects:

-- Leaf value
json_extract_scalar(detail, '$.data.orderId')

-- Nested object (returns JSON string)
json_extract(detail, '$.data.enrichment')

-- Array element
json_extract_scalar(detail, '$.data.items[0].name')

Saved Queries

Athena supports saved queries — named SQL that lives in the console. Save your most useful ad-hoc patterns so they’re one click away:

User timeline — parameterised by email
Error events for date range — parameterised by day
Session breakdown — the funnel debugging query above
Cohort deep-dive — parameterised by week

These aren’t views (they don’t appear in Glue) — they’re just bookmarks for queries you run often enough to save typing.

CTAS for Expensive Intermediate Results

If an ad-hoc investigation requires scanning large amounts of data multiple times, materialise the intermediate result with CREATE TABLE AS SELECT (CTAS):

CREATE TABLE ad_hoc.june_sessions
WITH (format = 'PARQUET', external_location = 's3://my-bucket/ad-hoc/june-sessions/')
AS
SELECT
    session_id,
    user_id,
    MIN(time) AS started_at,
    MAX(time) AS ended_at,
    COUNT(*) AS event_count,
    ARRAY_AGG(detail_type ORDER BY time) AS event_sequence
FROM pweb_enriched.all_events
WHERE detail_type LIKE 'streaming.%'
  AND year = '2026' AND month = '06'
GROUP BY session_id, user_id;

Now subsequent queries against ad_hoc.june_sessions scan a small Parquet file instead of the full event lake. Delete the table when you’re done.

When to Promote an Ad-Hoc Query

Not every ad-hoc query deserves to become a permanent view. Promote when:

Signal	Action
You ran the same query three times this week	Save as a named query
The question comes up in every bug investigation	Create a view in the enriched database
The answer should appear on the admin dashboard	Create a view + wire it to the UI
It was a one-off “I wonder if…”	Delete it and move on

The progression is: ad-hoc query → saved query → view → dashboard widget. Each step adds maintenance cost, so only promote what earns its keep.

The Investigation Toolkit

Here’s the set of ad-hoc query patterns worth having in your back pocket:

Pattern	When to use it
User timeline	Bug report from a specific user
Session breakdown	Funnel number looks wrong
Cohort deep-dive	Activation rate changed unexpectedly
Error correlation	Feature failure without obvious logs
Hourly event distribution	Checking for traffic anomalies or attacks
Feature adoption by plan	Deciding what to gate behind paid tiers
Time-to-action distribution	Understanding how long users take to reach milestones

Each of these takes 2-5 minutes to write and run. They cost fractions of a penny against partitioned Parquet data. And they answer questions that no predefined dashboard can anticipate.

Key Takeaways

Predefined views answer yesterday’s questions. Ad-hoc SQL answers the ones you haven’t thought of yet. Both run on the same event lake — the infrastructure investment pays off twice.
User timeline reconstruction is the killer query. When a user reports a problem, their full chronological event stream tells you more than any log file. Enrich events with user context at write time so this query is fast and cheap.
Drill into aggregate anomalies. When a cohort number or funnel metric looks wrong, break the aggregate apart — see the actual users, their events, where they stopped. The answer is always in the detail.
Control costs with three rules. Always filter by partition, select only needed columns, and set a workgroup scan limit. Ad-hoc queries on partitioned Parquet are pennies, not pounds.
Promote sparingly. Most ad-hoc queries should stay ad-hoc. Only graduate to a view or dashboard when you’re asking the same question repeatedly — the rest is exploratory work that served its purpose and can be discarded.

The most powerful analytics tool isn’t a dashboard — it’s the ability to ask a question you’ve never asked before and get an answer in 30 seconds.

Need help building an analytics stack that supports both dashboards and deep debugging? See our Architecture & Cloud Engineering services or get in touch for a free architecture review.