# dbt Daily Scheduling Design

Updated: 2026-03-24
Status: Planned (not yet implemented)

## 1. Problem

All dbt mart models (`mart_cost_daily`, `mart_cost_monthly`, etc.) are incremental but currently rely on manual `dbt build` execution. There is no automated scheduling to keep data fresh. The cost anomaly dashboards (`prod-cost-anomaly`, `dev-cost-anomaly`) require daily data updates to be useful.

## 2. Requirements

- Run `dbt build` daily to refresh all incremental marts
- Must support GCP Service Account authentication (not interactive `gcloud auth`)
- Should notify on failure (Slack or email)
- Must support manual trigger for ad-hoc runs
- Should complete within a reasonable time window (incremental runs process only the lookback window)

## 3. What `dbt build` Does

| Layer | Materialization | Daily Run Behavior |
|-------|----------------|-------------------|
| Staging (`stg_*`) | view | Re-register view definition (no data movement) |
| Intermediate (`int_*`) | view | Same as above |
| Marts (incremental) | incremental + insert_overwrite | Overwrite data within lookback window only |

Lookback windows:
- `mart_cost_daily`: 14 days (`mart_daily_lookback_days`)
- `mart_cost_monthly` and others: 3 months (`mart_lookback_months`)

Full rebuild (`--full-refresh`) is only needed when schema or logic changes.

## 4. Option Analysis

### Option A: GitHub Actions Cron (Recommended for MVP)

Pros:
- Zero infrastructure — runs on GitHub-hosted runners
- Version controlled alongside dbt code
- Built-in `workflow_dispatch` for manual triggers
- Free tier covers daily runs easily

Cons:
- Max 6-hour timeout (sufficient for dbt build)
- Requires GCP SA key stored as GitHub secret
- No built-in retry with backoff (can be added)

Estimated implementation:
- One workflow YAML file
- One GCP Service Account with BigQuery Data Editor role
- One GitHub secret (`GCP_SA_KEY`)

### Option B: GCP Cloud Scheduler + Cloud Run Job

Pros:
- Native GCP ecosystem, no cross-cloud credentials
- Workload Identity Federation (no key management)
- Built-in retry and dead-letter

Cons:
- Requires Docker image for dbt
- More infrastructure to maintain (Cloud Run service, Artifact Registry)
- Overkill for a single daily job at this stage

### Option C: Airflow / Dagster / Prefect

Pros:
- Rich DAG orchestration, dependency management
- Good for complex multi-step pipelines

Cons:
- Significant operational overhead
- Not justified until pipeline complexity grows

## 5. Recommended Approach

**Option A (GitHub Actions)** for MVP. Migrate to Option B if:
- The pipeline grows beyond a single `dbt build` command
- Credential rotation becomes burdensome
- Execution time exceeds GitHub Actions limits

## 6. Proposed Schedule

```
cron: '0 2 * * *'   # UTC 02:00 = Beijing 10:00 AM
```

Rationale:
- AWS CUR data typically lands by UTC 00:00-01:00
- 02:00 UTC gives buffer for source data availability
- Beijing morning (10 AM) means fresh data for daily review

## 7. Pre-requisites

1. Create GCP Service Account with roles:
   - `roles/bigquery.dataEditor` on `gcp-rd-research-dev`
   - `roles/bigquery.jobUser` on `gcp-rd-research-dev`
2. Export SA key JSON → store as GitHub repo secret `GCP_SA_KEY`
3. Ensure `dbt/profiles.yml` supports `method: service-account` (in addition to current `oauth`)
4. Set up Slack webhook or email for failure notifications (optional)

## 8. Skeleton Workflow

```yaml
# .github/workflows/dbt-daily.yml
name: dbt daily build
on:
  schedule:
    - cron: '0 2 * * *'
  workflow_dispatch: {}

jobs:
  dbt-build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: google-github-actions/auth@v2
        with:
          credentials_json: ${{ secrets.GCP_SA_KEY }}
      - uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - run: pip install dbt-bigquery
      - run: cd dbt && dbt build
      # Optional: notify on failure
      # - uses: slackapi/slack-github-action@v2
      #   if: failure()
      #   with:
      #     webhook: ${{ secrets.SLACK_WEBHOOK }}
      #     payload: '{"text": "dbt daily build failed: ${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}"}'
```

## 9. Open Questions

1. Should `dbt build` run seeds every time, or only on seed file changes?
2. Do we need separate schedules for daily vs monthly marts?
3. Should we add `dbt test` as a separate step with different failure handling?
4. Is Workload Identity Federation preferred over SA key for GCP auth?