What should I use for schema-drifting APIs and incremental loads?

For schema-drifting APIs and incremental loads, the best fit is usually a schema-on-read ingest pattern with raw landing + transformation layers, rather than trying to force a rigid schema at ingestion.

Recommended approach

1. Land the API payloads raw

   • Store each response as-is in a bronze/raw table or object store.
   • Keep metadata like:
     • ingestion timestamp
     • source endpoint
     • request params / cursor
     • record id
     • API version if available

2. Use semi-structured storage

   • Prefer formats like:
     • JSON for raw capture
     • Parquet/Delta/Iceberg/Hudi after normalization
   • These handle new or missing fields more gracefully than fixed relational tables.

3. Normalize in a downstream layer

   • Parse and flatten into silver/curated tables.
   • Use schema evolution features where possible:
     • add new columns automatically
     • preserve unknown fields in a payload / attributes column
   • For breaking changes, keep versioned transformations.

4. Track incremental state explicitly

   • Use one of:
     • updated_at / modified_since
     • cursor-based pagination
     • high-water mark / watermark
     • change tokens / sync tokens
   • Persist the last successful cursor/watermark per endpoint.

Good technologies

Depending on your stack:

• Delta Lake / Apache Iceberg / Apache Hudi
  • Great if you want schema evolution + incremental merges
• dbt + warehouse staging tables
  • Good for transformation and evolving models
• Airbyte / Fivetran / Meltano
  • Useful for ingestion, especially if connectors already exist
• Kafka / event streams
  • Better if the API can be polled and events are appended continuously

Practical pattern

A strong default is:

• Raw JSON landing
• Schema-evolving table format like Delta/Iceberg/Hudi
• MERGE/UPSERT by primary key
• Watermark/cursor-based incremental ingestion

If you want the simplest answer

Use:

• raw JSON in a landing zone
• Delta Lake or Iceberg for evolving tables
• cursor/high-water-mark tracking for incremental loads

If you want, I can recommend the best option for your specific stack (e.g. Snowflake, Databricks, BigQuery, Postgres, S3 + Spark, etc.).