Appendix A — Appendix

Published

Aug 2026

ID: DSDP-999
Type: Reference appendix
Audience: Data practitioners building and operating SQL-backed data pipelines
Theme: Commands, conventions, checklists, and troubleshooting patterns

This appendix is the quick-reference companion to the guide. Use it when you need to set up the repository, recall a SQL or Python pattern, validate a pipeline run, recover safely, or diagnose a failure. Run commands from the repository root unless a section says otherwise.

Environment setup

Create and activate the repository-specific Python environment:

python3.12 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

On Windows PowerShell, activate the environment with:

.venv\Scripts\Activate.ps1

Confirm that the expected tools are available:

python --version
python -m pip --version
quarto --version
git --version

Render the complete guide:

quarto render

Render only this appendix during editing:

quarto render 999-appendix.qmd

Deactivate the Python environment when finished:

deactivate

Repository map

Path	Purpose	Version-control convention
`data/raw/`	Immutable source extracts or local input samples	Keep large or sensitive data out of Git
`data/processed/`	Validated, transformed, analysis-ready outputs	Retain `.gitkeep`; regenerate outputs
`src/data_pipelines/`	Reusable pipeline package	Track source code
`scripts/python/`	Focused Python entry points and demonstrations	Track scripts
`scripts/bash/`	Repeatable command-line wrappers	Track executable scripts
`scripts/sql/`	Schema, query, join, aggregation, and extraction SQL	Track SQL files
`tests/`	Unit, integration, contract, and data-quality tests	Track tests and fixtures
`results/`	Small run summaries and generated evidence	Track only intentional artifacts
`results/figures/`	Figures generated by chapter scripts	Track publication-ready figures
`logs/`	Local operational records	Retain `.gitkeep`; ignore run logs
`docs/`	Rendered Quarto site	Generate through `quarto render`
`.github/workflows/`	Continuous-integration and delivery definitions	Track workflow files
`.githooks/`	Repository-specific Git hooks	Track hooks and activate locally
`library/`	Bibliography and supporting reference files	Track curated references

Activate the repository hooks after cloning:

git config core.hooksPath .githooks
git config --get core.hooksPath

Configuration conventions

Keep configuration separate from code. Store safe defaults in tracked configuration files, local overrides in ignored files, and secrets in environment variables or a managed secret store.

Typical environment variables include:

export PIPELINE_ENV=development
export DATABASE_URL='postgresql://user:password@localhost:5432/analytics'
export PIPELINE_RUN_ID='manual-2026-08-06'
export LOG_LEVEL=INFO

Read configuration explicitly in Python:

import os

database_url = os.environ["DATABASE_URL"]
pipeline_env = os.getenv("PIPELINE_ENV", "development")
log_level = os.getenv("LOG_LEVEL", "INFO")

Use os.environ[...] for required values so the process fails immediately when one is missing. Use os.getenv(...) only when a safe default exists. Never print credentials, tokens, or complete connection strings to logs.

SQL quick reference

Inspect and filter rows

SELECT customer_id, order_date, order_total
FROM orders
WHERE order_date >= DATE '2026-01-01'
  AND order_total > 0
ORDER BY order_date DESC;

Aggregate at an explicit grain

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(order_total) AS lifetime_value
FROM orders
GROUP BY customer_id;

State the intended grain before writing the query. In this example, the result must contain one row per customer_id.

Join without accidental duplication

SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM orders AS o
JOIN customers AS c
  ON o.customer_id = c.customer_id;

Before and after a join, compare row counts and key uniqueness. A one-to-many relationship legitimately expands rows; an unexpected expansion usually signals a duplicate key or an incomplete join condition.

Handle missing values deliberately

SELECT
    order_id,
    COALESCE(discount_amount, 0) AS discount_amount
FROM orders
WHERE cancelled_at IS NULL;

NULL means unknown or absent; it is not equal to zero or an empty string. Use IS NULL and IS NOT NULL, not = NULL.

Deduplicate deterministically

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC, source_record_id DESC
        ) AS row_number
    FROM customer_updates
)
SELECT *
FROM ranked
WHERE row_number = 1;

Always include a stable tie-breaker. Without one, repeated executions may select different records.

Use transactions for related writes

BEGIN;

DELETE FROM daily_sales
WHERE sales_date = DATE '2026-08-05';

INSERT INTO daily_sales (sales_date, store_id, revenue)
SELECT sales_date, store_id, SUM(order_total)
FROM orders
WHERE sales_date = DATE '2026-08-05'
GROUP BY sales_date, store_id;

COMMIT;

If validation fails before the commit, execute ROLLBACK;. Transaction behavior varies by database, statement type, and isolation level, so verify the target platform’s guarantees (PostgreSQL Global Development Group 2026b).

Parameterize values

cursor.execute(
    "SELECT * FROM orders WHERE order_date >= %s",
    (start_date,),
)

Use the placeholder style required by the database driver. Never construct SQL by concatenating untrusted values.

Python pipeline patterns

Use a clear entry point

