personal-reporting-pipelines

Development and Documentation

Data Refresh Workflows

Personal data integration and analytics platform using dlt (ingestion into Databricks Unity Catalog) and dbt (transformations on Databricks), orchestrated with GitHub Actions and managed locally with mise.

Overview

This repository contains a Docker Development Container for VSCode and the infrastructure and workflows for my personal data platform. It uses Databricks for raw data ingestion and storage, 1Password Environments for workflow credentials, and mise for repeatable local and CI commands.

Data Sources

The following data will be ingested from my personal systems into a Databricks warehouse (Unity Catalog) for automation and analysis.

1. Notion
2. HubSpot
3. Google Health

Architecture

Data Pipeline Stack

1. dlt hub - Extract, normalise, and load source data into Databricks raw layer
2. dbt core - Transform raw data into analytics-ready models and views
3. Databricks - Lakehouse platform for storage and analysis
4. 1Password Environments - Secure environment variables supplied to commands with the 1Password CLI
5. GitHub Actions - Automated orchestration and scheduling of data pipelines

Project Structure

The project follows modern data engineering best practices with clear separation of concerns:

├── pipelines/          # dlt data extraction pipelines
│   ├── sources/        # Source factory modules for each pipeline
│   │   ├── hubspot.py
│   │   └── notion.py
│   ├── hubspot.py      # Thin orchestration wrappers that delegate to central runner
│   ├── notion.py       # Thin orchestration wrappers that delegate to central runner
│   └── common/         # Shared utilities and helpers
├── dbt/                # dbt transformation models
│   ├── models/         # personal dbt models (staging, intermediate, marts)
│   ├── macros/         # custom macros for adapters and mocking
│   ├── tests/          # custom tests when missing from packages
│   └── seeds/          # reference tables and mock sources
└── .github/            # github actions workflows
    └── workflows/      # CI/CD and refresh orchestration

Naming Conventions

• dlt pipelines: {source}__{entity} (e.g., hubspot__contacts, google_health__steps)
• dbt models (except marts): {layer}_{source}__{entity} (e.g., stg_hubspot__contacts, contacts)
• dbt marts: dim_{entity}_v1 / fct_{entity}_v1 (e.g., dim_habit_v1, fct_engagement_v1)

Setup

Prerequisites

1. mise for project tools and task commands
2. GitHub Account with repository access
3. 1Password CLI beta and an Environment ID for credentialed tasks
4. Docker (optional, for containerized development)

Optional GCP / BigQuery rollback setup

1. Create a GCP Project and enable required APIs:

   • BigQuery API
   • Secret Manager API

2. Set up BigQuery:

   • Create a dataset for raw data (e.g., raw_data)
   • Create a dataset for transformed data (e.g., analytics)
   • Ensure proper IAM permissions for service accounts

3. Configure Secret Manager:

   • Configure GCP service account key as default application credentials

   • Store static keys and credentials for each data source in TOML fragments:

     [sources.hubspot]
     api_key = "your-key"
     
      [sources.notion]
      api_key = "your-key"
     
      [sources.google_health]
      client_id = "your-id"
      client_secret = "your-secret"
     

   • After authorising the Google Health OAuth client, store refresh token in dedicated secret

     • sources-google_health-refresh_token

4. Create Service Account:

   • Service account with BigQuery Admin and Secret Manager Secret Accessor roles
   • Download JSON key file for GitHub Actions

Local Development Setup

1. Clone and setup:

   git clone <repository-url>
   cd personal-reporting-pipelines
   mise install
   mise run setup
   

2. Configure credentialed tasks: install the 1Password CLI beta and export OP_ENVIRONMENT_ID plus OP_SERVICE_ACCOUNT_TOKEN in your shell. .env.databricks is deprecated and no longer loaded; provide warehouse and source credentials through the selected 1Password Environment, or use sbx secrets inside Docker AI Sandbox. Credentialed tasks run inside op run --environment.

