[Exploratory RFC] Evaluate a first-class DuckDB connector for HEC-RAS and HEC-HMS

[gpt-cmdr]

Intent

Evaluate a clean, first-class DuckDB connector for HEC model data that can create a useful SQL analytics surface without making DuckDB a core ras-commander dependency.

This is an exploratory architecture issue, not a decision that the connector must live in this repository.

Related ras-commander explorations

• [Exploratory] Speed up existing HDF result reads with direct slicing and bounded reductions #306 — transparent HDF slicing and bounded reductions
• [Exploratory] Add chunked mesh/results readers and geometry-free result extraction #307 — chunked readers and geometry-free extraction
• [Exploratory] Add an optional lazy/queryable result return type #308 — optional lazy/queryable result return type

Those improvements are valuable independently. A DuckDB connector should consume them rather than force them into a database-specific design.

Candidate project boundary

Consider a separate package/repository, tentatively hec-duckdb, shared by:

• ras-commander for optional query/export adapters
• ras2cng for HEC-RAS model and results ingestion
• hms-commander and/or hms2cng for HEC-HMS data
• notebooks, CLI workflows, and third-party analytics tools

A possible Python-first interface:

from hec_duckdb import connect

db = connect()
db.attach_ras(project_or_hdf, schema="ras")
db.attach_hms(project, schema="hms")

result = db.sql("""
    select mesh_name, max(depth)
    from ras.mesh_results
    group by mesh_name
""")

Candidate RAS surface

Expose stable relations or views for:

• projects, plans, geometries, and execution metadata
• mesh and cell/face identifiers
• geometry-free mesh summary results
• mesh, face, cross-section, and hydrograph time series
• optional geometry columns
• provenance and source fingerprints

ras-commander remains the semantic control plane for HDF paths, HEC-RAS version differences, timestamps, identifiers, and geometry interpretation.

Candidate HMS surface

Modern HEC-HMS data is mixed rather than entirely SQL:

• project/grid geometry can be stored in SQLite
• conventional time-series results remain DSS
• spatial results use HDF5
• some summaries and metadata use additional project/result formats

DuckDB's official SQLite extension could attach HMS SQLite files directly. hms-commander would provide the semantic layer for DSS, HDF5, and project conventions so the SQL surface feels coherent.

Direct HDF-to-DuckDB experiment

Benchmark three paths:

1. h5py direct/chunked reads from ras-commander
2. direct DuckDB HDF access using the community h5db extension
3. materialized Parquet/DuckDB tables for repeated analysis

The h5db extension advertises streaming HDF5 arrays, projection pushdown, outer-index predicate pushdown, and configurable batches. HEC-RAS commonly stores results as (time, spatial_element), so time filtering may align well with its chunk layout; arbitrary per-cell filtering may still decompress full timestep chunks and must be measured.

Because h5db is a third-party community extension, it should receive a dependency/supply-chain and correctness audit before becoming a recommended production path. Compound datatypes and HEC-specific semantics may still require ras-commander/h5py adapters.

References:

• DuckDB h5db community extension: https://duckdb.org/community_extensions/extensions/h5db
• h5db source: https://github.com/jokasimr/h5db
• DuckDB SQLite extension: https://duckdb.org/docs/current/core_extensions/sqlite
• DuckDB Parquet support: https://duckdb.org/docs/stable/data/parquet/overview

Proposed stages

Stage 0 — benchmark and schema spike

• Select representative small and large RAS/HMS projects
• Define 5–10 real analytical queries
• Measure elapsed time, peak memory, and repeated-query performance
• Test direct HDF, Arrow batches, Parquet, and materialized DuckDB paths

Stage 1 — thin RAS connector

• Consume [Exploratory] Add chunked mesh/results readers and geometry-free result extraction #307 geometry-free/chunked APIs
• Attach one plan HDF
• Publish stable mesh-summary and mesh-time-series relations
• Preserve source provenance and deterministic identifiers

Stage 2 — persistence and incremental refresh

• Materialize selected tables to DuckDB/Parquet
• Detect changed plan HDFs using fingerprints
• Refresh only affected sources
• Document type mappings and schema evolution

Stage 3 — HMS connector

• Attach HMS SQLite geometry directly
• Normalize DSS and HDF5 result adapters through hms-commander
• Expose consistent runs, elements, time series, and spatial-result relations

Stage 4 — community release

• Publish reproducible benchmarks and example notebooks
• Solicit schema feedback from RAS/HMS and DuckDB users
• Explore upstream collaboration with the DuckDB community
• Keep the core connector useful outside CLB-specific workflows

Decision criteria

A separate project is favored if it:

• keeps ras-commander lightweight and backward compatible
• eliminates duplicated DuckDB session/schema logic across downstream tools
• supports both zero-copy/streaming analysis and durable materialization
• has a stable domain schema independent of raw HDF path changes
• demonstrates compelling performance on real engineering queries
• can be documented and maintained as a credible open-source integration

A small optional adapter inside ras-commander is favored if the separate package would mostly be a thin wrapper with no durable cross-product schema.

Open questions

• Is hec-duckdb the right project boundary and name?
• Should geometry use DuckDB spatial types by default or remain optional?
• Which relations should be virtual versus materialized?
• Should direct HDF support rely on audited h5db, Arrow batches from commander libraries, or both?
• Can RAS and HMS share enough provenance, time-series, and geometry conventions to justify one connector?
• Which public benchmark would best demonstrate the value to both communities?