de-agent-skills

Airflow-orchestrated Iceberg lakehouse ETL pipelines — Bronze/Silver/Gold layer DAGs, Iceberg maintenance jobs (optimize/expire_snapshots/remove_orphan_files) as Airflow tasks, snapshot expiration scheduling, compaction DAG patterns, partition-by-partition backfill with dynamic task mapping, late-arriving data handling, watermark tracking table, post-load data quality gates (row count/freshness/null rate checks via TrinoHook), full medallion pipeline DAG example

Trino warehouse cost optimization — query scan cost analysis (system.runtime.queries/system.runtime.tasks), identifying expensive queries by CPU time and data scanned, scan reduction via partition pruning and file compaction, worker autoscaling patterns (scale-to-zero for batch), spot instance strategies for workers, S3 object storage cost (storage vs request costs), Iceberg compaction economics (fewer splits = fewer S3 GET requests), cost attribution by team/user, materialized view break-even analysis, result caching

Full local Trino Lakehouse Docker Compose stack — trinodb/trino coordinator+worker, Hive Metastore 3.x with PostgreSQL backend, MinIO object storage (S3-compatible), Apache Airflow, dbt-trino, Apache Superset BI, Prometheus+Grafana monitoring, healthchecks, startup ordering, persistent volumes, isolated networks, catalog property files, Iceberg catalog config, environment variable injection, Trino CLI access

Trino federated query architecture across heterogeneous sources — cross-catalog JOIN patterns (Iceberg+PostgreSQL+MySQL+Kafka+ClickHouse), pushdown behavior per connector type, minimizing cross-catalog data movement, materializing JDBC data into Iceberg, query routing strategy, connector-specific limitations (JDBC serial fetch, Kafka read-once), performance cost model for federated joins, metadata caching, CREATE TABLE AS SELECT federation patterns, catalog isolation design

Trino data file layout optimization for Iceberg — Parquet vs ORC file format selection, target file size tuning (iceberg.target-max-file-size), row group size, Parquet/ORC column encoding choices, Bloom filter indexes, sorted_by for min/max skipping, small file detection via $files metadata table, OPTIMIZE compaction strategies, partition design impact on file count, Z-order equivalent via sorted_by, split sizing and parallelism (iceberg.minimum-assigned-split-weight), write parallelism tuning

Trino Modern Data Stack end-to-end reference architecture — full docker-compose (Kafka + MinIO + Hive Metastore + Trino + Airflow + dbt + Superset + Prometheus + Grafana), medallion lake DDL and pipeline DAGs, Kafka→Iceberg streaming ingest, dbt project layout with Trino profiles, Superset dataset config, Prometheus alert rules, production Kubernetes Helm values, operational runbook for daily maintenance

Trino observability and monitoring platform — JMX Prometheus exporter configuration (running queries/failed queries/OOM kills/execution latency P50/P90/P99/memory pool metrics), Grafana dashboard panels, OpenTelemetry trace propagation, query-level event listener for structured logging, Prometheus alert rules (worker loss/queue depth/OOM/failure rate/p99 latency), log aggregation patterns, query history analysis via REST API, slow query detection SQL

Trino production readiness checklist and review — coordinator HA (active/passive with load balancer), worker autoscaling, TLS/HTTPS configuration, authentication setup, resource groups for multi-tenancy, JVM sizing, spill configuration, fault-tolerant execution for batch workloads, monitoring (Prometheus/alerting), graceful shutdown, backup strategy for Hive Metastore, catalog security, query history retention, log rotation, Kubernetes deployment checklist

Trino security hardening and data governance — TLS/HTTPS setup, internal-communication shared secret, authentication (LDAP/OAuth2/JWT), file-based access control (catalog/schema/table/column rules, column masking, row-level filtering, query kill permissions), OPA integration, Ranger for dynamic row-filter and column-mask, catalog isolation by domain, user group mapping, impersonation rules, audit logging via event listener, password file authentication for development

Autonomous self-healing Trino platform — Python watchdog agents for automatic worker restart detection, hung query killer, OOM-killed query resubmission, Iceberg compaction auto-trigger on small-file detection, stale statistics auto-ANALYZE, cluster memory pressure relief (kill low-priority queries), anomaly detection on query latency (Z-score), Claude-based RCA generation for incidents, Prometheus AlertManager webhook integration, Airflow self-healing sensor

