dbt Adapter

The dbt-Rockset adapter brings real-time analytics to dbt. Using the adapter, you can load data into Rockset and create Collections, by writing SQL SELECT statements in dbt. These collections can then be built on top of each other to support highly-complex data transformations with many dependency edges.

The following subsections describe the adapter's installation procedure and support for dbt:

• Installation and Set up
• Supported Materializations
• Real-Time Streaming ELT Using dbt + Rockset
• Persistent Materializations Using dbt + Rockset

📘

See the following blogs for additional information on dbt:

• Real-Time Analytics with dbt + Rockset
• Real-Time Data Transformations with dbt + Rockset.

Installation and Set up

The following subsections describe how to set up and use the adapter.

📘

See the adapter's GitHub repo for additional information on setting up the adapter.

Step 1: Install the Plug-in

Open a command-line window and run the following command to install the adapter:

pip3 install dbt-rockset

Step 2: Configure your Profile

Configure a dbt profile similar to the example shown below, to connect with your Rockset account. Enter any Workspace that you’d like your dbt collections to be created in, and any Rockset API key. The database field is required by dbt but unused in Rockset.

rockset:
  outputs:
    dev:
      type: rockset
      threads: 1
      database: N/A
      workspace: <rockset_workspace_name>
      api_key: <rockset_api_key>
      api_server: <rockset_api_server> # Optional, default is `api.usw2a1.rockset.com`, the api server for region Oregon.
      vi_rrn: <rockset_virtual_instance_rrn> # Optional, the VI to use for IIS queries
      run_async_iis: <async_iis_queries> # Optional, by default false, whether use async execution for IIS queries
  target: dev

Update your dbt project to use this Rockset dbt profile. You can switch profiles in your project by editing the dbt_project.yml file.

Supported Materializations

The dbt-Rockset adapter supports all four core dbt materializations:

• View Materializations: dbt models are rebuilt as Views in Rockset on each run
• Table Materializations: dbt models are rebuilt as collections in Rockset on each run
• Incremental Materializations: dbt inserts or updates records into Rockset collections since the last time that dbt was run
• Ephemeral Materializations: dbt interpolates Rockset SQL code from models as common table expressions

Real-Time Streaming ELT Using dbt + Rockset

As data is ingested, Rockset performs the following:

• The data is automatically indexed in at least three different ways using Rockset’s Converged Index™ technology.
• Your write-time data transformations are performed.
• The data is made available for queries within seconds.

When you execute queries on that data, Rockset leverages those indexes to complete any read-time data transformations you define using dbt, with sub-second latency.

Write-Time Data Transformations Using Rollups and Ingest Transformation

Rockset can extract and load semi-structured data from multiple sources in real-time.
For high-velocity data (e.g. data streams), you can roll it up at write-time. For example, when you have streaming data coming in from Kafka or Kinesis, you can create a Rockset collection for each data stream, and then set up Rollups to perform transformations and aggregations on the data as it is written into Rockset. This can help to:

• Reduce the size of large scale data streams
• De-duplicate data
• Partition your data

Collections can also be created from other data sources including:

• Data lakes (e.g., S3 or GCS)
• NoSQL databases (e.g., DynamoDB or MongoDB)
• Relational databases (e.g., PostgreSQL or MySQL)

You can then use Rocket’s Ingest Transformation to transform the data using SQL statements as it is written into Rockset.

Read-Time Data Transformations Using Rockset Views

The adapter can set up data transformations as SQL statements in dbt, using View Materializations that can be performed during read-time.

To set this up:

1. Create a dbt model using SQL statements for each transformation you want to perform on your data.
2. Execute dbt run. dbt will automatically create a Rockset View for each dbt model, which performs all the data transformations when queries are executed.

If queries complete within your latency requirements, then you have achieved the gold standard of real-time data transformations: Real-Time Streaming ELT.

Your data will be automatically kept up-to-date in real-time, and reflected in your queries. There is no need for periodic batch updates to “refresh” your data. You will not need to execute dbt run again after the initial set up, unless you want to make changes to the actual data transformation logic (e.g. adding or updating dbt models).

Persistent Materializations Using dbt + Rockset

If write-time transformations and views don't meet your application’s latency requirements (or your data transformations become too complex), you can persist them as Rockset collections.

Rockset requires queries to complete in under two minutes to cater to real-time use cases, which may affect you if your read-time transformations are too complicated. This requires a batch ELT workflow to manually execute dbt run each time you want to update your data transformations. You can frequently run dbt, to keep your transformed data up-to-date in near real-time.

Persistent materializations are both faster to query and better at handling query concurrency, as they are materialized as collections in Rockset. Since the bulk of the data transformations have already been performed ahead of time, your queries will complete significantly faster because you can minimize the complexity necessary during read-time.

There are two persistent materializations available in dbt:

• Incremental Models
• Table Models

Materializing dbt Incremental Models in Rockset

Incremental Models enable you to insert or update documents into a Rockset collection since the last time dbt was run. This can significantly reduce the build time since Rockset only needs to perform transformations on the new data that was just generated, rather than dropping, recreating, and performing transformations on the entire data set.

Depending on the complexity of your data transformations, incremental materializations may not always be a viable option to meet your transformation requirements. Incremental materializations are best suited for event or time-series data streamed directly into Rockset. To tell dbt which documents it should perform transformations on during an incremental run, provide SQL that filters for these documents using the is_incremental() macro in your dbt code. You can learn more about configuring incremental models in dbt here.

Materializing dbt Table Models in Rockset

A Table Model is a transformation that drops and recreates an entire Rockset collection with the execution of dbt. It updates that collection's transformed data with the most up-to-date source data. This is the simplest way to persist transformed data in Rockset, and results in much faster queries since the transformations are completed prior to query time.

However, Table Models can be slow to complete, since Rockset is not optimized for creating entirely new collections from scratch on the fly. This may significantly increase your data latency, as it may take several minutes for Rockset to provision resources for a new collection and then populate it with transformed data.

Putting It All Together

You can use Table Models and Incremental Models (in conjunction with Rockset Views), to customize the perfect stack to meet the unique requirements of your data transformations. For example, you can use SQL-based rollups to:

• Transform your streaming data during write-time.
• Transform and persist them into Rockset collections via Incremental or Table Models.
• Execute a sequence of view models during read-time to transform your data again.