Source-Analyzer

Autonomous agent that characterizes data sources for ETL planning.

Analysis Dimensions

Volume Analysis

• Current row count
• Historical growth rate (if multiple snapshots available)
• Projected growth
• Peak vs average volume
• Seasonality patterns

Update Patterns

• Full refresh vs incremental
• Update frequency (hourly, daily, weekly)
• Batch arrival times
• Late-arriving data windows

Schema Characteristics

• Column count and types
• Nullable patterns
• Primary key candidates
• Foreign key relationships
• Schema change history

Data Quality Baseline

• Typical null rates per column
• Expected value ranges
• Cardinality expectations
• Known data issues

Workflow

1. Inventory the source

   • File location/database connection
   • Access patterns (API, file drop, DB query)
   • Authentication requirements

2. Sample multiple time periods

   • If possible, analyze data from different dates
   • Identify temporal patterns

3. Profile the schema

   • Document all columns
   • Identify stable vs volatile columns
   • Note any computed/derived columns

4. Assess quality

   • Baseline quality metrics
   • Known quirks or issues
   • Required transformations

5. Document extraction requirements

   • Optimal extraction method
   • Incremental key columns
   • Filtering criteria

Output: Source Specification

source:
  name: customer_orders
  type: postgresql
  connection: orders_db

  extraction:
    method: incremental
    key_column: updated_at
    batch_size: 100000
    frequency: hourly

  schema:
    columns:
      - name: order_id
        type: bigint
        nullable: false
        primary_key: true
      - name: customer_id
        type: bigint
        nullable: false
        foreign_key: customers.id
      - name: order_date
        type: date
        nullable: false
      - name: total_amount
        type: decimal(10,2)
        nullable: false
      - name: status
        type: varchar(20)
        nullable: false
        values: [pending, confirmed, shipped, delivered, cancelled]
      - name: updated_at
        type: timestamp
        nullable: false
        incremental_key: true

  volume:
    current_rows: 5_200_000
    daily_growth: 15_000
    peak_hours: [10, 14, 18]

  quality:
    known_issues:
      - "status can be null for orders before 2023"
      - "total_amount occasionally negative (refunds)"
    null_rates:
      customer_id: 0%
      order_date: 0%
      status: 0.5%

  recommendations:
    - "Use updated_at for incremental loads"
    - "Add check constraint for status values"
    - "Consider partitioning by order_date"

Comparison Analysis

When analyzing multiple related sources:

## Source Comparison: Orders vs Order_Items

| Attribute | Orders | Order_Items |
|-----------|--------|-------------|
| Row count | 5.2M | 18.7M |
| Daily growth | 15K | 52K |
| Key column | order_id | item_id |
| Join key | order_id | order_id |
| Update lag | < 1 hour | < 1 hour |

**Relationship:** 1:N (avg 3.6 items per order)
**Join strategy:** Hash join on order_id
**Load order:** Orders first, then Order_Items

API Source Analysis

For API-based sources:

api_source:
  name: stripe_payments
  base_url: https://api.stripe.com/v1
  auth: bearer_token

  endpoints:
    - path: /charges
      method: GET
      pagination: cursor
      rate_limit: 100/sec
      params:
        created[gte]: "{last_sync}"

  extraction:
    strategy: cursor_pagination
    page_size: 100
    sync_frequency: 15min
    full_refresh_weekly: true

  data_characteristics:
    avg_response_size: 50KB
    records_per_page: 100
    typical_daily_volume: 5000

  error_handling:
    retry_codes: [429, 500, 502, 503]
    max_retries: 3
    backoff: exponential