def main() -> int:
    """Run the pipeline and return a process exit code."""
    extract()
    transform()
    load()
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

Manage database resources safely

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("data/pipeline.db")) as connection:
    connection.execute("PRAGMA foreign_keys = ON")
    rows = connection.execute(
        "SELECT customer_id, customer_name FROM customers"
    ).fetchall()

For writes, use the driver’s transaction context or commit explicitly only after validation. Python’s sqlite3 documentation distinguishes resource cleanup from transaction control and documents when explicit commit() or rollback() is required (Python Software Foundation 2026).

Log events with useful context

import logging

logging.basicConfig(
    level="INFO",
    format="%(asctime)s %(levelname)s %(name)s %(message)s",
)
logger = logging.getLogger(__name__)

logger.info(
    "pipeline_stage_complete stage=%s rows=%d run_id=%s",
    "transform",
    row_count,
    run_id,
)

Useful fields include run_id, environment, stage, source, target, row count, duration, and status. Avoid logging full records when they may contain personal or confidential data.

Write outputs atomically

Write to a temporary path, validate the result, and then replace the destination:

from pathlib import Path

target = Path("data/processed/customers.csv")
temporary = target.with_suffix(".csv.tmp")

dataframe.to_csv(temporary, index=False)
temporary.replace(target)

Atomic replacement prevents readers from observing a partially written file on supported filesystems.

Data contracts and quality checks

A useful data contract identifies:

the dataset owner and consumers;
the row grain and primary or business key;
required columns, data types, and nullability;
accepted value domains and units;
freshness and completeness expectations;
schema-evolution rules;
handling requirements for sensitive fields.

Apply inexpensive checks early and boundary-specific checks before publishing.

Check	Question answered	Example failure
Schema	Are expected columns and types present?	`order_total` changes from numeric to text
Completeness	Are required values populated?	Missing `customer_id`
Uniqueness	Is the declared key unique?	Duplicate `order_id`
Validity	Do values obey domain rules?	Negative quantity
Referential integrity	Do foreign keys resolve?	Order references an unknown customer
Freshness	Is the data recent enough?	Latest partition is two days late
Volume	Is row count within a plausible range?	Source delivers 5% of normal volume
Reconciliation	Do source and target totals agree?	Revenue changes during transformation

A failed hard contract should stop publication. A warning-level check may allow the run to continue, but it must be visible and assigned for follow-up. At the database boundary, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints provide enforceable safeguards rather than relying only on application-side validation (PostgreSQL Global Development Group 2026a).

Pipeline execution checklist

Before a run

Confirm the environment and target database.
Confirm that credentials are available without displaying them.
Record the logical data interval and a unique run identifier.
Verify source availability, schema, and expected freshness.
Check storage capacity and target permissions.
Decide whether the run is incremental, full, or a backfill.
Confirm that concurrent runs cannot process the same interval unsafely.

During a run

Record start and finish times for every stage.
Capture input, rejected, and output row counts.
Preserve checkpoints needed for safe retries.
Stop on contract-breaking errors.
Keep logs structured and free of secrets.

After a run

Validate schema, uniqueness, completeness, and referential integrity.
Reconcile important counts and totals with the source.
Confirm that the expected partition or watermark was published.
Record run status, duration, code version, and data interval.
Notify downstream consumers when required.
Retain evidence according to the project’s governance policy.

Testing reference

Run the complete test suite:

python -m pytest -q

Run a specific test module or test:

python -m pytest tests/test_transform.py -q
python -m pytest tests/test_transform.py::test_rejects_negative_quantity -q

Test level	Scope	Typical assertion
Unit	One transformation or helper	Known input produces known output
Schema	Dataset structure	Columns, types, and nullability match contract
Data quality	Dataset content	Keys are unique and values are valid
Integration	Database, API, or filesystem boundary	Component reads and writes correctly
Contract	Producer-consumer interface	Required fields and semantics remain compatible
End-to-end	Complete representative flow	Published result is correct and observable
Recovery	Retry or backfill behavior	Re-execution does not duplicate data

Good fixtures are small, deterministic, representative, and explicit about edge cases. Include empty input, missing values, duplicated keys, boundary dates, malformed records, and late-arriving data where relevant.

Idempotency, retries, and backfills

An idempotent run produces the same intended target state when repeated for the same logical input. Common strategies include:

upserting by a stable business key;
replacing a complete partition inside a transaction;
recording processed source identifiers;
using deterministic output names;
advancing a watermark only after successful publication.

Retry only failures likely to be temporary, such as timeouts, rate limits, or brief service unavailability. Use bounded attempts with exponential backoff and jitter. Do not blindly retry invalid credentials, malformed input, contract violations, or deterministic transformation errors.

For a backfill:

Define the exact historical interval.
Estimate source load, target load, runtime, and storage.
Isolate backfill state from the normal schedule when necessary.
Process bounded partitions in a controlled order.
Apply the same contracts as a normal run.
Reconcile each partition and the complete interval.
Confirm that current scheduled data was not overwritten or skipped.
Record who initiated the backfill, why, and what changed.