3. Common tasks:

   mise run test                                  # offline unit tests
   mise run test-e2e                              # starts SQL warehouse, then runs cloud tests
   mise run pipeline -- notion --full             # one dlt source
   mise run pipeline-all                          # all three dlt sources
   mise run dbt-build-mock                        # DuckDB fixture build
   mise run dbt-build-dev                         # starts SQL warehouse, builds Databricks dev
   mise run docs                                  # dbt + Sphinx documentation
   

   See docs/source/mise_tasks.md for the full task list and CI behavior.

4. Set optional local environment variables:

   export GCP_PROJECT_ID=your-project-id
   export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
   export RAW_SCHEMA_NAME=raw_data
   

Agentic coding (Docker AI Sandbox preferred)

The preferred path for AI coding agents (opencode, claude, codex) is Docker AI Sandbox (sbx CLI) configured by the repository-root sbxenv.yaml, with a dedicated GitHub machine user scoped to this repo. The .devcontainer/agent is the second supported option. Full details: docs/source/agent_container.md. That guide also explains how to export OpenCode sessions to Google Cloud Storage before removing an ephemeral environment and restore them later.

1. Machine user (one-time): create a dedicated GitHub account, add it as a collaborator on this repo only (Write, not Admin), and mint a short-lived fine-grained PAT limited to this repo (Contents: read/write, Pull requests: read/write, Metadata: read). Never use a personal token.

2. Install and sign in: brew trust docker/tap && brew install docker/tap/sbx (or winget install -h Docker.sbx / sudo apt install docker-sbx), then sbx login.

3. Store development credentials: sbx secret set github --command 'gh auth token' (run as the machine user) plus your model key, e.g. sbx secret set opencode --ref 'op://vault/item/field'.

4. Store service credentials: use custom secrets and specify dlt environment variable names. For Databricks, use either a service-principal OAuth pair: DESTINATION__DATABRICKS__CREDENTIALS__CLIENT_ID and DESTINATION__DATABRICKS__CREDENTIALS__CLIENT_SECRET, or a personal access token as DESTINATION__DATABRICKS__CREDENTIALS__ACCESS_TOKEN:

   sbx secret set-custom databricks-client-id --ref 'op://vault/item/client-id' --env DESTINATION__DATABRICKS__CREDENTIALS__CLIENT_ID
   sbx secret set-custom databricks-client-secret --ref 'op://vault/item/client-secret' --env DESTINATION__DATABRICKS__CREDENTIALS__CLIENT_SECRET
   # PAT alternative:
    sbx secret set-custom databricks-token --command 'powershell.exe -NoProfile -Command C:\Tools\Get-DatabricksToken.ps1' --refresh 30m --env DESTINATION__DATABRICKS__CREDENTIALS__ACCESS_TOKEN
   

   For dbt test and prod targets, also provide the CLI/dbt-native DATABRICKS_CLIENT_ID and DATABRICKS_CLIENT_SECRET variables. The warehouse preflight maps the dlt-style client credentials to Databricks CLI variables; the dbt profiles read the native names directly. Set OP_ENVIRONMENT_ID to the 1Password Environment ID used by credentialed mise tasks.

   Source credentials use the same custom-secret pattern, for example: sbx secret set-custom hubspot --ref 'op://vault/item/field' --env SOURCES__HUBSPOT__API_KEY

5. Run an agent:

sbx run --name reporting-agent --clone claude .   # or: codex | opencode

Inside: mise install and mise run setup, then mise run pipeline -- notion or opencode run "...". Credentialed tasks use op run --environment "$OP_ENVIRONMENT_ID" -- .... Review via git fetch sandbox-<name> before push/PR. Default to DBT_TARGET=mock inside sandboxes.

Alternative: the restricted .devcontainer/agent "AI Agents" container (non-root, read-only root fs, no Docker socket) is also supported — see docs/source/agent_container.md §4. Its home-level ~/.secrets/.agent.env file must include GITHUB_TOKEN set to the machine-user PAT.

