Automation & CI Helper Skill

This skill covers all automation in the insurance analytics project: GitHub Actions CI workflows, the Apache Airflow data pipeline, the Makefile task runner, pre-commit hooks, and quality-gate tooling.

Automation components at a glance

Component	Location	Purpose
GitHub Actions – CI	.github/workflows/ci-postgres.yml	Lint → test → SonarCloud on every push/PR
GitHub Actions – Release	.github/workflows/release.yml	Manual workflow dispatch: bumps patch version tag and creates a GitHub Release (no build artifact is uploaded)
GitHub Actions – Scheduled	.github/workflows/scheduled-pipeline.yml	Runs the pipeline on a schedule (now delegates to a reusable workflow)
GitHub Actions – Reusable pipeline	.github/workflows/run-pipeline.yml	Reusable workflow that owns Postgres service and runs ingest/ETL/upload
Composite actions	.github/actions/setup-python-env, .github/actions/run-etl	Shared actions for Python setup and running ETL steps
Airflow DAG	airflow/dags/insurance_pipeline_dag.py	Orchestrates the ETL end-to-end
Makefile	Makefile	Developer task runner (local shortcuts)
Pre-commit hooks	.pre-commit-config.yaml	Auto-formats and lints on every git commit

GitHub Actions: CI workflow (ci-postgres.yml)

The CI workflow has three sequential jobs:

lint  →  etl  →  sonarcloud

Job 1 – lint (static analysis)

