dbt streaming tables

Welcome! I am excited to help you master dbt and Databricks Streaming Tables. Before we jump in, let's make sure this session is perfectly tailored to your current workflow.

To get us started, could you share a bit about your experience level with dbt and Databricks, and what specifically brings you to streaming tables today?

I know dbt well, but I'm new to Databricks streaming.

It's great to have you here! Since you already know inside and out, we can skip the basics of modeling and focus exactly on how Databricks handles continuous data streams under the hood.

What is a Streaming Table?

In Databricks, a Streaming Table is a managed Delta table that has native, built-in support for incremental data processing. Instead of reading an entire raw dataset and rebuilding a table from scratch every time your pipeline runs (the classic batch approach), a streaming table only processes newly arrived data since the last run. Behind the scenes, Databricks leverages its (DLT) framework to automatically manage the state, checkpoints, and schema inference of this stream. This means you get real-time data freshness without having to configure complex streaming servers, manage offset state stores, or write Scala/Python spark streaming code.

How dbt Interfaces with Streaming Tables

Normally, you might run a dbt model materialized as a table or an incremental table. But with the dbt-databricks adapter, you can use a native materialization specifically designed for this pattern: materialized='streaming_table' When you use this configuration, dbt issues a special statement directly to Databricks to spin up and register the streaming table. Every subsequent run will incrementally fetch new rows from your source files or message queues.

Show me a dbt model example using this materialization

To show you exactly how this fits together, let's look at a concrete dbt model using the streaming_table materialization.

The Code: Ingesting Raw JSON Streams

Imagine you have raw JSON flight logs landing continuously in a cloud storage bucket (like S3 or ADLS) that is mounted as a in Databricks. Here is how you can write a dbt model to dynamically stream those files directly into a managed table.

{{ 
  config(
    materialized='streaming_table'
  )
}}

SELECT 
  id AS trip_id,
  origin,
  destination,
  dep_delay,
  -- 1. Capturing the source file name
  _metadata.file_path AS source_file,
  -- 2. Capturing the exact ingestion time
  _metadata.file_modification_time AS file_timestamp
FROM 
  stream(read_files(
    '/Volumes/raw_data/flights/incoming_json',
    format => 'json'
  ))

Unpacking the Streaming Magic

Let's break down the two most critical pieces of this model that differ from standard dbt code:

The stream() Wrapper and read_files(): Instead of referencing a normal table or view, we query stream(read_files(...)). The table-valued function leverages Databricks' Auto Loader engine to automatically detect when new files land, infer their schema, and pull them in. Wrapping it in stream() tells Databricks that this is a continuous, incremental feed rather than a one-time batch read.
The _metadata Column: When you read files using read_files, Databricks implicitly attaches a virtual column named _metadata. This is incredibly valuable for auditing because it lets you extract the exact path of the file that supplied the row (using _metadata.file_path) and when that file was modified (using _metadata.file_modification_time). This happens without having to write custom file-parsing scripts.