{"id":21875,"date":"2025-01-13T11:49:26","date_gmt":"2025-01-13T17:49:26","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=21875"},"modified":"2025-01-13T11:49:28","modified_gmt":"2025-01-13T17:49:28","slug":"near-real-time-processing-using-dbt-and-databricks","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/near-real-time-processing-using-dbt-and-databricks\/","title":{"rendered":"Near Real-Time Processing Using DBT and Databricks"},"content":{"rendered":"\n

Yes, DBT (Data Build Tool)<\/strong> can work with streaming data when using Databricks<\/strong>, but with certain considerations and limitations. Here\u2019s how it works and what to keep in mind:<\/p>\n\n\n\n

\n\n\n\n

1. Databricks and Streaming Data<\/strong><\/h3>\n\n\n\n

Databricks supports processing streaming data using Structured Streaming<\/strong>, which is built on Apache Spark. Databricks handles real-time data ingestion, transformations, and analytics through its native streaming capabilities.<\/p>\n\n\n\n

2. DBT’s Role in the Process<\/strong><\/h3>\n\n\n\n

DBT is primarily designed for batch transformations<\/strong> in a SQL-based analytics workflow. It uses SQL to model data in a warehouse or a lakehouse, such as the Databricks Lakehouse Platform<\/strong>. While DBT itself does not natively support streaming transformations (since it\u2019s oriented around batch processing), you can use it to:<\/p>\n\n\n\n

  • Define batch transformations<\/strong> that consume data from a streaming source that has been written into a Delta table.<\/li>
  • Work with Delta Lake<\/strong>, which supports both streaming and batch queries.<\/li><\/ul>\n\n\n\n

    3. Workflow for Streaming with <\/strong> DBT and Databricks<\/strong><\/h3>\n\n\n\n

    A typical approach to using DBT with streaming data in Databricks involves:<\/p>\n\n\n\n

    1. Streaming Ingestion to Delta Lake:<\/strong>
      • Use Databricks Structured Streaming to ingest real-time data into Delta Lake tables<\/strong>.<\/li>
      • These Delta tables act as both the source and sink for streaming data. <\/li><\/ul><\/li>
      • Batch Processing with DBT:<\/strong>
        • Define DBT models to perform batch transformations on the Delta tables.<\/li>
        • DBT can process data that has been ingested into Delta tables in micro-batches.<\/li>
        • You can define incremental models in DBT to process only the new data (e.g., is_incremental<\/code> logic). <\/li><\/ul><\/li>
        • Querying Delta Tables with Stream-Aware Logic:<\/strong>
          • Delta tables allow the mixing of batch and streaming queries. DBT can read the current state of Delta tables after each micro-batch is complete. <\/li><\/ul><\/li>
          • Materializing Output:<\/strong>
            • Use DBT to materialize tables\/views or write back to Delta tables for downstream batch processing. <\/li><\/ul><\/li><\/ol>\n\n\n\n

              4. Limitations<\/strong><\/h3>\n\n\n\n
              • No Native Streaming in DBT:<\/strong> DBT doesn\u2019t have built-in support for real-time streaming processing (e.g., handling continuous data ingestion).<\/li>
              • Latency Considerations:<\/strong> DBT can work with Delta tables in near-real-time scenarios (via incremental models), but it isn\u2019t suitable for low-latency, real-time requirements.<\/li>
              • Triggering Jobs:<\/strong> Streaming jobs are typically triggered and managed in Databricks, while dbt transformations are triggered as batch jobs.<\/li><\/ul>\n\n\n\n

                5. Example Workflow<\/strong><\/h3>\n\n\n\n
                1. Streaming Job in Databricks:<\/strong> from pyspark.sql import SparkSession spark = SparkSession.builder.getOrCreate() <\/code>

                  # Read from a streaming source (e.g., Kafka) streaming_df = spark.readStream.format(\"kafka\") \\ .option(\"kafka.bootstrap.servers\", \"server:9092\") \\ .option(\"subscribe\", \"topic\") \\ .load() <\/code>

                  # Write to a Delta table streaming_df.writeStream \\ .format(\"delta\") \\ .option(\"checkpointLocation\", \"\/delta\/checkpoints\/streaming\") \\ .table(\"streaming_table\") <\/code> <\/li>
                2. DBT<\/strong> Model for Incremental Processing:<\/strong> -- models\/transform_streaming_data.sql {{ config(materialized='incremental') }} <\/code>

                  WITH new_data AS ( SELECT * FROM {{ source('schema', 'streaming_table') }} WHERE _ingest_timestamp > ( SELECT MAX(_ingest_timestamp) FROM {{ this }} ) )<\/code>
                  SELECT *, CURRENT_TIMESTAMP AS processed_at FROM new_data <\/code> <\/li>
                3. Schedule dbt Jobs: Use a scheduler (e.g., dbt Cloud, Airflow, or Databricks Workflows) to trigger dbt models after streaming jobs written to Delta tables. <\/li><\/ol>\n\n\n\n
                  \n\n\n\n

                  6. When to Use <\/strong> DBT with Streaming Data<\/strong><\/h3>\n\n\n\n
                  • Use dbt for transformations on streaming data<\/strong> that has already been ingested into Delta tables.<\/li>
                  • Use Databricks streaming jobs<\/strong> for continuous real-time processing and writing to Delta tables.<\/li>
                  • Combine both tools when you need the flexibility of SQL transformations (DBT) alongside the scalability of Spark-based streaming (Databricks).<\/li><\/ul>\n\n\n\n

                    If you need low-latency real-time transformations<\/strong>, consider tools like Apache Flink<\/strong> or Spark Structured Streaming directly. DBT and Databricks are an excellent combination for\u00a0batch t<\/strong><\/span>ransformations with near-real-time data.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

                    Yes, DBT (Data Build Tool) can work with streaming data when using Databricks, but with certain considerations and limitations. Here\u2019s how it works and what to keep in mind: 1. Databricks and Streaming Data Databricks supports processing streaming data using Structured Streaming, which is built on Apache Spark. Databricks handles real-time data ingestion, transformations, and […]<\/p>\n","protected":false},"author":2,"featured_media":21478,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-21875","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-data-warehouse"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2024\/12\/databricks_logor_stacked_rgb_1200px-2830717254.png","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/21875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=21875"}],"version-history":[{"count":2,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/21875\/revisions"}],"predecessor-version":[{"id":21877,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/21875\/revisions\/21877"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/21478"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=21875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=21875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=21875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}