ingesting-data

Use boto3 to download objects from S3 and polars to parse CSV in chunks, then write batches to your database with write_database. For large files over 100MB, use chunked reading with read_csv_batched to avoid memory issues.

dlt (data load tool) is recommended for Python-first ETL with automatic schema evolution and incremental loading. Meltano or Airbyte fit better when you need hundreds of pre-built connectors, and Dagster suits complex orchestration needs.

Use cursor-based pagination by tracking a cursor or updated_at timestamp and passing it as a query parameter on each request. Store the cursor after each run so subsequent loads only fetch new or updated records.

Kafka consumers achieve exactly-once semantics through idempotent processing: store processed event IDs in the database and skip duplicates within a transaction. Disable auto-commit and commit offsets manually only after successful processing.

Loading entire large files into memory causes failures on files over 100MB. Use chunked or streaming readers like polars read_csv_batched or papaparse step callbacks to process data in batches instead.

Validate column names, data types, null constraints, and uniqueness before insertion using the included validate_csv_schema.py script or Pandera schemas. Rejecting bad data at ingestion prevents corrupt records downstream.