Trino cluster health monitoring and administration — coordinator/worker health checks, REST API status endpoints (/v1/info /v1/node /v1/query /v1/cluster), JMX MBean metrics (running queries/failed queries/OOM kills/memory pool), Prometheus JMX exporter config, Grafana dashboards, query queue depth monitoring, worker node stability, memory pressure detection, BLOCKED query diagnosis, Web UI interpretation, log analysis, graceful shutdown

Airflow orchestration for Trino pipelines — TrinoOperator and TrinoHook usage, trino_conn_id connection setup, idempotent DAG patterns (INSERT INTO / MERGE with deterministic run_ids), partition-aware scheduling with Airflow logical_date, sensor patterns (TrinoCheckOperator), SLA monitoring, retry configuration for Trino queries, incremental load orchestration, dbt + Trino Airflow integration (BashOperator/DbtRunOperator), metadata-driven DAG generation

Production dbt + Trino platform — dbt-trino adapter profiles.yml (ldap/kerberos/oauth/jwt/certificate auth), all materializations (table/view/incremental/materialized_view/ephemeral), incremental strategies (append/merge/delete+insert), Iceberg table_properties in config blocks (format/partitioning/sorted_by/location), on_schema_change, snapshots with TIMESTAMP(6), slim CI with state:modified+ and --defer, dbt project structure (staging/intermediate/mart), ANALYZE post-hook, session_properties in profiles, multi-thread parallelism

Optimizing dbt-generated SQL performance on Trino — ephemeral vs view vs table materialization trade-offs, CTE explosion patterns, partition-aware incremental filters (bounded watermarks), MERGE vs delete+insert strategy selection, avoiding full-refresh anti-patterns, query hints in dbt SQL (BROADCAST hint), dbt thread tuning, session_properties for dbt runs, ANALYZE post-hooks, incremental model design patterns for Iceberg, avoiding small file proliferation from frequent incremental runs

Trino EXPLAIN and EXPLAIN ANALYZE plan reading — DISTRIBUTED/LOGICAL/IO/VALIDATE formats, fragment types (SINGLE/HASH/ROUND_ROBIN/BROADCAST/SOURCE), exchange node analysis, stage bottleneck detection, data skew identification via task row-count variance, spill detection, operator-level metrics (ScanFilterProject/HashJoin/Aggregation/LocalExchange), cost estimate vs actual row disparity, slow plan patterns and fixes

Production Apache Iceberg best practices with Trino — hidden partitioning (day/month/year/hour/bucket/truncate transforms), partition evolution, sorted tables, file compaction (optimize/expire_snapshots/remove_orphan_files), snapshot management, metadata tables ($snapshots/$files/$partitions/$history), schema evolution, time travel queries, MERGE/UPDATE/DELETE DML, ANALYZE for CBO, Iceberg catalog types (HMS/Glue/REST/Nessie), small file prevention, Bloom filters

Trino-based Modern Data Platform architecture design — decoupled storage/compute, Iceberg as open table format, Hive Metastore/Glue catalog, MinIO/S3 object storage, Kafka ingestion layer, dbt transformation, Airflow orchestration, Superset BI — coordinator/worker topology, catalog design, metadata-driven architecture, multi-layer lakehouse (Bronze/Silver/Gold), federated query across heterogeneous sources

Trino memory management and spill-to-disk tuning — query.max-memory/query.max-total-memory/query.max-memory-per-node properties, memory.heap-headroom-per-node, JVM heap sizing (80% of RAM), spill-enabled/spiller-spill-path/spill-compression-codec configuration, exchange buffer tuning, OOM kill diagnosis, memory pool sizing, fault-tolerant execution exchange manager (S3/filesystem), session-level memory overrides, memory-intensive operator patterns (HashJoin/Sort/Window/GroupBy)

Trino distributed SQL query optimization — predicate/projection/aggregation pushdown, join reordering (AUTOMATIC/ELIMINATE_CROSS_JOINS), broadcast vs partitioned joins, dynamic filtering, CBO with ANALYZE, filter-early patterns, partition pruning, avoiding SELECT *, reducing shuffle, cross-catalog query cost, session property tuning, query hints, anti-patterns for slow Trino queries

Trino resource group workload governance — resource-groups.properties configuration (maxQueued/hardConcurrencyLimit/softMemoryLimit/hardCpuLimit/schedulingPolicy/schedulingWeight), hierarchical group trees, selector rules (user/source/queryType/clientTags/regex), multi-tenant tenant isolation patterns, per-user dynamic sub-groups (${USER} template), scheduling policies (fair/weighted_fair/weighted/query_priority), CPU quota periods, database-backed configuration (MySQL/PostgreSQL), JMX monitoring of group utilization

