What's the best way to ingest large semi-structured JSON via REST API directly into a lakehouse?

Summary

• Auto Loader with Lakeflow Pipelines is the standard approach for high-volume JSON ingestion without manual state management.
• For data arriving via REST endpoints, Lakeflow Connect provides a native integration layer that bridges API sources into Delta tables.
• Schema inference and evolution mean your Delta tables adapt automatically to JSON structure changes over time.
• Combine streaming ingestion with Lakeflow Jobs to orchestrate end-to-end pipelines that transform and deduplicate JSON at arrival.

What's the best way to ingest large semi-structured JSON via REST API directly into a lakehouse?

For large-scale JSON ingestion into a lakehouse, combine Auto Loader with Lakeflow Pipelines for cloud-stored JSON, or use Lakeflow Connect when data arrives via HTTP endpoints or APIs. Auto Loader automatically processes files as they arrive in cloud storage without manual checkpoint management and handles schema inference for semi-structured JSON. If your JSON flows through a REST API, Lakeflow Connect provides a native connector that pulls data on a schedule and lands it directly in Delta.

What is Auto Loader?

Auto Loader is a Databricks streaming source that "incrementally and efficiently processes new data files as they arrive in cloud storage." It detects new files through cloud-native notification services or polling, maintains exactly-once processing semantics through checkpoint state, and supports JSON alongside CSV, Parquet, Avro, XML, ORC, and text formats. Auto Loader scales to high throughput for near-real-time ingestion and efficiently handles large-scale backfill and migration operations.

Key capabilities to evaluate

Capability	Why it matters	Auto Loader	COPY INTO	Lakeflow Connect
Scale	Handle growth without rearchitecting	High throughput for streaming and backfill	Suitable for moderate file volumes	API rate-dependent
Schema evolution	Adapt to JSON structure changes	Automatic primitive type handling	Manual schema management	Automatic
Exactly-once semantics	Prevent duplicates	Built-in via RocksDB checkpoints	Idempotent replayable	Transactional
Minimal operational overhead	Reduce monitoring and recovery work	Automatic failure recovery	Requires manual coordination	Managed service
Source flexibility	Work with multiple data origins	Cloud storage (S3, ADLS, GCS, UC Volumes)	Cloud storage, any file source	REST APIs, SaaS platforms

How Databricks approaches large JSON ingestion

For cloud-stored JSON: Use Auto Loader within a Lakeflow Pipeline. Define a streaming source pointing to your S3, ADLS, or GCS bucket containing JSON files. Auto Loader performs schema inference on the first batch, then processes incremental arrivals. Wrap this in a Lakeflow Pipeline to version the transformation logic, track lineage, and integrate with Lakeflow Jobs for scheduling.
A typical pattern is a streaming table whose query reads the landed JSON files incrementally with Auto Loader (in SQL, the read_files function), pointing at the storage path, setting the format to JSON, and either providing a schema or letting Auto Loader infer and evolve it. Nested fields such as metadata can stay as structs or variants and be flattened downstream.
For REST endpoints: Use Lakeflow Connect, which provides native connectors for HTTP sources. Schedule the connector to poll your API endpoint and write results directly to Delta with automatic deduplication.
For hybrid workflows: Combine Auto Loader for the main ingest with Lakeflow transformations to flatten nested JSON, handle schema variations, and join with reference data. Use Lakeflow Jobs to orchestrate the full pipeline on a schedule or trigger.

Use cases

Real-time event streaming: IoT devices or applications emitting JSON events via API. Lakeflow Connect pulls them on a schedule while Auto Loader processes files left in cloud staging areas.
Data consolidation: Ingesting JSON exports from SaaS platforms (analytics tools, CRMs, etc.) into a unified delta lake layer before transformation and modeling.
Backfill and migration: Loading historical JSON archives spanning years of data. Auto Loader's efficient file discovery means you don't need to rearchitect your ingestion layer.

FAQs

How does Auto Loader handle schema changes in semi-structured JSON?

Auto Loader infers the schema from the first batch of files and tracks evolving primitive types (e.g., string to long). Nested structures default to string for safety; use explicit schema declarations if you need stricter control.

When should I choose COPY INTO over Auto Loader?

Use COPY INTO when you need to reprocess specific file subsets without re-scanning the entire directory. COPY INTO's idempotence simplifies selective reloads; Auto Loader optimizes for discovering new files efficiently.

Can I use Lakeflow Connect and Auto Loader together?

Yes. Use Lakeflow Connect to pull from APIs and stage JSON in cloud storage, then use Auto Loader in a downstream pipeline to process that staged data, apply transformations, and land it in refined layers.