Python ETL pipeline: daily CSV to PostgreSQL

This is a small teaching example, not a production ETL platform.
Run validation without a database: python etl.py orders.csv
Run local tests: python -m unittest -v test_etl.py
The validation path uses only the Python standard library.

Observed November 11, 2026: Python 3.12.14 on Windows; 18 local tests passed.
Psycopg 3.3.8 and psycopg-binary 3.3.8 were installed and imported locally.
No database connection was made. Tests of repeat validation are not database
idempotency tests. No SQL, transaction rollback, advisory-lock race, container,
scheduler, recovery, benchmark, or cloud deployment was executed.

The sample fixture has 3 records totaling 100.00 USD. Any rejected record blocks
the entire load. Reports contain record numbers and reason codes, not raw rows.
Input is bounded to 10 MiB and 10000 records. Field whitespace is trimmed.
The source file must be complete and stable before reading; publish it by atomic
rename after the producer closes it. This is not a streaming file reader.

Database steps (UNTESTED): create a disposable appdb with an app schema and role
using the PostgreSQL Compose article. Execute schema.sql once through that lab's
authenticated psql client. An existing table is an error, not permission to drop it.
Set PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSFILE for a verified private route,
install requirements.txt, then run python etl.py orders.csv --load.
Do not use a production database. Do not put passwords in command arguments.
Use verified TLS for a network route that requires it; private Compose examples
here do not configure database TLS.

Optional Compose adapter (UNTESTED): put this sample directory at pg-compose-lab/etl
beside the existing compose.yaml. Create etl/runs and protect that directory. Create
secrets/etl_pgpass with db:5432:appdb:app:YOUR_LAB_PASSWORD, escaping colon and
backslash as libpq requires. Protect the file and keep it out of Git and archives.
The container copies this file to a mode-0600 password file in its temporary
filesystem. The teaching container uses its image's default root user; it is not
privileged and does not mount the Docker socket. Harden and test a non-root
identity and directory ownership separately before deploying real workloads.

From pg-compose-lab, proposed commands:
docker compose -f compose.yaml -f etl/compose.etl.yaml --profile etl config --quiet
docker compose -f compose.yaml -f etl/compose.etl.yaml build etl
docker compose -f compose.yaml -f etl/compose.etl.yaml run --rm etl

The internal Compose hostname db does not resolve from the host or an unrelated
container. This adapter attaches the job to the existing private database network
without publishing port 5432. Build-time package fetching is separate from that
runtime network. Python image tags can move; record a tested image digest.

After a successful load, query the isolated table: expect 3 rows totaling 100.00.
Repeat the identical file: expect unchanged contents and changed_records 0.
These are expected results, not observed database output. UPSERT updates changed
values for an existing ID, so replaying an older file can overwrite newer changes.
Missing source rows are not deleted. Advisory locking coordinates only clients
using the same lock key; it does not exclude arbitrary SQL writers.

Exit codes: 0 validation or committed load (read mode/status); 2 rejected data;
1 configuration/runtime/report failure. A failure after commit can leave data
committed without a success report. Reconcile the database instead of inferring
rollback from a missing JSON report. Reports and commits are not one transaction.
The sample has no automatic retry, checkpoint ledger, alerting, or catch-up logic.

Scheduled operation must use a single scheduler, a verified timezone, stable input,
non-overlap controls and a monitored exit status. No cron entry is installed here.
See the article for the test checklist and limits before considering a CPU VM.