AIOps autonomous incident response agent — LLM-driven diagnosis loop (Claude tool-use agent with kubectl/SQL/Prometheus tools), alert-to-action pipeline (PagerDuty webhook → agent trigger), automated RCA generation (failure taxonomy + log correlation), self-healing action executor with approval gate, incident severity classification, runbook automation (structured YAML runbooks executed by agent), escalation logic, incident timeline auto-generation, Slack integration for human-in-the-loop approvals

AIOps capacity planning agent — predictive resource forecasting (Prophet/linear regression on Prometheus metrics), Kubernetes HPA/VPA right-sizing recommendations, node capacity headroom calculator, Kafka partition and broker capacity model, Spark executor sizing from job history, data lake storage growth forecast (S3/GCS), cluster autoscaler simulation, automatic HPA threshold tuning, cost-per-job attribution, capacity planning report generator

AIOps infrastructure anomaly detection — statistical baseline (z-score/IQR/seasonal decomposition), Prometheus anomaly rules (predict_linear/stddev_over_time/MAD), ML-based detection (Isolation Forest/Prophet/LSTM autoencoder on metrics), metric correlation clustering, automated alert threshold tuning from historical data, Kubernetes resource anomaly detection, data pipeline anomaly signals (row count z-score/freshness drift/throughput drop), Python sklearn/Prophet integration, Grafana anomaly panel

AIOps observability copilot — natural language to PromQL/LogQL translation, alert explanation in plain English (what fired/why/impact), Grafana dashboard auto-generation from service topology, anomaly narrative generation from metric patterns, on-call context enrichment (related alerts/recent deploys/runbook links), log pattern clustering for noise reduction, SLO status explanation, automated weekly observability health digest

AIOps continuous platform optimization agent — autonomous optimization loop (observe→analyze→recommend→apply), Kubernetes resource rightsizing (VPA recommendation reader + apply), Kafka consumer lag auto-scale, Spark job configuration tuning from history, idle resource detection (unused deployments/PVCs/topics), compaction and VACUUM scheduling, auto-ANALYZE stale statistics, cost savings attribution, optimization audit trail, human approval gate for high-risk changes

AIOps autonomous query cost analyzer — Trino system.runtime.query_history cost SQL (CPU time/peak memory/bytes scanned), ClickHouse system.query_log top expensive queries, Spark UI cost attribution by user/job/DAG, BigQuery INFORMATION_SCHEMA.JOBS cost analysis, LLM-driven query rewrite recommendations (missing partition filters/broadcast hints/aggregation pushdown), cost anomaly detection (user over daily budget), automated query tagging, chargeback report by team/project

Disaster recovery review for data platforms — RTO/RPO definitions, DR strategy selection (cold/warm/hot standby), Airflow metadata DB backup and restore (pg_dump/WAL), Kafka topic replication (MirrorMaker2), data lake replication (S3 CRR/GCS Transfer), Kubernetes cluster backup (Velero), runbook for DR failover, DR testing (game day exercises), backup validation, cross-region Terraform, recovery time measurement

DataOps blameless postmortem generation — severity matrix (SEV1-4), postmortem Markdown template with timeline/impact/root cause/action items, 3 filled-in examples (Kafka rebalance/dbt incremental data loss/ClickHouse schema mutation), CAPA framework (Corrective/Action/Preventive/Action), impact quantification SQL, stakeholder communication templates, 60-min facilitation guide, postmortem review checklist, Git-based postmortem repository management

Root cause analysis for DataOps incidents — 5-Why analysis for pipeline failures, failure taxonomy (infrastructure/data/logic/dependency/config/concurrency), Airflow diagnosis (task state SQL/scheduler heartbeat/DagBag errors), Spark diagnosis (OOM/skew/FetchFailed/serialization), Kafka consumer lag spike RCA, data quality anomaly investigation (volume/freshness/distribution shift), log correlation across components, timeline reconstruction, impact quantification SQL

Self-healing data platform — auto-restart failed Airflow DAGs (idempotency required), Kafka consumer group auto-resume after partition rebalance, circuit breaker for flapping pipelines, automatic partition backfill on gap detection, auto-scale Kubernetes resources on OOM detection, stale statistics auto-ANALYZE, DQ gate auto-quarantine pattern, dead letter queue reprocessing, watchdog agents for heartbeat monitoring, self-healing Airflow sensor (RoutineLoadLagSensor pattern)

