14  Database Workflows

14.1 A simple loading workflow

  1. Read raw data.
  2. Preserve identifiers as text.
  3. Clean missing values.
  4. Validate columns and row counts.
  5. Load into a staging table.
  6. Run database constraints and checks.
  7. Promote validated data to the target table.

14.2 Validate duplicate keys in Python

duplicate_rows = df.loc[df.duplicated("id", keep=False)]
duplicate_rows.sort_values("id")

14.3 Validate duplicate keys in SQL

SELECT
    id,
    COUNT(*) AS occurrences
FROM staging.dataset
GROUP BY id
HAVING COUNT(*) > 1;

14.4 Transaction pattern

with engine.begin() as connection:
    connection.execute(text("TRUNCATE TABLE staging.dataset"))
    # Load data here.

14.5 Things to remember

  • Validation is part of loading, not a separate optional task.
  • Record the source, date, and transformation version.
  • Prefer repeatable scripts over manual database changes.