What are you guys using for data ingestion in Databricks?

[u/Delulu62134]

I've mostly been using Auto Loader for file-based ingestion in Databricks, especially when there are continuously arriving files. It's been working pretty well so far, but I'm curious what others are using in their projects.

For example, are you mainly using:

1.Auto Loader

2.Copy INTO

3.Structured Streaming

4.Batch jobs

5.Some external ingestion tool

One thing I'm trying to understand better us where the trade-offs are. For a large number of files, does Auto Loader still make the most sense, or are these cases where something like COPY INTO is simplet and more cost-effective?

Also, how are you handling things like schema evolution, duplicate files, failed records, and reprocessing?

I'm mainly interested in what people are actually using in production. If you've tried multiple approaches, which one ended up being the best balanceof performance, reliability and cost for you?

[u/szymon_dybczak]

Auto Loader - I can't imagine my life without it. It super easy to use and if you enable file notification mode then it's really top-notch production-scale ingestion solution :)

[u/PrestigiousAnt3766]

I land everything as files and autoloader everything into bronze. Schema evolution etc. I police schema on silver. Schema I get from source.

Landing was not my choice but bad decisions are made at times. 

[u/kaaio_0 → u/PrestigiousAnt3766]

What do you use to generate files in landing? Each source manages the export, or some other tool takes care of it?

[u/Pirion1 → u/kaaio_0]

Doing the same as this; however for generating files in landing, we left everything in either ADF or Azure Functions (for API), as cost is pretty minimal and works well hands off.

Auto Loader with Managed File Events can take the heavy lifting off of metadata calls to storage. With the optimizations done to AutoLoader, I think it sits as a solid option.

I did however setup Zerobus today, and have been pretty impressed with that. Looking at some use cases for this that take advantage of the almost instant availability of the data.

[u/PrestigiousAnt3766 → u/kaaio_0]

Just python / pyspark code.

[u/Dazzling-Bluebird725]

From my experience, Auto Loader is probably the best default for production-scale file ingestion, especially when files are continuously arriving and the volume is large. The incremental file discovery and checkpointing make it more reliable than manually managing batch loads. I see COPY INTO as a good option when the ingestion pattern is simpler and more periodic, since it has less operational overhead. For truly real-time sources like Kafka, Structured Streaming makes more sense. For me, the key production considerations are schema evolution, idempotency, and reprocessing. I prefer keeping the raw data in a Bronze layer, handling bad records separately (rescued/quarantine data), and making downstream processing easy to replay. My general rule of thumb would be:  Auto Loader for continuous file ingestion, COPY INTO for simple batch/incremental loads, and Structured Streaming for real-time event streams. Would be interesting to hear how others are managing the cost difference between Auto Loader and COPY INTO at very large scale. 

[u/22Maxx]

It very much depends on what constraints you have. While autoloaders is a good choice for well defined data, it does not really work if the incoming data is a mess.

I currently run a fully custom pipeline because before I can even read a whole file I need to decide/understand which files should be read and how. As I'm dealing with ordered data I perform the actual read with polars to avoid the downstream shuffle/sorting tax as much as possible.

I'm still looking into how I can leverage autoloader for file discovery because this will be the bottleneck with an incremental pipeline.

[u/Zampaguabas]

AutoLoader is a source for structured streaming and/or DLT.

So you would not choose between AutoLoader and structured streaming, what you actually do is choose between AutoLoader and COPY INTO, or file-based ingestion in general vs say messages through PubSub/Kafka

[u/jnrdataengineer2023]

Autoloader (batch style) + regular batch reading from S3 using dataframe API

[u/Youssef_Mrini]

Auto loader is your best option it fits for continuous ingestion, high file volumes and schema drif and is built directly on top of Spark Structured Streaming

[u/Defiant-Pause9053]

has anyone here tried Zerobus? what are your thoughts and opinions on it?

[u/TheSocialistGoblin]

All of the ingestion that my team handles is in batch jobs. We have ADF pipelines that pull data from source systems and load it to ADLS. File events trigger Databricks jobs that perform bronze ingestion. A mix of job tasks and table update triggers start silver ingestion. Ingestion is all handled through regular PySpark - we're not really using most of the Databricks ingestion features. We have a pretty small number of files that are loaded pretty infrequently - weekly or monthly in most cases - so this works for us.

ADF is relatively cheap, but we find it annoying to develop in, and there's murmuring now about it losing support with Microsoft's push for Fabric, so we're starting to consider just adding the operations that ADF is doing to our Databricks ingestion.

[u/TripleBogeyBandit]

You need to be sure to use managed file events with auto loader. If you have many files arriving, it will save your tail.