Software Engineering Habits for Data Work

Habits that make a pipeline maintainable

Most data pipelines start as a script written to answer one question. Once it is scheduled and other reports depend on it, changing it means changing a shared service. Someone needs to know what its output means, which failures stop publication, and how a failed run can be recovered.

Tests provide evidence about behavior, review examines the reasoning and impact, refactoring makes the code easier to change, and documentation preserves what the next operator needs. Passing tests does not prove a system correct for every input. These habits work together to make assumptions visible and mistakes easier to detect.

Earlier articles covered Python packaging and tests, SQL, and transactions. Here those ideas connect to publication checks, code review, behavior-preserving refactoring, and operational documentation.

Tests: different questions need different checks

A unit test checks an isolated rule, such as assigning an aware timestamp to the agreed reporting date. An integration test checks a boundary such as writing and reading a real database transaction. An end-to-end test exercises the configured path from a source fixture to a selected consumer-visible result. Output data checks validate the batch actually received. Choose coverage from the failure risks; these are complementary forms of evidence, not four boxes every small script must fill.

Use tiny fixtures with deliberately chosen empty inputs, NULLs, duplicate keys, unmatched joins, date boundaries, and reruns. Add representative volume tests when scale can change behavior. Sanitized, access-controlled samples can expose distributions or source quirks that synthetic rows miss; do not copy sensitive production rows into the repository. A local SQLite integration test does not establish PostgreSQL locking behavior or a cloud warehouse’s SQL semantics.

Start a regression test with an independently justified expectation and observe it fail for the bug you intend to fix. Then apply the fix and keep the test. A failing test caused by a missing import is not evidence that the business error was caught. Golden files can preserve an approved output, but blindly recording the old program’s result can preserve its mistakes too. Review golden-file updates as behavior changes.

Make the data check a publication gate

A SQL query that returns false does not stop a job on its own. The runner must read the result, treat false or NULL as a failed required check, raise an error, and keep the candidate out of the published output. Stage, validate, and publish in that order. If readers already saw a bad table, stopping the next scheduled task does not undo that exposure.

The illustrative check contract below uses one paid order per row, one UTC date per batch, positive USD cents, and a complete customer reference table. Its 80–200-row bound is chosen for a 100-row example, not a production recommendation. A legitimate empty day, refunds, multiple currencies, or late-arriving customers would require a different contract. The staging table contains only the candidate batch, so the six checks below all evaluate that batch.

-- Illustrative SQLite checks on the named staging tables; :run_date is a bound parameter.
SELECT count(*) = 0 AS no_null_keys FROM candidate_orders WHERE order_id IS NULL;
SELECT count(*) = count(DISTINCT order_id) AS unique_keys FROM candidate_orders;
SELECT count(*) BETWEEN 80 AND 200 AS row_count_plausible FROM candidate_orders;
SELECT count(*) = 0 AS valid_amounts FROM candidate_orders
 WHERE typeof(amount_usd_cents) <> 'integer' OR amount_usd_cents <= 0;
SELECT count(*) = 1 AND coalesce(max(run_date = :run_date AND source_complete = 1), 0)
 AND NOT EXISTS (SELECT 1 FROM candidate_orders WHERE day IS NULL OR day <> :run_date)
 AS partition_ready FROM batch_meta;
SELECT count(*) = 0 AS no_orphans FROM candidate_orders o
 LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL;

A maximum event date is not a freshness or completeness proof: one recent row can hide a partial delivery, and a valid historical backfill can fail a global MAX(day) check. The illustrated checks instead use explicit batch metadata and checks the requested partition. The source_complete flag is trusted input to this toy loader; a real system must derive it from a delivery protocol or reconciliation. Measure arrival latency separately. Structural checks can still miss a plausible but wrong amount, so reconcile source totals within the same currency and business scope when that risk matters.

Run the checks where changes are accepted

