3.3 Batch & Streaming Connectors

3.3 Batch & Streaming Connectors

Databricks operates as the central hub of the modern lakehouse architecture, necessitating robust connectivity to a vast array of external data sources. Data engineers frequently need to ingest data from high-throughput streaming message buses, traditional Relational Database Management Systems (RDBMS), NoSQL data stores, and secure cloud object storage. Understanding the nuances, performance tuning options, distributed processing concepts, and security considerations for these batch and streaming connectors is essential for the Databricks Certified Data Engineer Professional exam, as well as for designing enterprise-grade pipelines.

Ingesting from Streaming Message Buses (Kafka, Event Hubs, Kinesis)

Message buses like Apache Kafka, Azure Event Hubs, and AWS Kinesis are the backbone of real-time event-driven architectures. They decouple data producers from consumers, providing a highly scalable, fault-tolerant buffer. Databricks Structured Streaming provides native, optimized connectors for these systems.

Apache Kafka and Azure Event Hubs Configuration

Azure Event Hubs provides a Kafka-compatible endpoint, meaning the standard Spark Kafka connector can be seamlessly used for both, simplifying multi-cloud deployments. When configuring a Kafka read stream in Databricks, several crucial options govern the pipeline's behavior, offset management, and fault tolerance:

1. startingOffsets: This dictates where the stream should begin reading if no prior checkpoint exists.
   • "earliest" processes all available historical data currently retained in the topic. This is vital for backfills or initial bootstrapping.
   • "latest" processes only new data that arrives after the stream is explicitly started.
   • You can also provide specific JSON configurations to dictate exact topic and partition offsets for granular control.
2. failOnDataLoss: In production environments, Kafka topics have strict retention policies (e.g., 7 days or 500GB). If a Databricks streaming job is offline longer than the retention period, or if a topic is accidentally purged, offsets are deleted.
   • Setting failOnDataLoss = "true" (the default) causes the stream to intentionally crash. This acts as a circuit breaker, alerting engineers to the data gap so they can investigate.
   • Setting failOnDataLoss = "false" forces the stream to skip the missing offsets and gracefully resume from the earliest available data. While this accepts the data loss, it ensures the pipeline keeps moving and downstream systems continue receiving fresh data.

# Streaming from Kafka with specific options
kafka_df = (spark.readStream
  .format("kafka")
  .option("kafka.bootstrap.servers", "broker1:9092,broker2:9092")
  .option("subscribe", "clickstream_topic")
  .option("startingOffsets", "earliest")
  .option("failOnDataLoss", "false")
  .load())

AWS Kinesis Integration

The Kinesis connector operates similarly but interacts with Kinesis streams and shards instead of topics and partitions. Careful consideration must be given to the number of shards, as each shard typically maps directly to a Spark task. Scaling Kinesis throughput often requires splitting shards at the AWS level and ensuring the Databricks cluster has adequate cores (executors) to process them in parallel. Rate limiting can be controlled using options like maxFetchDuration and maxFetchRecordsPerShard to prevent overwhelming the cluster during traffic spikes.

RDBMS Ingestion via JDBC: Distributed Reads

Ingesting historical, snapshot, or dimension data from relational databases (like PostgreSQL, MySQL, SQL Server, or Oracle) is achieved via the JDBC connector. A common pitfall for junior data engineers is executing a naive JDBC read operation using spark.read.jdbc(). By default, this executes sequentially on a single Spark core, regardless of whether the cluster has 10 or 1000 cores. This is disastrously slow for large tables, places massive strain on the source database's single thread, and can easily cause OutOfMemory errors on the driver node.

To unlock Spark's true distributed processing capabilities, engineers must leverage specific JDBC options to partition the read operation across multiple executors concurrently:

• numPartitions: The maximum number of concurrent database connections and resulting Spark partitions. This should be tuned based on the cluster size and the source database's connection limit capacity.
• partitionColumn: A numeric, date, or timestamp column used to distribute the data. It must be evenly distributed (high cardinality, low skew) to prevent data skew in Spark. Primary keys or auto-incrementing IDs are usually the best choice.
• lowerBound and upperBound: The minimum and maximum values of the partitionColumn. These are used mathematically to determine partition strides, not to filter the data.

Spark calculates the stride length (the size of each partition) using the formula: (upperBound - lowerBound) / numPartitions. It then generates concurrent SELECT queries with WHERE clauses for each stride. If the data is highly skewed (e.g., millions of records in one month and only thousands in others), some tasks will take significantly longer, creating a bottleneck.

Additionally, the fetchsize parameter determines how many rows are fetched per network round-trip. Increasing fetchsize reduces network latency for large tables by fetching data in larger batches, but requires more executor memory to hold the batch.

# Distributed JDBC Read Example
jdbc_df = (spark.read
  .format("jdbc")
  .option("url", "jdbc:postgresql://db.example.com/mydb")
  .option("dbtable", "massive_transactions")
  .option("user", db_user)
  .option("password", db_password)
  .option("partitionColumn", "transaction_id")
  .option("numPartitions", 100)
  .option("lowerBound", 1)
  .option("upperBound", 100000000)
  .option("fetchsize", 10000)
  .load())

Secure Cloud Storage and Unity Catalog

Connecting securely to cloud object storage (AWS S3, Azure ADLS Gen2, GCP GCS) has evolved significantly with the introduction of Unity Catalog. In older architectures, engineers relied on cluster-level IAM roles, instance profiles, or hardcoded access keys in notebooks, which posed massive security risks and auditing nightmares. Unity Catalog centralizes and secures this process using Storage Credentials and External Locations.

• Storage Credentials: These encapsulate the long-term cloud IAM roles or service principals required to access storage. They abstract the cloud-specific security mechanisms away from the users, centralizing credential rotation and management.
• External Locations: These pair a specific cloud storage path (e.g., s3://my-bucket/landing-zone) with a specific Storage Credential.

Once an External Location is defined by an administrator, data engineers can be granted standard SQL privileges (e.g., GRANT READ FILES ON EXTERNAL LOCATION my_location TO data_engineers). This allows engineers to query raw files or define external tables directly using SQL, without ever needing direct IAM access to the underlying cloud environment. This separation of duties is a core pillar of modern data governance.

Furthermore, Unity Catalog strongly distinguishes between Managed Tables (where UC manages the underlying file lifecycle, directory structure, and deletion) and External Tables (where the data remains in the explicitly defined External Location, and dropping the table only removes the metadata, leaving the files intact). Understanding when to use managed vs. external tables is critical for data lifecycle management and integrating with external systems that also need to read the raw files.

Comprehensive Integration Scenario

A global data engineering team needs to ingest a massive 5-terabyte historical transactions table from an on-premises Oracle database, followed immediately by a transition to real-time CDC via a Kafka topic.

First, they configure a distributed JDBC batch read, carefully analyzing the Oracle table to find a uniformly distributed numeric sequence ID. They utilize numPartitions=200, partitionColumn='sequence_id', and query the database to set accurate lowerBound/upperBound values. They also tune the fetchsize to minimize network overhead. This splits the massive read into 200 parallel queries, completing the historical load into a Delta table in minutes instead of hours.

Immediately following the successful batch load, they initiate a Structured Streaming job reading from the Kafka CDC topic. By explicitly setting startingOffsets="latest", the stream seamlessly picks up new transactions occurring after the historical load. They leverage Unity Catalog to ensure the resulting Delta table is securely governed and accessible to downstream analysts without exposing the underlying S3 bucket. This architecture provides a unified, high-throughput, and securely governed ingestion pipeline capable of handling both massive historical backfills and low-latency real-time updates.