Skip to main content

Case Study 9: Database ETL & Automated Backup Pipeline (Challenge)

:::info Challenge — no solution provided This is an open challenge, in the same style as Case Study 7. Design your own solution and use the suggested libraries as a starting point rather than a strict requirement. :::

Scenario​

A production database needs a nightly job that safely backs up its data, builds an analytics-friendly summary table, and removes backups older than a defined retention period. Your task is to automate this end-to-end pipeline.

Suggested Libraries​

  • sqlite3
  • gzip
  • shutil
  • pathlib
  • datetime

Sample Files You May Create​

  • production.db — a sample source database
  • analytics.db — the transformed summary database
  • backups/ — timestamped, compressed backup files

Requirements​

  • Extract data from normalized source tables.
  • Transform data into a denormalized analytics-friendly table.
  • Create a safe backup copy of the database.
  • Compress backup files to save space.
  • Apply a retention policy to remove old backups automatically.
  • Ensure the backup process is safe to run while the database is in use.
  • Log the outcome of each pipeline run.

Things to think about​

  • Why is copying a live database file directly (e.g. with cp) unsafe compared to using a database's dedicated backup API?
  • How would you structure the pipeline so a failure in the "transform" step doesn't also corrupt or skip the backup step?