A useful feedback loop runs fast unit and schema checks on each proposed change, then the relevant database or file integration checks. CI must report test failures as failures; a shell command that prints an error and exits successfully defeats the gate. Test discovery, fixtures, dependency versions, and configuration must match the intended environment. A passing suite only covers the paths and assertions it ran; coverage percentages do not establish correctness.

In a pytest project, keep production functions in their module and put checks in discovered test_*.py files with test_ functions. Do not catch AssertionError around a real regression test merely to print “failed.” Runtime publication checks use explicit exceptions, because Python assert statements can be disabled in optimized execution.

Reference: pytest assertions and expected exceptions.

Review: ask what the change means for the data

A reviewer checks more than syntax. What does one output row mean? Can a join repeat it? Are NULL handling and the reporting timezone explicit? What happens on a rerun or a historical backfill? Which consumers rely on the column names, types, and metric definition? A before/after result and an independent reconciliation help answer these questions. Linters and type checks support review but do not decide the business contract.

Keep a change focused enough to explain and verify. Separate unrelated formatting and behavior changes; small structural preparations can stay with a fix when that makes the reasoning clearer. Describe the problem, the resulting behavior, the evidence, and the rollout plan. Mark examples as examples rather than inventing an incident ticket or a staging result. Review comments should distinguish required fixes from questions and optional suggestions, and resolve disagreements against the contract and evidence.

Illustrative PR description

Problem: The report promises UTC days, but an order at 2026-03-09 06:30 UTC
is grouped under March 8 by the Los Angeles date conversion.
Change: Group aware timestamps by UTC date; reject naive timestamps.
Validation: Check that the regression case rejects local-day grouping and
 accepts UTC-day grouping. Compare both date buckets and totals.
Rollout: Identify affected dates and consumers; compare old and new per-day totals
 on an immutable input snapshot before publishing a backfill.
Recovery: Retain the previous output version. Reverting code alone does not
 restore changed data; republish the retained version or rerun validated inputs.

A query can duplicate an order through its item rows, omit March 31 after midnight, and drop customers without a matching paid order. A dropped order and an extra joined row can offset each other in the count. Count agreement alone is therefore insufficient; compare keys, grouped results, and monetary totals under the same filters.

Reference: Google code review guidance.

Refactoring: preserve behavior while changing structure

Refactoring changes structure while preserving the agreed observable behavior. For a data job that can include values, schema, ordering guarantees, rejected inputs, side effects, and transaction boundaries. Extract a function when it represents a coherent operation or useful test boundary. A comment does not automatically justify a function, and three repetitions are a heuristic rather than a mandatory extraction threshold.

First characterize behavior that must survive, then make one structural step and rerun the relevant checks. After extracting parsing, aggregation, or rendering, compare an independently calculated expectation with the result. For example, USD 10.00 + 2.50 on March 14 gives 12.50, while USD 7.00 on March 15 belongs in a separate date bucket. These cases do not establish equivalence for every possible malformed CSV. Contract changes such as rejecting a previously accepted format need their own decision and tests.

Descriptive local names help, but renaming exported functions, columns, or configuration keys can break consumers. Check usage before removing apparently dead paths. A gradual replacement can preserve existing behavior while a new path is compared on the same inputs; a rewrite can also be justified when constraints demand it. In either case, define acceptance criteria, comparison scope, a cutover point, and data recovery. Running two writers against the same destination without coordination is not a safe comparison.

Conventions make units and boundaries visible

Write naming conventions once and apply them consistently where they help readers: customer_id for a key, amount_usd_cents for a unit, ordered_at for an instant, reporting_date for a business calendar day. Preserve a source zone when its meaning matters. Normalizing instants to UTC does not require every report to use UTC calendar days; the business may require Asia/Seoul days. Document exceptions rather than silently changing a consumer-facing name to satisfy a style rule.

Documentation: enough to run, explain, and recover