Data platform SLA monitoring — freshness SLAs (data available by X:XX), completeness SLAs (row count variance < N%), latency SLAs (pipeline completes within N minutes), SLA breach detection (Prometheus/Soda/SQL), SLA burn rate alerting, SLA reporting dashboards, SLA miss root cause linking, cascading SLA dependencies (upstream delay propagation), SLA definition process, error budget tracking, consumer notification on SLA breach

Alert fatigue reduction — SLO-based alerting (burn rate vs threshold), multi-window burn rate alerts (short/long window), AlertManager inhibition and silencing, alert deduplication (group_by), routing by severity (PagerDuty for critical / Slack for warning), alert ownership labels, runbook links in annotations, alert review process (weekly noise audit), flapping detection (for/pending period tuning), absent() vs rate() alerting patterns, ticket-based escalation for warning-level noise

AWS data platform review — S3 data lake (lifecycle/replication/encryption/access points), EMR vs Glue ETL trade-offs, MSK Kafka configuration, RDS/Aurora for metadata, Redshift vs Athena for analytics, MWAA (managed Airflow), EKS for containerized pipelines, IAM roles (IRSA for EKS/EMREC2 instance profiles), Lake Formation row/column security, AWS Glue catalog, VPC data platform networking, cost optimization (S3 Intelligent-Tiering/Spot instances)

Azure data platform review — ADLS Gen2 (hierarchical namespace/RBAC/lifecycle), Azure Synapse vs Databricks vs HDInsight trade-offs, Event Hubs (Kafka protocol compatible), ADF vs Azure Databricks for ETL, Azure Managed Airflow, AKS with Workload Identity (AAD Pod Identity), Key Vault for secrets, Azure Monitor/Log Analytics, Purview for data catalog/lineage, Private Endpoints for data services, cost management (reserved instances/spot VMs)

Compliance readiness for data platforms — SOC2 Type II controls mapping (CC6/CC7/CC8), GDPR data processing requirements (PII inventory/right-to-erasure/data residency), PCI-DSS data platform scope reduction, CIS Benchmarks (Kubernetes/AWS/GCP), audit log completeness (Vault/Kubernetes/cloud trail), data retention and deletion policies, encryption at rest/transit evidence, access review processes, compliance-as-code (OPA policies), vulnerability management (CVE SLA)

GCP data platform review — BigQuery (dataset IAM/column-level security/partitioned tables/clustering/reservations), GCS data lake (uniform bucket-level access/lifecycle/CMEK), Dataproc vs Dataflow vs Spark on GKE, Pub/Sub streaming, Cloud Composer (managed Airflow), Workload Identity for GKE, VPC Service Controls, Data Catalog, Dataplex for data governance, BigQuery cost optimization (slot reservations vs on-demand), Cloud Logging/Monitoring

Grafana dashboard review — panel types (timeseries/stat/gauge/table/heatmap), variable templating (datasource/label_values/query), dashboard linking and drilldown, data platform dashboards (pipeline overview/DAG health/Kafka throughput/Spark performance), alerting from panels, annotation markers for deployments, dashboard-as-code (Grafonnet/Terraform grafana provider), performance optimization (query caching/recording rules), dark/light theme standards

Kafka cost optimization — tiered storage (S3/GCS remote log offload, local.retention.ms vs retention.ms), topic retention right-sizing (per-topic audit SQL), log compaction economics, partition count right-sizing (over-partitioned cluster detection), consumer group idle cleanup, broker instance right-sizing (disk vs compute), Redpanda vs Confluent Cloud cost comparison, MirrorMaker2 cross-region cost, compression savings calculator

Kafka production platform review — broker configuration (replication factor/min.insync.replicas/rack awareness/KRaft mode), topic design (partition count formula/compaction/retention), consumer group management (lag monitoring/rebalance tuning/cooperative sticky), producer tuning (acks/idempotence/compression/batching), security (SASL_SSL/ACLs/mTLS), JMX metrics to Prometheus (kafka-exporter/JMX exporter), alert rules (under-replicated partitions/ISR shrink/consumer lag/disk), capacity planning, Strimzi Kubernetes operator

Multi-cloud governance — cloud-agnostic data platform patterns, federated identity (OIDC/SAML between AWS/GCP/Azure), Terraform multi-cloud modules, cross-cloud data replication (S3↔GCS/Azure), unified cost management (FinOps Foundation framework), cloud-agnostic observability (OpenTelemetry), policy enforcement (OPA Gatekeeper across clouds), disaster recovery cross-cloud, vendor lock-in avoidance (open formats Iceberg/Parquet), centralized secrets management (HashiCorp Vault)