Observability and incident triage

Monitor the pipeline from three complementary views. Logs record events, metrics capture runtime measurements, and traces connect work across components; using these signals together makes diagnosis stronger than relying on any one signal alone (OpenTelemetry Authors 2026; Beyer et al. 2018a).

Signal	Examples	Diagnostic value
Logs	Stage transitions, validation failures, exception context	Explains individual events
Metrics	Duration, rows processed, failure rate, data freshness	Reveals trends and threshold breaches
Lineage and run metadata	Input version, code version, output partition	Connects a result to its causes

When an alert fires, preserve a working record of diagnosis and mitigation and keep ownership of the response clear (Beyer et al. 2018b):

Identify the affected environment, pipeline, run, stage, and data interval.
Determine whether the failure is active and whether bad data was published.
Protect consumers by pausing publication or marking affected data when required.
Inspect the first meaningful error and compare it with recent successful runs.
Classify the problem as source, code, configuration, infrastructure, data quality, or downstream.
Recover with the smallest safe action: retry, resume, roll back, or backfill.
Validate the repaired output before declaring recovery.
Document the timeline, impact, cause, and preventive action.

Security and governance checklist

The checklist below is a concise project-level interpretation of established access-control, audit, incident-response, contingency-planning, and data-protection control families (Force 2020).

Grant each service account only the permissions it needs.
Separate development, staging, and production credentials.
Encrypt sensitive data in transit and at rest.
Keep secrets out of source control, logs, notebooks, and generated pages.
Minimize collection and retention of personal data.
Mask or tokenize sensitive fields outside approved environments.
Record dataset ownership, classification, lineage, and retention rules.
Review schema and access changes through version control.
Test restoration procedures, not only backups.
Preserve auditable evidence for production changes and backfills.

Troubleshooting guide

The virtual environment is active, but imports fail

Confirm that python and pip resolve inside .venv, then install dependencies through that interpreter:

which python
python -m pip --version
python -m pip install -r requirements.txt

Using python -m pip avoids installing into a different interpreter by mistake.

A SQL query returns too many rows

Check the grain of each input, uniqueness of join keys, join predicates, and any many-to-many relationship. Compare these diagnostics before and after the join:

SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS distinct_orders
FROM orders;

A pipeline succeeds but publishes no data

Inspect the configured interval, timezone, watermark, filters, source freshness, and rejected-row counts. Treat an unexpectedly empty output as a quality failure rather than automatic success.

A retry creates duplicates

The write path is not idempotent for the chosen key or interval. Stop repeated retries, identify affected partitions, remove or supersede duplicates safely, and change the load strategy to an upsert or transactional partition replacement.

A backfill overwrites current data

Pause the affected writer, identify which partitions changed, restore or rebuild those partitions from authoritative inputs, and reconcile them. Before resuming, separate backfill and scheduled-run state or enforce interval-level locking.

Quarto reports a missing citation

Confirm that the citation key exists in library/references.bib, that the bibliography path is configured in _quarto.yml, and that the key matches exactly. Quarto accepts BibTeX and several other bibliography formats through the bibliography YAML field (Quarto Project 2026). Section identifiers should use the {#sec-*} convention, such as {#sec-appendix}.

Generated figures do not appear

Run the producing script, confirm that it writes to the path referenced by the chapter, and verify filename capitalization. Paths are case-sensitive in many continuous-integration environments even when they appear case-insensitive locally.

Exit codes

Command-line pipeline scripts should return a meaningful process status:

Exit code	Meaning
`0`	Successful completion
`1`	General pipeline failure
`2`	Invalid command-line arguments or configuration
`3`	Source unavailable or extraction failure
`4`	Data-contract or transformation failure
`5`	Load, publication, or reconciliation failure

These codes are a project convention, not a universal standard. Document any different scheme beside the entry point and keep it stable for schedulers and monitoring systems.

Glossary

Backfill
A controlled run that processes a historical interval that was missed or must be recomputed.

Checkpoint
Persisted progress that allows a failed run to resume without repeating all completed work.

Data contract
An explicit agreement about a dataset’s structure, semantics, quality, ownership, and delivery expectations.

Data lineage
The recorded relationships among sources, transformations, runs, and outputs.

Idempotency
The property that repeating an operation for the same logical input leaves the target in the same intended state.

Orchestration
Coordination of pipeline tasks, dependencies, schedules, retries, and operational state.

Partition
A bounded subset of data, commonly organized by date, region, or another processing key.

Recovery point objective (RPO)
The maximum acceptable amount of data loss measured in time.

Recovery time objective (RTO)
The target time for restoring a service or pipeline after disruption.

Schema drift
An unplanned or unmanaged change to the structure or types of source data.

Watermark
A recorded boundary indicating how far incremental processing has progressed.

Final release check

Before publishing a completed version of the guide:

python -m pytest -q
quarto render
git status --short

Confirm that tests pass, the book renders without unresolved citations or cross-references, generated artifacts are current, no credentials or local datasets are staged, and all intentional source files are included in version control.