Data Ingestion and Loading
Two ingestion paths
Auto Loader (spark.readStream.format('cloudFiles')) incrementally discovers new files using directory listing (cheap at small scale) or file notification via SNS/SQS or Event Grid (efficient at large scale). It infers and stores schema in a checkpoint, and evolves it via cloudFiles.schemaEvolutionMode (addNewColumns default, rescue, failOnNewColumns, none) plus schemaHints to pin types. A rescuedDataColumn retains rows that do not match the schema instead of dropping them. Auto Loader is append-only: it never updates or deletes rows.
COPY INTO is SQL-based and idempotent - it tracks which files were already loaded, making reruns safe. It works well for thousands of files but struggles with millions of files and with frequent schema drift.
Loading patterns
Bronze is a raw append; silver uses MERGE INTO for upserts and CDC; gold holds aggregates. MERGE fails when the source has more than one row matching a target row - deduplicate with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) first. JDBC reads parallelize with numPartitions, partitionColumn, lowerBound, upperBound.
Traps
- Assuming COPY INTO re-reads files each run; it skips already-loaded files unless forced.
- Assuming Auto Loader handles updates or deletes.
- Forgetting that the checkpoint and schema location must be a UC volume the job identity can write to.
Rule of thumb
Growing, continuously arriving, schema-drifting files → Auto Loader with the availableNow trigger. A bounded one-time load of a few thousand files → COPY INTO.