{"id":803,"date":"2025-08-23T15:29:18","date_gmt":"2025-08-23T15:29:18","guid":{"rendered":"https:\/\/dataopsschool.com\/blog\/?p=803"},"modified":"2025-09-01T15:43:15","modified_gmt":"2025-09-01T15:43:15","slug":"databricks-databricks-auto-loader-tutorial","status":"publish","type":"post","link":"https:\/\/dataopsschool.com\/blog\/databricks-databricks-auto-loader-tutorial\/","title":{"rendered":"Databricks: Databricks Auto Loader Tutorial"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n
\n\n\n\n

\ud83d\ude80 Databricks Auto Loader Tutorial<\/h1>\n\n\n\n
\"\"<\/figure>\n\n\n\n

(with Schema Evolution Modes & File Detection Modes)<\/strong><\/p>\n\n\n\n

Auto Loader in Databricks is the recommended way to ingest files incrementally and reliably<\/strong> into the Lakehouse. This tutorial covers:<\/p>\n\n\n\n

    \n
  • What Auto Loader is and when to use it<\/li>\n\n\n\n
  • File detection modes (Directory Listing vs File Notification)<\/li>\n\n\n\n
  • Schema handling (Schema Location, Schema Hints)<\/li>\n\n\n\n
  • Schema evolution modes (addNewColumns, rescue, none, failOnNewColumns)<\/li>\n\n\n\n
  • Exactly-once processing & checkpoints<\/li>\n\n\n\n
  • Full working examples<\/li>\n<\/ul>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    \n\n\n\n

    1\ufe0f\u20e3 Introduction to Auto Loader<\/h2>\n\n\n\n

    Auto Loader is a Databricks feature that:<\/p>\n\n\n\n

      \n
    • Incrementally ingests new files<\/strong> from cloud storage (ADLS, S3, GCS, or DBFS).<\/li>\n\n\n\n
    • Works with structured streaming<\/strong> via the cloudFiles<\/code> source.<\/li>\n\n\n\n
    • Supports both batch and streaming<\/strong> ingestion.<\/li>\n\n\n\n
    • Guarantees exactly-once delivery<\/strong> (no duplicate loads).<\/li>\n\n\n\n
    • Can scale to millions of files per hour<\/strong>.<\/li>\n<\/ul>\n\n\n\n

      \ud83d\udc49 Compared to COPY INTO<\/code>, which is retriable and idempotent, Auto Loader is designed for large-scale continuous ingestion<\/strong>.<\/p>\n\n\n\n

      \n\n\n\n

      2\ufe0f\u20e3 File Detection Modes in Auto Loader<\/h2>\n\n\n\n

      Auto Loader uses two file detection modes<\/strong> to track new files:<\/p>\n\n\n\n

      \ud83d\udd39 a) Directory Listing (Default)<\/h3>\n\n\n\n
        \n
      • Uses cloud storage list API<\/strong> calls.<\/li>\n\n\n\n
      • Tracks processed files in the checkpoint (RocksDB<\/code>).<\/li>\n\n\n\n
      • Works out-of-the-box<\/strong>.<\/li>\n\n\n\n
      • Best for low-to-medium ingestion<\/strong> volumes.<\/li>\n<\/ul>\n\n\n\n
        df = (spark.readStream\n      .format(\"cloudFiles\")\n      .option(\"cloudFiles.format\", \"csv\")\n      .load(\"dbfs:\/mnt\/landing\/year=*\/month=*\/day=*\"))\n<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        \ud83d\udd39 b) File Notification<\/h3>\n\n\n\n
          \n
        • Uses event services<\/strong> (Azure Event Grid + Queue, AWS S3 + SQS, GCP Pub\/Sub).<\/li>\n\n\n\n
        • Requires elevated cloud permissions<\/strong> to create these services.<\/li>\n\n\n\n
        • Efficient for very large ingestion pipelines<\/strong>.<\/li>\n<\/ul>\n\n\n\n
          df = (spark.readStream\n      .format(\"cloudFiles\")\n      .option(\"cloudFiles.format\", \"csv\")\n      .option(\"cloudFiles.useNotifications\", \"true\")\n      .load(\"s3:\/\/mybucket\/landing\"))\n<\/code><\/pre>\n\n\n\n
          \ud83d\udccc Tip<\/strong>: Start with Directory Listing \u2192 move to File Notification at scale.<\/p>\n\n\n\n
          \n\n\n\n
          3\ufe0f\u20e3 Using Auto Loader in Databricks<\/h2>\n\n\n\n
          Example: Reading from Nested Folder Structure<\/h3>\n\n\n\n
          Suppose files are stored as:<\/p>\n\n\n\n
          \/landing\/year=2024\/month=08\/day=30\/file.csv\n<\/code><\/pre>\n\n\n\n
          We can read them with wildcards:<\/p>\n\n\n\n
          df = (spark.readStream\n      .format(\"cloudFiles\")\n      .option(\"cloudFiles.format\", \"csv\")\n      .option(\"pathGlobFilter\", \"*.csv\")\n      .option(\"header\", \"true\")\n      .option(\"cloudFiles.schemaLocation\", \"dbfs:\/mnt\/checkpoints\/schema1\")\n      .load(\"dbfs:\/mnt\/landing\/year=*\/month=*\/day=*\"))\n<\/code><\/pre>\n\n\n\n
          Key Options:<\/p>\n\n\n\n
            \n
          • cloudFiles.format<\/code> \u2192 input format (csv<\/code>, json<\/code>, parquet<\/code>).<\/li>\n\n\n\n
          • cloudFiles.schemaLocation<\/code> \u2192 path to store schema metadata<\/strong>.<\/li>\n\n\n\n
          • pathGlobFilter<\/code> \u2192 filter file extensions.<\/li>\n\n\n\n
          • header<\/code> \u2192 handle CSV headers.<\/li>\n<\/ul>\n\n\n\n
            \n\n\n\n
            4\ufe0f\u20e3 Schema Location in Auto Loader<\/h2>\n\n\n\n
            Auto Loader requires a schema location<\/strong>:<\/p>\n\n\n\n
              \n
            • Stores schema evolution metadata.<\/li>\n\n\n\n
            • Ensures consistency across multiple runs.<\/li>\n\n\n\n
            • Lives in the checkpoint directory<\/strong>.<\/li>\n<\/ul>\n\n\n\n
              .option(\"cloudFiles.schemaLocation\", \"dbfs:\/mnt\/checkpoints\/autoloader\/schema\")\n<\/code><\/pre>\n\n\n\n
              Inside this folder:<\/p>\n\n\n\n
                \n
              • _schemas\/<\/code> \u2192 schema history<\/li>\n\n\n\n
              • rocksdb\/<\/code> \u2192 file tracking for exactly-once<\/li>\n<\/ul>\n\n\n\n
                \n\n\n\n
                5\ufe0f\u20e3 Schema Hints in Auto Loader<\/h2>\n\n\n\n
                Instead of defining the entire schema<\/strong>, you can hint only specific columns.<\/p>\n\n\n\n
                df = (spark.readStream\n      .format(\"cloudFiles\")\n      .option(\"cloudFiles.format\", \"csv\")\n      .option(\"cloudFiles.schemaHints\", \"Quantity INT, UnitPrice DOUBLE\")\n      .load(\"dbfs:\/mnt\/landing\"))\n<\/code><\/pre>\n\n\n\n
                This:<\/p>\n\n\n\n
                  \n
                • Infers other columns automatically.<\/li>\n\n\n\n
                • Forces Quantity<\/code> as integer, UnitPrice<\/code> as double.<\/li>\n<\/ul>\n\n\n\n
                  \u2705 Useful when schema is evolving but certain columns should remain strongly typed.<\/p>\n\n\n\n
                  \n\n\n\n
                  6\ufe0f\u20e3 Writing with Auto Loader<\/h2>\n\n\n\n
                  Write DataFrame into a Delta Table<\/strong> with checkpoints for exactly-once.<\/p>\n\n\n\n
                  (df.withColumn(\"file_name\", F.input_file_name())  # track source file\n   .writeStream\n   .option(\"checkpointLocation\", \"dbfs:\/mnt\/checkpoints\/autoloader\/run1\")\n   .outputMode(\"append\")\n   .trigger(availableNow=True)   # batch-like run\n   .toTable(\"dev.bronze.sales_data\"))\n<\/code><\/pre>\n\n\n\n
                    \n
                  • checkpointLocation<\/code> \u2192 prevents reprocessing of old files.<\/li>\n\n\n\n
                  • trigger(availableNow=True)<\/code> \u2192 processes once in batch style.<\/li>\n\n\n\n
                  • toTable()<\/code> \u2192 saves to a Delta table.<\/li>\n<\/ul>\n\n\n\n
                    \n\n\n\n
                    7\ufe0f\u20e3 Schema Evolution in Auto Loader<\/h2>\n\n\n\n
                    When new columns appear in source files, Auto Loader handles it via schema evolution modes<\/strong>:<\/p>\n\n\n\n
                    \n\n\n\n
                    \ud83d\udd39 a) addNewColumns<\/code> (Default)<\/h3>\n\n\n\n
                      \n
                    • If new columns are detected:\n
                        \n
                      • Stream fails once<\/strong>, updates schema in schemaLocation.<\/li>\n\n\n\n
                      • Rerun \u2192 succeeds, new columns appear in the table.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
                        .option(\"cloudFiles.schemaEvolutionMode\", \"addNewColumns\")\n<\/code><\/pre>\n\n\n\n
                        \n\n\n\n
                        \ud83d\udd39 b) rescue<\/code><\/h3>\n\n\n\n
                          \n
                        • New columns \u2192 pushed into a special column _rescued_data<\/code>.<\/li>\n\n\n\n
                        • Stream does not fail.<\/li>\n\n\n\n
                        • Useful when schema changes are frequent.<\/li>\n<\/ul>\n\n\n\n
                          .option(\"cloudFiles.schemaEvolutionMode\", \"rescue\")\n<\/code><\/pre>\n\n\n\n
                          Example Output:<\/strong><\/p>\n\n\n\n
                          InvoiceNo<\/th>Quantity<\/th>_rescued_data<\/th><\/tr><\/thead>
                          12345<\/td>10<\/td>{“State”: “CA”}<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                          \n\n\n\n
                          \ud83d\udd39 c) none<\/code><\/h3>\n\n\n\n
                            \n
                          • Ignores new columns completely.<\/li>\n\n\n\n
                          • No schema updates, no _rescued_data<\/code>.<\/li>\n<\/ul>\n\n\n\n
                            .option(\"cloudFiles.schemaEvolutionMode\", \"none\")\n<\/code><\/pre>\n\n\n\n
                            \n\n\n\n
                            \ud83d\udd39 d) failOnNewColumns<\/code><\/h3>\n\n\n\n
                              \n
                            • If new columns appear \u2192 stream fails.<\/li>\n\n\n\n
                            • Requires manual schema update.<\/li>\n<\/ul>\n\n\n\n
                              .option(\"cloudFiles.schemaEvolutionMode\", \"failOnNewColumns\")\n<\/code><\/pre>\n\n\n\n
                              \n\n\n\n
                              8\ufe0f\u20e3 Incremental Ingestion & Exactly Once<\/h2>\n\n\n\n
                                \n
                              • Processed files are tracked in RocksDB<\/strong> inside checkpoint.<\/li>\n\n\n\n
                              • Already-processed files are not re-ingested<\/strong>.<\/li>\n\n\n\n
                              • New files only \u2192 incremental load<\/strong>.<\/li>\n<\/ul>\n\n\n\n
                                \u2705 This ensures idempotent and exactly-once ingestion<\/strong>.<\/p>\n\n\n\n
                                \n\n\n\n
                                9\ufe0f\u20e3 File Notification Mode (Advanced)<\/h2>\n\n\n\n
                                Enable with:<\/p>\n\n\n\n
                                .option(\"cloudFiles.useNotifications\", \"true\")\n<\/code><\/pre>\n\n\n\n
                                This:<\/p>\n\n\n\n
                                  \n
                                • Creates event-based triggers in your cloud account.<\/li>\n\n\n\n
                                • Requires permissions to provision Event Grid (Azure), SQS (AWS), Pub\/Sub (GCP).<\/li>\n\n\n\n
                                • Best for large-scale ingestion<\/strong> with low-latency.<\/li>\n<\/ul>\n\n\n\n
                                  \n\n\n\n
                                  \ud83d\udd1f Putting It All Together \u2014 Example Pipeline<\/h2>\n\n\n\n
                                  df = (spark.readStream\n      .format(\"cloudFiles\")\n      .option(\"cloudFiles.format\", \"csv\")\n      .option(\"cloudFiles.schemaLocation\", \"dbfs:\/mnt\/checkpoints\/schema\")\n      .option(\"cloudFiles.schemaHints\", \"Quantity INT, UnitPrice DOUBLE\")\n      .option(\"cloudFiles.schemaEvolutionMode\", \"rescue\")\n      .load(\"dbfs:\/mnt\/landing\/year=*\/month=*\/day=*\"))\n\n(df.writeStream\n   .option(\"checkpointLocation\", \"dbfs:\/mnt\/checkpoints\/autoloader\/full_pipeline\")\n   .outputMode(\"append\")\n   .trigger(availableNow=True)\n   .toTable(\"dev.bronze.sales_data\"))\n<\/code><\/pre>\n\n\n\n
                                  \n\n\n\n
                                  \u2705 Summary<\/h1>\n\n\n\n
                                    \n
                                  • Auto Loader<\/strong> is the preferred way to ingest files into Databricks Lakehouse.<\/li>\n\n\n\n
                                  • File Detection Modes<\/strong>:\n
                                      \n
                                    • Directory Listing (default).<\/li>\n\n\n\n
                                    • File Notification (event-driven, needs cloud perms).<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                    • Schema Handling<\/strong>:\n
                                        \n
                                      • Schema Location \u2192 track schema history.<\/li>\n\n\n\n
                                      • Schema Hints \u2192 enforce types for specific columns.<\/li>\n\n\n\n
                                      • Schema Evolution Modes \u2192 handle new columns gracefully:\n
                                          \n
                                        • addNewColumns (default)<\/li>\n\n\n\n
                                        • rescue<\/li>\n\n\n\n
                                        • none<\/li>\n\n\n\n
                                        • failOnNewColumns<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                        • Checkpoints<\/strong> ensure exactly-once ingestion.<\/li>\n\n\n\n
                                        • Use availableNow<\/code> trigger for batch-like runs, or streaming triggers for continuous pipelines.<\/li>\n<\/ul>\n\n\n\n
                                          \n\n\n\n
                                          <\/p>\n","protected":false},"excerpt":{"rendered":"
                                          \ud83d\ude80 Databricks Auto Loader Tutorial (with Schema Evolution Modes & File Detection Modes) Auto Loader in Databricks is the recommended way to ingest files incrementally and reliably… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-803","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/803","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=803"}],"version-history":[{"count":5,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/803\/revisions"}],"predecessor-version":[{"id":828,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/803\/revisions\/828"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=803"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}