ETL Pipelines
Extract, Clean and Load Data With Python and SQL: Safe Reruns, Validation, Logging and Scheduling
-
- 21,99 €
-
- 21,99 €
Description de l’éditeur
Every data job works the first time. The trouble starts when it runs twice, when a column is renamed, when the source is down at 3 a.m., or when a report doubles overnight and nobody knows which run did it. This guide shows how to build ETL pipelines in Python and SQL that you can rerun safely, check before anyone sees the numbers, and read at 7 a.m. when something has gone wrong.
You build one small, complete pipeline with the standard library only. Order files, a product list, a customer web service and daily exchange rates go in; a checked report table comes out. You begin by reading messy sources and cleaning them, then design the tables and keys, and load in transactions with upserts that make a second run change nothing. After that come incremental loads, validation and reconciliation, logging, retries and alerts, and the command line, settings, secrets and schedule that let the job run without you.
Inside you will find:
• Readers for CSV files in different encodings and delimiters, JSON with missing keys, and paged web responses with timeouts
• Cleaning rules for money, dates, codes and missing values, with every rejected row stored together with its reason
• A table design with keys, constraints and a staging area, a loader that is proved rerun-safe by comparing table fingerprints, and a timing test of what a commit costs
• Watermarks with a lookback window, and handling for deleted rows and late data
• Blocking and warning checks that run before the commit, and a reconciliation that finds a one-cent difference
• Retries with backoff, exit codes, a ledger of every run and an alert for the run that never happened
• A command line with dry runs and backfill, a lock against overlapping runs and a look at what clock changes do to a night job
• A replay of four days of data, a runbook by exit code and a twenty-line readiness checklist
Examples use invented data and a small web service on your own computer, and nothing contacts a real site. The book is written for people who already write a little Python and SQL; it does not teach either from the beginning. Rules about personal data, security and retention differ between countries and organizations, so this is general information, and a data protection or security professional should review any pipeline that holds personal data.