Pipeline Observability and Monitoring AI Prompt

Design an observability framework for this cloud data pipeline.

Cloud provider: {{provider}}
Orchestrator: {{orchestrator}} (Airflow, Prefect, Dagster, dbt Cloud)
Pipeline count: {{pipeline_count}}
SLA requirements: {{sla}}

1. What to monitor:

   Pipeline health:
   - Success/failure rate per DAG/job over time
   - Duration trend: is a job getting slower? (may indicate data volume growth or a query regression)
   - Retry rate: high retries indicate flaky upstream dependencies

   Data freshness:
   - Time since last successful run per table
   - SLA breach: alert if a critical table has not been updated within {{sla}} hours

   Data quality:
   - Test failure rate per dbt model
   - Row count anomalies: significant drop or spike vs rolling average

   Infrastructure:
   - Cloud service quotas: Airflow task concurrency, Snowflake credit consumption
   - Storage growth: S3/GCS bucket size trends

2. Observability stack:
   - Airflow: built-in metrics via StatsD → Prometheus → Grafana
   - dbt: elementary package → data observability dashboard
   - Cloud-native: AWS CloudWatch / GCP Cloud Monitoring / Azure Monitor for infrastructure
   - Data catalog: Dataplex / Purview / Atlan for data lineage and freshness

3. Alerting design:
   - Alert on pipeline failure: Slack + PagerDuty (for SLA-critical pipelines)
   - Alert on SLA breach (job did not complete on time): escalate based on tier
   - Alert on data quality failure: Slack with affected model, failure reason, and link to dbt docs
   - Avoid alert fatigue: start with few high-signal alerts; add gradually

4. Lineage tracking:
   - Column-level lineage: which source columns feed each output column
   - Tools: dbt + Elementary (column-level lineage), DataHub, Atlan, OpenLineage
   - OpenLineage standard: emit lineage events from Airflow/Spark/dbt → centralize in Marquez or DataHub

5. Runbook for common failures:
   - Source freshness failure: check source system → check connector logs → retry
   - dbt test failure: run `dbt test --select <model>` in dev → investigate SQL → fix upstream
   - Airflow DAG stuck: check Airflow scheduler logs → check DB connections → manually clear task

Return: monitoring metric definitions, alerting configuration, lineage tooling recommendation, and runbook templates.