SOIL - Structured Observation Ingestion Library

Version: 0.2.10 · 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

Boilerplate - Scaffolding for the new project

The fastest way to start a new import project is to create a ready-to-use project from the packaged template. It creates a complete, self-contained project:

• a runner (.py file used to import the data),
• an importation template (YAML config file),
• Hydra DB (YAML config file), config, an .env.example, and a preconfigured Copilot agent under .github/ (chatmodes, prompts, skills and embedded knowledge about the package, the YAML format and the database model).

From the command line

# Scaffold into a new subfolder
soil-etl start-project my-soil-imports

# ...or into the current directory
soil-etl start-project

# Options
soil-etl start-project my-soil-imports --dest ./projects --overwrite

Equivalent module invocation:

python -m soil_etl start-project my-soil-imports

From Python

import soil_etl

soil_etl.start_project("my-soil-imports")     # into a new subfolder
soil_etl.start_project()                       # into the current directory
soil_etl.start_project(name="my-soil-imports", overwrite=True)

Generated project layout

my-soil-imports/
+-- pyproject.toml                    # depends on soil-etl
+-- .env.example                      # env vars (DB_CONN, etc.)
+-- .gitignore
+-- configs/
|   +-- configs.yaml                  # Hydra root (selects db: local)
|   +-- db/
|   |   +-- base_con.yaml
|   |   +-- local.yaml
+-- data/                             # source files (Excel/CSV) - not versioned
+-- pipelines/
|   +-- import_example_dataset.py     # 3-line runner
|   +-- example_dataset.yaml          # pipeline YAML config (to adapt)
+-- .github/                          # Copilot agent + embedded knowledge
    +-- copilot-instructions.md
    +-- chatmodes/                    # soil-etl-pipeline agent (VS Code)
    +-- agents/                       # same agent (PyCharm / JetBrains)
    +-- prompts/                      # /new-pipeline, /review-yaml-config, /debug-failed-import
    +-- instructions/                 # auto-applied skills (YAML + Python runner)
    +-- knowledge/                    # YAML format, DB model, enums, template

Next steps after boilerplate exec.:

cd my-soil-imports
poetry install                       # installs soil-etl
Copy-Item|cp .env.example .env          # then fill in DB_CONN
poetry run python pipelines/import_example_dataset.py   # dry-run

Maintainers: the editable source of the template lives in project-template/ at the repo root and is synced into the packaged copy (src/soil_etl/_project_template/) with python scripts/sync_template.py (use --check in CI to detect drift).

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:

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):

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 (e.g. ProfileElement -> Sample)
sample_information:            # Optional sample metadata (type, horizon, dimensions)
profile_elements:              # Soil horizons/layers: depth bounds + designation on a PROFILE feature
observations:                  # v2: each observation carries one nested result (1:1)

v2 data model: the pipeline targets om_observation_v2 / om_result_v2 / profile_element. Declare an observations: list where each observation carries exactly one nested result: (1:1). The observed property (element_name) lives on the observation. The observation type field is deprecated/ignored. Depth bounds live on profile_elements (a PROFILE feature of interest), not on the sample; a profile element can own an observation or be the parent of a SAMPLE. Soil horizons/layers are modeled with profile_elements (a PROFILE FOI carrying depth bounds + designation) with observations attached, following the chain FOI > Profile > Observation(v2) > Result(v2) (or FOI > Profile > Sample > [FOI/Profile/Sample relations] > Observation(v2) > Result(v2)). The former root keys results: / horizon_results: have been removed (rejected at load time). See docs/README_yaml_format_en.md.

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
• Project template: project-template/README.md