
## What is Splitgraph?

Splitgraph is building the Unified Data Stack – an integrated and modern solution
for working with data without worrying about its infrastructure.

You can try Splitgraph now! Browse
[the public data catalog](https://www.splitgraph.com/explore) to discover
40,000+ repositories to query with SQL, through the
[web IDE](https://www.splitgraph.com/query)
or [any Postgres client](https://www.splitgraph.com/connect).

## Introduction

There is a trend in data engineering to apply best practices from software engineering, for example, version control and code reviews. Splitgraph itself was originally inspired by Git and Docker, both famous developer tools.

Another DevOps tool we like is Terraform: it lets you use a declarative configuration language to define your cloud infrastructure. Terraform then takes care of interpreting this configuration and calling cloud vendor-specific APIs to create/destroy resources and align the real state of the world with the desired configuration.

We built something similar for the data stack: [`splitgraph.yml`](https://www.splitgraph.com/docs/splitgraph.yml/introduction), or Splitgraph Cloud project files.

You can think of `splitgraph.yml` as a Terraform file that lets you programmatically configure a dataset in Splitgraph, including its:

- metadata (README, tags, description)
- data source settings (databases, SaaS services, flat files)
- ingestion schedule
- dbt transformations

We use this format ourselves to declare data ingestion pipelines with Airbyte, build our own data warehouse with dbt and organize them in our Splitgraph data catalog (see a [previous blog post](https://www.splitgraph.com/blog/airbyte-dbt-splitgraph) for more details!)

In this post, we'll talk about how you can use `splitgraph.yml` to define your own data pipeline and show an end-to-end example that ingests some [sample data](https://data.cityofnewyork.us/Transportation/For-Hire-Vehicles-FHV-Active/8wbx-tsch) from Socrata and transforms it using dbt.

## Example `splitgraph.yml` file

Here is a basic example that builds two Splitgraph repositories:

  - [splitgraph-demo/for-hire-vehicles](https://www.splitgraph.com/splitgraph-demo/for-hire-vehicles): List of active for-hire vehicles in New York City ([source](https://data.cityofnewyork.us/Transportation/For-Hire-Vehicles-FHV-Active/8wbx-tsch))
  - [splitgraph-demo/for-hire-vehicles-summary](https://www.splitgraph.com/splitgraph-demo/for-hire-vehicles-summary): dbt transformation that summarizes the data in the first repository

```yaml
repositories:
- namespace: splitgraph-demo
  repository: for-hire-vehicles
  # Catalog-specific metadata for the repository.
  metadata:
    readme:
      text: For more information, see https://www.splitgraph.com/cityofnewyork-us/for-hire-vehicles-fhv-active-8wbx-tsch
    description: New York Active For Hire Vehicles
    topics:
    - socrata
    - new york
  # Data source settings
  external:
    plugin: csv
    params:
      connection:
        connection_type: "http"
        url: ""  # Will be overridden by the individual table
    tables:
      vehicles:
        options:
          url: "https://data.cityofnewyork.us/api/views/8wbx-tsch/rows.csv?accessType=DOWNLOAD"
        # Schema of the table (set to [] to infer)
        schema: []
    # Enable live querying for the plugin (creates a "live" tag in the
    # repository proxying to the data source).
    is_live: true

# dbt model that processes the previous repository
- namespace: splitgraph-demo
  repository: for-hire-vehicles-summary
  external:
    plugin: dbt
    credential: dbt-sample
    params:
      sources:
      # Map dbt source names to Splitgraph repositories
      - dbt_source_name: for_hire_vehicles
        namespace: splitgraph-demo
        repository: for-hire-vehicles
        hash_or_tag: latest
      git_branch: $THIS_SHA
    is_live: false
    tables: {}
  metadata:
    description: Sample dbt model
    readme:
      text: "## Sample dbt model\n\nThis is a model referencing data\
        \ from:\n\n  * [/splitgraph-demo/for-hire-vehicles](/splitgraph-demo/for-hire-vehicles)"
```

The full project that uses this file is located on [our GitHub](https://github.com/splitgraph/splitgraph-yml-example). You can also query the resultant dataset on Splitgraph:

<EmbeddedQuery
  query={`SELECT
  "Base Name",
  "Base Type",
  "Count"
FROM "splitgraph-demo/for-hire-vehicles-summary:latest"."vehicles_summary"
ORDER BY "Count" DESC;`} />

## Writing `splitgraph.yml` files

### Quick method: generating a Splitgraph Cloud project from a seed

The simplest way to get started with `splitgraph.yml` is by using our new "project seeds" functionality. This will generate a GitHub repository for you with a `splitgraph.yml` file, a dbt model and a GitHub Action file that you can use as a starting point to define your own pipeline.

We've done some magic to make the process smooth for you, so you can select data sources, copy a repo on GitHub, and paste a seed value into a GitHub action, to set up all your boilerplate.

Click the button to get started:

<ButtonLink href="/connect/splitgraph-yml">Generate splitgraph.yml and GitHub Repository</ButtonLink>
<br/>

Splitgraph supports over 100 sources. These include SaaS services (powered by the open Airbyte standard) as well as "live queries" to the most popular RDBMSs and data warehouses. The latter, powered by PostgreSQL Foreign Data Wrappers, lets you query data through Splitgraph without having to load it.

![Using the wizard to generate a splitgraph.yml file](/static-v2/images/blog/splitgraph-yml-terraform-data-pipeline/splitgraph-yml-wizard-selected.png)

The wizard will guide you through using our Splitgraph Cloud [template project](https://github.com/splitgraph/splitgraph-cloud-template) to generate a project from a "seed" (a representation of the data sources you chose).

![Using the Splitgraph Cloud template project to generate a splitgraph.yml file](/static-v2/images/blog/splitgraph-yml-terraform-data-pipeline/splitgraph-yml-project-seed.png)

After you go through the process, you will end up with a repository with Splitgraph Cloud project files:

  - A `splitgraph.yml` file that defines your chosen data
  - `build.moveme.yml` in the root with a GitHub Action definition used to ingest the data on a schedule or on-demand
  - Sample dbt model that references data from all the datasets you added

We created the [example project](https://github.com/splitgraph/splitgraph-yml-example) using this method.

You will need to perform some extra steps to finish the setup (see the README in the generated repository for more information):

  - Set up your Splitgraph API keys (we call them ["SQL credentials"](https://www.splitgraph.com/settings/sql-credentials), as you can also use them to connect to Splitgraph's PostgreSQL-compatible endpoint) as GitHub Secrets
  - Add credentials to `splitgraph.credentials.yml` (not required for our sample project, as the Socrata endpoint we're using to get the data is public)
  - Edit `splitgraph.yml` to set up the data source parameters
  - Set up GitHub Actions (copy `build.moveme.yml` to the `.github` directory)

### Advanced method: using `splitgraph.yml` with the `sgr` CLI

The example GitHub Action above calls the `sgr` CLI to interact with Splitgraph Cloud and manage datasets and pipelines.

You can run the same commands from your development machine or from another workflow orchestrator, like GitLab CI. Let's look at these commands in detail.

#### `sgr cloud stub`: generate a `splitgraph.yml` file

This command will generate a basic `splitgraph.yml` file for a specific plugin. You can use this file as a starting point [(documentation)](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-stub).

```bash
$ sgr cloud stub csv splitgraph-demo/csv
credentials:
  csv:  # This is the name of this credential that "external" sections can reference.
    plugin: csv
    # Credential-specific data matching the plugin's credential schema
    data:
      s3_access_key: ''  # AWS Access Key
      s3_secret_key: '' # AWS Secret Access Key
repositories:
- namespace: splitgraph-demo
  repository: csv
  # Catalog-specific metadata for the repository. Optional.
  metadata:
    readme:
      text: Readme
    description: Description of the repository
    topics:
    - sample_topic
  # Data source settings for the repository. Optional.
  external:
    # Name of the credential that the plugin uses. This can also be a credential_id if the
    # credential is already registered on Splitgraph.
    credential: csv
    plugin: csv
    # Plugin-specific parameters matching the plugin's parameters schema
    params:
      connection:  # Connection. Choose one of:
      - connection_type: http  # REQUIRED. Connection type. Constant
        url: '' # REQUIRED. URL. HTTP URL to the CSV file
      - connection_type: s3  # REQUIRED. Connection type. Constant
        s3_endpoint: '' # REQUIRED. S3 endpoint. S3 endpoint (including port if required)
        s3_bucket: '' # REQUIRED. Bucket name. Bucket the object is in
        s3_region: '' # S3 region. Region of the S3 bucket
        s3_secure: false # Secure. Whether to use HTTPS for S3 access
        s3_object: '' # S3 Object name. Limit the import to a single object
        s3_object_prefix: '' # S3 Object prefix. Prefix for object in S3 bucket
...
```

#### `sgr cloud load`: set up the catalog metadata

This command [(documentation)](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-load) uploads the README, tags, description as well as external data source settings and credentials to Splitgraph Cloud.

#### `sgr cloud sync`: run a data load

This command [(documentation)](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-sync) starts an ingestion job. If you previously ran `sgr cloud load`, you can use the existing data source settings uploaded to Splitgraph Cloud. You can also make this command use an existing `splitgraph.yml` file, which will run ingestion without Splitgraph Cloud storing your data source credentials.

#### Miscellaneous `sgr cloud` commands

There are other useful `sgr cloud` commands. See our [documentation](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-plugins) for a full list:

- [`sgr cloud upload`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-upload): upload a CSV file to Splitgraph Cloud
- [`sgr cloud download`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-download): export a SQL query result as a CSV file
- [`sgr cloud plugins`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-plugins): search for data source plugins
- [`sgr cloud search`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-search): search for repositories
- [`sgr cloud sql`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-sql): run SQL queries
- [`sgr cloud status`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-status): check the status of ingestion jobs

### `splitgraph.yml` reference

An in-depth reference for the `splitgraph.yml` format is available [here](https://www.splitgraph.com/docs/splitgraph.yml/introduction).

### In Alpha: Web UI

Configure datasets individually via the web GUI. You can try it here, but note
it's in alpha status and some configurations are not yet fully supported:

<ButtonLink href="/connect/data">Connect Data</ButtonLink>
<br />

![Upcoming Web UI to add data to Splitgraph](/static-v2/images/blog/splitgraph-yml-terraform-data-pipeline/splitgraph-yml-web-ui.png)

You can still store your catalog in the `splitgraph.yml` format with this method: use the [`sgr cloud dump`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-dump) command to dump existing repositories to `splitgraph.yml`.

## Conclusion

In this blog post, we showcased `splitgraph.yml`: a configuration format that lets you programmatically manage your datasets on Splitgraph. You can find an example project on [our GitHub](https://github.com/splitgraph/splitgraph-yml-example).

The example project also shows how to make the data repositories that you build private. You can then use our [sharing settings and invites functionality](/blog/data-sharing-settings-invites-private-repositories) to manage who has access to your datasets and invite collaborators.

Interested in tighter security guarantees or more team features? We also provide private versions of Splitgraph Cloud, deployed to a cloud vendor and region of your choice. Feel free to [try it out](https://www.splitgraph.com/demo)!


We showcase the splitgraph.yml format, which lets you programmatically manage your datasets on Splitgraph, change their data source settings and define dbt transformations.

We showcase the `splitgraph.yml` format, which lets you programmatically manage your datasets on Splitgraph, change their data source settings and define dbt transformations.

`splitgraph.yml`: Terraform for your data stack

splitgraph.yml: Terraform for your data stack


## Introduction

In a [previous blog post](/blog/airbyte-dbt-splitgraph), we talked about our own data stack that uses dbt and Airbyte to build our data warehouse.

![Our main analytics dataset, built with dbt on our private instance of Splitgraph Cloud.](/static-v2/images/blog/dbt-splitgraph-cloud/dbt-data-catalog.png)
_Our main analytics dataset, built with dbt on our private instance of Splitgraph Cloud._

Today, we will show you how you can add your own dbt project to Splitgraph Cloud to build Splitgraph images with dbt models. We'll also discuss how the dbt transformation functionality works.

**Don't have much time? [Our GitHub](https://github.com/splitgraph/dbt-transform-example/) has a forkable self-contained example that uses a GitHub Action to build [a dataset of top commodity exporters in each country](https://www.splitgraph.com/splitgraph/dbt-transform-example). Feel free to check it out!**

## Using the `splitgraph.yml` file

`splitgraph.yml` is a declarative way to define the datasets to add to Splitgraph. You can think of it as a Terraform template for your data stack. See more information in the documentation [here](https://www.splitgraph.com/docs/splitgraph.yml/introduction).

We are also working on a UI to add data to Splitgraph. It will let you ingest data from over 100 SaaS sources (powered by Airbyte and Singer). It will also support data federation for popular databases and warehouses (powered by PostgreSQL Foreign Data Wrappers).

This is what the part of our `splitgraph.yml` file responsible for the dbt transformation looks like:

```yaml
credentials:
  splitgraph_dbt:
    plugin: dbt
    data:
      git_url: https://[USERNAME]:[PASSWORD]@gitlab.com/splitgraph/core/analytics.git

repositories:

  # Data transformed with dbt
  - namespace: analytics
    repository: splitgraph-com
    external:
      credential: splitgraph_dbt
      plugin: dbt
      is_live: false
      schedule:
        enabled: true
        # NOTE: we currently don't support inter-task dependencies,
        #   so we run this 10 minutes after the main raw data update
        #   starts
        schedule: 10 */6 * * *
      params:
        sources:
          - dbt_source_name: airbyte_raw
            namespace: airbyte-raw
            repository: splitgraph-com
          - dbt_source_name: stopforumspam
            namespace: analytics
            repository: stopforumspam
      # This lets us select specific dbt models to build in this image
      tables:
        daily_summary:
          options: { }
          schema: [ ]
        dim_users:
          options: { }
          schema: [ ]
        fct_export_jobs:
          options: { }
          schema: [ ]
        fct_ingestion_jobs:
          options: { }
          schema: [ ]
        # ...
    metadata:
      topics:
        - analytics
        - splitgraph-com
        - processed
        - normalization:custom
        - dbt
      sources:
        - anchor: dbt model for splitgraph-com
          href: https://gitlab.com/splitgraph/core/analytics
      description: Main data warehouse for splitgraph.com analytics
      readme:
        text: |
          # Splitgraph Analytics

          Built dbt models for splitgraph.com analytics (users, activity etc)
          using https://gitlab.com/splitgraph/core/analytics

          This dataset on Metabase: https://admin.splitgraph.com/metabase/browse/5/schema/analytics~splitgraph-com
      extra_metadata:
        dbt:
          project: https://gitlab.com/splitgraph/core/analytics
```

This snippet:

  - uses the raw data we ingest into the `airbyte-raw/splitgraph-com` repository with Airbyte. It's also in this file but has been omitted.
  - uses the data in the `analytics/stopforumspam`  repository (available [here](https://www.splitgraph.com/splitgraph/stopforumspam))
  - builds a dbt model with statistics on active users and feature usage. It does that by joining the two datasets to exclude activity from users with known spam e-mails.

### Running the file

There are two ways you can run a dbt model on Splitgraph Cloud.

The first method is using the `schedule` stanza. This lets you specify a cron-like syntax for Splitgraph to run the model on schedule. Note that currently we don't support inter-task dependencies. For us, this means we have to give the source `airbyte-raw/splitgraph-com` dataset some time to load and start the dbt model later.

To do that, use the [`sgr cloud load`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-load) command to upload the `splitgraph.yml` file to Splitgraph Cloud.

The second method is by triggering the job from GitHub Actions. Our [GitHub example](https://github.com/splitgraph/dbt-transform-example/) runs the ingestion this way by executing [`sgr cloud sync`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-sync). This lets you control when the job starts as well as manage inter-task dependencies. You can find the [full GitHub Action code](https://github.com/splitgraph/dbt-transform-example/blob/main/.github/workflows/build.yml#L21-L22) here.

### Result

In either case, you will end up with a repository of images built from the dbt model. One benefit of using Splitgraph for this is that this repository is versioned. This means you can easily switch back to the old versions of the dataset.

![Tag selector letting you switch between dataset versions](/static-v2/images/blog/dbt-splitgraph-cloud/dbt-versioning.png)

You can also query the dataset on the [Splitgraph DDN](https://www.splitgraph.com/docs/query/ddn) with any SQL client. You can even run JOINs between different versions of the dataset to see how it evolved.

![Using DBeaver to query different versions of a dbt model](/static-v2/images/blog/dbt-splitgraph-cloud/dbt-versioning-query.png)

Interested in seeing how this works? Read on!

## How it works

At its core, Splitgraph is a tool for building and sharing versioned "data products" (we call them data images). Each image is a snapshot of a PostgreSQL schema that you can freely move around and query.

dbt itself expects to be able to work against a database with a single shared namespace. It handles multitenancy by letting you build a dataset in a schema with a custom prefix. Each dbt user uses a separate target schema. This means their models don't step on the actual production models. See the [dbt documentation](https://docs.getdbt.com/docs/building-a-dbt-project/building-models/using-custom-schemas#why-does-dbt-concatenate-the-custom-schema-to-the-target-schema) for more details.

This presents some challenges in how we integrate Splitgraph Cloud with dbt.

We talked a bit about how the open-source Splitgraph library can work with dbt in [a previous blog post](https://www.splitgraph.com/blog/dbt). When running it in a managed fashion, we need to be able to automate all these steps.

Roughly, here's what we do:

  - When a user adds a dbt project to Splitgraph, introspect it and let the user select which models they want to build in their dataset
  - When running the dbt project:
	  - Set up a temporary Splitgraph engine
	  - Set the dbt sources to point to specific Splitgraph datasets required by it
	  - Set the dbt target to a specific schema on the engine
	  - Run dbt
	  - Run `sgr commit` to build the image and push it out

### Introspection

This is a step that isn't required when using `splitgraph.yml`. However, we are currently working on a GUI to add data sources to Splitgraph. The first part of this workflow will be a "preview" stage. When you input the connection parameters, Splitgraph will introspect it and return a list of tables it discovered in the data source.

In case of dbt, we perform introspection by compiling your dbt project and extracting its [manifest file](https://docs.getdbt.com/reference/artifacts/manifest-json). Here's the [code](https://github.com/splitgraph/sgr/blob/7677454da0666d6e57c13e4129b9cd4dcc26a209/splitgraph/ingestion/dbt/utils.py#L199).

Then, we parse the manifest file and load a list of models in it ([code](https://github.com/splitgraph/sgr/blob/7677454da0666d6e57c13e4129b9cd4dcc26a209/splitgraph/ingestion/dbt/data_source.py#L123-L129)). Each model will become a table in your Splitgraph image.

### Patching sources

This is the part of the plugin configuration that's responsible for it:

```yaml
sources:
- dbt_source_name: airbyte_raw
  namespace: airbyte-raw
  repository: splitgraph-com
- dbt_source_name: stopforumspam
  namespace: analytics
  repository: stopforumspam
```

The dbt plugin crawls the project for all source references ([code](https://github.com/splitgraph/sgr/blob/7677454da0666d6e57c13e4129b9cd4dcc26a209/splitgraph/ingestion/dbt/utils.py#L74)). It then rewrites each source's schema to point to a temporary schema. This temporary schema has the dependent Splitgraph image checked out in [layered querying](https://www.splitgraph.com/docs/sgr-advanced/large-datasets/layered-querying) mode.

Here's an example of our dbt YAML file defining sources:

```yaml
version: 2
sources:
- name: airbyte_raw
  schema: "airbyte-raw/splitgraph-com"
  tables:
  - name: _airbyte_raw_celery_events_raw
  - name: _airbyte_raw_gql_api_calls_raw
  # ...
- name: stopforumspam
  schema: "analytics/stopforumspam"
  tables:
  - name: listed_email_365_all
  - name: listed_ip_365_ipv46_all
  - name: listed_username_365_all
```

The `schema` in this case is what the plugin patches out. The default value for the schema is the standard Splitgraph format of `namespace/repository`. This is to make local development easier (more below).

### Running dbt

We make a special [profile](https://github.com/splitgraph/sgr/blob/7677454da0666d6e57c13e4129b9cd4dcc26a209/splitgraph/ingestion/dbt/utils.py#L45) to run dbt against the Splitgraph engine and get it to write the output to a temporary schema. Note that this won't capture cases where a dbt model uses a [custom schema](https://docs.getdbt.com/docs/building-a-dbt-project/building-models/using-custom-schemas#how-do-i-use-custom-schemas).

Finally, we run `sgr commit` to build the image. We also give it a versioned tag and push it out. At this point, it becomes just another dataset in our catalog that we can query with any SQL client.

In our case, we point Metabase at this dataset to build beautiful BI dashboards.

## Local development

Developing the dbt model with this method is more complex than just running dbt against a data warehouse. We have plans to improve this.

In the meantime, here's how you can do local development.

First, you'll need to [install sgr](https://github.com/splitgraph/sgr/releases) and [set up a Splitgraph engine](https://www.splitgraph.com/docs/sgr-advanced/getting-started/installation#manual).

Then, set up a dbt profile file to work with the engine:

```yaml
$ cat ~/.dbt/profiles.yml
splitgraph:
  outputs:
    prod:
      dbname: splitgraph
      # Change the connection parameters to the ones
      # you set up for the engine
      host: 127.0.0.1
      pass: password
      port: 6432
      schema: my_target_schema
      threads: 32
      type: postgres
      user: sgr
  target: prod
```

Then, set up the source datasets. We'll be checking them out on the local engine into the `namespace/repository` PostgreSQL schemas. This is the reason we default to `schema: "namespace/repository"` in the dbt source file:

```bash
sgr clone my-source/repo:latest
sgr checkout --layered my-source/repo:latest
```

Finally, run the model:

```bash
dbt build --profile splitgraph
```

## Future plans

### Integrating the dbt docs site

Every dbt model comes with plenty of metadata about model and column meanings as well as tests. dbt uses this to generate its docs site.

Splitgraph could extend on its dbt manifest parsing and ingest that metadata as well. It would then be able to display it in the repository's overview page.

We could even integrate this with Splitgraph's lineage tracking features. This would let you click through to the source datasets for each built dbt model and query them.

### Cataloging dbt models built on other warehouses

Splitgraph also supports live querying (data federation). Instead of running the dbt model itself, it could parse the manifest for a dbt project built in another data warehouse (Snowflake, Redshift, BigQuery etc). It would then create a set of repositories pointing to the data in that warehouse.

### Autogenerating dbt models

With plenty of sources to come to Splitgraph, the next question will be how to make them immediately usable to people ingesting this data.

We could include some simple pre-generated queries for commonly used data sources. For example, when adding your data from Stripe, you could see a sample query for your monthly revenue.

We've done some research on feasibility of this. As part of that, we recently published an [Airbyte-to-Fivetran Stripe adapter](https://hub.getdbt.com/splitgraph/ab2ft_stripe/latest/) to dbt Hub. This package converts the Stripe data loaded by Airbyte into a format that's usable by [Fivetran's Stripe dbt package](https://hub.getdbt.com/fivetran/stripe/latest/).

### Writeable DDN

The local development story with running dbt models on Splitgraph is a bit lacking. Ideally, a user would be able to run dbt on the [Splitgraph DDN](https://www.splitgraph.com/docs/query/ddn) without running a local Splitgraph engine.

This will involve us building the ability to write to the DDN, including support for `CREATE TABLE AS` statements that dbt uses to build models.

## Conclusion

In this post, we talked about Splitgraph Cloud's support for dbt models and did an in-depth dive of how it works and how we use it ourselves.

On our GitHub, you can find [a similar self-contained example](https://github.com/splitgraph/dbt-transform-example/) of this feature. It uses a GitHub Action to build [a dataset of top commodity exporters in each country](https://www.splitgraph.com/splitgraph/dbt-transform-example).

We also discussed our future ideas for dbt support. If you have any ideas or use cases to share, feel free to [drop us an e-mail](mailto:support@splitgraph.com). If you're interested in a similar setup, [get in touch](https://4o99daw6ffu.typeform.com/to/zO5zBG3d?typeform-source=www.splitgraph.com) and join our private beta!


We showcase the ability to run dbt models on Splitgraph, triggering them on a schedule as well as using GitHub Actions. We also talk about how it works and share more plans for our dbt integration.

Scheduling, versioning and cataloging: introducing our dbt integration


**Note (February 2022): For a deeper dive on how our dbt integration works 
and how you can run dbt models on Splitgraph Cloud, check out a newer blog 
post [here](/blog/dbt-splitgraph-cloud)!**

## Introduction

[Some time ago](/blog/splitgraph-matomo-elasticsearch-metabase),
we wrote about how we use the Splitgraph engine for our own business
analytics. Soon, Splitgraph evolved into a cloud offering and we built more
integrations for it. Eventually, we created a private instance of Splitgraph to
use for our own data stack.

In this post, we'll talk about how we use Airbyte, dbt and Splitgraph for our
analytics needs. We'll also touch on our thoughts on Splitgraph's place in the
modern data stack as a simple discoverability, storage and orchestration layer.

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/assets/blog/content/blog/images/20211223-airbyte-dbt-splitgraph/01-data-stack.jpg)

## Splitgraph "BI agent": punching through the firewall

In our previous post, we used a single Splitgraph engine for our analytics data
and as a Metabase application database. It still exists, but we downgraded it to
the role of a "BI agent".

The "agent" is a PostgreSQL instance with foreign data wrappers. These proxy to
tables in MySQL (Matomo), Elasticsearch (event data) and our production
PostgreSQL database (other data). The "agent" acts as a SQL firewall to avoid
exposing all splitgraph.com services to the outside world while still letting us
export necessary data for analytics.

## Loading data: Airbyte

Airbyte is a new standard for data integrations that extends on Singer (more on
the specification [here](https://docs.airbyte.io/understanding-airbyte/airbyte-specification)).
Like Singer, it defines sources (taps) and destinations (sinks). The two
components communicate by emitting or receiving messages in a standardized
format:

- Sources load data from SaaS vendors or databases and output it to the standard
  output as a stream of JSON messages
- Destinations consume a stream of JSON messages and write it out to a database
  or a data warehouse

However, Airbyte also has two important improvements on Singer.

Airbyte connectors specify a JSONSchema for their configuration. Singer taps
would make the user dig around the documentation for each individual tap. With
Airbyte, one can run a connector with the `spec` argument to find out what
parameters it requires.

All connectors ship as Docker images. This means that there are no extra
dependencies to running them apart from Docker. Compare this with Singer, where
each tap or target requires a separate Python virtual environment to avoid
conflicts.

## Our usage of Airbyte

Instead of using the Airbyte web application, we run the Airbyte connectors
directly with Splitgraph. More on that later.

By default, Airbyte destinations store data they receive from connectors as a
table of raw JSON messages. This is how we store data from the "BI agent": we
use Airbyte's PostgreSQL connector to replicate the Matomo, Elasticsearch and
PostgreSQL data. Airbyte doesn't do anything else with that data, since we do
further processing on it anyway.

Airbyte also supports best-effort "basic normalization". This automatically
generates a dbt model from every stream, converting it into a set of normalized
tables. For example, it will unpack a nested JSON object into two separate
tables. We use this basic normalization for other SaaS services like Sendgrid,
Stripe, GitLab etc.

## Modelling: dbt

[dbt](https://www.getdbt.com/) is a modelling tool that lets analysts store
their data models in version control and build composable transformations from
small SQL snippets. It was a natural replacement for our ad hoc SQL scripts that
we used to run on our "BI agent".

We used Airbyte's default dbt model generator from their basic normalization as
the first step ("staging" in dbt terminology). This would unpack the data we
loaded from PostgreSQL as raw JSON into actual tables. Then, we rewrote our SQL
code into separate dbt model files that reference each other.

An example of a dbt model that runs on Splitgraph Cloud is
[here](https://github.com/splitgraph/dbt-transform-example). This
repository also has a `splitgraph.yml` Splitgraph Cloud project file in it.
This file lets us store our whole data stack, including the data source
definitions, dbt models and the catalog metadata, in a single version-controlled
repository.

We're currently working on a generator for these project files.

## Storage, orchestration and discovery: Splitgraph

Our dogfooding private Splitgraph instance (which we unimaginatively named
Eukanuba) has multiple purposes and ties our data stack together.

### Data warehouse

Splitgraph acts as our data warehouse. Each dataset, whether it's a raw Airbyte
table or a transformed dbt model, becomes a repository on Splitgraph. Because
Splitgraph uses columnar storage, the datasets take less space than they would
in a PostgreSQL database. In addition, analytical queries against this data are
faster.

We also store old versions of our dbt models, since we load each dbt result into
a separate Splitgraph [image](https://www.splitgraph.com/docs/concepts/images).
This lets us easily compare the output of dbt across points in time. For
example, this query looks at our blog post performance changes between two
different dates:

```sql
SELECT
	old.post_id,
	old."Total visits" AS old_visits,
	new."Total visits" AS new_visits
FROM
	"analytics/splitgraph-com:latest".blog_post_performance new
JOIN
	"analytics/splitgraph-com:20211120".blog_post_performance old
USING(post_id);
```

### Orchestration

Splitgraph runs the Airbyte connectors and dbt models for us. We wrote a couple
of wrappers around Airbyte and dbt, turning them into "data source" plugins.
Because Splitgraph is based on PostgreSQL, it was straightforward to do so.

For Airbyte, we connect an arbitrary source to the PostgreSQL destination,
pointing it to the Splitgraph engine. When the ingestion finishes, Splitgraph
runs the equivalent of `sgr commit`, snapshotting the dataset and making it
available on the instance.

For dbt, the plugin expects a "map" of dbt sources to Splitgraph repositories.
At runtime, Splitgraph "mounts" required source datasets and repoints the dbt
projects to them. After dbt builds the model, we similarly snapshot the dataset.

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/assets/blog/content/blog/images/20211223-airbyte-dbt-splitgraph/03-github-action.png)

To start these data loads and transformations, Splitgraph has a scheduler.
Recently, we've been experimenting with using GitHub Actions to trigger jobs. An
example of using dbt and Splitgraph to run data transformations with GitHub
actions
is [on our GitHub](https://www.splitgraph.com/splitgraph/dbt-transform-example).

### Data catalog

Finally, Splitgraph is our data catalog. We give each dataset relevant topics,
for example, tagging them by source, by provider (Airbyte, dbt) or by
normalization type. We also attach metadata to datasets, like the name of the
dbt model they correspond to.

In addition, Splitgraph supports "live" plugins that allow querying the data at
source. One of our customers uses this functionality to maintain a catalog of
CSV files in their S3 data lake.

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/assets/blog/content/blog/images/20211223-airbyte-dbt-splitgraph/02-discoverability.png)

In the future, we are planning to extend our dbt integration to also import the
dbt documentation website. This will let us, and other Splitgraph users, to view
their data documentation, explore data lineage and immediately query the data.
With live plugins, they'll also be able to point Splitgraph to the dbt models in
their warehouse without needing to use Splitgraph for storage.

## BI: Metabase

Metabase is the part of our data stack that has stayed constant. It's still an
amazing tool for exploratory data analysis and dashboard building and we spoke a
lot about it in the previous post. The only change we made to the setup was that
now it's connected to our Splitgraph Cloud instance's PostgreSQL-compatible
query endpoint.

## Conclusion

In this post, we talked about how we upgraded our analytics setup to a "modern
data stack" that uses Airbyte, dbt and Splitgraph.

On [our GitHub](https://github.com/splitgraph/), you can find the [sample Splitgraph Cloud project](https://github.com/splitgraph/dbt-transform-example)
that uses dbt to build a dataset from another dataset on Splitgraph. This
repository has a GitHub Actions job and more clarification.

You can run the ingestion job against
[splitgraph.com](https://www.splitgraph.com/),
loading data into private repositories.

In a [newer blog post](/blog/dbt-splitgraph-cloud), you will be able to find
a more complex example analytics project that we ourselves use to build our 
data warehouse.

If you're interested in a dedicated setup of Splitgraph Cloud for your business,
feel free to [get in touch](/join-beta) and join our private beta!


We talk about our modernized data stack that uses Airbyte for data ingestion, dbt for transformations and Splitgraph itself for storage, versioning, discoverability and querying.

Airbyte, dbt, Splitgraph: how we built our modern data stack


**Note (December 2021): We updated our data stack to use Airbyte and dbt
with Splitgraph. The new blog post is [here](/blog/airbyte-dbt-splitgraph)!**

[Splitgraph](https://www.splitgraph.com) is powered by data. We use [Metabase](https://www.metabase.com/) to build BI dashboards that can answer questions about how people interact with us. These dashboards reference our Web analytics data, user data and all events happening across the estate. We can find out how many people queried the Splitgraph [Data Delivery Network](https://www.splitgraph.com/connect) on a given week, how they found Splitgraph, or if they ever pulled a data image.

This works without any ETL pipelines or a data warehouse. How do we do it?

Well, we use Splitgraph.

In this post, we'll talk about our analytics stack. We'll discuss how we use Splitgraph's [`sgr mount`](https://www.splitgraph.com/docs/sgr-advanced/ingesting-data/foreign-data-wrappers/introduction) command to proxy to data from Matomo, Elasticsearch and PostgreSQL. We'll show a sample SQL query that runs a federated JOIN between these three databases. Finally, we'll talk about how we use Metabase to get a clear view of the business.

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20200918-splitgraph-matomo-elasticsearch-metabase/00-diagram.png)
_Architecture diagram of our analytics setup._

## Our analytics stack

We hate third-party trackers. At the same time, we would like to know what's happening on the website and across the company in general. In the age of CDNs, a visit to a website might never reach the origin server. HTTP server logs won't show the full story about website visitors.

To solve that, we started using **[Matomo](https://matomo.org/)**. Matomo is an open-source web analytics platform. It offers a similar interface and feature set to Google Analytics. However, unlike GA, it stores all data locally in a MySQL database.

Besides visiting the website, there's a lot of other ways users can interact with Splitgraph. For example:

  * Starring Splitgraph on GitHub or downloading a release
  * Querying the Splitgraph [Data Delivery Network](https://www.splitgraph.com/connect) from an SQL client
  * Pushing and pulling [data images](https://splitgraph.com/docs/concepts/images) to/from Splitgraph
  * Using the [REST API](https://www.splitgraph.com/docs/query/postgrest)
  * Checking for updates: we use this to estimate the number of active `sgr` users

We use **Elasticsearch** to log these and other interesting events.

Finally, we have a **PostgreSQL** database that stores actual user data. Some of it could be useful to know in an analytics context. For example: a user's primary e-mail address or their GitHub ID.

## How to bring the data together?

The idea for this setup came to us when we were trying to get some data from the Matomo Web UI. While it is pretty powerful, it's limited in the kinds of reports it can produce. Also, data we'd see in Matomo didn't include anything we store in Elasticsearch.

We wondered if we could query the data from Matomo's MySQL database directly. The [schema](https://developer.matomo.org/guides/database-schema), albeit complex, is well documented on their website.

We could ingest data into Elasticsearch. However, we were already using Kibana to visualize Elasticsearch data and its visualizations were sometimes frustrating to use. Basic functionality like plotting sums is only available through scripted Elasticsearch fields.

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20200918-splitgraph-matomo-elasticsearch-metabase/01-kibana.png)
_Pictured: five different visualization engines that Kibana lets you use_

But then we thought about it some more. Splitgraph itself is built on top of PostgreSQL. One of its features is making PostgreSQL [foreign data wrappers](https://www.splitgraph.com/blog/foreign-data-wrappers) more user-friendly. Splitgraph's `sgr mount` lets you instantiate an FDW with a single command. You can then query the data directly or snapshot it.

Could we use a Splitgraph instance and add a MySQL FDW to it to query Matomo data?

And if we did, could we use an Elasticsearch FDW to proxy to our events data?

And if we did that, could we use something like [Metabase](https://www.metabase.com/) and point it at Splitgraph, letting it query data across all our data silos?

Turns out, we could. Here's an abridged version of how we mount Matomo data on a Splitgraph instance. We have a full set of commands on [our GitHub](https://github.com/splitgraph/sgr/tree/master/examples/cross-db-analytics).

```
sgr mount mysql_fdw matomo_raw -c matomo:$PASSWORD@matomo-db -o@- <<EOF
{
  "dbname": "matomo",
  "tables": {
    "matomo_log_action": {
      "hash": "bigint",
      "idaction": "integer",
      "name": "character varying(4096)",
      "type": "smallint",
      "url_prefix": "smallint"
    },
    "matomo_log_visit": {
      "idvisit": "bigint",
      "idvisitor": "bytea",
      "user_id": "character varying(200)",
      "location_ip": "bytea",
      "referer_url": "text",
      "visit_entry_idaction_name": "integer",
      "visit_entry_idaction_url": "integer",
      "visit_exit_idaction_name": "integer",
      "visit_exit_idaction_url": "integer",
      "visit_first_action_time": "timestamp without time zone",
      "visit_last_action_time": "timestamp without time zone",
      "visit_total_actions": "integer",
      "visitor_count_visits": "integer",
      "visitor_days_since_first": "smallint",
      "visitor_days_since_last": "smallint",
      "visitor_returning": "smallint"
    }
  }
}
EOF
```

In this, we just pull out interesting tables and columns from Matomo. The full Matomo schema spec for Splitgraph is available [here](https://github.com/splitgraph/sgr/blob/master/examples/cross-db-analytics/mounting/matomo.json).

To query Elasticsearch, we used a [fork](https://github.com/splitgraph/postgres-elasticsearch-fdw) of `postgres-elasticsearch-fdw` with the ability to push down qualifiers. We made it available as an `sgr mount` subcommand. Here's an example:

```
sgr mount elasticsearch -c elasticsearch:9200 -o@- <<EOF
{
  "table_spec": {
    "github_scraper_data": {
      "schema": {
        "id": "text",
        "@timestamp": "timestamp",
        "sg.github.stars": "integer",
        "sg.github.issues": "integer",
        "sg.github.downloads_installer": "integer",
        "sg.github.downloads_osx": "integer",
        "sg.github.downloads_linux": "integer",
        "sg.github.downloads_windows": "integer"
      },
      "index": "sg-misc*",
      "rowid_column": "id"
    }
  }
}
EOF
```

This creates a table that proxies to the data dumped by our GitHub star scraper.

Adding our PostgreSQL database was easy. We made an analytics user and gave it access a limited amount of useful tables (we wrote about our [configuration and credential generation](https://www.splitgraph.com/blog/integration-tests) before):

```
sgr mount postgres_fdw sgr_auth -c [connstr] -o@- <<EOF
{
  "dbname": "auth",
  "remote_schema": "sgr_auth",
  "tables": [
    "user_emails",
    "profiles"
  ],
  "extra_server_args": {
    "use_remote_estimate": "true",
    "fetch_size": "10000"
  }
}
EOF
```

## Sample queries

Let's now query Elasticsearch from Splitgraph and find out how many GitHub stars Splitgraph has:

```sql
SELECT "sg.github.stars"
FROM elasticsearch_raw.github_scraper_data
ORDER BY "@timestamp" DESC
LIMIT 1;

 sg.github.stars
-----------------
             149
(1 row)
```

Only 149?! Make sure to [star Splitgraph on GitHub](https://github.com/splitgraph/sgr) if you're reading this!

### Federated JOIN

As a real-world example, let's say we wanted to:

  * Find users that visited our website in the last week
  * Also find out how many queries to our Data Delivery Network they made
  * Find out their e-mail addresses

This data lives across three different databases, as discussed. With this setup, we can bring these three silos together with one simple SQL query:

```sql
SELECT
	v.user_id,
	email,
	last_visit,
	COALESCE(total_ddn_queries, 0) AS total_ddn_queries
FROM sgr_auth.user_emails ue
LEFT OUTER JOIN (
	-- Get user IDs and how many DDN queries they made
	SELECT "sg.api.user_id" AS user_id, COUNT(1) AS total_ddn_queries
	FROM elasticsearch_raw.sql_api_queries
	WHERE "sg.sql.used_images" IS NOT NULL
	GROUP BY user_id
) d
ON ue.user_id::text = d.user_id
JOIN (
	-- Get last visit timestamp for users who visited the website
	-- in the last week
	SELECT user_id, MAX(visit_last_action_time) AS last_visit
	FROM matomo_raw.matomo_log_visit v
	WHERE user_id IS NOT NULL
	AND AGE(visit_last_action_time) < '1 week'
	GROUP BY user_id
) v
ON ue.user_id::text = v.user_id
WHERE ue.is_primary IS TRUE
ORDER BY last_visit DESC;
```

Here's the query plan for it:

```
 Sort
   Sort Key: (max(v.visit_last_action_time)) DESC
   ->  Hash Left Join
         Hash Cond: ((ue.user_id)::text = d.user_id)
         ->  Hash Join
               Hash Cond: ((ue.user_id)::text = (v.user_id)::text)
               ->  Foreign Scan on user_emails ue
                     Filter: (is_primary IS TRUE)
               ->  Hash
                     ->  HashAggregate
                           Group Key: v.user_id
                           ->  Foreign Scan on matomo_log_visit v
                                 Filter: (age((CURRENT_DATE)::timestamp without time zone, visit_last_action_time) < '7 days'::interval)
         ->  Hash
               ->  Subquery Scan on d
                     ->  GroupAggregate
                           Group Key: sql_api_queries."sg.api.user_id"
                           ->  Sort
                                 Sort Key: sql_api_queries."sg.api.user_id"
                                 ->  Foreign Scan on sql_api_queries
                                       Filter: ("sg.sql.used_images" IS NOT NULL)
                                       Multicorn: Elasticsearch query to <Elasticsearch([{'host': 'elasticsearch', 'port': 9200}])>
                                       Multicorn: Query: {"query": {"bool": {"must": [{"exists": {"field": "sg.sql.used_images"}}]}}}
```

As you can see, this resolves into a Hash Join across three foreign tables. It also pushes down most of the clauses to the three origin databases:

```
[PostgreSQL]
Foreign Scan on user_emails ue
  Filter: (is_primary IS TRUE)

[MySQL]
Foreign Scan on matomo_log_visit v
  Filter: (age((CURRENT_DATE)::timestamp without time zone, visit_last_action_time) < '7 days'::interval)

[Elasticsearch]
->  Foreign Scan on sql_api_queries
  Filter: ("sg.sql.used_images" IS NOT NULL)
  Multicorn: Query: {"query": {"bool": {"must": [{"exists": {"field": "sg.sql.used_images"}}]}}}
```

Normally, this would require a data warehouse and a few separate ingestion pipelines. With Splitgraph and PostgreSQL, we can query the data at source. This idea is called "data virtualization" or a "data fabric". We call it a "database proxy".

Is data virtualization always the right solution? No, but it should be a starting point. If performance becomes a concern, we'll be able to snapshot these tables as Splitgraph images. Splitgraph stores data in a columnar format (using
[`cstore_fdw`](https://www.splitgraph.com/docs/sgr-advanced/concepts/objects)), so we'll be able to query it much faster.

## Data modelling

We wrote a few views on these source foreign tables that wrangle the data and clean it up. For example ([SQL on GitHub](https://github.com/splitgraph/sgr/blob/master/examples/cross-db-analytics/mounting/matomo.sql)), we join the Matomo `log_action` and `log_visit` tables to get the URLs of entry and exit pages. The view also formats the IP addresses as strings rather than `bytea` values.

Finally, we wrote a view that joins across multiple tables to give us information on each user and their activity on Splitgraph. This includes their website visits and their activity on the DDN and the Splitgraph registry.

Querying these views still queries live data, but they're much more user friendly to query than the original data sources. One exception is Elasticsearch: there, we materialize some views for performance.

Currently, we build and organize these views ourselves. But there's nothing preventing us from running [dbt](https://www.getdbt.com/) to manage this process better. We wrote a [blog post](https://www.splitgraph.com/blog/dbt) earlier on how to use dbt with Splitgraph.

## Metabase

### Setting up

By far the most difficult thing about setting up Metabase with Splitgraph was getting it served on a non-root path behind a reverse proxy. To save you an hour of perusing GitHub issues, the settings are:

  * Make sure your reverse proxy **strips the path prefix**. For example, a request to `https://www.company.com/metabase/admin/datamodel` should be forwarded to `/admin/datamodel`.
  * Make sure your routing doesn't result in paths prefixed with double slashes!
  * Set the `MB_SITE_URL` environment variable to the full base URL, for example, `https://www.company.com/metabase/`. In this case, it must have a **trailing slash**!

### Insights

Besides that, Metabase worked surprisingly well on Splitgraph. We had to add a User ID primary key (on the [Data Model](https://www.metabase.com/docs/latest/administration-guide/03-metadata-editing.html) page) on our main user. After also adding the user ID as a foreign key in a few other views, we got amazing drill down capabilities.

For example, we could plot a graph of daily website visits (picture from the day our [DDN launch blog post](https://news.ycombinator.com/item?id=24233948) was #1 on Hacker News!):

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20200918-splitgraph-matomo-elasticsearch-metabase/02-visits.png)

We could then take a look at visits from that day and see if any of them were already Splitgraph users. Finally, we could click on that user ID and automatically get information on all other activity tables involving that user:

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20200918-splitgraph-matomo-elasticsearch-metabase/03-drilldown.png)

Metabase didn't care that these tables were actually views on other views on foreign tables. Behind the scenes, this would magically forward data to either Elasticsearch, Matomo or PostgreSQL, depending on what was being queried.

## Conclusion

In this post, we talked about our analytics setup that involves Metabase, Splitgraph itself and multiple backend data sources that we query through PostgreSQL foreign data wrappers. We discussed how it can provide business insights without adding extra complexity.

On [our GitHub](https://github.com/splitgraph/sgr/tree/master/examples/cross-db-analytics), you'll find a sample Docker Compose stack and configuration files that will let you query Matomo data from PostgreSQL.

If you want a similar setup, [get in touch](mailto:support@splitgraph.com) — [our private cloud program](/about/company/private-cloud-beta) is in early beta. It will let you set up own private Splitgraph cluster, managed by us and deployed to the cloud region of your choice.


We talk about how we use Metabase, Splitgraph and PostgreSQL foreign data wrappers to build BI dashboards that are backed by federated queries across our Matomo and Elasticsearch instances.

Splitgraph has been acquired by EDB! Read the blog post.

analytics

`splitgraph.yml`: Terraform for your data stack

Scheduling, versioning and cataloging: introducing our dbt integration

Airbyte, dbt, Splitgraph: how we built our modern data stack

Dogfooding Splitgraph for cross-database analytics in Metabase

Product

Support

Company

Splitgraph

Splitgraph has been acquired by EDB! Read the blog post.

analytics

splitgraph.yml: Terraform for your data stack

Scheduling, versioning and cataloging: introducing our dbt integration

Airbyte, dbt, Splitgraph: how we built our modern data stack

Dogfooding Splitgraph for cross-database analytics in Metabase

Product

Support

Company

Community

Splitgraph

`splitgraph.yml`: Terraform for your data stack