- Home
- Skills
- Data & Databases
- Pipeline Reliability Blueprints
Works with the AI Agents you already use
Pipeline Reliability Blueprints
Turn fragile ETL scripts into dependable batch and streaming pipelines with idempotent replay, quarantine paths, quality gates, and cost controls.
Free
Pipeline Reliability Blueprints
Example session with this skill installed
Our nightly Airflow job syncs orders from Postgres into Snowflake and it is not trustworthy: retries create duplicate rows, one bad record aborts the whole run, and dashboards show stale data. Freshness needs to stay under 15 minutes and we must replay any day safely. Using the pipeline reliability blueprints, redesign our ingestion layer with idempotent merges, watermark tracking, and a quarantine path, add dbt tests and metadata columns for lineage, and propose orchestration plus a quality and cost monitoring plan. Write the design doc and starter code into the pipeline/ directory.
- Read your context and instructions
- Compiled the pipeline reliability blueprints
Summary
Redesigned the Postgres-to-Snowflake orders pipeline around replay-safe ingestion: watermark-after-commit extracts, business-key upserts, quarantine for bad records, and explicit freshness/quality gates.
| Metric | Before | After |
|---|---|---|
| Duplicate rows on retry | ~2% of run | 0 (merge on order_id + version) |
| Bad-record blast radius | whole run aborts | row-level quarantine |
| Recovery point | manual re-extract | replay any _run_id |
| Freshness SLO | unmeasured | 15 min, watermark lag alerts |
| Silent partial success | common | contract gates + DLQ counts |
Reference blueprint applied
- sources → intake → validation → durable buffer → transformation → serving; each hop logs accepted/rejected counts
- Incremental extract with high-watermark, transactional upsert, quarantine writer (see pipeline/ingest_orders.py)
- Metadata columns: _ingested_at, _source_system, _run_id, _watermark_high, _record_status
- Airflow DAG: exponential-backoff retries, catchup=False, backfill isolated from routine schedule
- dbt staging model with row_number dedup, not_null/unique tests, freshness thresholds
Next steps
- Implement ingest_orders.py in pipeline/ and point the DAG at it
- Create the orders_quarantine table with payload + reason per rejected batch
- Wire watermark-lag and DLQ-rate alerts to your on-call channel
- Schedule daily source-to-target count reconciliation per partition
Connects securely to your tools. The creator never sees your data.
About this skill
The problem
Data pipelines often fail due to schema drift, unhandled poison records, or non-idempotent logic that creates duplicates during retries. Fragile scripts lack the observability and recovery paths needed for production reliability.
What it does
- Designs idempotent batch and streaming flows using high-watermark tracking and transactional merges.
- Implements multi-tier storage architectures with raw, conformed, and serving zones.
- Sets up dead-letter queues and quarantine paths to isolate malformed records without halting pipelines.
- Configures automated quality gates and freshness SLAs using dbt, Great Expectations, or SQL assertions.
- Builds orchestration logic for Airflow and Prefect including exponential backoff and dependency management.
Frameworks & tools
Airflow, Prefect, dbt, Spark, Delta Lake, Apache Iceberg, Kafka, Great Expectations, and SQL.
Why this beats prompting it yourself
General AI often suggests simple "append" scripts that fail in production. This skill enforces strict reliability principles like partition overwrites and transactional commits, ensuring your system handles failures gracefully without manual intervention.
Use cases
- Migrating fragile cron-based Python scripts to orchestrated Airflow DAGs.
- Building a Lakehouse architecture with Delta Lake or Iceberg for ACID compliance.
- Implementing real-time streaming ingestion with schema validation and DLQ support.
- Adding cost-aware compute scaling and storage retention policies to existing pipelines.
Known limitations
Does not provide physical infrastructure provisioning (Terraform/IaC). Requires existing connectivity to source and target systems.
Code samples are reference implementations that must be adapted to your specific connectors and warehouse; it does not cover infrastructure provisioning or tuning of Kafka, Iceberg clusters, or cloud warehouse sizing.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
4 uses
Times used to date
Free forever
No account required to browse
Trust & safety
Security scan
Security scan passed 1 month ago
- Free with an Agensi account
Permissions
File Scopes
Needs only read/write access to your pipeline, DAG, and dbt model directories to generate blueprint code and design docs; no network or live infrastructure is required.