dbt Job Run to Snowflake Query to Google Sheets

arazzo: 1.0.1
info:
  title: dbt Job Run to Snowflake Query to Google Sheets
  summary: Trigger a dbt Cloud job, query the transformed data in Snowflake, then export it to Google Sheets.
  description: >-
    A data pipeline workflow that triggers a dbt Cloud transformation job, submits a
    SQL query against the freshly transformed tables in Snowflake, and writes the
    resulting rows into a Google Sheets range for downstream reporting. Demonstrates
    chaining a transformation provider, a data warehouse, and a spreadsheet provider.
  version: 1.0.0
sourceDescriptions:
  - name: dbtAdminApi
    url: https://raw.githubusercontent.com/api-evangelist/dbt/refs/heads/main/openapi/dbt-cloud-administrative-api-openapi.yml
    type: openapi
  - name: snowflakeSqlApi
    url: https://raw.githubusercontent.com/api-evangelist/snowflake/refs/heads/main/openapi/sqlapi.yaml
    type: openapi
  - name: googleSheetsApi
    url: https://raw.githubusercontent.com/api-evangelist/google-sheets/refs/heads/main/openapi/google-sheets-openapi.yml
    type: openapi
workflows:
  - workflowId: dbt-job-to-snowflake-to-sheets
    summary: Run a dbt job, query Snowflake, and export results to a sheet.
    description: >-
      Triggers a dbt Cloud job run, executes a Snowflake SQL statement against the
      transformed model, and writes the returned values to a Google Sheets range.
    inputs:
      type: object
      properties:
        accountId:
          type: integer
        jobId:
          type: integer
        warehouse:
          type: string
        sqlStatement:
          type: string
        spreadsheetId:
          type: string
        range:
          type: string
    steps:
      - stepId: trigger-dbt-job
        description: Trigger a dbt Cloud job run for the transformation.
        operationId: $sourceDescriptions.dbtAdminApi.triggerJobRun
        parameters:
          - name: accountId
            in: path
            value: $inputs.accountId
          - name: jobId
            in: path
            value: $inputs.jobId
        requestBody:
          contentType: application/json
          payload:
            cause: Triggered by Arazzo data pipeline
        successCriteria:
          - condition: $statusCode == 200
        outputs:
          runId: $response.body#/data/id
      - stepId: query-snowflake
        description: Run a SQL statement against the transformed Snowflake tables.
        operationId: $sourceDescriptions.snowflakeSqlApi.SubmitStatement
        requestBody:
          contentType: application/json
          payload:
            statement: $inputs.sqlStatement
            warehouse: $inputs.warehouse
            timeout: 120
        successCriteria:
          - condition: $statusCode == 200
        outputs:
          statementHandle: $response.body#/statementHandle
          resultData: $response.body#/data
      - stepId: write-to-sheet
        description: Write the query results into a Google Sheets range.
        operationId: $sourceDescriptions.googleSheetsApi.updateValues
        parameters:
          - name: spreadsheetId
            in: path
            value: $inputs.spreadsheetId
          - name: range
            in: path
            value: $inputs.range
          - name: valueInputOption
            in: query
            value: RAW
        requestBody:
          contentType: application/json
          payload:
            range: $inputs.range
            majorDimension: ROWS
            values: $steps.query-snowflake.outputs.resultData
        successCriteria:
          - condition: $statusCode == 200
        outputs:
          updatedRange: $response.body#/updatedRange
          updatedCells: $response.body#/updatedCells
    outputs:
      runId: $steps.trigger-dbt-job.outputs.runId
      statementHandle: $steps.query-snowflake.outputs.statementHandle
      updatedRange: $steps.write-to-sheet.outputs.updatedRange