Google Cloud BigQuery Slot Utilization and Reservation Saturation Sentinel

id: gcp-bigquery-slot-utilization-saturation-sentinel
namespace: company.cloud

inputs:
  - id: project_id
    type: STRING
    description: Google Cloud Project ID to audit.
  - id: slot_utilization_threshold_pct
    type: INT
    defaults: 90
    description: "Maximum acceptable BigQuery slot utilization percentage before
      alerting (default 90%)."

tasks:
  - id: query_bigquery_slot_metrics
    type: io.kestra.plugin.gcp.cli.GCloudCLI
    projectId: "{{ inputs.project_id }}"
    serviceAccount: "{{ secret('GCP_SERVICE_ACCOUNT') }}"
    commands:
      - |
        # Query Cloud Monitoring for BigQuery allocated slots vs total reservation
        gcloud monitoring metric-descriptors describe "bigquery.googleapis.com/slots/allocated_for_project" --format=json > /tmp/bq_desc.json

        FILTER='metric.type="bigquery.googleapis.com/slots/allocated_for_project" AND resource.type="bigquery_project"'
        START_TIME=$(date -u -v-15M +%Y-%m-%dT%H:%M:%SZ)

        gcloud monitoring timeseries list \
          --filter="$FILTER" \
          --interval-start-time="$START_TIME" \
          --format=json > /tmp/bq_slots.json

        PEAK_SLOTS=$(jq '[.[]?.points[]?.value.int64Value // 0] | max // 0' /tmp/bq_slots.json)

        echo "::set-output name=peak_slots::$PEAK_SLOTS::"

  - id: evaluate_slot_saturation
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.query_bigquery_slot_metrics.vars.peak_slots | int > 1800 }}"
    then:
      - id: notify_data_platform
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK') }}"
        payload: |
          {
            "channel": "#gcp-alerts",
            "attachments": [
              {
                "color": "danger",
                "title": "🚨 Google Cloud BigQuery Slot Saturation Alert",
                "text": "Project `{{ inputs.project_id }}` recorded a BigQuery peak slot allocation of *{{ outputs.query_bigquery_slot_metrics.vars.peak_slots }} slots* over the last 15 minutes! Impact: Analytical queries and ETL pipelines are experiencing queue contention, increased execution duration, and potential SLA breaches due to slot capacity exhaustion.",
                "footer": "Kestra Cloud Sentinel • Google BigQuery"
              }
            ]
          }

errors:
  - id: alert_pipeline_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {
        "channel": "#gcp-alerts",
        "attachments": [
          {
            "color": "danger",
            "title": "⚠️ Google Cloud BigQuery Slot Utilization and Reservation Saturation Sentinel Pipeline Failure",
            "text": "Failed to query Google Cloud Monitoring for BigQuery slots. Verify project ID and service account permissions.",
            "footer": "Kestra Workflow Failure Sentinel"
          }
        ]
      }

triggers:
  - id: periodic_schedule
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "*/15 * * * *"
    disabled: true