Connect Your Analysis to a Live System, and Stop Bad Data Before It Reaches Anyone

T10-L04 · Data & analysis · Level 4 Integrator · 18-minute read, plus 4 hours of doing

The report was on time, and wrong

Deniz's cost review from T10-L03 works: anyone in finance can rerun a month from copied files and get the controller's numbers. So the controller asks for the next step. Every Monday at seven, the review should read the actuals straight from the ERP, the company's business software, and have a draft commentary ready for the management meeting at nine.

It's easy to build that badly. A login borrowed from a colleague in finance can read every booking in the ERP, including each employee's salary line. On the weekend the ERP's reporting copy fails to refresh, and on Monday the pipeline reports last Thursday's figures as this week's, without a word. The draft commentary goes to an AI model together with the booking texts, and they contain employees' names.

In a lab it's Clara's HPLC summary. The results now come from the institute's LIMS, the system that tracks samples and results. After an instrument update, the LIMS reports one assay in µg/mL instead of mg/L. The pipeline runs, and the chart shows a thousand-fold increase.

You are at Level 4 Integrator. The pipeline now reads a live system that belongs to other people, holds personal data, and changes without telling you. This book shows you how to connect it with the smallest access that works, and how to make it stop, not guess, when the data are wrong.

After this you can

• Get a read-only account from IT that sees one database view with only the fields your task needs, and prove it can't write.
• Write quality checks for shape, completeness, duplicates, gaps, ranges and freshness that stop the run before any output is written.
• Send an AI model only the fields you have cleared, and test that nothing else can reach it.
• Schedule the run so it can't overlap itself, keeps the last good result when a check fails, and tells a named person why.

Before you start

• Books: T10-L03. This book extends its pipeline: the uv environment, the sample with known answers, the check script and the README. T12-L04 for how IT handles service accounts and passwords. The four-quadrant method is in T01-L01.
• The people who can say yes: the data owner (the head of finance, or the lab head who owns the LIMS data), IT or the database administrator, and your data protection officer if any field could identify a person.
• An AI model your organisation has approved for internal figures, reachable from a script, if your pipeline drafts text. Many organisations provide one through an internal gateway.
• About 4½ hours: 18 minutes to read, about 4 hours of doing, over two weeks.

Last verified 23 September 2026. Pandera, the checking library in this book, is free under the MIT licence; its current version, 0.33.1, installs alongside the pandas 3 from T10-L03. The database driver psycopg is free too. Details for experts at the end of this book.

The idea in one page

Moving from files to a live source changes four things. The source can change shape overnight, without anyone committing code. The account can read far more than you need. The data can contain people. And the run happens when nobody is watching. Each one needs its own answer.

1. The smallest access that works. This is called least privilege. Don't ask for a login to the system; ask for a view, a saved query in the database that shows only the rows and columns your task needs, already summed up if possible. The pipeline gets a service account, a login for a program rather than a person, that may read that view and nothing else. Then prove it: try to write with it, and see it refused.

2. Checks that stop before output. A quality check is a rule the data must meet before anything is calculated from them. Six catch almost every real failure:

Set every threshold before you see a failure, with the person who knows the data, and write down who agreed it. A threshold changed after a failure to make the run pass is no longer a check.

3. Choose the fields before the model. Select only the fields you need in the view, not in the prompt. An AI model that drafts text gets an even smaller set: the aggregated table, no names, no free text. A test proves it: plant a fake name in a test row, and check it never appears in anything that leaves the pipeline.

4. A run nobody watches. Pick one rule for when a run is still going as the next one starts. The simplest is skip: the second run exits and says so. Write the new output to a temporary file and swap it in only after every check has passed, so a failed run leaves last week's result exactly as it was. And when the run stops, a named person hears why, the same morning.

Who signs. The data owner signs the field list: these fields, for this purpose. IT signs the account and the view. The data protection officer (DPO) looks at anything that could identify a person, including what goes to the model. Where a view would show individual employees' data, in a company with a works council, the elected staff body, it has a say too. Keep that page with the pipeline.

The way back. A read-only account can't damage the source. Outputs are dated files, never overwritten in place, so a bad week can be withdrawn and rerun. And the service account can be switched off by IT in one step.

Worked example: two live sources, four quadrants

Both versions follow the four quadrants from T01-L01. This time the company one is told in full, because it continues where T10-L03 stopped. Names, numbers and dates are illustrations.

Company: the Monday cost review, straight from the ERP

Structure it. Deniz writes it down with the controller. "Every Monday by 07:30: actuals month to date per cost centre and cost type, compared with the valid budget, plus a draft commentary for the management meeting. A useful result: the numbers are right, or the run says clearly why it stopped; nobody's personal data leaves finance."

