Skip to content
Tutorials Center
My learning

Topics

Lesson 52 of 57 ยท Real-World Projects

Project: Serverless Data Pipeline and Report

Build a serverless ETL on Azure: CSV lands in Blob Storage, a Python function cleans it, loads a database, and a timer builds a daily report with alerts.

Almost every business has a version of this task: a partner or a system drops a CSV file every day, someone has to clean it, put it into a database, and produce a report. In this project you design that pipeline with serverless pieces, so there are no servers to run and you pay only while the code executes.

The flow is ETL: extract the file from Blob Storage, transform it (validate, clean, de-duplicate), load it into a database, then report on the stored data on a schedule, with monitoring around every step. The Azure resources need an account, so their commands and code are read-only. The runnable part is a complete ETL written in Python and executed in real Pyodide in your browser, using the same logic the function would run, with sqlite3 playing the part of the database. You will use ideas from Azure Functions, Storage Accounts and Blob Storage, Data and Analytics Services and Azure SQL Database.

The architecture

flowchart LR
    S["Partner or system<br/>uploads sales-2025-03-05.csv"] --> I[("Blob container<br/>incoming")]
    I -->|"blob created"| F1["Function: ingest<br/>(validate, clean)"]
    F1 -->|"good rows"| DB[("PostgreSQL<br/>sales table")]
    F1 -->|"rejected rows"| R[("Blob container<br/>rejected")]
    F1 -->|"moves processed file"| A[("Blob container<br/>archive")]
    T["Timer 06:00 UTC"] --> F2["Function: daily report"]
    F2 -->|"reads"| DB
    F2 -->|"writes report"| O[("Blob container<br/>reports")]
    F1 -.-> M["Application Insights<br/>logs, metrics, alerts"]
    F2 -.-> M

Two functions with different triggers share one database. Bad rows go to a quarantine container instead of disappearing.

Design principles for the whole project:

  • Idempotent loads. Running the same file twice must not double the numbers. Storage events can be delivered twice, and people re-upload files.
  • Never lose data silently. Rows that fail validation are saved with the reason, so someone can fix the source.
  • Keep the raw file. The original goes to an archive container so you can re-run the pipeline after fixing a bug.
  • Observe everything. Count rows read, accepted and rejected on every run and alert when the numbers look wrong.

Step 1: storage and containers

Create a storage account with four containers: incoming, rejected, archive and reports (read-only commands):

Terminal
az group create --name rg-etl --location westeurope

az storage account create --name stetl001 --resource-group rg-etl \
  --sku Standard_LRS --allow-blob-public-access false

for c in incoming rejected archive reports; do
  az storage container create --account-name stetl001 --name $c --auth-mode login
done

A lifecycle rule can move archive blobs to the cool or archive tier after 30 days and delete them after a year (see Storage Accounts and Blob Storage). The function app's managed identity receives Storage Blob Data Contributor on the account; no connection strings or keys appear anywhere (see Managed Identities and Service Principals).

Step 2: the ingest function

The Python v2 programming model declares triggers with decorators. This code is read-only: it needs the Functions runtime and the Azure SDK packages. It shows the shape of the real thing:

Python
import logging
import azure.functions as func

app = func.FunctionApp()

@app.blob_trigger(arg_name="blob", path="incoming/{name}", connection="StorageConn")
def ingest(blob: func.InputStream):
    name = blob.name.split("/")[-1]
    text = blob.read().decode("utf-8-sig")        # utf-8-sig removes a BOM added by Excel

    result = transform(text)                      # pure function: easy to unit-test
    with get_connection() as conn:
        inserted = load(conn, result.accepted)    # idempotent: ON CONFLICT DO NOTHING

    save_rejected(name, result.rejected)          # quarantine container
    archive(name)                                 # copy to archive, delete from incoming

    logging.info("etl_summary %s", {
        "file": name, "read": result.read, "accepted": len(result.accepted),
        "rejected": len(result.rejected), "inserted": inserted,
    })

The important design choice is that transform and load are ordinary functions with no Azure types in them. That makes them testable on a laptop (as you will do below), and it keeps the cloud code thin. A blob trigger that polls can be slow to notice files on busy accounts; if latency matters, use the Event Grid based trigger source or an Event Grid subscription feeding a queue, as built in Project: Event-Driven Image Processing.

The database connection uses a managed identity token instead of a password, exactly as in Project: Containerized API with a Database:

Python
import os
import psycopg
from azure.identity import DefaultAzureCredential

def get_connection():
    token = DefaultAzureCredential().get_token(
        "https://ossrdbms-aad.database.windows.net/.default").token
    return psycopg.connect(
        host=os.environ["PGHOST"], dbname=os.environ["PGDATABASE"],
        user=os.environ["PGUSER"], password=token, sslmode="require")

Step 3: transform, load and report (runnable)

Now the core of the project. The program below contains a small CSV with typical problems: missing values, a negative quantity, an impossible date, a non-numeric price, inconsistent case and spaces, and a duplicate order. It runs the whole pipeline: transform into accepted and rejected rows with reasons, load into a database table with a primary key, load the same file a second time to prove idempotency, then report with SQL and check an alert threshold.

