A modern ETL (Extract, Transform, Load) pipeline for processing HURDAT2 hurricane track data into a structured DuckDB database.
This project implements a complete ETL pipeline for the HURDAT2 dataset, the National Hurricane Center's Atlantic hurricane database. The pipeline:
- Extracts raw data from HURDAT2 text files
- Transforms it into structured, validated data models
- Loads it into a DuckDB database for easy querying
The implementation follows modern software engineering practices, including clean architecture principles, domain-driven design, comprehensive test coverage, and strong type safety.
- Clean Architecture: Separation of concerns with distinct Extract, Transform, and Load stages
- Rich Progress UI: Dynamic progress tracking with color-coded status indicators
- Statistical Analysis: Comprehensive summary of processed hurricane data
- Robust Error Handling: Custom exception hierarchy and detailed error reporting
- Comprehensive Tests: 80%+ code coverage with unit and integration tests
- Type Checking: BasedPyright standard diagnostics for source and tests
- DuckDB Integration: Fast, efficient geospatial querying capabilities
- Detailed Logging: Structured logging throughout the pipeline
- Python >=3.13,<4 (development pin: 3.13)
- uv (validated with 0.12.10)
-
Clone the repository:
git clone https://github.com/snarkipus/hurdat2_etl_demo.git cd hurdat2_etl_demo -
Install the pinned Python and locked project/development dependencies:
uv python install 3.13 uv sync --locked
uv sync --lockeduses.python-versionand refuses an out-of-dateuv.lockinstead of updating dependency selections. Generate dependency changes withuv lock; never hand-edit the lockfile. Poetry is not required.
Run the ETL pipeline with:
uv run --locked etl-pipeline --input /path/to/hurdat2.txt --output /path/to/database.duckdbThis is a single-command app: do not add a run or run-etl subcommand.
The output parent directory must already exist. DuckDB Spatial installation
may need network access (or a compatible cached extension); unavailable Spatial
prevents publication, rather than silently disabling verification.
Options:
--input,-i: Path to the input HURDAT2 text file (required)--output,-o: Path to the output DuckDB database file (required)--replace: Explicitly allow atomic replacement of existing output. The CLI builds and verifies a sibling candidate before publication; keep external writers stopped. Publication failure retains the candidate at the reported path for manual recovery, without changing old output. Supported local filesystems must provide atomic replace/hard-link operations; no copy fallback or automatic backup is provided.--log-level,-l: Logging level (defaults to INFO)
Diagnostics append structlog-backed JSON lines to logs/pipeline.log in the
working directory, separate from Rich progress and summaries. Events carry a
UTC timestamp, severity, run ID, and applicable operation/stage and error context.
Each CLI invocation owns and closes its log handler; library diagnostics use the
same bridge and run-level filter (library-specific thresholds still apply).
Stage construction alone does not create a log file or configure host logging.
Existing host handlers remain attached; run exit restores the caller's logging
level and context. This is local diagnostic output, not an audit/rotation or
concurrent-run logging service. Manage log retention externally. Events avoid
full source records and SQL bound-parameter dumps by default, but paths, IDs,
exception text and tracebacks can appear: review logs before sharing them.
Each run is a full refresh: the CLI applies packaged Alembic migrations to
head on a unique sibling candidate, loads and commits it, then independently
checks persisted counts against accepted loader inputs. It closes database
resources before publishing. Existing output is never migrated, appended to,
or used as an upsert target. Direct Unit-of-Work callers must initialize schema
explicitly; entering a UoW does not create or repair tables. Normal CLI execution
does not use the repository's alembic.ini or its data/hurdat.duckdb default.
Observation geom is WKT text, POINT(longitude latitude), not a native
geometry column; spatial queries use ST_GeomFromText(geom). Every stored
geometry must parse as a nonempty point with finite longitude in [-180, 180]
and latitude in [-90, 90], inclusive. No clamping, wrapping, coordinate repair,
or dropping persisted rows makes verification pass: 270E still normalizes to
270.0 and fails output acceptance. Existing skipped-record and last-wins storm
deduplication rules remain; even zero accepted rows can pass. This gate is not
a source checksum or a complete meteorological data-quality audit.
To deliberately replace output after preserving any operator-required backup:
uv run --locked etl-pipeline --input /path/to/hurdat2.txt --output /path/to/database.duckdb --replaceUse one active run per destination on an ordinary local filesystem, with
external writers stopped for the entire run. Destination symlinks, non-regular
files, input/output aliases and detected destination .wal files are refused.
Never delete an old WAL to bypass refusal; resolve it through the owning
database's normal shutdown/recovery. WAL checks are not writer coordination.
Allow space for the old database, the complete candidate and transient WAL/temp
state (and any backup you make); input and transformed rows are also materialized
in memory. No automatic space reservation or backup is provided.
Creation uses an atomic hard link that refuses late collisions; explicit replacement uses atomic replace. Unsupported/denied operations fail without copy, forced access, or delete-first fallback. Network filesystems, hostile path changes and concurrent ownership are outside the supported envelope. Atomic visibility is not guaranteed power-loss durability: crashes/abrupt termination can leave artifacts, with no automatic sweeping, retry or crash recovery.
- Processing, verification or finalization failure exits non-zero, preserves old output and attempts to remove only run-owned incomplete artifacts. Cleanup warnings identify leftovers; these are not verified recovery candidates.
- Publication failure exits non-zero and reports the finalized verified candidate retained path; old output remains unchanged. Fix the reported permission/lock/filesystem problem before considering manual publication.
- After publication, redundant-name cleanup or optional summary failures warn without undoing output or changing success. A leftover candidate name after successful hard-link publication can refer to the same file as the output.
There is no recovery CLI subcommand. For a candidate explicitly reported as
verified and retained after publication failure, a conservative manual recovery
is to expose it under a new, unused sibling name, leaving the old destination
and retained candidate untouched. First stop writers, confirm the reported
candidate is a regular non-symlink standalone file with no .wal, and ensure
neither the chosen new name nor its .wal exists. From the checkout, substitute
the actual absolute paths in this command (ordinary local directory only):
uv run --locked python -c 'import os, sys; os.link(sys.argv[1], sys.argv[2])' /absolute/path/.database.duckdb.RUN.duckdb /absolute/path/recovered.duckdbThe hard-link operation refuses an existing destination atomically and leaves
the retained name in place. If it fails, stop; do not add a forced overwrite or
copy/delete fallback. Reopen the recovered database read-only and inspect its
contents before using it or removing the redundant retained name. Both names
refer to the same data, not independent backups. Do not use this procedure
for incomplete or unexplained crash leftovers. Replacing the original pathname
is a separate deliberate operator decision; alternatively rerun the ETL with
--replace after resolving the cause. A successful replacement keeps no old-file
backup.
etl_pipeline/
├── data/ # Default directory for storing output data (e.g., DuckDB file)
├── docs/ # Workflow guide, README assets, and historical archive
├── openspec/ # Accepted specifications and proposed changes
├── ref/ # Reference materials (e.g., data format specs, source data files)
├── src/ # Main source code directory
│ └── etl_pipeline/ # Core package for the ETL pipeline
│ ├── extract/ # Modules for extracting data from the source (HURDAT2)
│ ├── load/ # Modules for loading transformed data into the target (DuckDB)
│ ├── migrations/ # Database schema migration scripts (Alembic)
│ ├── transform/ # Modules for transforming and validating extracted data
│ ├── utils/ # Shared utility functions and classes
│ ├── cli.py # Command-line interface logic (using Typer)
│ ├── core.py # Shared stage execution, logging, and Rich progress
│ └── exceptions.py # Custom exception classes for the pipeline
└── tests/ # Test suite for the project
├── integration/ # Integration tests (testing component interactions)
└── unit/ # Unit tests (testing individual modules/functions)
The ETL pipeline follows a three-stage architecture:
-
Extract Stage:
- Processes the raw HURDAT2 text file
- Yields rows of raw string data
-
Transform Stage:
- Parses raw data into structured objects
- Validates data using Pydantic models
- Retains existing normalization and skipped-record rules
-
Load Stage:
- Sets up DuckDB with spatial extensions
- Implements the Repository pattern
- Uses Unit of Work pattern for database transactions
The ETL process generates a comprehensive summary report, including:
- Total storms and observations processed
- Full date range of the dataset
- Peak recorded wind per storm across all observation statuses, in neutral bands (<34, 34-63, 64-82, 83-95, 96-112, 113-136, and >=137 knots); these bands do not assign tropical-cyclone status or classification from wind alone
- Decade-by-decade distribution of hurricane activity
- Longest-duration storms in the record
See the documentation map and
agentic workflow before starting a change.
OpenSpec owns specification and intent; Beads drives implementation, with
tasks.md reconciled continuously from verified outcomes. Historical plans and
diagrams are retained in the documentation archive,
not treated as accepted requirements or an active backlog.
Run tests with pytest:
uv run --locked pytest # Full suite, branch coverage, 80% minimum
uv run --locked pytest --no-cov tests/unit/transform/test_parser.py # Focused onlyRun BasedPyright with standard diagnostics for src (including migrations) and
tests, without blanket diagnostic suppressions:
uv run --locked basedpyrightRun ruff linter:
uv run --locked ruff check src tests
uv run --locked ruff format --check src testsUse uv run --locked ruff format src tests to apply formatting.
uv run --locked pre-commit install
uv run --locked pre-commit run --all-filesAll four hooks run the full configured scope through uv run --locked: Ruff
lint with fixes, Ruff formatting, BasedPyright, then pytest with the same 80%
branch-measured coverage gate. Hooks can change files; inspect their diff and
rerun until clean. The non-fixing commands above are the verification gates.
No remote Ruff/type-checker environments or separately resolved dependencies
are used. Hooks also run for configuration-only changes.
BasedPyright 1.40.0 is the selected stable checker. Ruff 0.11.2 and pre-commit
4.2.0 remain locked: both support this Python 3.13 workflow, and hook parity
does not require upgrading them or the existing test/build tools. Dependency
updates must regenerate uv.lock with uv and pass these same checks.
Build the PEP 621/Hatchling sdist and wheel with uv build (outputs to dist/).
The wheel includes the CLI and Alembic resources. For the Linux acceptance smoke,
use an existing temporary directory outside the checkout:
uv run --locked python tests/installed_package_smoke.py --temp-root /tmpThe script builds the wheel from the sdist, installs locked runtime dependencies
with hashes into a fresh non-editable environment, runs pip check, then checks
installed CLI help and fixture ETL values/migrations independently. CLI output
and logs stay in its isolated temporary working directory, removed on exit.
It needs uv, Python 3.13 and package-index/Spatial access or caches; do not use
-O/PYTHONOPTIMIZE, which would disable its assertions.
GitHub Actions CI runs on PRs targeting main and
pushes to main, using uv 0.12.10 and the shared Python 3.13 pin:
- Linux quality and wheel (
ubuntu-24.04): locked non-fixing Ruff/format, BasedPyright, full pytest with the 80% branch-measured gate, and the installed wheel smoke outside the checkout. - Windows runtime and publication (
windows-2025): ten targeted real DuckDB/new-and-replacement output and file-lifecycle cases, not the full Linux suite or coverage gate.
Both jobs explicitly install/load/query Spatial and fail on errors; they do not skip unavailable Spatial. They require no private credentials for public-fork PRs. Workflow checks do not by themselves configure required branch-protection checks. Test runs write ignored coverage artifacts; fixtures isolate their working directories so they do not modify the developer's pipeline log.
This project is licensed under the MIT License - see the LICENSE file for details.
- HURDAT2 Dataset from the National Hurricane Center
- DuckDB for the embedded database