GitHub Actions Setup

1. Configure repository credentials:

   • OP_SERVICE_ACCOUNT_TOKEN (GitHub secret)
   • OP_ENVIRONMENT_ID (GitHub variable containing the 1Password Environment ID)
   • Ensure the 1Password Environment exports the dlt source and Databricks variables used by the pipelines.

2. Configure workflow schedules in .github/workflows/:

   • Scheduled pipelines for source refreshes: Notion, HubSpot, Google Health
   • Scheduled pipeline for data transformations after source refresh
   • Manual trigger option for full refresh scenarios

3. Gemini workflows are leveraged from run-gemini-cli

4. Agentic workflows use GitHub Agentic Workflows (gh-aw) — see setup steps below.

GitHub Agentic Workflows (gh-aw) Setup

Agentic workflows (.github/workflows/*.md + compiled .lock.yml) are managed via the gh-aw extension.

Install the extension:

gh extension install github/gh-aw

Add the engine secret (this project uses Gemini):

gh secret set GEMINI_API_KEY

Author or edit a workflow — modify the .md source file in .github/workflows/. Frontmatter controls engine, triggers, permissions, and safe outputs; the Markdown body is the agent prompt.

Compile after frontmatter changes:

gh aw compile                  # recompile all workflows
gh aw compile my-workflow      # recompile one workflow

Commit both the .md source and the generated .lock.yml. The .md body is loaded at runtime so prompt-only changes don't require recompilation.

Run a workflow manually:

gh aw run my-workflow

Integrations

1. Databricks Unity Catalog — primary destination for dlt and dbt
2. 1Password Environments — source and warehouse credentials for local and CI tasks

Pipeline Refresh Patterns

The pipelines support flexible refresh modes for data loading:

• Incremental (default): Only loads new/changed data since last run
• Full refresh: Completely reloads all data, useful for data quality issues or schema changes

How to Trigger Full Refresh

Method 1: Environment Variable Override (Global)

FORCE_FULL_REFRESH=true mise run pipeline -- hubspot

Method 2: Pipeline-Specific Override

# Force full refresh for HubSpot only
HUBSPOT_FULL_REFRESH=true mise run pipeline -- hubspot

Central pipeline task

The mise task wraps the central pipelines.run_pipeline runner, starts the Databricks SQL warehouse, and loads source credentials from the selected 1Password Environment:

mise run pipeline -- notion --full
mise run pipeline -- hubspot --incremental --select hubspot__contacts,hubspot__companies

Direct function parameter

from pipelines.hubspot import refresh_hubspot

# Force full refresh
refresh_hubspot(is_incremental=False)

# Use environment-based detection (default)
refresh_hubspot()  # or refresh_hubspot(is_incremental=None)

GitHub Actions

steps:
  - name: run HubSpot pipeline
    env:
      OP_SERVICE_ACCOUNT_TOKEN: ${{ secrets.OP_SERVICE_ACCOUNT_TOKEN }}
      OP_ENVIRONMENT_ID: ${{ vars.OP_ENVIRONMENT_ID }}
    run: mise run pipeline -- hubspot

Environment Variables Reference

Variable	Description	Example
FORCE_FULL_REFRESH	Global override for all pipelines	export FORCE_FULL_REFRESH=true
PIPELINE_NAME	Pipeline identifier for specific overrides	export PIPELINE_NAME=HUBSPOT
{PIPELINE_NAME}_FULL_REFRESH	Pipeline-specific full refresh flag	export HUBSPOT_FULL_REFRESH=true

Best Practices

1. Default to incremental: Always use incremental loading for regular operations
2. Use full refresh sparingly: Only when necessary for data quality or schema changes
3. Environment-based control: Use environment variables for production deployments
4. Logging: Always log the refresh mode being used for debugging
5. GitHub Actions integration: Use workflow inputs and environment variables to control refresh mode dynamically
6. Secret management: Store credentials in the configured 1Password Environment or GCP Secret Manager, never in code
7. Monitoring: Set up alerts for pipeline failures and data quality issues

Development and Testing

Environments

• Development: Local development with duckdb database
• Testing: Local tests with non-production BigQuery schemas
• Production: Automated production pipelines via GitHub Actions

Local Development

1. Build Dev Container in VSCode, this will run mise run setup to install dependencies
2. Run pipelines locally for testing:

   # Offline unit tests
   mise run test
   # Live APIs + Databricks e2e tests (via the configured 1Password Environment)
   mise run test-e2e
   

3. Test dbt models:

   # Local DuckDB fixtures
   mise run dbt-build-mock
   # Databricks dev (starts and waits for the SQL warehouse)
   mise run dbt-build-dev
   

Testing

1. Unit tests for pipeline functions and utilities
2. Integration tests for dlt sources and dbt models
3. End-to-end tests for complete pipeline execution
4. Data quality tests using dbt's built-in testing framework

dbt Development

1. Model development: mise run dbt -- run --target mock --select model_name
2. Model testing: mise run dbt -- test --target mock --select model_name
3. Documentation: Generate the combined site with mise run docs

Model structure

Every dbt SQL model follows the same top-to-bottom shape so a reader can debug it without scrolling:

1. Import CTEs first. One CTE per ref()/source(), placed at the very top of the model. This is the only place {{ ref(...) }} appears, so the model's inputs are obvious at a glance.
2. Transform CTEs next. All shaping, joining, casting, and derivation happens in named CTEs after the imports, reading from the imports or earlier CTEs.
3. Select from final last. The model ends with a final CTE holding the output shape, followed by exactly select * from final. Selecting the wildcard keeps the output decoupled from column order and makes it cheap to inspect the final shape while debugging.

with
-- Import CTEs: one per upstream model, selected as-is
stg_hubspot__contacts as (
    select * from {{ ref('stg_hubspot__contacts') }}
),

-- Transform CTEs: all reshaping happens here
filtered as (
    select * from stg_hubspot__contacts where email is not null
),

final as (
    select contact_id, email from filtered
)

select * from final

This convention applies to every model (staging, core, intermediate, and marts).

GitHub Actions Orchestration

Workflow Structure

The project uses GitHub Actions for automated pipeline execution:

• Scheduled runs: Daily/weekly automated data ingestion
• Manual triggers: On-demand pipeline execution with full refresh options
• Pull request validation: Automated testing on code changes
• Deployment: Automated deployment to production environment

Monitoring and Alerts

• Pipeline status: GitHub Actions workflow status monitoring
• Data quality: dbt test results and alerts
• Performance: Execution time tracking and optimization
• Error handling: Automated retry logic and failure notifications

dlt Logging and Artifacts

dlt pipelines handle logging and artifacts differently than traditional applications:

• Console logging: All pipeline output goes to stdout/stderr (captured by GitHub Actions)
• Pipeline state: Stored in ~/.dlt/ folder (home directory) with metadata, progress, and state information
• No log files: dlt doesn't create separate log files in a logs/ directory
• Targeted artifacts: GitHub Actions uploads only state.json files for debugging and monitoring
• Pipeline info: The pipeline.run() method returns execution information that gets logged

Note: The ~/.dlt/ folder location is dlt's default behavior. You can customize this using the DLT_DATA_DIR environment variable if needed, but for GitHub Actions, the default home directory location works well.

What's in state.json Files

The state.json files contain essential pipeline information without the bulk of raw data:

• Pipeline execution status: Success/failure states and timestamps
• Resource progress: Which data sources were processed and their status
• Incremental state: Cursor values and last processed timestamps
• Error information: Any failures and their context
• Schema changes: Data structure modifications detected during execution

This targeted approach provides debugging insights while keeping artifact sizes manageable.