It is a stand-in in two ways: sqlite3 replaces PostgreSQL (the SQL used here is portable), and a string replaces the blob. The logic is the logic you would deploy.

Python A complete ETL: clean, load, report and check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output from top to bottom. Each rejected row carries a line number and a reason, which is what you would write into the rejected container. The second load inserts nothing, because ON CONFLICT DO NOTHING plus a primary key makes the load safe to repeat. The accepted rows are also normalized (spaces stripped, country upper-cased), so us and US land in one group in the report. A pipeline's quality is largely decided by these small rules.

A few points about doing this for real:

  • Validate in one place, load in bulk. Use batched inserts or COPY in PostgreSQL for large files; row-by-row inserts over a network are slow.
  • Decide what a bad row means. Here a bad row is skipped and reported. Some feeds must reject the whole file if too many rows are bad; the 20 percent threshold above is a business decision, not a rule of nature.
  • Upserts versus ignore. If a corrected order may arrive later, use ON CONFLICT (order_id) DO UPDATE and keep an audit trail instead of ignoring it.
  • Use a staging table for big loads: load raw rows, validate with SQL, then insert into the final table in one transaction.
  • Time limits. On the Consumption plan a function has a maximum run time. For huge files, split work across queue messages or use Data Factory (see Data and Analytics Services).

Step 4: the scheduled report

A timer trigger runs a function on a schedule written as an NCRONTAB expression with six fields: second, minute, hour, day of month, month and day of week. All times are UTC unless you configure a time zone setting for the app. Read-only code:

Python
@app.timer_trigger(schedule="0 0 6 * * *", arg_name="timer", run_on_startup=False)
def daily_report(timer: func.TimerRequest):
    if timer.past_due:
        logging.warning("timer is running late")
    with get_connection() as conn:
        report = build_report(conn)               # the SQL from the runnable example
    upload_blob("reports", f"sales-{date.today()}.txt", report)

0 0 6 * * * means "at second 0, minute 0, hour 6, every day". The next stand-in is a small NCRONTAB evaluator that lists upcoming run times, so you can check an expression before deploying it. It supports numbers, *, */n steps and comma lists, which is enough for most schedules; it ignores ranges and names for brevity, and treats weekday 0 as Sunday.

Python Check an NCRONTAB schedule before deploying

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Checking a schedule this way is quick insurance against the classic mistake of writing a five-field Unix cron expression, which Functions rejects, and against forgetting that the times are UTC.

Step 5: monitoring and alerts

A pipeline that fails quietly is worse than one that fails loudly. Send logs to Application Insights and check these things (see Azure Monitor, Log Analytics and Application Insights):

  • Failures: any exception in ingest or daily_report should raise an alert through an action group (email, chat or a ticket).
  • Freshness: no etl_summary message in 24 hours means the partner stopped sending, or the trigger broke.
  • Quality: a rejected ratio above your threshold means the source changed.

A KQL query for the daily trend (read-only; the property names depend on how you log):

KUSTO
traces
| where timestamp > ago(7d)
| where message startswith "etl_summary"
| extend d = parse_json(replace_string(replace_string(message, "etl_summary ", ""), "'", "\""))
| project timestamp, file = tostring(d.file), read = toint(d.read), rejected = toint(d.rejected)
| extend rejected_pct = round(100.0 * rejected / read, 1)
| order by timestamp desc

A log-based scheduled query alert can run a query like this every few minutes and fire when it returns rows. Pair it with a missing data alert for the freshness check. Finally, add a budget (see Pricing, Cost Management and Budgets): the Consumption plan is cheap, but a loop that re-triggers itself is not.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is the load step written with ON CONFLICT DO NOTHING and a primary key?

    Events can be delivered twice and files can be re-uploaded. An idempotent load makes repeating the work harmless.
  2. 2. What should happen to rows that fail validation?

    Silent loss hides problems at the source. Quarantining rows with reasons lets someone fix and replay them, and the counts feed your alerts.
  3. 3. What does the schedule '0 0 6 * * *' mean in an Azure Functions timer trigger?

    NCRONTAB has six fields: second, minute, hour, day, month and day of week. Here second 0, minute 0, hour 6, every day.
  4. 4. Why keep transform and load as plain functions without Azure types?

    Separating business logic from triggers and SDK calls makes it testable and portable, as the runnable example shows.
  5. 5. A freshness alert fires because no etl_summary log appeared for 24 hours. What is the most likely meaning?

    A missing success signal means the pipeline did not run or did not finish. Alerting on absence catches failures that never raise an exception.

Summary

  • A serverless ETL: a blob arrives, a function cleans and loads it, rejected rows are quarantined, the original is archived, and a timer function builds a report.
  • Keep transform and load as pure, testable functions; keep triggers and SDK calls thin.
  • Make loads idempotent with keys and ON CONFLICT, normalize data (trim, case) before it reaches the database, and record a reason for every rejected row.
  • NCRONTAB timer schedules have six fields and run in UTC by default; check them before deploying.
  • Log row counts as a structured summary, then alert on failures, freshness and quality.
  • Use managed identities for storage and the database, and set a budget.

Next: Project: Secure Three-Tier Network Architecture.