Skip to content This library is free, offered as dana. If it helps your work, donate here →
penguin tree ai
← back to libraryDonate here

This is one of 3,622 free prompts in the penguin tree ai library. Sign up once and the whole library opens, with new prompts by email.

open the library →

Data source integration evaluator

Evaluates a new data source and recommends a complete integration approach — covering source classification, connectivity method, extraction pattern, schema and change-data-capture strategy, incremental loading, error…

download .md

Purpose

Assess any new data source and produce a concrete integration recommendation — extraction method, load pattern, change-capture strategy, error handling, and a phased build plan — so the engineer can move from "we need to ingest this" to a defensible implementation decision without reinventing the evaluation each time.

When to Use

Invoke this skill when the user asks to:

  • Decide how to ingest a new API, database, SaaS platform, file source, or event stream
  • Compare batch vs. streaming vs. CDC for a given source
  • Evaluate whether to build a custom connector or use an off-the-shelf tool (Fivetran, Airbyte, Debezium, etc.)
  • Design an incremental extraction strategy for a source with rate limits or large tables
  • Plan schema handling and drift management for a new integration
  • Estimate effort and risk before committing to a source integration

Inputs Required

Before starting, collect:

  • Source type: REST/GraphQL API, relational DB, NoSQL DB, SaaS app, flat files (S3/SFTP), or event stream (Kafka/Kinesis/PubSub)
  • Access method available: API key/OAuth, DB credentials + network path, webhook, log-based CDC access, file drop location
  • Data volume & velocity: Approximate row/record count, daily change volume, and how fresh the data must be (real-time, hourly, daily)
  • Destination: Warehouse/lake target (Snowflake, BigQuery, Redshift, Databricks, Postgres, etc.)
  • Constraints: Rate limits, API pagination limits, maintenance windows, PII/compliance requirements
  • Existing tooling: Orchestrator (Airflow, Dagster, dbt), any ingestion platform already in use
  • Team capacity: Rough sense of build-vs-buy appetite and who maintains it

If volume/velocity or freshness requirements are unknown, assume daily batch as the default and flag the assumption explicitly.

Instructions

Step 1: Classify the Source

Identify the source archetype, because it dictates the entire integration pattern:

  • Pull API (REST/GraphQL): Rate-limited, paginated, often no native change feed
  • Relational database: Supports incremental keys and/or log-based CDC
  • NoSQL / document store: Schema-flexible; extraction depends on export API or change streams
  • SaaS application: Often best served by a managed connector; APIs may be undocumented or unstable
  • Flat files (S3/SFTP): Batch by nature; watch for late-arriving and out-of-order files
  • Event stream (Kafka/Kinesis/PubSub): Streaming-native; ordering, offsets, and replay are the concerns

State which archetype this source is and the one property of that archetype that most constrains the design.

Step 2: Determine Connectivity & Auth

Assess how you actually reach the data:

  • Auth model: API key, OAuth2 (token refresh handling), service account, DB user with least-privilege read
  • Network path: Public endpoint, VPC peering, PrivateLink, SSH tunnel, static IP allowlisting
  • Reliability of interface: Documented + versioned API vs. unversioned; stable schema vs. frequently changing
  • Flag any auth pattern that requires token rotation or credential vaulting (Secrets Manager, Vault)

Step 3: Recommend Extraction Pattern

Choose and justify one primary pattern:

  • Full refresh: Small tables (<1–5M rows), no reliable change key — simplest, most robust
  • Incremental by high-water mark: updated_at / monotonic ID exists and is trustworthy — most common
  • Log-based CDC: DB with WAL/binlog access, high change volume, low-latency need — use Debezium or native connector
  • Event consumption: Source already emits events — consume with consumer groups and offset management
  • Change API / cursor: SaaS with a native "changes since cursor" endpoint

For the chosen pattern, specify the state to persist (cursor value, offset, LSN, last file timestamp) and where it lives.

Step 4: Decide Build vs. Buy

Score against three questions:

  1. Does a mature managed connector exist (Fivetran/Airbyte/Stitch) for this exact source?
  2. Is the data volume/rate within the connector's cost tolerance?
  3. Do compliance or transformation needs require custom control?

