
## Introduction

[Splitgraph](https://www.splitgraph.com) is a tool and platform for building, versioning, querying and sharing datasets. Inspired by Docker and Git, it works on top of PostgreSQL and integrates seamlessly with anything that uses PostgreSQL. Our [data catalog](https://www.splitgraph.com/explore) already includes over 40,000 datasets from government open data portals, all queryable via SQL.

The Splitgraph catalog classifies these datasets as [external repositories](/docs/splitgraph-cloud/external-repositories). These are different from the default [Splitgraph repositories](/docs/concepts/repositories), which are collections of [Splitgraph images](/docs/concepts/images). Yet Splitgraph allows you to query them in the same way as you do Splitgraph images. For example, you can use SQL to query any repository or [`JOIN` between multiple of them](/docs/ingesting-data/socrata#using-metabase-to-join-and-plot-data-from-multiple-data-portals). Or you can use Splitfiles to [build reproducible datasets](/docs/ingesting-data/socrata#splitfile) from them. And every external repository includes an [auto-generated PostgREST API](/docs/splitgraph-cloud/publish-rest-api).

External repositories allow Splitgraph Cloud to index live data without actually ingesting it. This way, you can use the catalog to discover live data. But you only need to ingest it when you're ready to query it, or snapshot it as part of a Splitgraph image.

## Data should be discoverable and composable

Many services exist for cataloging data and making it discoverable. For example, [Google Dataset Search](https://datasetsearch.research.google.com/) provides a nice interface for searching and discovering datasets (in fact, [it even includes Splitgraph repositories](https://datasetsearch.research.google.com/search?query=opd%20crimes&docid=jrM9a8yTUXMZaY1QAAAAAA%3D%3D)). The problem is, the data is fragmented and siloed across different data portals. It's nice to be able to search for data and download a CSV file. But most datasets are uninteresting in isolation. The real power comes from the ability to combine datasets and query them together.

Splitgraph does not only provide an index for discovering open data. It also provides the tools for composing open datasets together. For example, mounting the data from the [Cambridge](https://data.cambridgema.gov) and [Chicago](https://data.cityofchicago.org) data portals is as simple as running two commands:

```shell-session
$ sgr mount socrata chicago -o '{"domain": "data.cityofchicago.org"}'
Connecting to remote server...
Mounting Socrata domain...
Getting Socrata metadata
warning: Requests made without an app_token will be subject to strict throttling limits.
Loaded metadata for 504 Socrata tables

$ sgr mount socrata cambridge -o '{"domain": "data.cambridgema.gov"}'
Connecting to remote server...
Mounting Socrata domain...
Getting Socrata metadata
warning: Requests made without an app_token will be subject to strict throttling limits.
Loaded metadata for 137 Socrata tables
```

At this point, all the datasets in these two data portals are available for querying. You can query them in isolation, or you can query them together. You can use a Splitfile, `sgr sql`, or any standard SQL client:

[![DBeaver overview](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/docs/0300_ingesting-data/images/socrata/1_dbeaver_overview.png)](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/docs/0300_ingesting-data/images/socrata/1_dbeaver_overview.png)

Here's how you can compare daily COVID cases in Chicago and Cambridge (from two separate data portals) with a standard `JOIN` query:

```sql
SELECT
    cambridge_cases.date AS date,
    chicago_cases.cases_total AS chicago_daily_cases,
    cambridge_cases.new_positive_cases AS cambridge_daily_cases
FROM
    chicago.covid19_daily_cases_deaths_and_hospitalizations_naz8_j4nc chicago_cases
FULL OUTER JOIN
    cambridge.covid19_case_count_by_date_axxk_jvk8 cambridge_cases
ON
    date_trunc('day', chicago_cases.lab_report_date) = cambridge_cases.date::timestamp
ORDER BY date ASC;
```

(For more details and in-depth instructions, see the [Socrata FDW documentation](/docs/ingesting-data/socrata).)

Note that this is not limited to combining multiple public datasets. Often, the work of a data analyst includes combining internal data with public or licensed datasets from external vendors. The same semantics of "mounting" data in Splitgraph apply.

## Mounting vs. Cloning Data

With Splitgraph, there are two primary ways to ingest data: cloning it or mounting it.

["Cloning" (and checking-out)](/docs/working-with-data/clone-vs-checkout) an image means downloading a versioned data image, which is a snapshot of a database comprised of delta-compressed diffs. For example, the result of running a Splitfile is an image.

"Mounting" means establishing a connection to a live data source. The term comes from the idea of "mounting" a filesystem. A mounted table uses a [foreign data wrapper](/docs/ingesting-data/foreign-data-wrappers/introduction) (FDW), and you don't ingest data from it until you query it. For example, the [Socrata FDW](/docs/ingesting-data/socrata) translates SQL queries to [SoQL queries](https://dev.socrata.com/docs/queries/) and forwards them to the Socrata server.

(For more details on mounting data, FDWs and custom mount handlers, read our recent ["foreign data wrappers" blog post](/blog/foreign-data-wrappers).)

## Mounting data in Splitgraph Cloud

Mounting is the key abstraction that allows Splitgraph Cloud to index external repositories with features like an auto-generated REST API. On the backend, the "query API" (a subject for a later post) uses the Splitgraph library and Socrata mount handler to mount repositories on demand. Then it exposes the mounted schemata to a customized version of [PostgREST](http://postgrest.org/) which creates the API.

Separately, a periodic Airflow task queries the Socrata metadata API to discover and index over 40,000 repositories. Conveniently, the same Socrata software powers over 200 government open-data portals, so one mount handler provides a large catalog of useful live data.

## Avoiding the pull of Data Gravity

Mounting is a powerful abstraction because it allows you to interact directly with upstream data sources, avoiding the need for ETL. In 2010, GE Engineer Dave McCrory coined the term "[data gravity](https://datagravitas.com/2010/12/07/data-gravity-in-the-clouds/)." In his blog post, he observed that "data if large enough can be virtually impossible to move."

Splitgraph, as a data versioning solution, should work with all your data, not just the subsets of it that you can move. Traditional ETL tools force you to ingest or duplicate your data before you can interact with it. With Splitgraph, you only need to pull upstream data into your images at query time. This allows incremental adoption and quick experimentation; there is no need to move your data warehouse to start using Splitgraph. Instead, you only need to setup an FDW.

Note that the idea of data gravity applies to versioned data images (which you clone) as much as it does to upstream, live data (which you mount). What if you want to import only a subset of data from a large image? This is the use case for [layered querying](/docs/large-datasets/layered-querying), which allows you to "check out" an image without downloading it. Instead, Splitgraph creates an FDW that queries only the "layers" of the image necessary to satisfy the query. You can think of layered querying like a mount handler for Splitgraph images.

## Looking to the future

At the moment, Splitgraph Cloud only uses the [Socrata FDW](/docs/ingesting-data/socrata) as a mount handler for external repositories, since the [Socrata data platform](https://www.tylertech.com/products/socrata) powers most government open-data portals.  In the future, it could use additional mount handlers to provide access to a wider array of upstream sources. To create an external repository, Splitgraph just needs a suitable FDW and a way to index the upstream data. For example, it's easy to imagine indexing Google BigQuery datasets. More interestingly, an on-premise version of Splitgraph could index private databases or data warehouses behind a firewall.

Our goal for Splitgraph is to make tools for data science as easy and pleasurable to use as tools for coding. That's why our main philosophy is to "stay out of the way." Mounting data is a great example of this philosophy in action. Why force your data warehouse to talk to Splitgraph, when Splitgraph can talk to your data warehouse?

In the meantime, make sure to [explore data](/explore) on Splitgraph. If you know SQL, you can [get started](/docs/getting-started/five-minute-demo) in less than 10 minutes.


Learn about how Splitgraph indexes over 40,000 datasets from government and public sources using the Socrata API, Splitgraph mounting, and PostgreSQL foreign data wrappers.

Querying 40,000+ datasets with SQL


The [Splitgraph registry](https://www.splitgraph.com/explore) is a complex piece of software that consists of about 60 containers that use a dozen programming languages.

Besides application code, our business logic also lives in multiple configuration files (Varnish, NGINX, HAProxy...) and SQL functions.

We would like to have confidence that we exercise our code while still maintaining the freedom to make big architectural changes. This is why we have taken a liking to loose end-to-end integration tests.

In this series of articles, we're discussing Splitgraph's internal build, test and deploy infrastructure. Today, we'll be talking about tests.

## GitLab CI

We use GitLab CI for our builds and tests. We talked about builds in the [previous article](/blog/makefile).

[GitLab CI](https://docs.gitlab.com/ee/ci/) allows us to provide a Docker image to run CI in. However, we want to run our integration tests _in_ Docker as well. So, we use a Docker-in-Docker image as the base for our CI image. There aren't many dependencies on top of that:

```dockerfile
FROM docker:stable-dind

ENV GLIBC_VERSION="2.27-r0"

# use docker-compose that supports Buildkit builds
ENV DOCKER_COMPOSE_VERSION="1.25.0"

RUN apk add --no-cache \\
    # bash for buildscripts, gettext for envsubst, make to build the thing,
    # openssh-client to deploy it
    curl bash gettext make openssh-client git jq \\
    # libc -- required for docker-compose
    && wget -q -O /etc/apk/keys/sgerrand.rsa.pub https://alpine-pkgs.sgerrand.com/sgerrand.rsa.pub \\
    && wget https://github.com/sgerrand/alpine-pkg-glibc/releases/download/${GLIBC_VERSION}/glibc-${GLIBC_VERSION}.apk \\
    && apk add glibc-${GLIBC_VERSION}.apk \\
    && wget https://github.com/sgerrand/alpine-pkg-glibc/releases/download/${GLIBC_VERSION}/glibc-bin-${GLIBC_VERSION}.apk \\
    && apk add glibc-bin-${GLIBC_VERSION}.apk \\
    # docker-compose itself
    && wget https://github.com/docker/compose/releases/download/${DOCKER_COMPOSE_VERSION}/docker-compose-`uname -s`-`uname -m` -O docker-compose \\
    && chmod +x docker-compose \\
    && mv docker-compose /usr/local/bin/docker-compose

RUN wget -q -O mkcert https://github.com/FiloSottile/mkcert/releases/download/v1.4.0/mkcert-v1.4.0-linux-amd64 \\
    && chmod +x mkcert \\
    && mv mkcert /usr/bin/mkcert

RUN apk add libcap

ENV DOCKER_TLS_CERTDIR=

COPY builder-entrypoint.sh /usr/local/bin/
ENTRYPOINT ["/usr/local/bin/builder-entrypoint.sh"]
CMD []
```

The only dependencies for running the full test suite are:

  * Docker and Docker Compose
  * Make
  * mkcert to generate self-signed SSL certificates

Everything else in this image (gettext, openssh-client, jq) is used for deployment.

## Overview of tests

With these dependencies, anyone run the full test suite with a single command, `make test`. We also have Make test targets for specific parts of the stack. Each of these targets is self contained:

  * builds the required images
  * starts them with Docker Compose
  * runs the test suite in a separate container

Most of our tests are written in Python and use the [Pytest](https://docs.pytest.org/en/latest/) framework. We have some unit tests for our Lua code too where we use [Busted](https://olivinelabs.com/busted/).

### End-to-end tests

A lot of our business logic is spread around multiple configuration files, third-party components or SQL code. We even consider the way we connect components together via Docker Compose an important part of our infrastructure that we want to automatically test.

As an example, we have a [REST API](https://www.splitgraph.com/docs/splitgraph-cloud/publish-rest-api) that we make available for every dataset pushed to Splitgraph. It consists of multiple components:

  * An instance of [Openresty](https://openresty.org/en/) that intercepts HTTP requests and sends a request to a...
  * ...component that mounts the dataset into a temporary schema using [layered querying](https://www.splitgraph.com/docs/large-datasets/layered-querying)...
  * Openresty rewrites the HTTP request to be in terms of this temporary schema and forwards it to...
  * ...custom version of [PostgREST](https://postgrest.org/en/latest/) that can perform introspection and schema discovery on the fly.

The only way to be sure that this kind of setup works correctly is end-to-end tests.

We also have a suite of "acceptance" tests that run the public Splitgraph client against the registry. They exercise the whole Splitgraph Cloud stack like a normal user would:

  * registering on the registry
  * pulling a data image
  * making changes to it
  * pushing it back
  * querying it via our REST API

### Pytest

We use Pytest to exercise even the components that aren't written in Python. This is because of its versatility and support for [fixtures](https://docs.pytest.org/en/stable/fixture.html).

For example, we have fixtures that set up some users and some repositories. These fixtures run against the dev registry. They're reusable and make it easy for anyone to exercise their new feature.

While we sometimes use mocks, we're not big fans of them. To us, the best way to test something that uses a database connection is to actually spin up a database and run code against it. This is especially true because a lot of our code is inside SQL functions.

### Dev-prod parity

Our integration tests run against a stack that is _very_ similar to what we run in production. We even use the same Docker Compose files and run the same configuration generation.

This eliminates a whole class of problems where production containers have slightly different configuration or are mis-wired.

We try to keep development and production containers the same. In particular, we deliberately build production Python containers to use editable installs for the Python package. In development, we bind mount Python code into the container. This lets us iterate faster while still maintaining dev-prod parity.

## Configuration generation

The kinds of tests we want to run can touch many containers, including HAProxy, our authentication gateway or our registry. Running these tests requires a fundamentally different approach to configuration generation.

### Jinja

We use [Jinja templates](https://jinja.palletsprojects.com/en/2.11.x/) to build configuration files for all services. This includes third-party ones like HAProxy or Varnish.

We made a small change to Jinja's templating to allow for secret generation. Here's an example template for an `.sgconfig` file that we use for Splitgraph instances powering our [REST API](https://www.splitgraph.com/docs/splitgraph-cloud/publish-rest-api):

```ini
[defaults]
...
SG_ENGINE_DB_NAME=splitgraph
SG_ENGINE_USER=sgr
SG_ENGINE_PWD={{ password.engine.sgr }}
SG_ENGINE_ADMIN_USER=sgr
SG_ENGINE_ADMIN_PWD={{ password.engine.sgr }}
SG_ENGINE_POSTGRES_DB_NAME=postgres
SG_META_SCHEMA=splitgraph_meta

[remote: registry]
SG_ENGINE_HOST=haproxy
SG_ENGINE_PORT=5431
SG_IS_REGISTRY=true
SG_ENGINE_USER=registry_api_user
SG_ENGINE_PWD={{ password.registry.api_user }}
```

When Jinja sees a variable like `{{ password.engine.sgr }}`, it first checks a special `config.json` file to see if it exists in there. If not, it generates a random password and writes it to that file. All other configuration files that reference this variable get the same password.

We use this `config.json` file to store other configuration relevant to the deployment. For example, a developer running the registry on their own machine can disable the monitoring stack with a single flag. This removes all monitoring backends from the HAProxy config.

The disadvantage of this approach is that all secrets are in a single file. However, having this kind of setup in place means that it's easy for us to add an extra database role or an extra service. We can rotate credentials easily when needed. We're also free to secure interactions even between internal services.

### CI and production

We use this workflow in CI as well. The configurator generates all configuration files from these templates. We use random passwords and throw them away at the end of the CI run. Because we run end-to-end tests, this can quickly identify issues with misconfigured roles or missing credentials.

In production, we bind mount these configuration files into the containers. This allows us to avoid rebuilds for hotfixes to configuration. For example, if we need to change some HAProxy configuration on the fly, we can do that, then send a `SIGHUP` signal to the container. This will reload the configuration file without downtime.

## Conclusion

In this article, we talked about how we approach testing at Splitgraph. Instead of relying on mocks, we try to test the whole stack as it would run in production, using powerful tools like Docker Compose.

In the next article in this series, we'll talk about how we run the Splitgraph registry in production and how we deploy and monitor it.

If you're interested in learning more about Splitgraph, you can check our [frequently asked questions](/docs/getting-started/frequently-asked-questions) section, follow our [quick start guide](/docs/getting-started/five-minute-demo) or visit our [website](https://www.splitgraph.com).


We continue our overview of Splitgraph's internal build infrastructure by talking about how we run end-to-end integration tests. We also discuss using Jinja to generate configuration and inject secrets into our components.

Splitgraph infrastructure, part 2: Integration testing with Docker Compose


## Introduction

At the time of writing, the [Splitgraph registry](/explore) runs using about 60 Docker containers. About half of them we build ourselves in CI. Our stack has code written in C, Haskell, Lua, Python, SQL and TypeScript.

Despite this complexity, any developer is able to build and spin up the whole stack in a matter of minutes. Our continuous integration system can build, test and deploy a change to any part of the codebase in about half an hour.

In this series of blog posts, we'll discuss Splitgraph's internal continuous integration and test infrastructure. We'll start by talking about how we use Make, Docker Compose and BuildKit to efficiently build Docker images.

## Docker

Every Splitgraph service runs in Docker. We use [GitLab CI](https://docs.gitlab.com/ee/ci/introduction/) to build images and push them to the GitLab registry.

We tag images with `$DOCKER_REPO/$image:$DOCKER_TAG`. In CI, the tag is the current commit hash, for example, `registry.gitlab.com/some_repo/some_image:abcdef12`. This lets us easily deploy any commit to production and roll back to previous versions.

However, there is a lot of nuance around using Docker for this.

### BuildKit

[BuildKit](https://docs.docker.com/develop/develop-images/build_enhancements/) is an advanced Docker image build engine. We use BuildKit globally for all builds. Its advantages over normal Docker builds are:

  * Running multi-stage image builds concurrently
  * More caching methods. For example, referencing a common `apt` cache without having to delete it from the image in the same build step.
  * Build cache import/export

To use BuildKit, you need to export a couple of environment variables:

```bash
# Get docker build to use BuildKit
export DOCKER_BUILDKIT=1

# Get Compose to use CLI to build images (so that it uses BuildKit too)
export COMPOSE_DOCKER_CLI_BUILD=1
```

### Custom build contexts

A common issue with Docker builds is the need to include files from the outside of the directory of the Dockerfile.

For example, we keep our Lua codebase in one part of the source tree. Multiple services depend on it. We do not want to run Docker from the root of the codebase, since then every build will send the whole source tree to the Docker daemon.

One potential solution is using a `.dockerignore` file to specify which directories should be excluded from the build context. However, this is not going to work for services that share only some dependencies. One service's excluded directory might be required by another service.

We solve this by having custom build scripts for some images. These scripts use `tar` to package up a build context that includes all files that the Dockerfile needs. The build script then pipes this context to `docker build`:

```
#!/bin/bash -ex

SRC_DIR_REL="../../../src"

THIS_DIR="$(cd -P -- "$(dirname -- "$0")" && pwd -P)"

cd "$THIS_DIR"

# Create a custom build context: besides the Dockerfile and
# configs from this directory, we also want the Lua modules
# from src.

tar \
  -cf - . "$SRC_DIR_REL"/luamods \\
  | docker build -t "$DOCKER_REPO"/openresty:"$DOCKER_TAG" \\
    $( [[ -n $CACHE_FROM ]] && echo "--cache-from=$CACHE_FROM" ) \\
    --build-arg BUILDKIT_INLINE_CACHE=1 \\
    -f Dockerfile -
```

This lets the Dockerfile reference any file in the source tree.

### Caching

Public GitLab CI instances run every build in a fresh environment. This means that the Docker daemon state doesn't persist between builds. Luckily, BuildKit allows us to use some advanced caching patterns.

The script above passes `--cache-from=some_repo/image:some_tag` to `docker build`. This tells BuildKit which image it can use as Docker cache. At the beginning of a CI run, we run [`docker manifest inspect`](https://docs.docker.com/engine/reference/commandline/manifest_inspect/) to find a suitable tag from a previous build.

Even better, if the cache image isn't on the local Docker daemon, BuildKit will selectively pull parts of it from the registry to perform the build.

On our developer machines, we have a script that sets `$DOCKER_CACHE_REPO` and `$DOCKER_CACHE_TAG` to the last successful GitLab build. This means that any developer can bring their environment up to date quickly.

Note that this requires you to pass `--build-arg BUILDKIT_INLINE_CACHE=1` to the invocation. This gets BuildKit to put cache metadata inside the image. See the [Docker documentation](https://docs.docker.com/engine/reference/commandline/build/#specifying-external-cache-sources) for more information.

### Docker Compose

Splitgraph's codebase contains a few dozen [Docker Compose](https://docs.docker.com/compose/) files. They define various aspects of building and testing the Splitgraph registry. We even use Docker Compose to run Splitgraph in production.

We do this by relying on Compose's less well-known feature: the ability to [combine multiple Compose files](https://docs.docker.com/compose/extends/). For example:

```
$ docker-compose -f docker-compose.prod.yml \\
                 -f docker-compose.service_group_1.yml \\
                 -f docker-compose.service_group_2.yml \\
                 up -d
```

We reuse a lot of the same Compose configuration in development, CI and production. This gives us confidence in our deploys. We will discuss this in a future article.

We also use Compose to build services where we do not need a custom build context. Here is an example Compose snippet that builds our base Python toolchain:

```yaml
  pybase:
    image: ${DOCKER_REPO}/pybase:${DOCKER_TAG}
    build:
      context: ./src/py/base
      dockerfile: Dockerfile
      cache_from: ["${DOCKER_CACHE_REPO}/pybase:${DOCKER_CACHE_TAG}"]
      args:
        BUILDKIT_INLINE_CACHE: 1
```

## Bringing it all together: Make

Until now, we only discussed building one image. How do we manage building and keeping up to date thirty of them, especially if some images depend on others?

The answer is Make. Make is a famous UNIX tool which we can easily adapt to being a generic task execution engine.

Make works using file modification timestamps. Every Make rule describes how to build a file:

```makefile
output.txt: input_1.txt input_2.txt
	cat input_1.txt input_2.txt > output.txt
```

If the output is older than any of the inputs, Make rebuilds it.

But Make targets don't have to be actual executables. In our build system, Make targets build Docker images. Every target produces an empty sentinel file in the `.build` directory. Downstream targets can depend on these files. This lets us define dependency trees between images and factor some common functionality out into base images.

For example, we have a `pybase` image that contains common Python tooling. Python services inherit from it and have two stages:

  * Install required Python packages into a virtual environment
  * Copy the virtual environment into a lightweight `python:slim` image

Our Make tasks also depend on the files that go into the build context of the image. So, if the build context doesn't change, Make doesn't even need to run Docker.

This synergizes well with Git. Git checkouts only bump timestamps of files that have changed. After a checkout, Make will not rebuild images that didn't change between Git commits.

Finally, we use Make for other development tasks which are out of scope for this article:

  * Running integration tests
  * Starting up the whole stack, including the registry, the website and our [query REST API](/docs/splitgraph-cloud/publish-rest-api), in development
  * Running code formatters and other utilities

## Sample Makefile

This is a snippet from the actual (now almost 600 lines long) Makefile that we use to build and deploy the Splitgraph registry. It brings all these ideas together:

```makefile
# Tag images as $DOCKER_REPO/$image:$DOCKER_TAG
# We override this in CI to push to the GitLab registry directly.
export DOCKER_REPO ?= splitgraph
export DOCKER_TAG ?= development

# Cache from the same image in dev.
# In CI, we detect a suitable image to use as cache.
export DOCKER_CACHE_REPO ?= $(DOCKER_REPO)
export DOCKER_CACHE_TAG ?= $(DOCKER_TAG)

# Force to use buildkit for all images and for docker-compose to invoke
# Docker via CLI (otherwise buildkit isn't used for those images)
export DOCKER_BUILDKIT=1
export COMPOSE_DOCKER_CLI_BUILD=1

# BuildKit uses fancy output by default, making CI logs for multiple jobs
# unreadable.
export BUILDKIT_PROGRESS=plain

SHELL=/bin/bash

INTERACTIVE:=$(shell [ -t 0 ] && echo 1)
ifdef INTERACTIVE
	# For interactive invocations, interleave everything, since otherwise
	# shell targets (e.g. local_registry.shell) will hang forever not echoing
	# output.
	MAKEFLAGS := -j 4 --no-builtin-rules
else
	MAKEFLAGS := -j 4 --no-builtin-rules --output-sync=line
endif

# find invocation that ignores various files we do not want to include
# in the build context
FEXCL := -name __pycache__ -prune -o -not -name __pycache__ \\
		-a -not -path "*.egg-info*" \\
		-a -not -path "*htmlcov*" \\
		-a -not -path "*.mypy_cache*" \\
		-a -not -path "*.pytest_cache*" \\
		-a -not -path ".coverage" \\
		-a -not -path "*/node_modules/*" \\
		-a -not -path "*/.next/*" \\
		-a -not -path "*/libmdx/*" \\
		-print

# --- SAMPLE SERVICE ---

.PHONY: service_group.build

# Sample service that uses Docker Compose to build the image
.build/service_1: $(shell find src/py/service_1 $(FEXCL))
	docker-compose -f docker-compose.base.build.yml build service_1
	mkdir -p .build && touch $@

# Sample service that uses a build script
.build/service_2: $(shell find src/pgexts $(FEXCL))
	CACHE_FROM=${DOCKER_CACHE_REPO}/service_2:${DOCKER_CACHE_TAG} components/service_2/build.sh
	mkdir -p .build && touch $@

# PHONY Makefile task that builds both services.
service_group.build: .build/service_1 .build/service_2
```

## Conclusion

In this article, we talked about using Makefiles to manage builds of multiple Docker images. We think this approach strikes a good balance between abstracting away complexity and still being extensible enough to handle a diverse codebase.

In the future articles in this series, we'll discuss other aspects of how we test the Splitgraph registry and run it in production.

If you're interested in learning more about Splitgraph, you can check our [frequently asked questions](/docs/getting-started/frequently-asked-questions) section, follow our [quick start guide](/docs/getting-started/five-minute-demo) or visit our [website](https://www.splitgraph.com).


We begin our overview of Splitgraph's internal build infrastructure by discussing how we build Docker images in development and CI using Make and Docker BuildKit.

Splitgraph infrastructure, part 1: Using Make to build multiple Docker images efficiently


## Introduction

[Foreign data wrappers](https://www.postgresql.org/docs/12/ddl-foreign-data.html) are an advanced [PostgreSQL](http://postgresql.org/) feature. They allow you to link a remote database to PostgreSQL and represent it as a set of foreign tables that behave like normal ones.

Imagine being able to run SQL on a MongoDB collection or querying MySQL data from your PostgreSQL instance. Or, imagine running a `JOIN` between an SQLite file and an Oracle cluster without having to write and maintain an ETL job.

In this article, we'll discuss PostgreSQL foreign data wrappers and how [Splitgraph](https://www.splitgraph.com/) makes it much easier for anyone to use them.

## Using a custom FDW with Splitgraph

This example ([source code on GitHub](https://github.com/splitgraph/splitgraph/tree/master/examples/custom_fdw)) will show you how to write a custom [foreign data wrapper](https://www.splitgraph.com/docs/ingesting-data/foreign-data-wrappers/introduction) using Multicorn. [Multicorn](https://github.com/Segfault-Inc/Multicorn) is a PostgreSQL extension that makes it possible to write foreign data wrappers in Python.

We will then integrate it with Splitgraph's [`sgr mount`](https://www.splitgraph.com/docs/sgr/data-import-export/mount) command. This will make it queryable from the Splitgraph engine and allow Splitgraph to use it in [Splitfiles](https://www.splitgraph.com/docs/concepts/splitfiles).

In particular, we will be running a foreign data wrapper that queries the [Firebase Hacker News API](https://github.com/HackerNews/API). This will let us run SQL queries on the top, best and new stories from Hacker News, as well as Show HNs and Ask HNs.

This foreign data wrapper returns a list of rows from the remote database without performing any filtering. However, Multicorn also passes qualifiers and sort keys to the Python extension. This allows filtering to happen on the remote database as well, if it supports it.

To run this demo, you will need to have [Docker](https://www.docker.com/get-started) and [Docker Compose](https://docs.docker.com/compose/install/).

### Clone the repository

Clone Splitgraph and go to the [example project](https://github.com/splitgraph/splitgraph/tree/master/examples/custom_fdw):

```shell-session
$ git clone https://github.com/splitgraph/splitgraph.git
$ cd splitgraph/examples/custom_fdw
```

The Compose stack consists of:

  * a custom version of the [Splitgraph engine](https://www.splitgraph.com/docs/architecture/splitgraph-engine) (with the FDW installed);
  * a small Python container with the [`sgr` client](https://www.splitgraph.com/docs/architecture/sgr-client).

The source code for the foreign data wrapper is in [src/hn_fdw/fdw.py](https://github.com/splitgraph/splitgraph/blob/master/examples/custom_fdw/src/hn_fdw/fdw.py). The mount handler that exposes it to Splitgraph is in [src/hn_fdw/mount.py](https://github.com/splitgraph/splitgraph/blob/master/examples/custom_fdw/src/hn_fdw/mount.py).

### Start up the stack

Start the shell and initialize the engine. This will also build and start the engine container:

```shell-session
$ docker-compose run --rm sgr

$ sgr init
Initializing engine PostgresEngine LOCAL (sgr@engine:5432/splitgraph)...
Database splitgraph already exists, skipping
Ensuring the metadata schema at splitgraph_meta exists...
Running splitgraph_meta--0.0.1.sql
Running splitgraph_meta--0.0.1--0.0.2.sql
Running splitgraph_meta--0.0.2--0.0.3.sql
Installing Splitgraph API functions...
Installing CStore management functions...
Installing the audit trigger...
Engine PostgresEngine LOCAL (sgr@engine:5432/splitgraph) initialized.
```

Check the help for the HN mount handler:

```shell-session
$ sgr mount hackernews --help
Usage: sgr mount hackernews [OPTIONS] SCHEMA

      Mount a Hacker News story dataset using the Firebase API.

Options:
  -c, --connection TEXT       Connection string in the form
                              username:password@server:port

  -o, --handler-options TEXT  JSON-encoded dictionary of handler options:

                              endpoints: List of Firebase endpoints to mount,
                              mounted into the same tables as     the endpoint
                              name. Supported endpoints:
                              {top,new,best,ask,show,job}stories.

  --help                      Show this message and exit.
```

### Mount the data and run some queries

Now, actually "mount" the dataset. This will create a series of foreign tables. When a PostgreSQL client queries these tables, the foreign data wrapper will forward the queries to the Firebase API:

```shell-session
$ sgr mount hackernews hackernews
Mounting topstories...
Mounting newstories...
Mounting beststories...
Mounting askstories...
Mounting showstories...
Mounting jobstories...
```

You can now run ordinary SQL queries against these tables:

```shell-session
$ sgr sql -s hackernews "SELECT id, title, url, score FROM topstories LIMIT 10"

23648942  Amazon to pay $1B+ for Zoox                                             https://www.axios.com/report-amazon-to-pay-1-billion-for-self-driving-tech-firm-zoox-719d293b-3799-4315-a573-a226a58bb004.html                              55
23646158  When you type realty.com into Safari it takes you to realtor.com        https://www.facebook.com/story.php?story_fbid=10157161487396994&id=501751993                                                                               653
23648864  Turn recipe websites into plain text                                    https://plainoldrecipe.com/                                                                                                                                 30
23644253  Olympus quits camera business after 84 years                            https://www.bbc.com/news/technology-53165293                                                                                                               548
23648217  Boston bans use of facial recognition technology                        https://www.wbur.org/news/2020/06/23/boston-facial-recognition-ban                                                                                          51
23646953  Curl Wttr.in                                                            https://github.com/chubin/wttr.in                                                                                                                          190
23646164  Quora goes permanently remote-first                                     https://twitter.com/adamdangelo/status/1276210618786168833                                                                                                 267
23646395  Dwarf Fortress Creator Explains Its Complexity and Origins [video]      https://www.youtube.com/watch?v=VAhHkJQ3KgY                                                                                                                152
23645305  Blackballed by PayPal, Sci-Hub switches to Bitcoin                      https://www.coindesk.com/blackballed-by-paypal-scientific-paper-pirate-takes-bitcoin-donations                                                             479
23646028  The Acorn Archimedes was the first desktop to use the ARM architecture  https://spectrum.ieee.org/tech-talk/consumer-electronics/gadgets/why-wait-for-apple-try-out-the-original-arm-desktop-experience-today-with-a-raspberry-pi  111

```

PostgreSQL handles the actual query planning and filtering. The foreign data wrapper's job is to fetch records from the API. This setup supports any SQL syntax:

```shell-session
$ sgr sql -s hackernews "SELECT id, title, url, score FROM showstories ORDER BY score DESC LIMIT 5"
23643096  Show HN: Aviary.sh – A tiny Bash alternative to Ansible                           https://github.com/team-video/aviary.sh  235
23626167  Show HN: HN Deck – An alternative way to browse Hacker News                       https://hndeck.sagunshrestha.com/        110
23640069  Show HN: Sourceful – Crowdsourcing the best public Google docs                    https://sourceful.co.uk                  102
23627066  Show HN: Splitgraph - Build and share data with Postgres, inspired by Docker/Git  http://www.splitgraph.com                 79
23629125  Show HN: Deta – A cloud platform for building and deploying apps                  https://www.deta.sh/                      78
```

## What are foreign data wrappers?

The easiest way to describe foreign data wrappers is by considering UNIX files. A UNIX file isn't always pointing to a local file on your disk. It could be a file stored on NFS, in a remote S3 bucket or even a shim that lets you manipulate a device on your machine.

In UNIX, a file is a pretty strong abstraction that doesn't get broken. An IDE doesn't need to know that the file it's editing is on a remote filesystem. It can work with it by issuing the same ordinary read and write calls. Someone who writes a new filesystem doesn't need to make sure that it's compatible with all existing tools. Instead, they can write a driver that mounts the filesystem to a mountpoint.

PostgreSQL FDWs resemble mounting in that respect. Foreign tables mostly behave like normal tables and can receive the same `SELECT/INSERT/UPDATE/DELETE` queries. Behind the scenes, the wrapper's only job is to emit tuples.

## Why should I use FDWs?

Foreign data wrappers are a great alternative to ETL. Instead of loading data into your warehouse that you might never use, you can mount the remote database and query it on demand.

Because they integrate with the PostgreSQL query planner, FDWs have a decent performance. The FDW can push down qualifiers and even aggregations to the remote database. FDWs can report the rough costs of starting a scan and fetching a tuple back to the query planner. This lets the query planner choose, for example, whether running multiple small single scans or one big one is a better strategy for satisfying a JOIN.

It's straightforward to write your own FDW and the [PostgreSQL documentation](https://www.postgresql.org/docs/12/fdwhandler.html) covers it extensively. The [hello-world FDW](https://github.com/wikrsh/hello_fdw/) that returns "Hello, World" in a table is only a couple hundred lines of C code. If you prefer to focus on development simplicity, you can use Multicorn instead of raw C.

There are plenty of open-source foreign data wrappers available. They let you query other RDBMSs, NoSQL databases, CSV files or column stores. The [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Foreign_data_wrappers) lists some of them.

Using a foreign data wrapper for your database or data source gives you access to a huge ecosystem of software that works with PostgreSQL. We can almost call this a network effect. A new data source that is accessible via an FDW enhances everything that works with PostgreSQL.

## Why use FDWs with Splitgraph?

[Foreign data wrappers](https://www.splitgraph.com/docs/ingesting-data/foreign-data-wrappers/introduction) are one of the ways that Splitgraph aims to make data engineers' and data scientists' work easier.

Splitgraph's [`sgr mount`](https://www.splitgraph.com/docs/sgr/data-import-export/mount) command handles the boilerplate around FDW initialization. This command creates a schema on your engine with foreign tables. Any tool (like [DataGrip/Metabase/dbt](https://www.splitgraph.com/product/splitgraph/integrations)) can query them. Using [`sgr import`](https://www.splitgraph.com/docs/sgr/image-management-creation/import), you can snapshot them into a Splitgraph image. Finally, you can use a [Splitfile](https://www.splitgraph.com/docs/concepts/splitfiles) to build reproducible datasets that derive from the mounted database.

We ship with a few open-source FDWs as well as a couple of our own ones.

`postgres_fdw`, `mysql_fdw` and `mongo_fdw` let you access remote databases directly from Splitgraph. `cstore_fdw` is a columnar store extension that Splitgraph itself uses to store data.

We also have [a fork](https://github.com/splitgraph/Multicorn) of Multicorn that we modified to speed up [layered querying](https://www.splitgraph.com/docs/large-datasets/layered-querying). This lets you query any remote Splitgraph dataset without checking it out or having to download it completely. Like with any other foreign data wrapper, existing tools have no idea that this is happening.

Recently, we wrote a [foreign data wrapper](https://www.splitgraph.com/docs/ingesting-data/socrata) for the [Socrata open data platform](https://www.tylertech.com/products/socrata). It lets you explore any Socrata domain from your SQL client and even use Socrata datasets in Splitfiles.

We index over 40000 Socrata datasets in [our catalog](https://www.splitgraph.com/splitgraph/socrata). Each one is immediately queryable from the Splitgraph engine.

## Conclusion

Foreign data wrappers are a powerful feature than can provide an alternative to ETL jobs. Splitgraph has first-class support for foreign data wrappers that make them easier for anyone to use.

If you're interested in learning more about Splitgraph, you can check our [frequently asked questions](/docs/getting-started/frequently-asked-questions) section, follow our [quick start guide](/docs/getting-started/five-minute-demo) or visit our [website](https://www.splitgraph.com).


We talk about foreign data wrappers, a PostgreSQL feature that lets you query remote databases directly from your PostgreSQL instance. We also demonstrate how to integrate them with Splitgraph.

Foreign data wrappers: PostgreSQL's secret weapon?


## Introduction

A lot of advancements in software engineering and operations haven't yet made their way to data science and data engineering.

Data engineers manually craft and manage lots of distinct datasets, each of which needs special attention and care. This is similar to how software worked before we started treating more services like **cattle, not pets**.

In this article, we would like to expand on this analogy and talk about how our tool, [Splitgraph](https://www.splitgraph.com), can help apply it to data workflows.

## Pets and cattle in software

In software, a [**pet server**](http://cloudscaling.com/blog/cloud-computing/the-history-of-pets-vs-cattle/) is indispensable. System administrators build and manage them manually and carefully look after them. If such a server goes down, it's a big issue.

A **cattle server** is disposable. Automated processes provision them from filesystem images in a matter of seconds. If a cattle server fails, cluster management systems remove it and spin up another one.

There's one rule to a cattle server: Is it a big deal if it goes away? Can you rebuild and re-provision it from scratch?

## Data as cattle?

Let's try to apply the same rule to identifying cattle datasets. If it's not a big deal that a dataset gets deleted, it's a cattle dataset. Ultimately, a cattle dataset must be able to be rebuilt from some known state.

We think **more datasets should be like cattle, not pets**. This model, complete with similar battle-tested workflows and tools from software engineering, can help data engineers and data scientists in their work.

So, what does this mean exactly and what are the implications of this idea?

### Declarative definition files

Engineers build and provision services using Dockerfiles, Ansible playbooks or Terraform configuration. These are self-contained and version-controlled **declarative definition files**.

We should have them for data as well. These files would explicitly list every dependency that the dataset needs and declare how to build it.

This lets data scientists and engineers experiment with datasets:

  * Rebuild them against data from a different vendor;
  * See the impact on the final model of excluding some dataset from a pipeline;
  * If a source dataset needs offboarding, it's easy to see which data it affected and what needs to be rebuilt.

In Splitgraph, [**Splitfiles**](/docs/concepts/splitfiles) let you build data images using familiar SQL. They provide Dockerfile-like caching and allow referencing other Splitgraph datasets and even other databases directly from the Splitfile.

Every dataset built by a Splitfile has [provenance tracking](/docs/working-with-data/inspecting-provenance). This lets you know exactly where each dataset came from and how to rebuild it.

### Version control and CI

Developers use **version control** for all code and configuration that goes into a service. Data should be version controlled, too. Datasets are not append-only and their history often changes. An engineer or a researcher should be able to ["diff"](/docs/sgr/image-information/diff) two versions of a dataset and see where they changed.

However, collaboration on data at the row level via pull requests is of limited use. Developers don't collaborate on a Docker image or the final binary. They collaborate on the source code. Changes trigger automated **continuous integration** systems that run the actual build and test pipelines.

ETL pipelines should be more like CI pipelines.

We can treat a CI pipeline as a self-contained function that takes in a part of the source tree and outputs an "artifact". This is a useful abstraction for data, too. It lets anyone replay an ETL pipeline at any time. It also enables more robust integration testing. It decreases iteration times and brings development and production closer.

[Splitfiles](/docs/concepts/splitfiles) are self-contained and the Splitfile executor can run them on any machine. It automatically pulls required datasets from the registry. This makes Splitfiles easy to integrate into your data CI pipeline.

### Portability

Software artifacts like filesystem images and statically linked binaries are **portable**.

  * Anybody can pull an image and run it locally for development or debugging;
  * There are libraries of public images that a developer can integrate in their stack in a matter of minutes;
  * In production, runtimes like Docker and Nomad can remap ports and filesystem paths. This lets multiple services co-exist on the same server.

Datasets should also be portable, not bound to a specific schema or a specific database. Anyone should be able to pull a dataset and query it or "mount" it to a certain location. This kind of standardization will let libraries of public datasets to grow, ready for researchers to reference and join with private data.

Splitgraph achieves this in several ways.

With [layered querying](/docs/large-datasets/layered-querying), a Splitgraph engine can query a remote dataset with a limited amount of local cache.

[Foreign data wrappers](/docs/ingesting-data/foreign-data-wrappers/introduction) let Splitgraph "mount" a full remote database or a dataset on the engine. This creates a "shim" schema locally that any client can query.

Any Splitgraph engine can act as a [remote](/docs/architecture/splitgraph-remotes). This enables **decentralized data sharing**.

### Not getting in the way

Docker and Git would be of limited use if they required developers to make their software run with a new kernel or a new filesystem. Good tools [**don't get in the way**](/blog/datagrip). Users can adopt them incrementally without forcing huge workflow changes.

Anything that manages data must let existing applications integrate with it without code changes. Doing it otherwise will mean that adoption will be an uphill battle.

Splitgraph is compatible with anything that uses PostgreSQL. It gives superpowers to tools like [DataGrip](/docs/integrating-splitgraph/datagrip), [dbt](/docs/integrating-splitgraph/dbt), [Metabase](/docs/integrating-splitgraph/metabase) or [PostGIS](/docs/integrating-splitgraph/postgis). We collect the most interesting integrations on our [documentation page](/product/splitgraph/integrations).

## Conclusion

We should treat more datasets like cattle, not pets. We built Splitgraph in line with this philosophy.

If you're interested in learning more about Splitgraph, you can check our [frequently asked questions](/docs/getting-started/frequently-asked-questions) section, follow our [quick start guide](/docs/getting-started/five-minute-demo) or visit our [website](https://www.splitgraph.com).


We talk about the "pets versus cattle" idea in software and discuss how Splitgraph helps to apply it to data science and data engineering.

Treat your datasets like cattle, not pets


[Splitgraph](https://www.splitgraph.com) is a tool that makes it easier for data engineers and data scientists to build, query and share datasets. It uses [PostgreSQL](https://www.postgresql.org/) as a foundation and is compatible with anything that works with PostgreSQL.

In this article, we'll discuss a philosophy of not breaking existing abstractions that we think explains the success of tools like Docker and Git. We'll also talk about how we applied it when we designed Splitgraph and how it let us launch with first-class support for tools like [DataGrip](/docs/integrating-splitgraph/datagrip), [dbt](/docs/integrating-splitgraph/dbt), [Metabase](/docs/integrating-splitgraph/metabase) or [PostGIS](/docs/integrating-splitgraph/postgis).

## Integrating DataGrip with Splitgraph

Shortly before we went public with Splitgraph, we were figuring out any interesting integrations we could launch it with.

Here's a play-by-play of us realizing that we didn't need to do anything to get [JetBrains DataGrip](/docs/integrating-splitgraph/datagrip) to work with Splitgraph's [layered querying](/docs/large-datasets/layered-querying) or PostGIS-enabled Splitgraph images:

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20202606-datagrip/01-splitgraph-datagrip.png)

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20202606-datagrip/02-layered-querying.png)

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20202606-datagrip/03-geodata.png)

This was kind of anticlimactic. Integrating [other clients](/docs/integrating-splitgraph/other-clients) took us about half a day. Building an example of Splitgraph [working with dbt](/docs/integrating-splitgraph/dbt) took one day.

We think that this is due to a big design decision we made in Splitgraph's early days.

## Isn't it cool to write a database?

When you want to build a feature for working with data, you have two options.

**The first option** is that you can build a brand new database from scratch and reimplement everything that makes a database a database. Most of these things won't be related to your core feature set that makes your product different. But, by building from the ground up, you might be able to squeeze out that extra bit of performance or make big unconstrained architectural decisions.

Dozens of new database systems get released every year. Some have fast columnar storage. Some do versioning. Some work with timeseries data. Some operate on live data pipelines. Writing a database is indeed very cool.

Imagine writing your own query planner or a parser. Figuring out a format to store the data on disk. Coming up with some indexes you could add to your database. Transactions are also in the pipeline. You'd use MVCC or something. Durability, eventually. You should try to be compatible with a wire protocol as well.

This is fun and technically challenging work. Engineers love building things and "built a database" is a great entry to put on your CV.

But then, the problems start.

  * The database segfaults if some application calls something in a weird way.
  * People ask you to implement boring things, like support for Kerberos, or replication, or that one specific JOIN that they rely on, or stored procedures, or triggers.
  * [Somebody](https://jepsen.io/) fires a bunch of queries at your database and finds corner cases that completely break it.

Is the tradeoff of adding all these features and bringing your database to feature parity with existing offerings worth it?

## Isn't it cool to not write a database?

Splitgraph chose the **second option**.

From the very beginning, we knew that we didn't have the staff, the time or the funds to build a new database. Besides, building a new database felt contrary to everything we knew about computers and existing successful tooling. After all, ["we do not break userspace"](https://unix.stackexchange.com/a/235532):

  * Git adds versioning to files by working on top of an existing abstraction (the filesystem) and enhancing it. It doesn't force users to use a new filesystem.
  * Docker containerizes applications by using the tools provided by the kernel itself. It doesn't force users to use a new kernel.

IDEs, editors, compilers and other applications don't need to be aware of these tools to benefit from them. They **enhance existing abstractions instead of breaking them**.

## Enhance abstractions without breaking them

For Splitgraph, we chose PostgreSQL as a foundation to build on. A Splitgraph engine is a PostgreSQL instance with some extra custom extensions. Most of Splitgraph's operations use the SQL standard. After you [check out](/docs/sgr/image-management-creation/checkout) out a Splitgraph image, it's just a set of ordinary tables that you can use all features of PostgreSQL on.

When we realized that there should be a way to avoid the checkout step, we wrote a [layered querying](/docs/large-datasets/layered-querying) plugin. Layered querying is a [foreign data wrapper](/docs/ingesting-data/foreign-data-wrappers/introduction), which is a first-class PostgreSQL feature. The database takes care of all query parsing for us. We only need to figure out how to give Splitgraph tuples to it fast enough. We could now experiment with hotswapping data into Splitgraph on the fly or speeding queries up. And, we would still support any PostgreSQL statement, past, present or future.

When we wanted to improve Splitgraph's data pushes and pulls, we started storing Splitgraph [objects](/docs/concepts/objects) in [`cstore_fdw`](https://github.com/citusdata/cstore_fdw) files. It's a columnar store that offers low IO load and fast performance for OLAP workloads. Splitgraph can query `cstore_fdw` files directly without having to load them into the database. This meant that we could now store them on S3 and swap them in and out of local cache when we needed to use them.

When we got ambitious and wanted to try [PostGIS](/docs/integrating-splitgraph/postgis), it just worked. Our [REST API](https://data.splitgraph.com/splitgraph/london_wards/latest/-/rest/city_merged_2018?district=eq.City%20of%20Westminster), powered by [PostgREST](/docs/integrating-splitgraph/postgrest), would return geospatial data as GeoJSON. `cstore_fdw` is compatible with custom PostgreSQL types, so it would store `geometry` columns without problems.

## Conclusion

DataGrip, dbt, Metabase, PostGIS and anything else in PostgreSQL's vast ecosystem of clients, tools and extensions aren't (yet!) aware of Splitgraph.

Despite that, it gives superpowers to all of them.

Imagine building a [dbt model](/docs/integrating-splitgraph/dbt) on versioned data to perform what-if analyses. Or, imagine using [Metabase](/docs/integrating-splitgraph/metabase) to plot data from remote datasets with a small local cache space.

Splitgraph enhances existing abstractions without breaking them. All existing PostgreSQL clients, tools and extensions are compatible with Splitgraph. We collect examples of the [most interesting integrations](/product/splitgraph/integrations) in our documentation.

If you're interested in learning more about Splitgraph, you can check our [frequently asked questions](/docs/getting-started/frequently-asked-questions) section, follow our [quick start guide](/docs/getting-started/five-minute-demo) or visit our [website](https://www.splitgraph.com).


We discuss a philosophy of not breaking existing abstractions that we think explains the success of tools like Docker and Git and how we applied it to Splitgraph, helping us launch with multiple integrations.

It took 10 minutes to add support for DataGrip to Splitgraph


## Introduction

Splitgraph is a tool that makes working with data as easy as working with code. An oft-repeated trope is that data scientists spend 80% of their time cleaning data. This may not be strictly true, but certainly there is significant overhead for every deliverable. Software engineers have tools like Git and Docker that allow them to iterate and confidently deploy their code to production. For data scientists, the workflows in the experimentation phase are not nearly as productive. Too much effort is spent sourcing, cleaning and merging data together. And when it’s time to deploy a deliverable, it’s often impossible to keep it up to date without some sort of manual and brittle effort.

In a lot of ways, the domain of data science resembles system administration prior to the age of dev-ops and containerization. It used to be that every server was its own special snowflake, with brittle scripts holding it together and all sorts of failure modes when deploying new code. But “infrastructure-as-code” and technologies like Docker changed that for the better. We think that data science has similar problems. Whether you’re deploying ML models, generating reports, or building dashboards, maintaining them seems to require a lot of effort, and it seems like you’re repeating yourself every time you start a new project.

We’re building Splitgraph to address these problems. It’s inspired by Docker and Git, so its semantics and conventions should feel somewhat familiar. And it’s powered by Postgres, so it will work with nearly all your existing tools.

## Where does 80% of the time go?

When starry eyed graduates and machine learning PhDs join the workforce as “data scientists,” they dream of spending all day training neural networks on GPU clusters, and applying Gaussian processes and support vector machines to their company’s databases to unlock new sources of business value. Turns out, there’s a lot more grueling, repetitive work to do before any of that can happen. Questions start to arise, like:

- Which of these dozens of databases in the company warehouse has the data I need?
- How can I get this data into the form I need it?
- Where can I find some public data to augment this proprietary information?

When they finally manage to create something useful, a whole new set of questions arises:

- Where can I deploy my version of this data?
- How can my teammates pull my data and iterate on it?
- When the upstream data sources change, how do I keep my deliverable up to date?

Sometimes, the data scientist can’t even answer these questions, and needs to pass it off to a data _engineer_ instead, sort of like how developers used to need to send their code to a deployment team.

## What do we like about software engineering workflows?

Software engineering has a lot of concepts that have made software more pleasant to build and maintain. Years of pain have led developers to coalesce around common workflows for versioning, building, packaging and deploying code:

- **Versioning and revision control** allows for rapid experimentation and collaboration. Engineers can track and revert every change to code.
- **Package managers** put millions of libraries at your fingertips, and integrating them into your project is as simple as running a command
- Docker helps build your code into **self-contained images** in a common format that you can drop into any stack
- With “infrastructure as code,” software engineers can control the deployment, provisioning and scaling process with **repeatable, version-controlled definition files**

Finally, good tools that implement all of this **enhance existing abstractions without breaking them**. You don’t need to adopt a new filesystem or use a specialized IDE to version your code with Git. You can build Docker images without changing your code to work in Docker or recompiling it to use some special Docker system calls. Git and Docker are good tools because they _stay out of the way_ while still adding value.

## Introducing Splitgraph

Splitgraph aims to bring these ideas and concepts into data science.

Splitgraph makes it easy to package datasets into [**self-contained data images**](/docs/concepts/images) that you can combine, query, extend and share
with other Splitgraph instances.

[**Splitfiles**](/docs/concepts/splitfiles) give you a declarative language, inspired by Dockerfiles, for expressing data transformations in ordinary SQL familiar to any researcher or business analyst. You can reference other images, or even other databases, [with a simple `JOIN`](/docs/splitfiles/splitfile-sql).

When you build data with Splitfiles, you get [**provenance tracking**](/docs/working-with-data/inspecting-provenance) of the resulting data: it's possible to find out what sources went into every dataset and know when
to rebuild it if the sources ever change. You can easily integrate Splitgraph into your existing CI pipelines, to keep your data up-to-date and stay on top of changes to upstream sources.

Splitgraph images are also **version-controlled**, and you can manipulate them with Git-like operations
through a CLI. You can “check out” any image into a PostgreSQL schema and interact with it using
any PostgreSQL client. Splitgraph will capture your changes to the data, and then you can commit them as **delta-compressed changesets** that you can package into new images.

Finally, Splitgraph isn't opinionated and **doesn't break existing abstractions**. To any existing PostgreSQL
application, Splitgraph images are just another database. We have carefully designed Splitgraph to not break the
abstraction of a PostgreSQL table and wire protocol, because doing otherwise would mean throwing
away a vast existing ecosystem of applications, users, libraries and extensions. This means that
a lot of **tools that work with PostgreSQL work with Splitgraph out of the box**.

We collect examples of the most interesting ones on the [integrations page](/product/splitgraph/integrations).

## Conclusion

When we started building Splitgraph in 2018, there was nothing around that combined all these concepts
into one product. Things are beginning to change and a lot of interesting tools are getting released to help data work. However,
we still don't feel like any of them completely satisfy our vision. We list applications and services that
are most similar to Splitgraph on our [Frequently Asked Questions](/docs/getting-started/frequently-asked-questions#why-not-just-use) page.

Over the next few weeks, we will be publishing a series of posts that dive deeper into our philosophy,
how Splitgraph works internally and some example use cases for Splitgraph.

In the meantime, Splitgraph is fully functional and ready to use. Head on to our
[documentation](/docs/getting-started/introduction) page for installation instructions, check out
[possible use cases](/product/splitgraph/use-cases) or take a look at our [integrations page](/product/splitgraph/integrations) to see how Splitgraph can help you.


Announcing Splitgraph, a data versioning and management system that allows you to work with data like you work with code.

Querying 40,000+ datasets with SQL

Splitgraph infrastructure, part 2: Integration testing with Docker Compose

Splitgraph infrastructure, part 1: Using Make to build multiple Docker images efficiently

Foreign data wrappers: PostgreSQL's secret weapon?

Treat your datasets like cattle, not pets

It took 10 minutes to add support for DataGrip to Splitgraph

Welcome to Splitgraph

Product

Support

Company

Splitgraph

Querying 40,000+ datasets with SQL

Splitgraph infrastructure, part 2: Integration testing with Docker Compose

Splitgraph infrastructure, part 1: Using Make to build multiple Docker images efficiently

Foreign data wrappers: PostgreSQL's secret weapon?

Treat your datasets like cattle, not pets

It took 10 minutes to add support for DataGrip to Splitgraph

Welcome to Splitgraph

Product

Support

Company

Community

Splitgraph