soil-etl 0.1.0a5

YAML-driven ETL mappers for ForSITE soil database imports.

SOIL — Structured Observation Ingestion Library

Version: 0.1.0-a.2 (alpha) · Python: ≥ 3.10 · PyPI: soil-etl

YAML-driven ETL package for importing Canadian forestry soil datasets into the ForSITE PostGIS database model, built on the OGC Observations, Measurements & Samples specification.

Acknowledgements

Project Partners

This project was developed through joint funding provided by Agriculture and Agri-Food Canada (AAFC) under the CSBO program and Natural Resources Canada (NRCan) for the ForSITE-Soil degradation project.

The code developed as part of this project includes contributions from:

• Olivier Forgues-Nhan
• Xavier Malet
• Catlan Dallaire
• David Gagné

---

Installation

pip install soil-etl

Or with Poetry:

poetry add soil-etl

---

Quick Start

YAML-only pipeline (recommended)

from pathlib import Path
from soil_etl.yaml_mapper import YamlDatasetMapper

mapper = YamlDatasetMapper(
    config_path=Path("my_config.yaml"),
    data_file_path=Path("data.xlsx"),
)
metrics = mapper.import_data(
    database_connection_string="postgresql+psycopg2://user:password@host:5432/database",
)
print(metrics)

All new ETL pipelines must use YamlDatasetMapper. See AGENTS.md for full architecture guidance.

Canonical imports

from soil_etl.yaml_mapper import YamlDatasetMapper
from soil_etl.bindings import Binding, FromColumn
from soil_etl.db import ImportationInterface

---

Database Connection

Connection resolution priority (first non-null wins):

1. Explicit database_connection_string argument to import_data()
2. DB_CONN environment variable
3. Hydra config directory (database_config_path / CONFIG_PATH env var)

# Option 1 — direct connection string
mapper.import_data(
    database_connection_string="postgresql+psycopg2://user:password@host:5432/database",
    database_engine_kwargs={"pool_size": 10, "pool_pre_ping": True},
)

# Option 2 — Hydra config files
mapper.import_data(
    database_config_path="configs",
    database_config_name="configs.yaml",
)

# Option 3 — environment variables
import os
os.environ["DB_CONN"] = "postgresql+psycopg2://user:password@host:5432/database"
mapper.import_data()

Environment variables recognized:

Variable	Description
DB_CONN	Full SQLAlchemy connection string
DB_USER	Database username (used alongside Hydra config)
DB_PASSWORD	Database password (used alongside Hydra config)
CONFIG_PATH	Hydra config directory path
IMPORT_BATCH_SIZE	Override batch size for this run (positive integer)
EXPORT_RETREATED_DATAFRAME	Export transformed DataFrame to *_retreated.xlsx (1/true/yes)

---

import_data() Signature

mapper.import_data(
    error_file_path=Path("errors.xlsx"),            # defaults to <dataset_title>_ERROR.xlsx
    dry_run=False,                                   # True: extract + validate only, no DB writes
    database_connection_string="postgresql+...",
    database_config_path="configs",
    database_config_name="configs.yaml",
    database_engine_kwargs={"pool_size": 10},
) -> ImportRunMetrics

Returns an ImportRunMetrics dataclass:

@dataclass(frozen=True)
class ImportRunMetrics:
    rows_processed: int
    error_rows: int
    generic_results: int
    horizon_results: int
    feature_relationships: int
    sample_information: int
    batches: int
    batch_size: int
    dry_run: bool
    extract_seconds: float
    persist_seconds: float
    total_seconds: float

---

YAML Configuration

The YAML config drives the entire mapping: Excel reading parameters, project metadata, feature of interest hierarchy, observations, and results.

Reference files (packaged):

File	Purpose
src/soil_etl/yaml_mapper/dummy_config_file.yaml	Fully-documented boilerplate with all options
src/soil_etl/yaml_mapper/template.yaml	Real-world example with YAML anchors
src/soil_etl/yaml_mapper/examples/on_master.yaml	Province-specific example
examples/configs/example_config.yaml	Additional annotated example

For full documentation of the YAML format, see docs/README_yaml_format_en.md.

Top-level sections

source:                        # Excel reading params (sheet_name, header, skiprows, null_values…)
export_retreated_dataframe: false
import_batch_size: 1000        # Rows per DB batch (default: 5000)
project:                       # Project metadata (strategy, name, abbrev, province, type…)
partners:                      # Partner organizations
stewards:                      # Data steward contacts
feature_relationships:         # Optional parent-child FOI hierarchies
sample_information:            # Optional sample metadata (type, depth, horizon, dimensions)
results:                       # Generic result bindings
horizon_results:               # Horizon-specific results (includes hz_from, hz_to)

---

Python Subclass (advanced)

When YAML alone cannot express the required logic, subclass YamlDatasetMapper and override properties:

from pathlib import Path
from soil_etl.yaml_mapper import YamlDatasetMapper
from soil_etl.model_bindings import FeatureOfInterestBinding

class MyMapper(YamlDatasetMapper):
    @property
    def _feature_of_interest(self) -> FeatureOfInterestBinding:
        # custom logic here
        ...

mapper = MyMapper(
    config_path=Path("my_config.yaml"),
    data_file_path=Path("data.xlsx"),
)
mapper.import_data(database_connection_string="postgresql+psycopg2://...")

See examples/subclass_runner.py for a complete example.

---

Development

pip install poetry
poetry install --with dev,test
poetry run pytest tests

Tests use testcontainers with postgis/postgis:17-3.4 — Docker must be running.

Linting & formatting

poetry run black src tests
poetry run ruff check src tests
poetry run isort src tests

---

Build & Publish

python -m build
twine check dist/*
twine upload dist/*

---

Links

• Repository: gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl
• Issues: soil-etl/issues
• YAML format reference: docs/README_yaml_format_en.md
• Testing guide: docs/testing.md