A README, runbook, decision record, and column contract cover four useful needs. They can be separate files or sections of a smaller document. Keep the authoritative version discoverable, assign an owner, and update it with the change; a maintained wiki can work too. Location alone does not keep documentation accurate. The illustrative documents below describe a miniature daily-partition loader, not a deployed Airflow project.

README: purpose, contract, and entry points

# Example: validate and publish a daily partition
Output: orders_daily, one paid order per row, positive integer USD cents.
Input: a complete batch for one UTC day and a complete customer reference table.
Owner and consumers: fill in the actual team and consumers before operational use.
Entry points: document the actual command and its required configuration.
Dependencies: Python with sqlite3; no external database or credentials.
Validation: verify the candidate against its contract and retain prior output on failure.
Illustrative storage: in-memory SQLite; its data is discarded when the process exits.
Limits: 80–200-row synthetic contract; no distributed writers or source delivery protocol.
Production adoption: define persistence, source completion evidence, locking,
 configuration, secrets management, and consumer publication before scheduling.

The README should identify the output grain, units, consumer expectations, supported runtime, and actual entry point. In a real repository, test installation and run commands from a clean environment. Do not write invented vendor quirks or a supposed currency-code migration as history; link the evidence for any compatibility rule.

Runbook: decisions during failure

Failure: DataQualityError
- Inspect the named checks and batch identity; do not change a threshold just to pass.
- Verify the prior published partition remains available. Quarantine the bad input.
- Fix the source or obtain an approved contract change, then validate and rerun that date.

Failure: source delivery incomplete
- Confirm the source manifest or completion signal and its expected arrival deadline.
- Keep the previous published version; expose its age to consumers.
- Escalate to the assigned source owner after the agreed deadline.

Failure: database write or commit error
- Confirm rollback and the published state; a remote system may have an unknown commit outcome.
- Resolve storage/permission/locking problems before retrying the same immutable input.
- Check the job's concurrency and idempotency contract; avoid overlapping backfills.

Recovery evidence
Record run ID, input version, code version, affected dates, and validation results.
For irreversible source changes, use a retained output snapshot or a forward correction.
These are procedures to adapt; no scheduler-specific backfill command is assumed.

A runbook needs thresholds, an escalation route, recovery steps, and a way to verify recovery. Redact sensitive records from logs before sharing diagnostics. “Revert and rerun” works only when the required inputs and compatible schema remain available. A code rollback alone does not roll back already published data. Verify scheduler commands against the installed scheduler version rather than copying a command from a different release.

Decision records and column contracts

A decision record captures context, alternatives, the decision, consequences, and when to revisit it. For a report whose consumer contract specifies UTC calendar days, explain why that boundary was chosen; a local-day report is a valid alternative with different boundaries. Regression checks verify adherence to the chosen boundary, not that UTC is the correct business choice. Store the record with a date and a link to the tests or specification that support it.

Example orders_daily column contract
order_id: non-null globally unique order key; one published row per order.
customer_id: required key in the complete customer reference used for validation.
day: ISO YYYY-MM-DD UTC reporting date; one requested date per incoming batch.
amount_usd_cents: positive integer USD cents for a paid order; excludes refunds.
Table policy: validated replacement of one date in a single SQLite transaction.
Boundary: tax/discount meaning must be specified before adopting this teaching schema.
A multi-currency or refund output needs a revised contract and corresponding tests.

Comments explain non-obvious decisions, constraints, or mechanisms that the code does not make clear. Repeating “increment i” adds little, but explaining what a complex window frame does can be useful. Extracting a function helps only if the new boundary improves the reader’s understanding. Keep explanations and contracts synchronized with the code they describe.

A change is ready when its risk is accounted for

For a change that affects published data, agree on the required behavioral checks, publication validation, review evidence, consumer communication, and recovery plan. Update documents when interfaces or operation change. A formatting-only edit does not need an invented boundary test or a backfill. Assign a responsible team and an escalation path so ownership continues after the author leaves. During rollout, compare the expected signals with observed results and stop or recover when the acceptance criteria fail.