DuckDB の前にデータベースを置かずに、定期実行する。

# duckdb-nightly-rollup.yaml
schedule: "0 3 * * *"
max_active_runs: 1

resources:
  limits:
    memory: "8Gi"

steps:
  - id: rollup
    action: duckdb@v1
    with:
      database: /data/analytics.duckdb
      query: |
        INSTALL httpfs; LOAD httpfs;
        CREATE OR REPLACE TABLE daily_sales AS
          SELECT order_date, region, sum(amount) AS amount
          FROM read_parquet('s3://warehouse/raw/orders/*.parquet')
          GROUP BY 1, 2;
    retry_policy:
      limit: 2
      interval_sec: 300

  - id: export
    action: duckdb@v1
    with:
      database: /data/analytics.duckdb
      query: |
        COPY daily_sales TO '/data/export/daily_sales.parquet' (FORMAT parquet);
    depends: rollup

  - id: count_rows
    action: duckdb@v1
    with:
      database: /data/analytics.duckdb
      readonly: true
      query: SELECT count(*) AS row_count FROM daily_sales;
    depends: export

  - id: verify
    env:
      - COUNT_JSON: ${steps.count_rows.outputs.result}
    run: test "$(printf '%s\n' "$COUNT_JSON" | jq -r '.[0].row_count')" -gt 0
    depends: count_rows

handler_on:
  failure:
    run: /opt/analytics/notify-failure.sh

mail_on:
  failure: true

# duckdb-incremental-load.yaml
schedule: "*/15 * * * *"
max_active_runs: 1

steps:
  - id: load_cursor
    action: state.get
    output: CURSOR
    with:
      key: cursors/events-loaded-through
      default:
        loaded_through: "2026-01-01T00:00:00Z"

  - id: window
    run: |
      printf 'since=%s\n' "$(printf '%s\n' "$CURSOR" | jq -r .value.loaded_through)" >> "$DAGU_OUTPUT_FILE"
      printf 'until=%s\n' "$(date -u +%Y-%m-%dT%H:%M:%SZ)" >> "$DAGU_OUTPUT_FILE"
    outputs:
      - name: since
      - name: until
    depends: load_cursor

  - id: append_new_events
    action: duckdb@v1
    with:
      database: /data/analytics.duckdb
      query: |
        INSTALL httpfs; LOAD httpfs;
        INSERT INTO events
          SELECT * FROM read_parquet('s3://warehouse/events/*.parquet')
          WHERE ingested_at >  TIMESTAMP '${steps.window.outputs.since}'
            AND ingested_at <= TIMESTAMP '${steps.window.outputs.until}';
    depends: window
    retry_policy:
      limit: 3
      interval_sec: 60

  - id: save_cursor
    action: state.set
    with:
      key: cursors/events-loaded-through
      value:
        loaded_through: "${steps.window.outputs.until}"
    depends: append_new_events

mail_on:
  failure: true