Rule of thumb: Standard SaaS + common DB → managed connector unless cost or compliance forces custom. Bespoke internal API or event stream → custom is usually warranted. State the recommendation and the single deciding factor.

Step 5: Schema & Drift Strategy

  • Schema handling: Explicit typed schema vs. schema-on-read (JSON/variant column)
  • Drift policy: Auto-add new columns, alert-on-drop, fail-on-incompatible-change — pick one and justify
  • PII/compliance: Fields requiring masking, hashing, or exclusion at ingest
  • Contracts: Whether a data contract / schema registry (e.g., Avro + registry for streams) applies

Step 6: Error Handling & Idempotency

  • Retries: Exponential backoff with jitter for transient/429 errors; cap and dead-letter after N attempts
  • Idempotency: Deduplication key and merge/upsert strategy so re-runs don't duplicate data
  • Partial-failure recovery: How to resume from the last committed cursor/offset without gaps or double-counting
  • Late/out-of-order data: Watermarking or reprocessing window for streams and file sources

Step 7: Monitoring & SLAs

Define what to watch:

  • Freshness: Time since last successful load vs. the required freshness SLA
  • Volume anomaly: Row-count deltas outside expected range trigger alerts
  • Schema drift alerts: Fired on unexpected structural changes
  • Failure alerting: Retry exhaustion and dead-letter growth State the specific metric and threshold for each, not just "add monitoring."

Step 8: Phased Implementation Plan

Produce a 3-phase rollout:

  • Phase 1 — Proof of connection: Auth, single successful pull, schema inspection
  • Phase 2 — Production extraction: Chosen pattern, incremental state, error handling, load to destination
  • Phase 3 — Hardening: Monitoring, drift policy, alerting, documentation and runbook Include a rough effort estimate (S/M/L) per phase and the top risk for each.

Output Format

  • Structure: 8 labeled sections following the steps, plus a one-line recommendation summary at the top
  • Length: 900–1,300 words
  • Format: Structured document — short prose per section with bulleted decisions; extraction-pattern and build-vs-buy rendered as clear recommendations with a stated deciding factor
  • Sections: Recommendation Summary → Source Classification → Connectivity & Auth → Extraction Pattern → Build vs. Buy → Schema & Drift → Error Handling & Idempotency → Monitoring & SLAs → Phased Plan
  • Tone: Senior data engineer in a design review — decisive, tradeoff-aware, no hedging

Quality Checklist

Before delivering output, verify:

  • [ ] The source is classified into exactly one archetype with its key constraint named
  • [ ] A single primary extraction pattern is recommended, with the persisted state artifact specified (cursor/offset/LSN/timestamp)
  • [ ] Build-vs-buy gives a clear verdict and names the one deciding factor
  • [ ] Error handling addresses idempotency with a stated dedup/merge key — not just "add retries"
  • [ ] Each monitoring item has a concrete metric and threshold
  • [ ] The phased plan has 3 phases, each with an effort estimate (S/M/L) and a named top risk

Notes

  • The most common failure mode is trusting an updated_at column that isn't reliably updated on every mutation — verify before committing to incremental-by-timestamp.
  • For SaaS sources, a managed connector is usually cheaper than the true fully-loaded cost of maintaining a custom one; only override this for compliance, cost-at-scale, or unsupported-source reasons.
  • Log-based CDC is powerful but adds operational surface area (connector, offsets, schema history) — recommend it only when change volume or latency genuinely requires it.

How to use this skill

This file is a Claude Code skill in Anthropic's standard format — markdown with YAML frontmatter at the top.

Installing in Claude Code:

  • ~/.claude/skills/<folder-name>/SKILL.md — auto-loads in every session anywhere on your machine (recommended for skills you'll use across multiple projects)
  • <your-project>/.claude/skills/<folder-name>/SKILL.md — auto-loads only when working in that project (recommended for project-specific skills)

Restart Claude Code or open a new session to register.

Using in Claude.ai, ChatGPT, Gemini, or other AI tools:

  1. Drag this file into a new chat, or copy and paste its contents
  2. Send your task or request as your next message — the AI will apply the skill where it fits

For Claude Code skill details: https://docs.anthropic.com/en/docs/claude-code/skills