Understand it. She asks IT for access, and the first answer is fast: the standard finance reporting login, the one the team's spreadsheets use. She tries it on the ERP's reporting copy, a second database IT refreshes from the live ERP every night so reports don't slow it down. She writes down what she finds.

• The login reads 340 tables, including personnel costs per employee, with names and salary lines.
• The booking texts, free text that people type into each posting, contain names too: "Travel Hamburg M. Schulz".
• The reporting copy has a column with the time of its last refresh. On the weekend of 13 September the refresh failed. A pipeline run that Monday would have reported Thursday's figures as current.
• The personnel cost type is one line per employee. Summed per cost centre, it identifies nobody except in two cost centres with only one employee.

The finding that changes her problem is the first one. She had been thinking about checks. The bigger risk was the access: a scheduled script with a password that reads everyone's salary. Back to Structure it: "The pipeline sees one view: actuals summed per cost centre, cost type and month, with no booking text and no employee. Personnel costs in cost centres with fewer than three people are merged into one line. The pipeline stops if the copy is older than one day. The model gets the summary table only."

Choose tools.

• A PostgreSQL view on the reporting copy, created by IT, and a service account svc_cost_review that can read only that view. The ERP's reporting copy already runs on PostgreSQL, a common free database, so there is nothing new to install.
• psycopg, the standard Python library for talking to PostgreSQL, added to the T10-L03 project with uv. (For a Microsoft SQL Server source, the equivalent is pyodbc.)
• Pandera for the checks. It's a Python library in which you declare what a table must look like (columns, types, allowed values, ranges) and it checks a pandas table against that and lists every failure at once. It fits because Deniz already works in pandas and the rules read like a table. The alternative is Great Expectations, which adds browsable reports of every check but needs much more setup.
• The model your organisation has approved for internal figures, called from the script, for the draft commentary.
• Windows Task Scheduler on the finance reporting server, which IT already manages, to run the job every Monday at 06:00 as the service account.

Build it: the view and the account (with IT). Deniz writes the field list on one page, with a purpose line, and the head of finance signs it as data owner. IT turns it into three statements on the reporting copy:

-- the only thing the pipeline can see: sums, no names, no booking text
CREATE VIEW reporting.v_cost_actuals AS SELECT ... ;
-- a login for the pipeline that may read that view and nothing else
GRANT SELECT ON reporting.v_cost_actuals TO svc_cost_review;
-- every session of this login is read-only, whatever the script asks for
ALTER ROLE svc_cost_review SET default_transaction_read_only = on;

The view has six columns: cost centre, cost type, month, the actual amount in euros, the number of bookings that went into each sum, and the time of the last nightly copy. The DPO reads the field list, asks about the two small cost centres, sees that they're merged, and signs. IT puts the connection line, meaning the server, the database, the account name and its password in one string, into the Task Scheduler job's settings as an environment variable called COST_DB. An environment variable is a setting the operating system hands to the program when it starts. The password never appears in the code, the README or Git.

Then she proves the limits, with IT watching. As svc_cost_review she tries to read the personnel table: permission denied. She tries to update one number in the view: cannot execute UPDATE in a read-only transaction. Both messages go into a test note next to the field list.

Build it: the checks. She adds the two libraries to the T10-L03 project:

uv add "psycopg[binary]" pandera

uv installs them at pinned versions and records them in the lockfile, as in T10-L03. The [binary] part brings along the PostgreSQL client library, so nothing else needs installing on the server. Then she writes the checks together with the controller, who knows the data. Every threshold has a reason, written next to it:

# checks.py — the rules the live data must meet before anything is calculated
import pandera.pandas as pa

COST_CENTRES = ["1000", "1100", "1200", "2000", "2100", "3000", "3100",
                "3200", "4000", "4100", "5000", "6000", "7000", "9000"]

actuals = pa.DataFrameSchema(
    {
        "cost_centre": pa.Column(str, pa.Check.isin(COST_CENTRES)),
        "cost_type": pa.Column(str),
        "month": pa.Column(str, pa.Check.str_matches(r"^\d{4}-\d{2}$")),
        # a single cost line above 2 million or below minus 500,000 has never
        # happened; controller, 2026-10-01
        "actual_eur": pa.Column(float, pa.Check.in_range(-500_000, 2_000_000)),
        "postings": pa.Column(int, pa.Check.ge(1)),
        "refreshed_at": pa.Column(nullable=False),   # age checked below
    },
    strict=True,                                  # no extra or missing columns
    unique=["cost_centre", "cost_type", "month"],  # no duplicates
)

