Debugging a SQL-Triggered Azure Function: Real Bugs from an Event-Driven Pipeline

Batch pipelines fail loudly. Event-driven ones fail quietly — a trigger keeps firing, the logs look almost normal, and nothing actually happens. This post is a field report from a real pipeline I built and debugged: an Azure Function triggered by inserts into a SQL table, running mood analysis on incoming chat messages and pushing the results to a FHIR healthcare server.

The architecture was simple on paper:

dbo.messages (INSERT)  ──SQL trigger──▶  Azure Function
                                            │  parse change event
                                            │  filter sender = 'user'
                                            │  analyze mood (Azure OpenAI)
                                            ▼
                                     FHIR Observation  ──POST──▶  FHIR server

And yet for the first day it processed exactly zero messages while looking like it was working. Here’s every bug I hit, how I found it, and what it taught me.

Bug #1 — The case-sensitivity trap that ate every message

Symptom: the logs were full of Change missing 'operation' field — even though I could see perfectly valid change events arriving.

Investigation: I dumped the raw payload the SQL trigger actually delivers. This is what it looked like:

[
  {
    "Operation": 0,
    "Item": {
      "id": "2820eb1c-f63c-4178-8b34-a21ebd8cb9e2",
      "sender": "user",
      "message": "Am tired",
      "created_at": "2025-03-13T13:33:23.389+01:00"
    }
  }
]

There it was: the trigger sends "Operation" with a capital O. My code was reading change.get("operation") — lowercase. That returned None, my if not operation: guard fired, and the message was silently skipped. Every single time.

Fix: handle both casings defensively at the boundary, since the payload shape isn’t mine to control:

operation = change.get("operation") or change.get("Operation")

Lesson: at any integration boundary, the payload shape is a contract someone else owns. Never assume field casing — log the raw event once, verify it, and code defensively against what actually arrives, not what the docs imply.

Bug #2 — The “infinite loop” that was really an early exit

Symptom: the function seemed to hang forever, processing nothing. It felt like an infinite loop.

Investigation: there was no loop. Because Bug #1 made JSON handling bail out early, the trigger kept re-firing on the same backlog of changes, never advancing past the parse step. No logs appeared beyond the missing 'operation' line — which is exactly what “stuck” looks like in an event-driven system.

Fix: once parsing was corrected, I added an explicit empty-batch guard so a genuinely empty changes_list short-circuits instead of spinning:

changes_list = json.loads(changes)
if not changes_list:
    logging.info("Empty change batch — nothing to process.")
    return

Lesson: in event-driven systems, “hanging” is usually an early exit in disguise. If you can’t see a step in the logs, assume execution never reached it — don’t assume it’s slow.

Bug #3 — Sender filtering that never ran

Symptom: messages from sender = 'assistant' were (apparently) getting through, not just 'user' messages.

Root cause: my in-code filter if sender != "user" was correct — but it lived after the parsing step that Bug #1 was aborting, so it never executed. There was also no database-level filter as a safety net.

Fix: I strengthened the in-code check and added logging to confirm the actual sender values flowing through, so I could see the filter doing its job rather than trust it blindly.

Lesson: a filter you can’t observe is a filter you don’t trust. Log what it lets through and what it drops, at least until you’ve seen it behave in production.

Bug #4 & #5 — The FHIR server fought back

Once messages finally flowed end-to-end, the FHIR upload surfaced two environment problems:

• Sorry, you have been blocked — a Cloudflare block, caused by pointing at a raw http://3.142.231.50:80 address with a questionable auth token.
• Not Found — an Apache 404 from a FHIR_SERVER_URL aimed at an endpoint that didn’t exist.

Fix: use a valid HTTPS FHIR base URL, verify the auth token, and — critically — reproduce the call outside the function first:

curl -i -H "Authorization: Bearer $FHIR_AUTH_TOKEN" \
     https://your-fhir-server/fhir/metadata

If curl can’t reach it, your function never will.

Lesson: isolate the external dependency before blaming your own code. A one-line curl would have saved me an hour of re-reading Python that was already correct.

The bigger design question: SQL trigger vs. timer trigger

Half the value of this exercise was deciding how the pipeline should be driven at all. Here’s the comparison I landed on:

Where I landed: a timer trigger is proactive and periodic — you write custom logic to go fetch new data. A SQL trigger is reactive and event-driven — changes come to you. Because the requirement was to process messages the moment they arrive, the SQL trigger won. The cost of that choice is exactly the class of bugs above: you inherit whatever payload shape the change-tracking layer hands you.

The setup, for reference

The trigger itself is a few lines:

import azure.functions as func

app = func.FunctionApp()

@app.sql_trigger(
    arg_name="changes",
    table_name="dbo.messages",
    connection_string_setting="SqlConnectionString",
)
def chat_with_knowbot_sql_trigger(changes: str) -> None:
    changes_list = json.loads(changes)
    if not changes_list:
        return
    for change in changes_list:
        operation = change.get("operation") or change.get("Operation")
        if operation is None:
            logging.warning("Change missing operation field: %s", change)
            continue
        item = change.get("item") or change.get("Item", {})
        if item.get("sender") != "user":
            continue
        # ... mood analysis + FHIR upload ...

Configuration lives in local.settings.json (never in code):

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "SqlConnectionString": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=your-server;PORT=1433;DATABASE=your-database;UID=your-username;PWD=your-password;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;",
    "FHIR_SERVER_URL": "https://your-fhir-server/fhir",
    "FHIR_AUTH_TOKEN": "your-token"
  }
}

And the Python dependencies:

azure-functions==1.17.0
openai==1.14.0
requests==2.31.0
pyodbc==5.1.0
python-dotenv==1.0.0

What I’d carry into the next event-driven build

1. Log the raw event once, on day one. Every bug here traced back to assuming the payload shape instead of looking at it.
2. Treat “nothing happened” as an early exit, not slowness. Add a log line at every stage boundary so silence is diagnostic.
3. Push filtering as close to the source as possible. An in-code sender = 'user' check works, but a database-level view or filter is a safety net that survives code bugs.
4. Reproduce external calls outside the function. curl first, debug your code second.

Event-driven pipelines are wonderful when they work and maddening when they don’t — because the failure mode is silence. The fix, every time, was to make the invisible visible: log the payload, log each stage, and test each dependency in isolation.

I build production data platforms and pipelines across AWS, GCP, and Azure. If you’re wiring up event-driven systems and hitting the quiet-failure wall, I’d like to compare notes — find me on LinkedIn.