# RDB Loader for Redshift and Databricks > Load Snowplow events into Redshift, Databricks, or Snowflake with transformation and deduplication using Spark or stream transformers. > Source: https://docs.snowplow.io/docs/api-reference/loaders-storage-targets/snowplow-rdb-loader/ > Documentation index: https://docs.snowplow.io/llms.txt We use the name RDB Loader (from "relational database") for a set of applications that can be used to load Snowplow events into a data warehouse. Use these tools if you want to load into **Redshift** (including Redshift serverless), **Databricks**, or **Snowflake** (the latter not recommended). For other destinations, see [here](/docs/api-reference/loaders-storage-targets/). **Redshift:** **AWS (Batching, recommended):** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Redshift, stores them in an S3 bucket and instructs Redshift to load them. ```mermaid flowchart LR stream[["Enriched Events (Kinesis stream)"]] s3loader{{"S3 Loader"}} prebucket[("Enriched Events (S3 bucket)")] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (S3 bucket)")] subgraph "Redshift" table[("Events table")] end stream-->s3loader-->prebucket-->loader-->bucket--->Redshift ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Redshift. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to TSV Note over Transformer: Write data to the S3 bucket Transformer->>Loader: Notify the loader (via SQS) Loader->>Redshift: Send SQL commands for loading Note over Redshift: Load the data from the S3 bucket using “COPY FROM” end ``` **AWS (Micro-batching):** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Redshift, stores them in an S3 bucket and instructs Redshift to load them. ```mermaid flowchart LR stream[["Enriched Events (Kinesis stream)"]] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (S3 bucket)")] subgraph "Redshift" table[("Events table")] end stream-->loader-->bucket--->Redshift ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Redshift. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to TSV Note over Transformer: Write data to the S3 bucket Transformer->>Loader: Notify the loader (via SQS) Loader->>Redshift: Send SQL commands for loading Note over Redshift: Load the data from the S3 bucket using “COPY FROM” end ``` *** **Databricks:** > **Note: Cloud** > > The cloud selection below is for your _pipeline_. We don’t have restrictions on where Databricks itself is deployed. **AWS (Batching, recommended):** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Databricks, stores them in an S3 bucket and instructs Databricks to load them. ```mermaid flowchart LR stream[["Enriched Events (Kinesis stream)"]] s3loader{{"S3 Loader"}} prebucket[("Enriched Events (S3 bucket)")] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (S3 bucket)")] subgraph "Databricks" table[("Events table")] end stream-->s3loader-->prebucket-->loader-->bucket--->Databricks ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Databricks. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to Parquet Note over Transformer: Write data to the S3 bucket Transformer->>Loader: Notify the loader (via SQS) Loader->>Databricks: Send SQL commands for loading Note over Databricks: Load the data from the S3 bucket using “COPY FROM” end ``` **AWS (Micro-batching):** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Databricks, stores them in an S3 bucket and instructs Databricks to load them. ```mermaid flowchart LR stream[["Enriched Events (Kinesis stream)"]] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (S3 bucket)")] subgraph "Databricks" table[("Events table")] end stream-->loader-->bucket--->Databricks ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Databricks. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to Parquet Note over Transformer: Write data to the S3 bucket Transformer->>Loader: Notify the loader (via SQS) Loader->>Databricks: Send SQL commands for loading Note over Databricks: Load the data from the S3 bucket using “COPY FROM” end ``` **GCP:** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Databricks, stores them in an GCS bucket and instructs Databricks to load them. ```mermaid flowchart LR stream[["Enriched Events (Pub/Sub stream)"]] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (GCS bucket)")] subgraph "Databricks" table[("Events table")] end stream-->loader-->bucket--->Databricks ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Databricks. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to Parquet Note over Transformer: Write data to the GCS bucket Transformer->>Loader: Notify the loader (via Pub/Sub) Loader->>Databricks: Send SQL commands for loading Note over Databricks: Load the data from the GCS bucket using “COPY FROM” end ``` *** **Snowflake:** > **Tip: Streaming Loader or RDB Loader?** > > Both [Snowflake Streaming Loader](/docs/api-reference/loaders-storage-targets/snowflake-streaming-loader/) and [RDB Loader](/docs/api-reference/loaders-storage-targets/snowplow-rdb-loader/) can load data into Snowflake. > > Snowflake Streaming Loader is newer and has two advantages: > > - Much lower latency — you can get data in Snowflake in seconds, as opposed to minutes with RDB Loader > - Much lower cost — unlike with RDB Loader, there is no need for EMR and extensive Snowflake compute to load batch files > > We recommend the Streaming Loader over the RDB Loader. If you already use RDB Loader, see the [migration guide](/docs/api-reference/loaders-storage-targets/snowflake-streaming-loader/migrating/) for more information. > **Note: Cloud** > > The cloud selection below is for your _pipeline_. We don’t have restrictions on where Snowflake itself is deployed. **AWS (Batching, recommended):** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Snowflake, stores them in an S3 bucket and instructs Snowflake to load them. ```mermaid flowchart LR stream[["Enriched Events (Kinesis stream)"]] s3loader{{"S3 Loader"}} prebucket[("Enriched Events (S3 bucket)")] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (S3 bucket)")] subgraph "Snowflake" table[("Events table")] end stream-->s3loader-->prebucket-->loader-->bucket--->Snowflake ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Snowflake. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to JSON Note over Transformer: Write data to the S3 bucket Transformer->>Loader: Notify the loader (via SQS) Loader->>Snowflake: Send SQL commands for loading Note over Snowflake: Load the data from the S3 bucket using “COPY FROM” end ``` **AWS (Micro-batching):** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Snowflake, stores them in an S3 bucket and instructs Snowflake to load them. ```mermaid flowchart LR stream[["Enriched Events (Kinesis stream)"]] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (S3 bucket)")] subgraph "Snowflake" table[("Events table")] end stream-->loader-->bucket--->Snowflake ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Snowflake. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to JSON Note over Transformer: Write data to the S3 bucket Transformer->>Loader: Notify the loader (via SQS) Loader->>Snowflake: Send SQL commands for loading Note over Snowflake: Load the data from the S3 bucket using “COPY FROM” end ``` **GCP:** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Snowflake, stores them in an GCS bucket and instructs Snowflake to load them. ```mermaid flowchart LR stream[["Enriched Events (Pub/Sub stream)"]] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (GCS bucket)")] subgraph "Snowflake" table[("Events table")] end stream-->loader-->bucket--->Snowflake ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Snowflake. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to JSON Note over Transformer: Write data to the GCS bucket Transformer->>Loader: Notify the loader (via Pub/Sub) Loader->>Snowflake: Send SQL commands for loading Note over Snowflake: Load the data from the GCS bucket using “COPY FROM” end ``` **Azure:** At the high level, RDB Loader reads batches of enriched Snowplow events, converts them to the format supported by Snowflake, stores them in an Azure Blob Storage bucket and instructs Snowflake to load them. ```mermaid flowchart LR stream[["Enriched Events (Kafka stream)"]] loader{{"RDB Loader (Transformer and Loader apps)"}} bucket[("Transformed Events (Azure Blob Storage bucket)")] subgraph "Snowflake" table[("Events table")] end stream-->loader-->bucket--->Snowflake ``` RDB loader consists of two applications: Transformer and Loader. The following diagram illustrates the interaction between them and Snowflake. ```mermaid sequenceDiagram loop Note over Transformer: Read a batch of events Note over Transformer: Transform events to JSON Note over Transformer: Write data to the Azure Blob Storage bucket Transformer->>Loader: Notify the loader (via Kafka) Loader->>Snowflake: Send SQL commands for loading Note over Snowflake: Load the data from the Azure Blob Storage bucket using “COPY FROM” end ``` *** *** > **Tip: Schemas in Redshift, Snowflake and Databricks** > > For more information on how events are stored in the warehouse, check the [mapping between Snowplow schemas and the corresponding warehouse column types](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/). To run RDB Loader, you will need to run one instance of the Transformer and one instance of the Loader. ## How to pick a transformer The transformer app currently comes in two flavours: a Spark job that processes data in batches, and a long-running streaming app. The process of transforming the data is not dependent on the storage target. Which one is best for your use case depends on three factors: - cloud provider you want to use (AWS, GCP or Azure) - your expected data volume - how much importance you place on deduplicating the data before loading it into the data warehouse. ### Based on cloud provider If you want to run the transformer on AWS, you can use Spark transformer (`snowplow-transformer-batch`) or Transformer Kinesis (`snowplow-transformer-kinesis`). If you want to run the transformer on GCP, you can use Transformer Pubsub (`snowplow-transformer-pubsub`). If you want to run the transformer on Azure, you can use Transformer Kafka (`snowplow-transformer-kafka`). ### Based on expected data volume The Spark transformer (`snowplow-transformer-batch`) is the best choice for big volumes, as the work can be split across multiple workers. However, the need to run it on EMR creates some overhead that is not justified for low-volume pipelines. The stream transformer (`snowplow-transformer-kinesis`, `snowplow-transformer-pubsub` and `snowplow-transformer-kafka`) is a much leaner alternative and suggested for use with low volumes that can be comfortably processed on a single node. However, multiple stream transformers can be run parallel therefore it is possible to process big data volume with stream transformer too. To make the best choice, consider: - What is the underlying infrastructure? For example, a single-node stream transformer will perform differently based on the resources it is given by the machine it runs on. - What is the frequency for processing data? For example, even in a low-volume pipeline, if you only run the transform job once a day, the accumulated data might be enough to justify the use of Spark. ### Based on the importance of deduplication The transformer is also in charge of [deduplicating](/docs/api-reference/loaders-storage-targets/snowplow-rdb-loader/transforming-enriched-data/deduplication/) the data. Currently, only the Spark transformer can do that. If duplicates are not a concern, or if you are happy to deal with them after the data has been loaded in the warehouse, then pick a transformer based on your expected volume (see above). Otherwise, use the Spark transformer. ## How to pick a loader based on the destination There are different loader applications depending on the storage target. Currently, RDB Loader supports Redshift (AWS only), Snowflake and Databricks. For loading into **Redshift** (including Redshift serverless), use the `snowplow-rdb-loader-redshift` artifact. For loading into **Snowflake**, use the `snowplow-rdb-loader-snowflake` artifact. For loading into **Databricks**, use the `snowplow-rdb-loader-databricks` artifact. ## How `transformer` and `loader` interface with other Snowplow components and each other The applications communicate through messages. The transformer consumes enriched tsv-formatted Snowplow events from S3 (AWS) or stream (AWS, GCP and Azure). It writes its output to blob storage (S3, GCS or Azure Blob Storage). Once it's finished processing a batch of data, it issues a message with details about the run. The loader consumes a stream of these messages and uses them to determine what data needs to be loaded. It issues the necessary SQL commands to the storage target.