strict=True makes Pandera stop on any column that isn't in the list, so a new field in the view can't slip through. Gaps are covered too: a column in Pandera doesn't accept empty values unless you say it may. Two checks don't fit a table rule, so they go into the pipeline itself: every cost centre must appear in the current month, and refreshed_at must be less than 24 hours old.

Build it: the pipeline. She asks OpenCode in Plan mode, as in T10-L03, where it proposes a change before writing anything, to extend analyse.py: connect read-only, run the checks, stop with a clear message on any failure, write the output only after everything passed, and send only four columns of the summary to the model. After her review, the new part reads:

import os, sys, tempfile, datetime as dt
import pandas as pd, pandera.errors, psycopg
from checks import actuals, COST_CENTRES

LOCK = OUT_DIR / "run.lock"
try:
    LOCK.touch(exist_ok=False)          # skip if last run is still going
except FileExistsError:
    stop("SKIPPED: the previous run is still going (or crashed; see README).")
try:
    if "test_rows" in P:                # check.py only: a saved copy, no database
        rows = pd.read_pickle(settings.parent / P["test_rows"])
    else:
        with psycopg.connect(os.environ["COST_DB"]) as conn:
            conn.read_only = True       # second lock behind the database's own
            cur = conn.execute("SELECT cost_centre, cost_type, month, actual_eur::float8, "
                               "postings, refreshed_at FROM reporting.v_cost_actuals "
                               "WHERE month >= %s", (P["from_month"],))
            rows = pd.DataFrame(cur.fetchall(), columns=[c.name for c in cur.description])
    try:
        rows = actuals.validate(rows, lazy=True)   # lazy: list every failure
    except pandera.errors.SchemaErrors as err:
        stop(f"STOP: {len(err.failure_cases)} rule(s) failed:\n{err.failure_cases}")
    now_month = rows[rows["month"] == P["month"]]
    missing = sorted(set(COST_CENTRES) - set(now_month["cost_centre"]))
    if missing:
        stop(f"STOP: no actuals for cost centres {missing}")
    age = dt.datetime.now(dt.timezone.utc) - rows["refreshed_at"].max()
    if age > dt.timedelta(hours=24):
        stop(f"STOP: reporting copy is {age} old; last nightly refresh failed?")

    summary = compare_with_budget(rows, P)      # the T10-L03 logic, unchanged
    payload = summary[["cost_centre", "budget_eur", "actual_eur", "variance_pct"]]
    commentary = draft_commentary(payload.to_csv(index=False))
    with tempfile.NamedTemporaryFile("w", dir=OUT_DIR, delete=False,
                                     suffix=".tmp", encoding="utf-8") as tmp:
        tmp.write(render_report(summary, commentary))
    os.replace(tmp.name, OUT_DIR / f"cost-review-{P['month']}-{dt.date.today()}.md")
finally:
    LOCK.unlink(missing_ok=True)

P holds the month's settings and OUT_DIR is the output folder, as in the T10-L03 script. In the query, ::float8 turns the database's exact decimal amounts into ordinary numbers, which the schema expects.

Each part answers one of the four changes. The lock file makes a second run skip while the first is still going. The two read-only locks mean the script can't write, even by mistake. Pandera lists every broken rule at once, and the two extra checks catch a missing cost centre and a stale copy.

The report is written to a temporary file and only renamed into place, in one step, after everything passed; a stop leaves last week's report untouched. stop() is a small function she adds near the top of the script. It writes the message to a log, emails it through the company's mail relay, the internal server that passes on mail from programs, and ends the run. IT gives her the relay's name for a second environment variable; the sender and the two recipients go into the settings as mail_from and notify:

import smtplib
from email.message import EmailMessage

def stop(text):
    """Log why the run stopped, email Deniz and Ole, end the run."""
    with open(OUT_DIR / "run.log", "a", encoding="utf-8") as fh:
        fh.write(f"{dt.datetime.now():%Y-%m-%d %H:%M} {text}\n")
    if "test_rows" not in P:                   # check.py runs send no email
        msg = EmailMessage()
        msg["Subject"], msg["From"], msg["To"] = text.splitlines()[0], P["mail_from"], P["notify"]
        msg.set_content(text)
        with smtplib.SMTP(os.environ["MAIL_RELAY"]) as relay:
            relay.send_message(msg)
    sys.exit(text)

draft_commentary(), also near the top, sends the four columns to the approved model with a fixed instruction: "Write three sentences for the management meeting on the largest variances. Use only these numbers." The company's gateway accepts the same kind of request as OpenAI's service, so she uses the openai library, added with uv add openai. The gateway's address, key and model name come from three more environment variables. In a check run the function only saves what it would have sent:

