
Serverless and edge computing have allowed application developers to bring their applications closer to the end user.

Instead of maintaining a group of servers in a single location, developers can let companies like Cloudflare, Fastly or Akamai handle their content delivery.

With [Function as a service](https://en.wikipedia.org/wiki/Function_as_a_service), companies pay for what they use. They can avoid having to provision a server that stays idle most of the time.

In this article, we want to talk about these trends and how we can apply them to databases. We'll also talk about our decision to make the API for our Splitgraph registry work over a public SQL connection. We'll use this experience to propose the idea of a **data delivery network**.

## How content delivery networks work

Content delivery networks provide a straightforward way to scale a read-only HTTP layer. They use existing HTTP cache semantics like the Cache-Control header. The developer only needs to point their DNS records to use the CDN's nameservers. The CDN handles everything else for them. It has points of presence around the world and peering agreements with other ISPs. It can selectively cache data, handle DDoS protection and offer extra services on top.

The value proposition behind edge computing is simple. For a lot of companies, scaling compute is not their core competency. They can spend time and money provisioning servers and configuring something like Varnish. Or, they can use services that will handle scaling and caching for them.

However, applications still need to run SQL queries. A CDN doesn't completely help an application that performs client-side rendering. The database becomes the next performance bottleneck in scaling a service.

There are many ways to scale a database, for example, replication or sharding. But again, this requires specialist knowledge about a database that is easy to get wrong.

## Why do you need a backend, anyway?

Let's change gears and consider a classic Web application. It consists of the frontend, the backend and the database.

There are several purposes that a backend serves:

  * **Business logic**. The backend converts higher level API calls into low-level SQL queries. It prepares data for presentation and writes it back when needed.
  * **Authorization**. The backend acts as a security barrier, validating API calls. This is necessary because the frontend is running on the user's machine: the client is not trusted.
  * **Multiplexing**. A database connection has a larger overhead than an HTTP connection. A backend can shunt hundreds of simultaneous clients over to a few database connections.

### Alternatives to CRUD services

One big issue with writing RESTful backends is that there's a lot of boilerplate. The programmer has to write very similar code to handle every action. They have to care of validation, typechecking and handling edge cases.

Libraries like [PostgREST](https://postgrest.org/en/latest/) and [PostGraphile](https://www.graphile.org/postgraphile/) have helped developers decrease iteration times. They introspect database schemas and generate REST and GraphQL APIs for them.

PostgREST and PostGraphile perform their authorization using database methods like [row level security](https://postgrest.org/en/v7.0.0/auth.html). In essence, they decrease the size of the ["trusted computing base"](https://en.wikipedia.org/wiki/Trusted_computing_base).

Often, services that use these kinds of tools don't even have a separate backend. Client side code can call the automatically generated GraphQL/REST API directly.

### Splitgraph's architecture

The database can perform a lot of work that the backend does more quickly and more efficiently.

We use this idea in the API for the Splitgraph registry that allows you to push and pull [data images](/docs/concepts/images). A [Splitgraph client](/docs/sgr-advanced/architecture/sgr-client) can access it over a normal PostgreSQL connection to `postgresql://data.splitgraph.com:5432/sgregistry`.

Our API implements all **business logic** as PostgreSQL functions. This has a few immediate advantages:

  * Lets PostgreSQL precompile them
  * Avoids an extra hop from the backend, decreasing latency
  * Makes basic validation and type checking trivial. It's not possible to call a function with a wrong number of arguments or different types.

For more complex logic, we wrote it in higher-level languages like [PL/Python](https://www.postgresql.org/docs/current/plpython.html) or PL/Lua. PostgreSQL even supports languages like C or JavaScript.

We solved the problem of **multiplexing and authorization** by adding [PgBouncer](https://www.pgbouncer.org/), a connection pooler, in front of our database. Our fork of PgBouncer injects a signed cookie into every transaction as a local variable. Downstream procedures validate this cookie for authentication and authorization. This lets us decouple PostgreSQL users from application users. Multiple inbound sessions can use the same connection.

Our fork of PgBouncer even inspects queries on the fly and filters them. This makes sure that the client can only call Splitgraph SQL API functions.

For the web frontend at [www.splitgraph.com](https://www.splitgraph.com/), we use PostGraphile. Besides not having to write an extra API server, it lets us generate TypeScript client code.

## Data delivery network

We can apply these ideas and concepts to the problem of building a **"data delivery network"**. Such a network would completely abstract away all the issues around making sure that data is available at the edge. It can also provide plenty of other useful services.

Here's a quick sketch of what a DDN's administration interface would look like:

![](https://raw.githubusercontent.com/splitgraph/splitgraph.com/master/content/blog/images/20200713-data-delivery-network/admin-panel.png)

To use a DDN, a developer would create a read-only account on their database and give the DDN the credentials. It will then make a few services available:

The DDN will create an **SQL endpoint**. Any existing SQL client or application will be able to connect to it and run queries.

Besides SQL, the DDN will also be able to introspect the origin database and provide **REST and GraphQL API endpoints**. A client, running in the user's web browser, can use these endpoints instead of a backend server.

The DDN will be able to **cache** read-only SQL transactions with configurable policies. It will only forward the query to the origin database if there's a cache miss or expiry.

The client code doesn't need to be trusted. The DDN can intercept and **firewall** queries or **rate limit** them. To simplify migrations, the DDN can **rewrite** queries on the fly before forwarding them.

The DDN's work doesn't need to stop at handling queries. It can also manage **data imports and exports**. For example, it can make data from other services available to clients. Or, it can export data to Google Sheets or a data warehouse.

In the case of Splitgraph, we envision you being able to even run a `JOIN` across a public Splitgraph image and your private data.

## Conclusion

The database is the next frontier of serverless and edge computing. One of Splitgraph's goals is building a data delivery network to handle these problems.

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/sgr-advanced/getting-started/five-minute-demo) or visit our [website](https://www.splitgraph.com).


We discuss the trends of serverless and edge computing, talk about why our SQL server is open to the public and propose the idea of a data delivery network.

Throwing away the backend: Towards a data delivery network


## 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/add-data/data-sources/supported-modes). 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/sgr-advanced/ingesting-data/socrata#using-metabase-to-join-and-plot-data-from-multiple-data-portals). Or you can use Splitfiles to [build reproducible datasets](/docs/sgr-advanced/ingesting-data/socrata#splitfile) from them. And every external repository includes an [auto-generated PostgREST API](/docs/query/postgrest).

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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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


## 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/sgr/tree/master/examples/custom_fdw)) will show you how to write a custom [foreign data wrapper](https://www.splitgraph.com/docs/sgr-advanced/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/sgr-advanced/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/sgr/tree/master/examples/custom_fdw):

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

The Compose stack consists of:

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

The source code for the foreign data wrapper is in [src/hn_fdw/fdw.py](https://github.com/splitgraph/sgr/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/sgr/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/large-datasets/layered-querying), a Splitgraph engine can query a remote dataset with a limited amount of local cache.

[Foreign data wrappers](/docs/sgr-advanced/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/sgr-advanced/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/splitgraph-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/sgr-advanced/integrating-splitgraph/datagrip), [dbt](/docs/sgr-advanced/integrating-splitgraph/dbt), [Metabase](/docs/sgr-advanced/integrating-splitgraph/metabase) or [PostGIS](/docs/sgr-advanced/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/sgr-advanced/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/sgr-advanced/integrating-splitgraph/datagrip), [dbt](/docs/sgr-advanced/integrating-splitgraph/dbt), [Metabase](/docs/sgr-advanced/integrating-splitgraph/metabase) or [PostGIS](/docs/sgr-advanced/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/sgr-advanced/integrating-splitgraph/datagrip) to work with Splitgraph's [layered querying](/docs/sgr-advanced/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/sgr-advanced/integrating-splitgraph/other-clients) took us about half a day. Building an example of Splitgraph [working with dbt](/docs/sgr-advanced/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) 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/sgr-advanced/large-datasets/layered-querying) plugin. Layered querying is a [foreign data wrapper](/docs/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/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/sgr-advanced/integrating-splitgraph/dbt) on versioned data to perform what-if analyses. Or, imagine using [Metabase](/docs/sgr-advanced/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/sgr-advanced/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.

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

philosophy

Throwing away the backend: Towards a data delivery network

Querying 40,000+ datasets with SQL

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

Product

Support

Company

Splitgraph

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

philosophy

Throwing away the backend: Towards a data delivery network

Querying 40,000+ datasets with SQL

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

Product

Support

Company

Community

Splitgraph