# Documentation

Rubicon is a modern streaming platform that enables developers to build complex data pipelines using SQL. Rubicon run continuous transformations on the changes in the data from your data sources and maintains a materialized view in the destination of your choice completely eliminating the complexity of running scheduled jobs and providing real-time updates for use cases like search, customer facing dashboards and AI driven features like recommendations.

## Step 1: Connect Source

On the **Sources** page, select your data source from our supported connectors list. Fill out the setup form using the instructions embedded in the dashboard, then click **Save & test** to create your connector. Check here for the [list](/sources) of supported sources.

## Step 2: Connect Destination

On the **Destination** page, select your destination from our supported destinations list. Fill out the setup form using the instructions embedded in the dashboard, then click **Save & test** to connect your destination. Check here for the [list](/destinations) of supported destinations.

## **Step 3:** Install Github App on Rubicon

All your transformations are written in the form of SQL statements in your Github repository. Follow these [instructions](/documentation/connecting-github-app-to-rubicon) to connect Rubicon App to your Github repo - This allows us to start new version of pipelines automatically when you modify your transformations.

##

## Step 4: Wait for the initial sync to complete

If your source is an OLTP database, Rubicon has to do an initial sync before the data is available in Rubicon to build pipelines. The initial sync can take a few hours depending on the size of your data source. You will be notified by email when your sources are synced and ready to be used in your first transformation.  You can find instructions [here](#step-6-write-your-transformation) on how to write your first transformation here using the demo source and destination already available to you while you wait for the data to sync.

## Step 5: Write your transformation

On the **Models** tab, write your transformation as a SQL statement and submit it. See the documentation [here](/transformations) for details on how to write transformation.

## Step 6: Build a pipeline

Go to [https://app.rubicon.io/](https://app.rubicon.io) and select the Git branch you want to deploy the pipeline in on the top right-hand side.  The main branch is considered production and supports continuous transformation. Other environments are considered development and support running transformation on a bounded amount of data.

## Step 7: Invite users (optional)

Click on your icon at the right and invite users to the platform. They will be able to join your team 


# Core Concepts

All data in Rubicon is treated as tables and schemas. Rubicon does not require you to define the schema and instead infers the schema from the source itself.  When you connect a source to Rubicon, it starts reading the data in a streaming fashion and makes it available to process on Rubicon platform. You can then write transformations in SQL and build a pipeline that maintains a materialization of the transformation into a destination of your choice. 

## Sources

Sources are databases or event streams that you want to use for building real-time streaming pipelines. Rubicon supports a variety of sources including all major OLTP databases, event platforms. The data from sources is represented as tables in Rubicon and available in a single namespace. So, you can join data from Postgres with data in Kafka. You can see the full list [here](/sources).

## Destination

Destination is the system you want the materialized view of your pipeline output to be saved. This can be a data warehouse, a SQL database like MySQL or specialized database like ElasticSearch to power your search. You can see the full list of supported destinations [here](#destination).<br>

## Transformations

Transformations are defined in SQL in files referred to as `models`. The SQL statement can refer tables from a connected source or other models enabling you to build complex pipelines without defining an explicit DAG. You can learn more about transformation [here](#transformations).<br>

## Pipelines

A pipeline runs a transformation continuosly and streams the results into a materialized view of a destination. You can learn more about how to setup pipelines [here](/pipelines).


# Connecting Github App to Rubicon

Rubicon stores the SQL files in your Github account and integrates with Github to make it easier for you to develop, test and maintain your model files.. We recommend you create a separate repo for Rubicon. Rubicon will create a folder called "`Rubicon`" in the repo you connect to Rubicon to save the models.

In Github, follow these steps to create a personal access token that allows access to your repository.

1. In the upper-right corner of any page, click on your profile photo, then click **Settings**.
2. Click **Developer Settings**
3. Click the **Personal access tokens** tab.
4. Click the **Generate new token** button
5. Enter a token description
6. Select the **repo** scope and **workflow** scope, and click the **Generate token** button. **workflow** scope is needed in case your repository has GitHub Action workflows.
7. Copy the token to your clipboard. You enter this token in Rubicon under **Settings > Git integration**.


# Sources

The following sources are supported by Rubicon

* [Snowflake](/sources/snowflake)
* [Postgres](/sources/postgresql)
* [MySQL](/sources/mysql)
* [Redshift](/sources/redshift)


# Snowflake

1. Click on the **Sources** tab at the top of the app<br>

2. Click **Add Source** on the top right hand corner<br>

3. Choose **Snowflake** as the source type<br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FhTxDaTdo8JMdBkSyRO6a%2Fsnowflake1.png?alt=media&amp;token=3ac6f467-6b2a-4ade-a1f9-4c59fa7689ad" alt=""><figcaption></figcaption></figure>

4. Enter the information required to connect to Snowflake. Follow the instructions in the product to allow Rubicon the permissions to connect to Snowflake.<br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FempVj2zyAuYDtGwWPZAF%2Fsnowflake2.png?alt=media&amp;token=5802e4cf-0d3b-4202-8407-c5a09891046c" alt=""><figcaption></figcaption></figure>

5. Once complete, you will be able to look at the tables in the Rubicon Catalog.


# PostgreSQL

1. Click on the **Sources** tab at the top of the app<br>

2. Click **Add Source** on the top right hand corner<br>

3. Choose **PostgreSQL** as the source type<br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FBjROzDSsi56H1SBbGIQG%2Fpostgresql1.png?alt=media&amp;token=451161cb-4f99-462c-8b92-cd31dcd67808" alt=""><figcaption></figcaption></figure>

4. Enter the information required to connect to PostgreSQL. Follow the instructions in the product.<br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FFYlocGlYnSx1XBc5IL9Y%2Fpostgresql2.png?alt=media&amp;token=81e8385d-dbdb-418e-b016-17f2258dd92d" alt=""><figcaption></figcaption></figure>

5. Once added, Rubicon will start syncing the data. It will notify you once the initial sync is complete and the tables are available for building pipelines in the Rubicon Catalog.&#x20;


# MySQL

1. Click on the **Sources** tab at the top of the app.<br>

2. Click **Add Source** on the top right hand corner.<br>

3. Choose **MySQL** as the source type.\ <br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FPKvFkIVJdHZpAVRBt8NP%2Fmysql1.png?alt=media&amp;token=79fae33f-328e-4428-9e84-f5b48bb3bc97" alt=""><figcaption></figcaption></figure>

4. Enter the information required to connect to MySQL. <br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FgOuNsB0uVskcHwbx4bQ3%2Fmysql2.png?alt=media&amp;token=5f6086b2-6f5e-4620-87a5-494d0c090afd" alt=""><figcaption></figcaption></figure>

5. Once added, Rubicon will start syncing the data. It will notify you once the initial sync is complete and the tables are available for building pipelines in the Rubicon Catalog. \ <br>


# Redshift

1. Click on the **Sources** tab at the top of the app<br>
2. Click **Add Source** on the top right hand corner<br>
3. Choose **Redshift** as the source type<br>
4. Enter the information required to connect to Redshift


# Destinations

The following destinations are supported by Rubicon

* [Elasticsearch](/destinations/elasticsearch)
* [Snowflake](/destinations/snowflake)
* [Redshift](/destinations/redshift)


# ElasticSearch

1. Click on the **Destination** tab at the top of the app<br>
2. Click **Add Destination** on the top right hand corner<br>
3. Choose **ElasticSearch** as the destination type


# Snowflake

1. Click on the **Destination** tab at the top of the app<br>
2. Click **Add Destination** on the top right hand corner<br>
3. Choose **Snowflake** as the destination type


# Redshift

1. Click on the **Destination** tab at the top of the app<br>
2. Click **Add Destination** on the top right hand corner<br>
3. Choose **Redshift** as the destination type


# Transformations


# Writing your first transformation

So, you have connected Rubicon Github App to your repository, connected a few sources and destination to Rubicon and are ready to write your first transformation. This document provides an example of how to do it using the test demo database `rubicon_demo` that should already be connected in your account.

1. Check the table and schemas available in the **catalog** by clicking on the catalog icon on the Models tab.<br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2Fkr2N5ZD1JvwiD4oeLh0A%2Fcatalog_cropped.png?alt=media&amp;token=5a49c27a-1c1f-492e-b497-3419dc007e99" alt=""><figcaption></figcaption></figure>

2. Create a file called `order_and_refunds_by_hour.sql` with the following SQL statement that computes hourly refunds for each merchant. \
   \ <mark style="color:blue;">`SELECT`</mark>` ``hour, merchant_id, SUM(order_amount), SUM(refund_amount)`\ <mark style="color:blue;">`FROM`</mark>` ``orders`` `<mark style="color:blue;">`JOIN`</mark>` ``merchants`` `<mark style="color:blue;">`JOIN`</mark>` ``refunds`

3. Click on the "play" button at the top right hand side to do a preview of the results. You should see something like this:<br>

   <figure><img src="https://2422785524-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8sE6KqVrQf6AFKNd0468%2Fuploads%2FEieKOQk4sLeeLq6evKfM%2Fcatalog_play.png?alt=media&amp;token=338043c1-6a38-4a8e-958c-09bed504f052" alt=""><figcaption></figcaption></figure>

4. Submit the SQL transformation to your Github repo. Now, you are ready to set up your first pipeline. You can see the instructions [here](/pipelines/deploy-your-first-pipeline).

&#x20;


# Rubicon SQL

This page describes the SQL language supported by Rubicon. It is based on PostgresSQL with a few modifications to support streaming use cases.&#x20;

### Data Types

Rubicon does not require the user to explicitly define the data types for the source tables or the output of a transformation. All data types are implicitly inferred from the transformation and the destination where the output is being materialized.

### Operations

<mark style="color:blue;">**SELECT CLAUSE**</mark>

**Syntax**

```sql
SELECT select_list FROM table_expression [ WHERE boolean_expression ]
```

\
**Example**

```sql
SELECT order_id, price + tax FROM Orders
```

[<mark style="color:blue;">**WITH CLAUSE**</mark>](#user-content-fn-1)[^1]

**Syntax**

```sql
WITH <with_item_definition<> [, ... ]
SELECT... FROM ....;

<with_item_definition>:
    with_item_name (column_name[, ...n]) AS ( <select_query> )
```

\
**Example**

```sql
WITH orders_with_total AS (
    SELECT order_id, price + tax AS total
    FROM Orders
)
SELECT order_id, SUM(total)
FROM orders_with_total
GROUP BY order_id;
```

[^1]:


# Pipelines

Pipelines are  transformation jobs that execute a specified transformation (referred as model) continuously and maintains an incremental view of the output in our destination. Rubicon support two kinds of pipelines:

1. **Production pipeline**&#x20;

Used for models in the `main` Github branch. Rubicon assumes any pipeline for a model in `main` branch to be in-production and supports&#x20;

* Continuous ETL of the transformation.
* Automatic deployment of new versions of pipeline when a new version of the transformation is checked-in
* Sending metrics to your observability platform

2. **Development pipeline**&#x20;

Used for testing your `models` while developing them. Rubicon assumes any pipeline for a model in a non-`main` branch is development and creates a materialized view of the transformation for data but does not continuously update after it has processed all the data up until the time the pipeline was created. &#x20;

## &#x20;

## Creating a new pipeline

**Step 1:** Go to <https://app.rubicon.io/pipelines>. Select the Github branch for the model&#x20;

**Step 2:**  Pick any of the available **models** in the branch that you want to build a pipeline for.

**Step 3:**  Pick a destination for the output of your transformation.

**Step 4:** Deploy the pipeline.&#x20;


# Deploy your first pipeline

This document describes the steps needed to deploy a pipeline. We will continue using the transformation `order_by_day_and_hour`.sql we created earlier to build a pipeline.

1. Go to <https://app.rubicon.io/pipelines> and click on **Add** pipeline.
2. Pick `order_by_day_and_hour.sql` model&#x20;
3. Pick the test `rubicon_demo_elasticsearch` cluster as the destination.
4. Deploy the pipeline.