from openai import OpenAI

INSTRUCTION = ("Write three sentences for the management meeting on the largest "
               "variances. Use only these numbers.")

def draft_commentary(table_csv):
    if "test_rows" in P:                       # check.py: keep the payload, send nothing
        (OUT_DIR / "payload.csv").write_text(table_csv, encoding="utf-8")
        return "(check run, no model call)"
    client = OpenAI(base_url=os.environ["MODEL_URL"], api_key=os.environ["MODEL_KEY"])
    answer = client.chat.completions.create(model=os.environ["MODEL_NAME"], messages=[
        {"role": "system", "content": INSTRUCTION}, {"role": "user", "content": table_csv}])
    return answer.choices[0].message.content

If your gateway takes a different kind of request, ask IT for its example; only these few lines change. The report shows the draft under the heading "Draft commentary (AI), to be checked by the controller". The controller edits it before the meeting; the figures above it come from the pipeline, not the model.

Understand it: break it on purpose. A check you haven't seen fail is a guess. She saves one week's output of the view as a test file with pandas' to_pickle, which keeps every column and its type. Her T10-L03 check script now builds broken versions of it on each run: one with a renamed column, one with cost centre 4100 missing, one with refreshed_at two days old. It also builds one with a test row in which the cost type reads "Personal M. Schulz", a fake name planted to see whether it travels.

Each copy gets a small settings file that names it as test_rows. The check sets the refresh time to an hour ago in every copy except the stale one, so the saved week doesn't count as old. Then it runs the pipeline on each, without the database:

• renamed column: STOP, "column 'actual' not in schema";
• missing cost centre: STOP, "no actuals for cost centres ['4100']";
• stale copy: STOP, "reporting copy is 2 days old";
• planted name: the check searches payload.csv, what would have gone to the model, and the report for "Schulz", and finds it in the report. The model payload was clean, because it takes only four columns, but the full summary table in the report listed cost types by name.

That last one sends her back to Structure it. Cost types are free-ish text maintained by accounting, and someone could type a name into one. The field list changes: the view shows the cost type's number and a name from the chart of accounts, never typed text. The data owner signs version 2. The test passes.

Build it: the schedule and the run sheet. IT sets up the Task Scheduler job: Mondays at 06:00, as svc_cost_review, with the password in the job's environment. The run sheet, one page next to the README:

• A STOP email arrives: the last report is still in place and still dated. Read the message. A schema or completeness stop goes to IT with the message; a stale copy means the nightly refresh failed, so ask IT to rerun it, then start the job by hand: in Task Scheduler, right-click the job and choose Run. Tell the controller the report is late, and why.
• SKIPPED: the previous run is still going or crashed. If nothing is running, delete run.lock and start again.
• A wrong report went out: move it into withdrawn/, tell the controller, fix, rerun. Nothing in the ERP needs undoing, because the pipeline can't change it.
• Switch it off: IT disables the Task Scheduler job and the service account. Both steps are in IT's ticket, with names.

Her deputy, Ole, reruns a stopped week from the run sheet alone: he restores the fresh copy with IT, starts the job, and finds the new report next to the old one, both dated.

In the first month the pipeline stops twice: once for a failed nightly refresh, once because accounting created a new cost centre, 4200, which wasn't on the list. The second stop is right. The controller adds 4200 to the list, with a date and her initials, and the run goes through.

What it taught her: the checks took an afternoon. The access, the field list and the signed version 2 took two weeks, and they're what made it safe to run unwatched.

Lab: HPLC results from the LIMS

Structure it. Clara's summary from T10-L03 should read new results directly from the institute's LIMS every Friday, instead of from exported files.

Understand it. The LIMS administrator shows her the data. Each result row carries the sample ID, group, injection, result, unit and instrument, but also the operator's name and, for samples from the clinical collaboration, a pseudonymised patient code. That code is personal data: the clinic holds the key. And the unit isn't fixed: after last month's instrument update, one assay reports µg/mL. Back to Structure it: "The pipeline reads a view with only the six fields the summary needs, never the operator or the patient code, and stops on any unit it doesn't expect."

Choose tools and Build it. The same pattern. The LIMS administrator creates the view and a read-only account; the lab head signs the field list as data owner; the DPO confirms the patient code stays out. Clara's Pandera rules add unit with pa.Check.isin(["mg/L"]), and the range check on results is 0 to 500 mg/L, agreed with the PI, the group's lead researcher. On the first Friday the run stops: "unit 'µg/mL' not allowed". The chart from last week stays on the group drive. The LIMS administrator converts the unit in the view, and the next run passes. A stop, not a wrong chart.

