IGH Data Transformation

Developer Getting Started

Prerequisites

• UV - Fast Python package manager (manages Python versions automatically)

Installation

1. Install UV (if not already installed):

   curl -LsSf https://astral.sh/uv/install.sh | sh

2. Clone the repository:

   git clone <repository-url>
   cd igh-data-transform

3. Install Python and dependencies:

   # UV will automatically install Python 3.12 if needed
   uv sync

Running the Application

You can run the CLI tool without activating the virtual environment using uv run:

# Show available commands
uv run igh-transform --help

Alternatively, activate the virtual environment first:

source .venv/bin/activate  # On Linux/Mac
# or
.venv\Scripts\activate     # On Windows

# Then run normally
igh-transform --help

CLI Commands

Bronze to Silver Transformation

Transform raw Bronze layer data to cleaned Silver layer:

uv run igh-transform bronze-to-silver --bronze-db ./data/bronze.db --silver-db ./data/silver.db

This applies cleanup transformations:

• Drops columns that are entirely null (preserves valid_from/valid_to)
• Normalizes whitespace in text fields
• Ready for table-specific column renames and value mappings

Silver to Gold Transformation

Transform Silver layer to a star schema Gold layer (dimensions, facts, bridges):

uv run igh-transform silver-to-gold --silver-db ./data/silver.db --gold-db ./data/star_schema.db

Running the ETL Pipeline

Two wrapper scripts run the full pipeline and copy the resulting star schema database to the backend.

sync-and-run-etl.sh - Sync + Transform

Syncs data from Dataverse, then runs Bronze -> Silver -> Gold -> Backend:

# Fresh sync (default) - deletes existing bronze DB and syncs from scratch
./sync-and-run-etl.sh

# Incremental sync - keeps existing bronze DB
./sync-and-run-etl.sh --update

# Skip sync entirely - use an existing bronze DB
./sync-and-run-etl.sh --skip-sync

# Use a custom .env file for Dataverse credentials
./sync-and-run-etl.sh --env-file /path/to/.env

run-etl.sh - Transform Only

Runs the transformation pipeline on an existing bronze DB (no Dataverse sync):

# Use the default bronze DB path (data/dataverse_complete_raw.db)
./run-etl.sh

# Use a custom bronze DB path
./run-etl.sh /path/to/bronze.db

Both scripts produce star_schema.db and copy it to ../backend/ and ../backend/tests/.

Pulling Data from Dataverse

This project uses igh-data-sync to pull data from Microsoft Dataverse before applying transformations.

Setup:

1. Configure environment variables - Create a .env file with your Dataverse credentials:

   CLIENT_ID=your-azure-client-id
   CLIENT_SECRET=your-azure-client-secret
   SCOPE=https://your-org.crm.dynamics.com/.default
   API_URL=https://your-org.api.crm.dynamics.com/api/data/v9.2/
   SQLITE_DB_PATH=./data/dataverse.db

2. Run the sync - Pull data from Dataverse to local SQLite:

   uv run sync-dataverse

3. Verify the data (optional) - Check foreign key integrity:

   uv run sync-dataverse --verify

The synced data will be stored in a SQLite database with SCD2 (Slowly Changing Dimension Type 2) versioning for historical tracking.

Development Workflow

The project uses UV for dependency management. Common commands:

• Add a dependency: uv add <package-name>
• Add a dev dependency: uv add --dev <package-name>
• Update dependencies: uv sync
• Run commands without activating venv: uv run <command>
• Run all QA checks (lint + format + tests): bash scripts/qa.sh
• Run a single QA step: bash scripts/qa.sh lint, bash scripts/qa.sh format, bash scripts/qa.sh test
• Pass extra pytest flags: bash scripts/qa.sh test -v --all
• Run e2e tests: E2E_BRONZE_DB_PATH=/path/to/bronze.db bash scripts/qa.sh test --e2e -v

The same scripts/qa.sh is used by the GitHub Actions CI workflow, so local and CI checks always match.

If data/dataverse_complete_raw.db exists (the default output of sync-and-run-etl.sh), e2e tests are included automatically. You can also set E2E_BRONZE_DB_PATH explicitly to point at a different Bronze DB.

Special Case comments

Code paths that work around known defects in the upstream CRM data are marked with a # Special Case: comment followed by an explanation on the next line. These are intended to be temporary — once the source data is corrected, search for Special Case to find and remove them.

Documentation

• Adding Transformations - Guide for data analysts on how to add new data transformations