Runs on every push to main/master that touches src/**, tests/**, src/sql/**, requirements.txt, pyproject.toml, sonar-project.properties, or .github/workflows/ci-postgres.yml. Pull requests to those branches trigger lint only when src/**, tests/**, requirements.txt, or pyproject.toml are changed (src/sql/** and other config files do not trigger CI on PRs).

Steps:

1. ruff format --check – formatting check
2. ruff check + pylint – linting (ruff is non-blocking on auto-fixable issues; pylint is non-blocking)
3. mypy src – static type checking
4. bandit -r src – security scan
5. radon cc + radon mi – cyclomatic complexity and maintainability index

Job 2 – etl (ETL smoke test with PostgreSQL)

The project now centralises the integration pipeline in a reusable workflow: .github/workflows/run-pipeline.yml.

Key points:

• The reusable workflow owns the Postgres 15 service and offers two modes via inputs:
  • seed-db: true — initialise and seed the DB with stable fixtures (used by CI tests)
  • seed-db: false — run a live Kaggle ingest via src.load (used by the scheduled run)
• Schema application is no longer performed with runner psql -f ... in multiple places; the loader/seed script executes the canonical DDL (src/sql/ddl_create_tables.sql) via the same Python helper (_apply_ddl()), so CI and scheduled runs share the exact schema logic.
• The test flow (CI) typically calls the reusable workflow with seed-db: true, runs the ETL steps, and then a dedicated coverage job collects and uploads coverage artifacts.

Key environment variable set by the workflow:

DATABASE_URL: postgresql+psycopg2://postgres:postgres@localhost:5432/insurdb

Job 3 – sonarcloud

Downloads the test artifacts (coverage + JUnit XML) and runs SonarCloud analysis. The Sonar job now depends on the CI coverage job (which produces build/reports/coverage.xml and build/reports/junit-report.xml).

Secrets used:

• SONAR_TOKEN – SonarCloud authentication
• CODECOV_TOKEN – Codecov upload (used by the CI coverage job)

Triggering CI manually

Push any change to a file under src/, tests/, src/sql/, requirements.txt, pyproject.toml, sonar-project.properties, or .github/workflows/ci-postgres.yml on a branch targeting main/master. A pull request against those branches also triggers CI when src/**, tests/**, requirements.txt, or pyproject.toml are modified.

Note: because the ETL and service logic are now in a reusable workflow, changes to the pipeline are tested more consistently across CI and scheduled runs.

Makefile: local task runner

Run make help to see all targets. Most-used ones:

make install        # pip install all dependencies
make test           # pytest with coverage
make lint           # flake8 + ruff + pylint
make format         # black + isort + ruff format + docformatter
make check-types    # mypy --strict
make quality        # bandit + radon + xenon + cohesion + vulture
make check          # lint + check-types + test + quality (full gate)

make db-init        # apply src/sql/ddl_create_tables.sql
make db-reset       # DROP SCHEMA + re-apply DDL
make kaggle-load    # download Kaggle dataset and load into PostgreSQL
make setup          # install + db-init + kaggle-load (full first-time setup)

make airflow-up     # start Airflow via Docker Compose (port 8080)
make airflow-down   # stop Airflow
make airflow-logs   # tail scheduler logs

Airflow DAG

The DAG is defined in airflow/dags/insurance_pipeline_dag.py and orchestrates:

kaggle_ingest  →  [run_transform_kpis, run_ml_model]  →  generate_excel_report

Task	Module invoked	Notes
kaggle_ingest	src.load --kaggle-config	Downloads dataset from Kaggle and loads into DB; requires KAGGLE_USERNAME + KAGGLE_KEY env vars
run_transform_kpis	src.transform	Runs in parallel with run_ml_model
run_ml_model	src.model	Runs in parallel with run_transform_kpis
generate_excel_report	src.report	Runs after both transform and model complete

The schedule defaults to daily at 06:00 UTC and can be overridden via the AIRFLOW_PIPELINE_SCHEDULE environment variable. The project directory is set via AIRFLOW_PROJECT_DIR.

Starting Airflow locally

make airflow-up
# Airflow UI → http://localhost:8080   login: admin / admin
make airflow-down   # stop when done

Airflow runs via Docker Compose (airflow/docker-compose-airflow.yml) on a dedicated insurance-network Docker network.

Pre-commit hooks (.pre-commit-config.yaml)

Pre-commit runs automatically on git commit. To run it manually against all files:

make pre-commit
# or directly:
pre-commit run --all-files

This project uses Ruff as the unified linter and formatter in pre-commit, replacing the former black, isort, and flake8 hooks. Typical hooks: ruff (lint + fix), ruff-format, trailing-whitespace / end-of-file fixers.

First-time setup:

pip install pre-commit
pre-commit install   # installs the git hook

Quality gate script (scripts/check_quality.sh)

A shell script that aggregates multiple quality checks in one pass:

make quality-check
# or directly:
./scripts/check_quality.sh

Run this locally before opening a pull request to catch issues that CI will also catch.

Adding a new CI step

Prefer adding new behaviour as a composite action or to the reusable workflow so it can be exercised by both CI and scheduled runs. General steps:

1. If the change affects runtime/test behaviour (DB, ETL), update ./.github/workflows/run-pipeline.yml or add a new composite action under ./.github/actions/ so callers can reuse it.
2. Otherwise add a step in ci-postgres.yml (lint/coverage) or in run-pipeline.yml for runtime steps.
3. Add required Python tooling to requirements.txt and/or pyproject.toml.
4. Test locally (run make test) and push to a feature branch to validate the workflow in Actions.

Common CI failures and fixes

Failure	Likely cause	Fix
ruff format --check fails	Code not formatted	Run make format locally, then commit
mypy errors	Missing type annotations or wrong types	Add/fix type hints in src/
bandit security warning	Use of a flagged function (e.g. subprocess, pickle)	Refactor or add # nosec with justification
Postgres connection refused	Service container not ready	The reusable workflow uses health-checks on the Postgres service; if you see failures, confirm the run-pipeline.yml service definition and health options.
pytest collection error	Import error in src/	Run pytest -q locally with the same DATABASE_URL to reproduce
SonarCloud gate fails	Coverage below threshold or new bugs found	Fix the issues reported in the SonarCloud PR comment

Secrets required in GitHub repository settings

Secret name	Used by
SONAR_TOKEN	SonarCloud scan (job 3)
CODECOV_TOKEN	Codecov coverage upload (job 2)
KAGGLE_USERNAME	Scheduled pipeline — Kaggle dataset ingest
KAGGLE_KEY	Scheduled pipeline — Kaggle dataset ingest

Additional notes:

• The canonical DDL lives in src/sql/ddl_create_tables.sql (loaded via importlib.resources in src.load). The Python helpers _apply_ddl() and _table_columns() (in src.load) are the preferred way to apply and reflect the schema programmatically; the seed script .github/scripts/seed_test_db.py reuses them.
• Test and coverage artifacts are written to build/reports/ by default (junit-report.xml, coverage.xml, htmlcov/). Update pyproject.toml / Makefile if you need a different location.