Finding answers hidden in application error logs.

The problem

Structured reporting could show that certain packages failed an automated reconciliation process, but it often did not explain why. The underlying explanation was buried inside application error logs and mixed payload text.

How I investigated it

I traced failures into the logs, looked for recurring patterns, extracted useful identifiers from complex text, and converted long-form error messages into standardized reason categories. When the evidence was unclear, I validated conclusions with the appropriate business and technical teams.

Technique

-- Synthetic example illustrating the approach
SELECT
    event_id,
    REGEXP_EXTRACT(inbound_payload, '420[0-9]+', 0) AS tracking_identifier,
    CASE
        WHEN error_text LIKE '%manifest lookup%' THEN 'Manifest Lookup'
        WHEN error_text LIKE '%invalid account%' THEN 'Account Validation'
        WHEN error_text LIKE '%timeout%' THEN 'System Timeout'
        ELSE 'Needs Review'
    END AS reason_category
FROM synthetic_application_logs;

What this enabled

• Consistent grouping of previously difficult-to-interpret failures.
• Faster identification of technical versus business-process issues.
• Reusable reason codes for reporting and downstream analysis.
• A clearer path from raw error text to the team or action required to resolve the issue.

Public lab