In both cases the checks were the visible part. What made the connection safe was the view: one page of fields, signed by the person who owns the data, read by an account that can't do anything else.

What goes wrong

Borrowing a person's login. Symptom: the pipeline runs with a colleague's or your own account, which reads far more than the task needs and stops working when that person leaves. Fix: a service account that reads one view, with the password held by IT in the scheduler, and a test that it can't read or write anything else.

Selecting everything. Symptom: the query starts with SELECT *, and a new column with names or salaries flows through to the report. Fix: name every field in the view and in the query, set strict=True in the schema, and have the data owner sign the list.

Thresholds set after the failure. Symptom: a range is widened on Monday morning so the report goes out. Fix: agree thresholds in advance with the person who knows the data, write down who and when, and change them only with the same person, as a dated change.

A stale source served as fresh. Symptom: numbers look plausible, but they're from before the weekend. Fix: a freshness check on the source's own refresh time, with a limit that fits its schedule.

A failed run that overwrites the good one. Symptom: half a report replaced last week's full one. Fix: write to a temporary file, rename it into place only after every check passed, and keep dated outputs.

Names reaching the model through a side door. Symptom: the model payload is clean, but a free-text field or a label carries a name into the report or the prompt. Fix: a planted fake name in the test data, and a check that searches everything leaving the pipeline for it.

Nobody hears about the stop. Symptom: the pipeline stopped correctly on Monday, and the controller finds out at the meeting. Fix: the stop message goes by email to a named person and a deputy, and the run sheet says what each message means.

Do it yourself (4 hours, over two weeks)

1. Structure it (15 min). Write what the pipeline must deliver, when, from which system, and what must never leave it.
2. Understand it (60 min). With IT, look at the source through a trial account. List every field, which ones identify people, how the source refreshes, and what has changed in it recently. Rewrite your problem sentence.
3. Choose tools (15 min). Write the field list with a purpose line. Decide the checks, the schedule and the overlap rule. Get the data owner's signature on the field list, and the DPO's where people are involved.
4. Build it: access (30 min, plus waiting). Ask IT for the view and a read-only service account. Prove it: try to read something else, and try to write.
5. Build it: checks and pipeline (60 min). Add psycopg and Pandera with uv. Write the schema with the person who knows the data, add completeness and freshness, the lock, the temporary-file swap and the stop email. Keep the model payload to the cleared columns.
6. Understand it: break it (40 min). Save one output of the view with to_pickle. Have the check make three broken copies and one with a planted fake name, each named as test_rows in its own settings file. Run the check. Fix what gets through.
7. Build it: schedule and run sheet (20 min). Have IT schedule the job under the service account. Write the run sheet, and let a colleague rerun a stopped week from it.

Exit check

Passing test: a scheduled pipeline reading a live source through a read-only account, with:

• the signed field list, and a test note showing the account can't read outside its view or write anything;
• the quality checks, with each threshold's reason and who agreed it;
• a check script with at least three deliberately broken copies of the source and one planted fake name;
• a run sheet with what each stop means and who to tell.

It passes when each broken copy makes the run stop with a clear message and leaves the last accepted output unchanged, the planted name appears nowhere outside the pipeline, and a colleague has rerun a stopped week from the run sheet.

Rule to remember

Give the pipeline one view it can only read, and let it stop rather than guess.

Details for experts

Checked on 23 September 2026. You don't need this to follow the book; it's for setting things up or answering a colleague.

Keeping the pipeline running for months, with drift and versioned outputs: more in T10-L05.

Further reading and tools

Next books

• T10-L03 · Build an Analysis Your Colleague Can Rerun and Get the Same Numbers: the pipeline this book connects.
• T10-L05 · Keep a Data Pipeline Right for Years, and Trace Any Published Number to Its Source: tracing a published number back to its source, and running it over years.
• T12-L04 · Lock Down a Self-Hosted AI Service Before Real Data Goes In: service accounts, passwords and logs.
• T14-L04 · Build a Read-Only Connector for a System Your Assistant Can't Reach Yet: when the source is a web service rather than a database.
• T17-L04 · Run a Pilot with Real Users and Real Data: data permissions for a pilot before the pipeline.

Tools

• Git: Taught. Code, checks and field list versions, with history.
• OpenCode: Catalogued. Drafts the pipeline changes in Plan mode.
• KNIME: Catalogued. A visual alternative with database nodes, when your team doesn't read Python.