Snowflake Task Execute to Google Sheets Export

arazzo: 1.0.1
info:
  title: Snowflake Task Execute to Google Sheets Export
  summary: Execute a Snowflake task, query its target table, then export the rows to Google Sheets.
  description: >-
    A data pipeline workflow that manually executes a scheduled Snowflake task to refresh a
    target table, submits a SQL query to read the refreshed rows, and writes them into a
    Google Sheets range for reporting. Demonstrates orchestrating a Snowflake task and SQL
    API together with a spreadsheet export.
  version: 1.0.0
sourceDescriptions:
  - name: snowflakeTaskApi
    url: https://raw.githubusercontent.com/api-evangelist/snowflake/refs/heads/main/openapi/snowflake-task-api-openapi.yml
    type: openapi
  - name: snowflakeSqlApi
    url: https://raw.githubusercontent.com/api-evangelist/snowflake/refs/heads/main/openapi/_original/sqlapi.yaml
    type: openapi
  - name: googleSheetsApi
    url: https://raw.githubusercontent.com/api-evangelist/google-sheets/refs/heads/main/openapi/google-sheets-values-api-openapi.yml
    type: openapi
workflows:
  - workflowId: task-execute-to-sheet
    summary: Execute a Snowflake task, query the result, and export to a sheet.
    description: >-
      Executes a Snowflake task to refresh a table, queries the refreshed rows via the SQL
      API, and writes them to a Google Sheets range.
    inputs:
      type: object
      properties:
        database:
          type: string
        schema:
          type: string
        name:
          type: string
        sqlStatement:
          type: string
        warehouse:
          type: string
        spreadsheetId:
          type: string
        range:
          type: string
    steps:
      - stepId: execute-task
        description: Manually execute the Snowflake task to refresh the table.
        operationId: $sourceDescriptions.snowflakeTaskApi.executeTask
        parameters:
          - name: database
            in: path
            value: $inputs.database
          - name: schema
            in: path
            value: $inputs.schema
          - name: name
            in: path
            value: $inputs.name
        successCriteria:
          - condition: $statusCode == 200
        outputs:
          executeStatus: $statusCode
      - stepId: query-results
        description: Query the refreshed table via the Snowflake SQL API.
        operationId: $sourceDescriptions.snowflakeSqlApi.SubmitStatement
        requestBody:
          contentType: application/json
          payload:
            statement: $inputs.sqlStatement
            warehouse: $inputs.warehouse
            timeout: 120
        successCriteria:
          - condition: $statusCode == 200
        outputs:
          resultData: $response.body#/data
      - stepId: export-to-sheet
        description: Write the query results into the 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-results.outputs.resultData
        successCriteria:
          - condition: $statusCode == 200
        outputs:
          updatedRange: $response.body#/updatedRange
    outputs:
      executeStatus: $steps.execute-task.outputs.executeStatus
      updatedRange: $steps.export-to-sheet.outputs.updatedRange