**Note (April 2023): In the meantime we've [migrated](/blog/seafowl-delta-storage-layer) Seafowl's storage layer to use
the Delta Lake protocol. While the high-level functionality and feature-set remain the same, some implementation details
in this blog post are no longer up to date. **

The primary specialty of Seafowl is data-crunching over large slowly-changing data sets at the edge. It does so by leveraging the
standard HTTP cache semantics [natively](/blog/seafowl-sql-cdn) in the provided query execution endpoint(s).

The query result then becomes a regular static HTTP asset, eligible for caching at various locations (from the browser to the CDN), thus
saving on the compute time. Seafowl also utilizes the usual HTTP cache revalidation mechanism, so that when the underlying data
does change, the query is re-run in a performant manner (thanks to Rust and [Apache DataFusion](https://arrow.apache.org/datafusion/)).
In a sense, it can be said that Seafowl is a fast, lightweight and self-contained version of the [Splitgraph DDN](/blog/data-delivery-network-launch)

Despite being an analytical query engine at it's core, Seafowl (like the DDN) also supports writing data using DDL
(`CREATE`, `ALTER` and `DROP`) as well as DML (`INSERT`, `UPDATE` and `DELETE`) SQL statements. In this blog post we'll
take a look at the interplay between such statements and the way Seafowl chunks and stores the data in partition objects.
We'll also describe how table versioning and query time travel concepts naturally arise in this context.

## Table version and partition dynamics

To start off, let's create a new table with some dummy data after [spinning up](/seafowl/docs/guides/installing) a Seafowl instance.
There are a number of options for getting data into Seafowl, such as pointing a table to a source table from a
[remote database](/seafowl/docs/guides/remote-tables), or by pointing a table to a [local or remote](/seafowl/docs/reference/ddl-queries#create-external-table)
CSV/Parquet file.

For this example though let's use the [upload endpoint](/seafowl/docs/guides/uploading-csv-parquet). It is intended
for when you can't or don't want to place your data file on the same machine where Seafowl is running, nor spin up
a one-off server for serving it over HTTP:

```bash
$ $ cat > some_numbers.csv << EOF
> name,number
> one,1
> two,2
> EOF
$ curl \
 -H "Authorization: Bearer write_password" \
 -F "data=@some_numbers.csv" \
 http://localhost:8080/upload/some/numbers
done
```

Even though the primary speciality of Seafowl is querying over HTTP, for the sake of conciseness we'll run the rest of the
queries in this blog post via a client. Specifically, we'll exploit the fact that Seafowl can also expose a [PostgreSQL-compatible endpoint](/seafowl/docs/guides/querying-postgresql)
and use `psql`:

```sql
seafowl=> SELECT * FROM some.numbers;
 name | number
------+--------
 one | 1
 two | 2
(2 rows)

seafowl=> SELECT column_name, data_type FROM information_schema.columns
seafowl-> WHERE table_name = 'numbers';
 column_name | data_type
-------------+-----------
 name | Utf8
 number | Int64
(2 rows)
```

No surprises there; the data was uploaded into a table and the schema got introspected since we haven't specified one[^1].
Since we've just created it, our table so far only has a single version and a single partition, which in Seafowl are stored
as Parquet files. To verify this we can utilize a couple of [system tables](/seafowl/docs/reference/database-structure#system-schema)
that Seafowl provides, providing insights into the table metadata:

```sql
seafowl=> SELECT * FROM system.table_versions;
 table_schema | table_name | table_version_id | creation_time
--------------+------------+------------------+---------------------
 some | numbers | 1 | 2022-11-18 13:11:24
(1 row)

seafowl=> SELECT table_version_id, table_partition_id, object_storage_id, row_count FROM system.table_partitions;
 table_version_id | table_partition_id | object_storage_id | row_count
------------------+--------------------+--------------------------------------------------------------------------+-----------
 1 | 1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
(1 row)
```

Given that we haven't specified a custom object store, Seafowl defaults to using the local file system to store the objects in the `seafowl-data` folder:

```bash
$ ls -l seafowl-data/ | grep .parquet
-rw------- 1 ubuntu ubuntu 854 Nov 18 13:11 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet
```

Our table consists of only one partition file since our example data is tiny; had the uploaded file been
sufficiently large[^2], Seafowl would have partitioned it into multiple Parquet files.

#### Modifying the data with the `INSERT` command

When it comes to adding new data via an `INSERT` statement Seafowl will create new partition(s) from the input, and link-up
with existing partitions of the referenced table to make a new version of it. Hence, each separate invocation generates at least one
partition[^3]:

```sql
seafowl=> INSERT INTO some.numbers VALUES ('three', 3), ('four', 4), ('five', 5);
...
seafowl=> INSERT INTO some.numbers VALUES ('one', 1), ('two', 2);
seafowl=> SELECT * FROM some.numbers;
 name | number
-------+--------
 one | 1
 two | 2
 one | 1
 two | 2
 three | 3
 four | 4
 five | 5
(7 rows)
```

The combined effect of the preceding two `INSERT` statements is the creation of two new table versions (2 and 3). Each one
inherits all the partitions from the previous version:

```sql
seafowl=> SELECT * FROM system.table_versions;
 table_schema | table_name | table_version_id | creation_time
--------------+------------+------------------+---------------------
 some | numbers | 1 | 2022-11-18 13:11:24
 some | numbers | 2 | 2022-11-18 13:12:58
 some | numbers | 3 | 2022-11-18 13:13:35
(3 rows)

seafowl=> SELECT table_version_id, table_partition_id, object_storage_id, row_count FROM system.table_partitions;
 table_version_id | table_partition_id | object_storage_id | row_count
------------------+--------------------+--------------------------------------------------------------------------+-----------
 1 | 1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
 2 | 1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
 2 | 2 | b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet | 3
 3 | 1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
 3 | 2 | b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet | 3
 3 | 3 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
(6 rows)
```

However, note that the last `INSERT` added the same data that was used to create the table initially via the upload endpoint.
Therefore, even though our table now has three logical partitions, two of them (`table_partition_id` 1 and 3) are
physically identical, pointing to the same file:

```bash
$ ls -l seafowl-data/ | grep .parquet
-rw------- 1 ubuntu ubuntu 854 Nov 18 13:13 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet
-rw------- 1 ubuntu ubuntu 883 Nov 18 13:12 b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet
```

This example illustrates how this partitioning scheme naturally lends itself to supporting time travel querying that we'll discuss shortly.
In particular, what makes this proposition very appealing is that by storing the objects for the latest/current table version,
we're also able to re-construct prior table versions with minimal additional storage overhead due to partition re-use.
In this particular example three different table versions can be obtained from just two objects.

#### Modifying the data with the `UPDATE` and `DELETE` command

Unlike `INSERT`, the two remaining DML statements will in general replace some of the existing partitions with new ones.
The specifics depend on the exact qualifier used in the `WHERE` clause, as well as the statement type:

```sql
seafowl=> DELETE FROM some.numbers WHERE number = 3 OR number = 5;
...
seafowl=> UPDATE some.numbers SET name = 'two', number = 2 WHERE number = 1;
seafowl=> SELECT * FROM some.numbers;
 name | number
------+--------
 four | 4
 two | 2
 two | 2
 two | 2
 two | 2
(5 rows)

seafowl=> SELECT * FROM system.table_versions where table_version_id > 3;
 table_schema | table_name | table_version_id | creation_time
--------------+------------+------------------+---------------------
 some | numbers | 4 | 2022-11-18 13:15:18
 some | numbers | 5 | 2022-11-18 13:15:53
(2 rows)
```

Here, the `DELETE` statement specifically targets the 2nd partition (created by the first `INSERT` statement), while leaving the
other two partitions (which are actually identical) unchanged and eligible for re-use. The targeted partition is processed, and the rows matching
the qualifier selection are filtered out, thus leading to a new, in this case smaller, partition (or no partition at all if all rows are filtered out).
When there are multiple partitions that match the selection, for the sake of optimal partition size, we bundle them together, and then perform
required row filtration followed by potential re-chunking of partitions.

The `UPDATE` on the other hand leaves the total number of rows unchanged, but is also subject to merging of partitions
that need to be processed. In the above case, `UPDATE` targets the two "twin" partitions, changes two out of four rows,
and merges the result in one final partition:

```sql
seafowl=> SELECT table_version_id, table_partition_id, object_storage_id, row_count
seafowl-> FROM system.table_partitions WHERE table_version_id > 3;
 table_version_id | table_partition_id | object_storage_id | row_count
------------------+--------------------+--------------------------------------------------------------------------+-----------
 4 | 1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
 4 | 3 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet | 2
 4 | 4 | 78ae77c39b4eac1a80d7238af473af82a8038b41aebb908c890dd78323a48d65.parquet | 1
 5 | 4 | 78ae77c39b4eac1a80d7238af473af82a8038b41aebb908c890dd78323a48d65.parquet | 1
 5 | 5 | da56d46a18b13f3d67c2816f107a837d41c760c8d67afcc015c334375f5a5e87.parquet | 4
(5 rows)
```

As a final result we now have a total of four partition objects:

```bash
$ ls -l seafowl-data/ | grep .parquet
-rw------- 1 ubuntu ubuntu 854 Nov 18 13:13 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet
-rw------- 1 ubuntu ubuntu 844 Nov 18 13:15 78ae77c39b4eac1a80d7238af473af82a8038b41aebb908c890dd78323a48d65.parquet
-rw------- 1 ubuntu ubuntu 883 Nov 18 13:12 b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet
-rw------- 1 ubuntu ubuntu 835 Nov 18 13:15 da56d46a18b13f3d67c2816f107a837d41c760c8d67afcc015c334375f5a5e87.parquet
```

This partition-scoping mechanism for `UPDATE`/`DELETE` ultimately builds on top of [partition pruning](/seafowl/docs/learning/partition-pruning)
that Seafowl uses for reducing the number of objects needed for scanning in response to usual `SELECT` queries with filtration. In turn, partition pruning
relies on simple statistics gathered during partition generation, such as max/min value for each column,
as well as the presence/count of null values. Consequently, if a particular column can't/doesn't have such statistics collected
the partition pruning doesn't work, and all table partitions need to be processed.

## Table time travel

Having demonstrated how older table versions can be re-constructed from the constituent objects, let's discuss how that looks in practice. First, a
bit of background on the way table resolution works in general.

Each query invocation is accompanied by a fresh Seafowl context, wrapping the inner DataFusion context itself. The wrapped
context in turn contains the nested maps of all databases (also referred to as catalogs) -> schemas -> tables, so that it can always determine
which particular table implementations are needed for executing the given query. By default all latest table versions at the
moment of query invocation are populated in the nested maps.

#### Temporal querying syntax

To support time travel queries, we opted for (ab)using the table function syntax[^4], whereby the target table reference
can have a timestamp value specified inside parenthesis. For instance to go back and inspect the initial table contents
or the contents after the two `INSERT`'s (versions 1 and 3, respectively) we can use:

```sql
seafowl=> SELECT * FROM some.numbers('2022-11-18 13:13:35');
 name | number
-------+--------
 one | 1
 two | 2
 three | 3
 four | 4
 five | 5
 one | 1
 two | 2
(7 rows)

seafowl=> SELECT * FROM some.numbers('2022-11-18 13:11:24');
 name | number
------+--------
 one | 1
 two | 2
(2 rows)
```

This works by Seafowl doing an initial walk over the query AST during the logical planning phase, and recording
the presence (and value) of temporal versions used in the query. If none are found, then the query proceeds as usual,
with the default context table map unchanged.

On the other hand, if Seafowl detects that time travel querying is used for some table(s) it then performs triage for the exact `table_version_id`
required. Once it has that information it will rewrite the affected table names in the AST to a unique one using the specific `table_version_id` fetched.

Finally, Seafowl updates the context table mapping for each table version specified with the unique name that was used in the AST rewrite.
Note that this only involves fetching the table metadata, such as which particular partitions correspond to a given `table_version_id`.
Loading of the partitions themselves is only done during the scan phase, once all the optimizations have been performed,
and we have the table filters handy for partition pruning.

## Future work

There are a couple of limitations in Seafowl time travel implementation that we aim to improve down the line. One such example
is lack of support for using time travel in write queries, as this mechanism is only supported for `SELECT` queries at present.

Another would be adding more flexibility to our existing time travel syntax, for instance by adding relative time
(e.g. table version from exactly 2 days ago) and version specification support (e.g. table version 2 versions prior to the latest one).
Alternatively we may also eventually align it with the `AS OF` syntax standard that is gaining more traction in the industry.

Nonetheless, the present mechanism is quite robust and versatile, capable of supporting advanced queries, such as CTEs and table JOINs
that use multiple different versions of the same table at once.

[^1]:
 For CSV file uploads you can also explicitly specify a desired schema. Parquet files have an embedded schema so this is not required.
[^2]:
 The threshold size for partition chunking is currently controlled via the
 [`max_partition_size`](/seafowl/docs/reference/seafowl-toml-configuration#max_partition_size) config parameter.
[^3]:
 Since Seafowl usually assumes relatively infrequent and large writes this shouldn't lead to suboptimal partition sizes.
 However, if that happens to be the case one can always force re-partitioning of an entire table using `CREATE TABLE AS` statement.
[^4]:
 Temporal table version selection syntax has been part of the ANSI SQL [since 2011](https://en.wikipedia.org/wiki/SQL:2011),
 but in our case it was technically simpler to go with the table function syntax.

We discuss how Seafowl performs table partitioning to enable efficient versioning and time travel queries

Table partitioning and time travel queries: Seafowl case study

## Introduction

**[Seafowl](https://seafowl.io)** is a database for **analytics at the edge**:
data-driven Web visualizations, dashboards and notebooks that require
low-latency execution of analytical queries.

To put it plainly, Seafowl lets you stop building these kinds of custom APIs for
every visualization in your application:

```bash
curl https://custom-api.myproject.io/basic_summary?country_of_import=BRAZIL&commodity=COFFEE&calculate=country_of_production,avg_volume,total_entries
```

and execute plain SQL queries straight from the user's browser instead:

```bash
curl https://demo.seafowl.io/q \
 -XPOST -H "Content-Type: application/json" \
 -d@- <<EOF
{"query": "SELECT \
 country_of_production, \
 AVG(volume) AS avg_volume, \
 COUNT(*) AS total_entries \
FROM supply_chains \
WHERE country_of_import = 'BRAZIL' \
 AND commodity = 'COFFEE' \
GROUP BY 1 ORDER BY 2 DESC"}
EOF
```

The key feature of Seafowl is being **CDN- and HTTP-cache friendly**. Seafowl's
SQL query results can be cached by CDNs like Cloudflare or HTTP caches like
Varnish (as well as the end user's browser cache).

This decreases the latency of applications that are powered by Seafowl, as well
as the resource requirements for running a Seafowl instance.

## Try it out

You can run Seafowl on any infrastructure provider. For this example, we have a
sample Seafowl deployment running on [Fly.io](https://fly.io) behind Cloudflare:

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-hit.png)

This is an Observable notebook backed by a 3M row dataset served by this
deployment (thanks to [Trase](https://trase.earth) for the data):

<iframe
 width="100%"
 height="900"
 frameBorder="0"
 src={`https://observablehq.com/embed/@seafowl/interactive-visualization-demo?cells=viewof+productionCountry,viewof+importCountry,viewof+commodity,topExportersL,topExportersP,volumeByYearL,volumeByYearP,allImportsL,allImportsP`}
/>

Because you loaded this page, the result for the aggregation queries that
produced these visualizations is already cached in your browser. On first load,
it should have also been served from Cloudflare's edge cache.

Press F12 to go to your browser's Developer Tools and go to the Network tab. If
you refresh this page now, you should see HTTP requests to
`https://demo.seafowl.io` being cached by your browser.

![](/static-v2/seafowl/tutorial/observable/observable-network-cached.png)

You can also check "Disable Cache" and refresh the page again: you should see
requests going directly to the source. If you inspect one of them, it should
have a `CF-Cache-Status: HIT` header, meaning it was cached by Cloudflare.

![](/static-v2/seafowl/tutorial/observable/observable-cdn-cached.png)

So, how does this work? Let's dive in![^1]

## Query results are static assets

HTTP has provisions for caching **static assets**. If we can make our query
results behave like other assets (images, JavaScript, documents), we can benefit
from caching as well.

How do caches handle static assets? They:

- use the URL of the asset as part of the cache key
- usually only cache GET requests
- cache based on the MIME type or the
 [file extension (Cloudflare)](https://developers.cloudflare.com/cache/about/default-cache-behavior/)

Seafowl is designed to power Web applications backed by slowly-changing data.
This means we can make some assumptions that make this easier to build.

In particular, we can assume that the result of a single SQL query is
**identified by the text of the SQL query** (what if the query result changes
because the source data changed? More on that later).

This means also assuming that the original SQL query doesn't contain
**volatile** functions (like `random()`).

## Let's put the query in the URL!

**Update (May 2023): As of Seafowl `0.4.1` this approach, due to it being the simplest one,
 [is also supported](/seafowl/docs/guides/querying-cache-cdn#query-in-the-url-path),
 despite some of the potential shortcomings discussed in this paragraph. **

A first approximation of getting this to work is putting the query text into the
URL. We can URI-encode special characters in the query to make it a valid URL.
However, this has a couple of drawbacks.

First, we can hit URL length limits. These vary between browsers and HTTP
intermediaries (like CDNs). The limit can be anywhere from 2KB to 64KB
([source](https://stackoverflow.com/a/417184)), depending on the exact
implementation.

It also looks suspicious. A SQL query inside of the URL looks too much like an
attempt at SQL injection. We haven't explicitly confirmed it, but it could be
something a Web application firewall or an intrusion detection system would
flag.

## Let's put something else in the URL?

How about we use a string that changes with the query but isn't as long as the
query itself? Something like a SHA-256 hash of the query?

This raises another question: how does the server now recover the original query
to actually execute it? There are two ways we can do this.

We can pass the original query as a **GET request body**. This is unusual but is
sometimes used by servers like ElasticSearch.
[RFC7231](https://www.rfc-editor.org/rfc/rfc7231#section-4.3.1) doesn't
explicitly forbid it, but does warn against using it:

> A payload within a GET request message has no defined semantics; sending a
> payload body on a GET request might cause some existing implementations to
> reject the request.

Unfortunately, it's also unsupported by some clients that can reject or quietly
drop the GET body, to the point where the Fetch standard explicitly disallows it
(see the discussion in the
[GitHub issue](https://github.com/whatwg/fetch/issues/551)).

This leaves us with the other option: **using an HTTP header**. We can pass the
original query as the header, percent-encoding it as well. HTTP headers support
some characters that would normally have to be encoded in a URL, like spaces.
This means that we only need to encode non-ASCII characters and newlines,
decreasing the length of the query (HTTP headers have length limits too!).

Seafowl supports both of these methods. For example, with the HTTP header:

```bash
curl -H "X-Seafowl-Query: SELECT 'Hello, Seafowl'" \
https://demo.seafowl.io/q/7468d08fa2c3a2dc59905511dc478784a550e2d4ad67d101802a6411099d690f

{"Utf8(\"Hello, Seafowl\")":"Hello, Seafowl"}
```

You can also bypass the cached GET API altogether and execute queries with POST
requests instead:

```bash
curl https://demo.seafowl.io/q \
 -XPOST -H "Content-Type: application/json" \
 -d@- <<EOF
{"query": "SELECT 'Hello, Seafowl'"}
EOF
```

### Client code

Instead of making a plain HTTP request, the client does have to do extra work by
hashing the query and URL-encoding it. This, however, is very straightforward to
do.

We have a sample
[Observable client](https://observablehq.com/@seafowl/client#querySeafowl) that
implements the
[DatabaseClient specification](https://observablehq.com/@observablehq/database-client-specification).

You can also use `curl` and Bash:

```bash
#!/bin/bash -e
SEAFOWL_HOST = "http://localhost:8080"
query=$1
hash=$(echo -n "$query" | sha256sum | cut -f 1 -d " ")

curl -v\
 -H "X-Seafowl-Query: ${query}" \
 "$SEAFOWL_HOST/q/$hash"
```

Or
[Node.js](https://github.com/splitgraph/seafowl/blob/main/examples/clients/node/seafowl-client.js),
or
[run JavaScript from the browser](https://seafowl.io/docs/guides/querying-cache-cdn#querying-from-the-browser-using-the-fetch-api).

### Do we need to normalize the query?

You'll notice that multiple queries with the same semantic meaning but different
text (for example, with different formatting) will hash to different values,
resulting in a cache miss.

This is not that big of a deal. If you control the application that queries
Seafowl, you also control the shape of SQL queries it will make and will be able
to minimize cache misses. You can also use the changes in the formatting (or add
comments to your SQL query) as an "escape hatch" to bust the cache and force a
query to re-execute.

## What about invalidation?

What happens when the source dataset changes and we need to update the query
result? Luckily, the HTTP standard has provisions for that: it supports
["Entity tags", or ETags](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/ETag).

An ETag is something an HTTP server can attach to a response, denoting the
version of the resource that has been served. Clients (including HTTP caches and
CDNs) can then use this ETag to make a **conditional request**:

- give the ETag back to the server as an `If-None-Match` header
- the server will return the resource only if the version provided by the server
 is newer than the provided
- otherwise, it will return a `304 Not Modified` HTTP response ETag.

Seafowl keeps track of the latest version of each table. When you write data to
Seafowl, it bumps the table version. It then uses the version numbers of all
tables participating in a query to compute the ETag, which it attaches to all
responses to its cached GET API.

## Putting it all together

Let's now take a look at how this works with Seafowl running behind a CDN. In
this case, we're using our demo deployment running on Fly.io behind Cloudflare,
but you can use any other CDN or an
[HTTP cache like Varnish](https://github.com/splitgraph/seafowl/tree/main/examples/varnish),
or no CDN at all and rely on your user's browser cache.

### Request 1: cache miss

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-initial-miss.png)

When a browser requests a query from Seafowl's GET API for the first time, it's
uncached by Cloudflare.

Cloudflare forwards the query to the origin (Seafowl). Seafowl executes it and
computes an **ETag** for the latest version of all tables that participated in
the query.

Cloudflare caches the ETag and the response and forwards it to the client. The
client's browser caches the response as well.

We do a small hack here: instead of requesting the query hash, we also append a
`.csv` extension to the URL. This is because by default, Cloudflare only
[caches certain extensions](https://developers.cloudflare.com/cache/about/default-cache-behavior/)
and this tricks it into caching our response. The extension is discarded by
Seafowl (it always returns responses in the JSON Lines format).

### Request 2: cache hit

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-hit.png)

If another browser (or the same browser with the browser cache disabled)
requests the same query within a certain time window (a few hours), Cloudflare
won't forward it to the Seafowl origin. Instead, it serves it from its own
cache. We can see this happened because Cloudflare returns a
`CF-Cache-Status: HIT` header.

### Request 3: cache revalidation

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-revalidation.png)

Let's now say some time has passed since the original query and the CDN needs to
**revalidate** the response to make sure it's still up to date.

Cloudflare makes a **conditional** HTTP request with the `If-None-Match` header
set to its stored ETag. Seafowl inspects the inbound ETag in the `If-None-Match`
header, calculates the current ETag for a given query and compares them.

This operation is much more lightweight than actually executing the query (where
Seafowl would need to potentially download and scan through all
[partitions](/seafowl/docs/learning/storage)).

In this case, the source dataset hasn't changed and Seafowl responds with a
`304 Not Modified` HTTP response (without the query result).

Cloudflare returns the cached response back to the user with a
`CF-Cache-Status: REVALIDATED` header to note that it has revalidated the
response and it's still up to date.

### Request 4: cache invalidation

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-invalidation.png)

Finally, let's say the source data has been updated. The user's browser makes
the same request to Cloudflare and Cloudflare makes the same conditional request
to Seafowl with a stale ETag.

In this case, Seafowl's computed ETag will be different from the one passed to
it by Cloudflare. That is, the response has **expired**. Seafowl re-executes the
query and returns the new result to Cloudflare, with a new ETag.

Cloudflare caches the new response and the new ETag. Finally, it forwards the
response to the user's browser with a `CF-Cache-Status: EXPIRED` header to note
that the response was stale and got reloaded from the origin.

## Conclusion

And that's it! With a few tricks we made our HTTP API cache-friendly. Any HTTP
cache or a CDN can now cache Seafowl query results and invalidate them when the
source data changes. This makes applications powered by Seafowl respond faster
and require less resources.

If you're interested in learning more about Seafowl, feel free to check out our
[Observable demo](https://observablehq.com/@seafowl/interactive-visualization-demo),
go through our [quickstart](/seafowl/docs/getting-started/quickstart) or through
a longer [tutorial](/seafowl/docs/getting-started/tutorial-fly-io/introduction)
in which you'll deploy Seafowl to Fly.io, put it behind Cloudflare or Varnish
and reproduce our Observable demo notebook.

## Other questions

### Bringing the data or the query results to the edge?

You might ask, why go through all this trouble to cache query results when we
could deploy our application at the edge instead? The key insight lies in how
analytical queries work:

- They usually reduce a big (millions/billons of rows, a few GB of data) dataset
 into a small (hundreds/thousands of rows, a few KB) summarized response
- They take hundreds of milliseconds or seconds instead of milliseconds or
 microseconds (like transactional queries would)

Let's say:

- an analytical query normally takes 500ms
- the latency to the origin is 100ms
- the latency to the edge location is 10ms

In that case, executing the query closer to the user would turn a 600ms
end-to-end latency to 510ms, which is not a noticeable improvement. It also has
to come at the expense of replicating the data and running the query engine at
the edge location[^2].

However, if a query result is cacheable, caching it at the edge instead of at
the origin would bring a 100ms response time down to 10ms.

Simply put, analytical queries normally take too much time to benefit from
moving the execution closer to the user.

### Using a non-HTTP cache?

An alternative to using HTTP for this would be implementing caching in Seafowl
itself or deploying a cache like Redis or Memcached. However, this would also
mean either requiring some scratch disk space for a Seafowl deployment to store
the query result cache or requiring Seafowl users to run caches at edge
locations.

We'd also need to write a more sophisticated client for querying Seafowl in
order to get query results cached in your application user's browser.

Using HTTP caches gives us all of this for free, including the ability to rely
on existing HTTP infrastructure.

### How does this break?

There are a few known issues with this kind of caching as we implemented it:

- Updating the definition of a
 [user-defined WASM function](/seafowl/docs/guides/custom-udf-wasm) doesn't
 invalidate the cache for queries that use those functions
 ([GitHub issue](https://github.com/splitgraph/seafowl/issues/57))
- Updating a table and then applying an opposite update still changes the table
 version ID, even though the content of the table is effectively unchanged
 ([GitHub issue](https://github.com/splitgraph/seafowl/issues/57))
- Queries that reference
 [external tables](/seafowl/docs/guides/csv-parquet-http-external) don't
 support the invalidation mechanism
- You can't currently change the Cache-Control headers sent out by Seafowl
 ([GitHub issue](https://github.com/splitgraph/seafowl/issues/56))

[^1]:
 This is a longer-form version of
 [our tutorial](/seafowl/docs/getting-started/tutorial-fly-io/part-3-cached-get-api)
 and the [documentation](/seafowl/docs/guides/querying-cache-cdn) for the
 cached GET API feature.

[^2]:
 In the extreme case, the "edge location" is the user's browser itself (see
 [DataStation](https://datastation.multiprocess.io/) or
 [this blog post](https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/)),
 which works pretty well if the query engine doesn't need to load more than a
 few MB of data (because it's on the user's machine already or because it's
 small enough to begin with).

A deep dive into how we designed Seafowl's REST API to be HTTP cache and CDN friendly, including some discussion of ETags and other HTTP cache mechanics.

(Ab)using CDNs for SQL queries

For the past few months, we've been hard at work on something interesting and
it's finally ready for people to start experimenting with.

**[Seafowl](https://seafowl.io)** is our new standalone project, inspired by the
design of modern cloud data warehouses like Snowflake and BigQuery, as well as
the lessons we learned over the past four years of building Splitgraph and the
Splitgraph DDN.

It's a **CDN and HTTP-cache friendly** database designed for **analytics at the
edge**: dashboards, notebooks, visualizations and other data-driven
read-intensive Web applications that need to be responsive while operating on
large datasets.

```bash
curl https://demo.seafowl.io/q/30dcf5da00f94e3fbd28cf98242f7de3b164a440cadfaa6f7de74369ffc2177d.csv \
 -i -XGET -H "Content-Type: application/json" \
 -d@- <<EOF
{"query": "SELECT country_of_production, COUNT(*) AS count \
FROM supply_chains \
GROUP BY 1 ORDER BY 2 DESC"}
EOF

HTTP/2 200
...
cf-cache-status: HIT

{"country_of_production":"BRAZIL","count":2386600}
{"country_of_production":"ARGENTINA","count":260293}
{"country_of_production":"INDONESIA","count":155751}
{"country_of_production":"ECUADOR","count":96842}
{"country_of_production":"PARAGUAY","count":29848}
...
```

## Fast analytics...

Seafowl is written in Rust and relies on
[Apache DataFusion](https://github.com/apache/arrow-datafusion) to plan and
execute queries. For analytical queries, it's:

- up to 10x faster than vanilla PostgreSQL
- ...or up to 4x faster than PostgreSQL with the `parquet_fdw` extension
- ...or around the level of DuckDB in performance, sometimes beating it (apart
 from some pathological queries where it has memory issues, as well as queries that use a lot of subqueries where the optimizer might not be able to build an optimal query plan)

We have some [benchmarks](https://observablehq.com/@seafowl/benchmarks) which
aren't as in-depth as TPC-DS, but do show that Seafowl makes a lot of seemingly
heavy SQL queries now feasible for powering interactive applications.

<iframe
 width="100%"
 height="400"
 frameBorder="0"
 src="https://observablehq.com/embed/@seafowl/benchmarks?cells=viewof+benchmarkRun,viewof+databases,viewof+queries,benchmarkPlot"
/>

For `SELECT` queries, Seafowl supports a large subset of the PostgreSQL dialect.
If there's something missing, you can
[write a user-defined function](/seafowl/docs/guides/custom-udf-wasm)
for Seafowl in anything that compiles to WebAssembly.

You can write data to Seafowl with:

- [standard SQL statements](/seafowl/docs/guides/writing-sql-queries)
 (including `CREATE TABLE AS` statements for future dbt support!)
- by
 [uploading a CSV or a Parquet file](/seafowl/docs/guides/uploading-csv-parquet)
- by
 [setting up a "live" table](/seafowl/docs/guides/querying-http)
 that points to a file on an HTTP server

You can also
["bake" a dataset into a Docker image](/seafowl/docs/guides/baking-dataset-docker-image)
by bundling Seafowl together with its data. This is a nifty scaling strategy for
smaller (well, up to 1GB, which can be as big as the 55M row subset of the New
York Taxi dataset we use for our benchmarks) and read-only data.

## ...at the edge

"The edge" is a handwavy and marketing-y term, but it describes what Seafowl is
all about well enough. You can deploy Seafowl to the edge or cache query results
at the edge.

Seafowl ships as a single 50MB binary that starts in 10ms, making it easily
deployable to modern serverless environments, anywhere in the world. Our own
[tutorial](/seafowl/docs/getting-started/tutorial-fly-io/introduction) uses
Fly.io as an example.

Seafowl's query execution API follows HTTP cache semantics. This means you can
[put Seafowl behind a CDN](/seafowl/docs/guides/querying-cache-cdn)
like Cloudflare or a cache like Varnish and have query results cached and
delivered to your users in milliseconds.

Even without a dedicated cache, you can get the benefits of caching query
results in your user's browser (to see it in action, go to our
[demo](https://observablehq.com/@seafowl/interactive-visualization-demo), press
F12, go to the Network tab and refresh the page).

## Try it out!

Seafowl is now available to download and experiment with, though, like with all
early stage projects, it likely has plenty of bugs and missing features. As we
continue its development, we might break APIs or change storage formats in a
backwards-incompatible way.

Feel free to check out our
[Observable demo](https://observablehq.com/@seafowl/interactive-visualization-demo),
go through our
[quickstart](/seafowl/docs/getting-started/quickstart)
or through a longer
[tutorial](/seafowl/docs/getting-started/tutorial-fly-io/introduction)
in which you'll deploy Seafowl to Fly.io, put it behind Cloudflare or Varnish
and reproduce our Observable demo notebook.

When something breaks, please do
[raise a GitHub issue](https://github.com/splitgraph/seafowl/issues/). And, if
it doesn't (or doesn't break badly enough), **give us a
[GitHub star](https://github.com/splitgraph/seafowl)**!

## Future features

There are many features we're planning for Seafowl. Where appropriate, we'll
also aim to upstream these changes into DataFusion itself.

### JSON support

Seafowl currently supports a limited subset of PostgreSQL operators and
functions. The most notable missing feature is the JSON data type for storage of
arbitrary semi-structured objects, as well as helper functions like
`json_extract_path` / `json_agg`.

See the [GitHub issue](https://github.com/splitgraph/seafowl/issues/137) for more details.

### Live querying

You can currently query Parquet/CSV files over HTTP from Seafowl, but you can't
query any other databases "live", like you could with Splitgraph. It's always
been difficult for us to write PostgreSQL foreign data wrappers in C, since we'd
have to do a lot of work to correctly push down various parts of the PostgreSQL
query tree. With the extensibility of DataFusion and the memory safety of Rust,
we expect manipulating the query plan to be much easier.

See the [GitHub issue](https://github.com/splitgraph/seafowl/issues/136) for more details.

### Advanced PostgreSQL client support

Splitgraph has always been mostly PostgreSQL-first, with great support for
PostgreSQL clients (including ones that run complex introspection queries, like
Metabase).

With Seafowl, we flipped this upside down: it's HTTP-first, with basic
PostgreSQL wire protocol support as an afterthought. Perhaps it would be
interesting to polish that more, add more `information_schema` tables and make
Seafowl queryable by existing PostgreSQL clients, dashboarding and BI tools.

This would also open up more data sources, for example, using
[Airbyte](https://airbyte.com/) to load data into Seafowl.

### ...Seafowl Cloud?

No open-source project announcement nowadays is complete without teasing a
managed version of it.

In our case, we already have [Splitgraph](https://www.splitgraph.com) and we're
currently working on a better UI for it, one that moves us from obscure concepts
of "data images" and "repositories" to more familiar ones like "tables" and
"databases". Over time, we're also planning on migrating Splitgraph's query
execution engine from PostgreSQL to Seafowl.

With that in mind, we could provide a management interface for your Seafowl
instance or, later on, a true multi-tenant pay-for-what-you-use analytical query
execution API, powered by Seafowl.

In the words of a certain company, "we're just getting started".

Our new project: a CDN-friendly analytical database that's up to 10x faster than PostgreSQL and up to 5x faster than Splitgraph.

Seafowl: a database for analytics at the edge

![hero](/static-v2/images/blog/select-from-the-browser/hero.jpg)

## Serverless SQL Queries

Frontend developers who write SQL have traditionally been required to sequester
their SQL queries to a backend server. The popular
[adage](https://stackoverflow.com/a/60332563/2044952) has more or less been
'don't run SQL directly from the frontend.'

For Splitgraph users, it turns out the old rules no longer apply. Thanks to the
[DDN HTTP API](https://www.splitgraph.com/docs/query/ddn-http), the browser can
work directly with raw SQL queries. In this post we demonstrate by building a
client-side only dashboard with taxi ride data.

## In a nutshell

Today we build a 100% client-side JavaScript app. We will:
- download official NYC taxi data and publish it to a Splitgraph repo
- write some SQL to roll up taxi zone activity across a 24h period
- clone and run a SPA to present taxi activity on an interactive map of NYC

## Dashboard goal: NYC Covid recovery followup
Whereas we previously focused on
[subway rides](./data-driven-app-powered-by-splitgraph), cab rides
are also a familiar sight to New Yorkers. Rendering taxi activity on a map of
NYC may give us additional indicators into how the Covid recovery is going.

I assume you have a Splitgraph account
([need one?](https://www.splitgraph.com/auth/sign_up)), Node.js, and the
[sgr](https://www.splitgraph.com/docs/sgr-advanced/getting-started/installation#installation-1)
CLI available.

## Download the data
Taxi ride datasets are published at
[nyc.gov](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) on a
monthly basis. April is the most recent month available at the time of writing,
so let's download yellow cab records for April, across 2020, 2021, and 2022. It
looks something like this:

![April 2022](/static-v2/images/blog/select-from-the-browser/download.jpg)

Note: as of May 2022 nyc.gov started publishing this data in Parquet format, but
we need CSV. Thus please convert however you prefer.

<details>
<summary style={{display: "list-item"}}>parquet2csv.py helper (optional)</summary>

A [script](https://github.com/splitgraph/nyctaxis/master/) is available. It
depends on `pandas` and `fastparquet`.

```shell
pip install pandas fastparquet
```

</details>

However you make your CSVs once they're ready, please upload them via the `sgr` CLI.

```shell
$ sgr cloud upload $yourSplitgraphUser/nyctaxi yellow_tripdata_2020-04.csv yellow_tripdata_2021-04.csv yellow_tripdata_2022-04.csv
```
![Upload](/static-v2/images/blog/select-from-the-browser/upload.jpg)

Once the CSVs have been ingested your repo should look like this:
![Table](/static-v2/images/blog/select-from-the-browser/table.jpg)

Download and publish taxi data to Splitgraph: ✅

## SQL rollup

To populate the taxi zones with a 24 hour sample, let's
[roll up](https://github.com/splitgraph/nyctaxis/master/ddn-fetcher.ts#L6) all taxi
pickups and drop-offs that occurred during the given day.

<details>
<summary style={{display: "list-item"}}>
Want to see the query?
</summary>

<EmbeddedQuery
 minHeight={600}
 query={`WITH
pickups AS
(
SELECT
 "PULocationID" as location_id,
 COUNT("PULocationID") as pickup_count
FROM "paws/nyctaxi"."yellow_tripdata_2022-04"
WHERE
 tpep_pickup_datetime >= '2022-04-01'
AND
 tpep_pickup_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS'
GROUP BY "PULocationID"
),
dropoffs AS
(
SELECT
 "DOLocationID" as location_id,
 COUNT("DOLocationID") as dropoff_count
FROM "paws/nyctaxi"."yellow_tripdata_2022-04"
WHERE
 tpep_dropoff_datetime >= '2022-04-01'
AND
 tpep_dropoff_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS'
GROUP BY "DOLocationID"
),
joined AS
(
SELECT
 pickups.location_id, pickup_count, dropoff_count
FROM
 pickups LEFT JOIN dropoffs ON pickups.location_id = dropoffs.location_id
)
SELECT * from joined`}
 layout="hsplit"
/>
</details>

Roll up taxi activity: ✅

## Clone the React app

The [repo](https://www.github.com/splitgraph/nyctaxis) is available on GitHub.

<details>
<summary style={{display: "list-item"}}>
Want to step through the code tag by tag?
</summary>

- [Initial CRA](https://github.com/splitgraph/nyctaxis/tree/setup-cra) Tag: `setup-cra`
- [Add taxi zone data](https://github.com/splitgraph/nyctaxis/tree/taxizone-data) Tag: `taxizone-data`
- [Handle fetching + mapping](https://github.com/splitgraph/nyctaxis/tree/fetch-and-map) Tag: `fetch-and-map`

</details>

Since we use `mapbox-gl` for the interactive map, get yourself
a free API key at [mapbox.com](https://www.mapbox.com/) if you need one.

Changing the date and clicking 'load' will cause new data to be fetched and
rendered on the app.

Clone and run the SPA: ✅

## Conclusion
The DDN HTTP API offers a convenient way to move beyond the old rules of the
road, and let frontend developers run queries from their end user's browser.

Thanks for reading and keep following the Splitgraph blog for more posts.

##### Image credit
[Eduards Ceravs](https://flic.kr/p/b67zX6)

How Splitgraph's DDN HTTP API lets you run SQL queries directly from the browser, opening new possiblities for client-side data-driven apps.

SELECT directly from the browser

## Introduction
Data-driven apps like interactive dashboards offer a valuable way for users to more easily draw individual conclusions backed by data. However, delivering those benefits may require significant cross-discipline engineering skills: backend, frontend, data ingestion/pipeline, DevOps and potentially infra to run all of it.

In this post we illustrate how Splitgraph helps lower barriers for data-driven apps by addressing key pain points. The Splitgraph DDN integrates smoothly with anything that speaks Postgres, and oftentimes reduces the need to stand up and maintain infra.

Today we will:
- upload a CSV to a Splitgraph repo
- use SQL to query the repo via the DDN
- use Python to parameterize the queries and do basic aggregations
- tie it all together with [Streamlit](https://streamlit.io) in a rich web UI

## Dashboard idea: How is NYC's Covid recovery going?
To answer this question one might consider various year-over-year data, e.g. real estate [occupancy rates](https://papers.ssrn.com/sol3/papers.cfm?abstract_id=4124698), sales tax [revenue](https://comptroller.nyc.gov/reports/annual-state-of-the-citys-economy-and-finances/), or [Broadway](https://www.broadwayleague.com/research/grosses-broadway-nyc) ticket sales.

How about public transit? NYC has one of the world's largest metro systems, and it turns out turnstile activity is regularly [published](http://web.mta.info/developers/turnstile.html) by the MTA. While not every New Yorker uses the subway, many consider it vital, and such data could serve as a reasonable indicator of whether city life is back to pre-pandemic levels (spoiler: or not, as it turns out).

OK, we've got a dashboard goal! Let's plot data for NYC's most popular subway stations, by month, across 2020, 2021, and 2022.

I assume you have a Splitgraph account ([need one?](https://www.splitgraph.com/auth/sign_up)) and Python 3.x installed.

## Enter Splitgraph
<details>
<summary style={{display: "list-item"}}>Just want to see the code?</summary>

A self-contained [repo](https://www.github.com/splitgraph/nyc-subway-dashboard) is available on GitHub.

Each step has been tagged:
- [Connect to DDN](https://github.com/splitgraph/nyc-subway-dashboard/tree/sql-querying) Tag: `sql-querying`
- [Render the first graph via Streamlit](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/first-graph) Tag: `first-graph`
- [Year-over-year graphs](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/year-over-year-graphs) Tag: `year-over-year-graphs`

</details>


#### Upload the CSV
A [totals rollup CSV](https://static.observableusercontent.com/files/ad48776aa4a65719ef8b57810eb825aad469ee25f5b216efce578bfa69a6df47664590d2a58927ab0a32eac164428a5c750e88669aacdc4f1d4d22c3dbb5f51d?response-content-disposition=attachment%3Bfilename*%3DUTF-8%27%27mta_all_points%25402.csv) is a good starting point and what we're going to use. Go ahead and download it.


With the CSV in hand, you can drag it here (yes, right into this blog post):
<EmbeddedUpload />

If you prefer the [CLI](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-upload) that is also an option. A [previous blogpost](https://splitgraph.com/blog/csv-to-sql-upload-to-data-delivery-network-ddn) covers uploading in more detail.

![Drag and drop the CSV](/static-v2/images/blog/data-driven-app/upload-step-1.png)
_Drag and drop the CSV_

![I went with "nyc-transit" for a repo name](/static-v2/images/blog/data-driven-app/upload-step-2.png)
_I went with "nyc-transit" for a repo name_

![Repo page view](/static-v2/images/blog/data-driven-app/upload-step-3.png)
_Repo page view_

Upload a CSV to a Splitgraph repo: ✅

#### Let's query it!
With the repo now hosting the data, let's query it using SQL.

Here's a query for January 2020 turnstile activity:
<EmbeddedQuery
 query={`SELECT "START_DATE", "STATION", SUM("TOTAL")
FROM "paws/nyc-transit"."turnstile-activity"
WHERE to_date("START_DATE", 'MM/DD/YYYY') BETWEEN '1/1/2022' AND '2/1/2022'
GROUP BY "START_DATE", "STATION"`}
 layout="hsplit"
/>


#### DDN Time
For querying the repo in our app, we'll use the Splitgraph DDN. This feature makes repos conveniently available via `postgresql://data.splitgraph.com:5432/ddn`, and most Postgres clients should work. You will need a Splitgraph API key and secret to access the DDN; generate those by going to your [account settings](https://www.splitgraph.com/settings/sql-credentials) and set them aside.

The following example snippet will connect a Python app to the DDN. Swap user & password for the creds you just made.

```python
conn = psycopg2.connect(
 user = "myuser",
 password = "supersecret",
 host = "data.splitgraph.com"
 port = "5432"
 database = "ddn"
)
```

If you prefer you can check out a working example repo at this step. [Source](https://github.com/splitgraph/nyc-subway-dashboard/tree/sql-querying) - just [add the creds](https://docs.streamlit.io/streamlit-cloud/get-started/deploy-an-app/connect-to-data-sources/secrets-management) to `.streamlit/secrets.toml`

Use SQL to query the repo: ✅

## Enter Streamlit
<details>
<summary style={{display: "list-item"}}>Prefer an empty slate?</summary>

Streamlit has an [example](https://github.com/streamlit/streamlit-example) repo ready to clone/fork.

</details>

## Handling aggregations and data cleanup with Python
We have all this data available to work with from the DDN. Now, let's write some Python to clean it up and prepare it for plotting.
Let's do a simple aggregation so we can pass it into a Pandas DataFrame.


```python
data = {"DATE": [], "TOTAL": []}
for k, v in totals.items():
 data['DATE'].append(k)
 data['TOTAL'].append(v)

source = pd.DataFrame.from_dict(data)
line_chart = alt.Chart(source).mark_line().encode(
 alt.X('DATE:T', title='Time'),
 alt.Y('TOTAL:Q', title='Weekly rides'),
 ).properties(title="MTA total weekly turnstile activity (Jan 2020-May 2022)")
```
The [rest of the code](https://github.com/splitgraph/nyc-subway-dashboard/blob/master/streamlit_app.py#L45) is available.

Use Python to do basic aggregations: ✅


## How to integrate Splitgraph with Streamlit
Streamlit lets Python developers write interactive dashboards, with sliders, inputs, and graphs, all without having to write a single line of JavaScript.

For example `st.slider()` will render a slider on the page whose value is available as a variable.
```python
month = st.slider("Choose a month (Jan = 1, Feb = 2, etc.)", min_value=1, max_value=5)
```
Consider seeing the [tag](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/year-over-year-graphs) for a fleshed out example.

Tie it all together with Streamlit: ✅

## Conclusion
Is NYC back to business as usual? Well, take a look for yourself:
![January activity, year-over-year](/static-v2/images/blog/data-driven-app/jan-year-over-year.png)

How about April?
![April activity, year-over-year](/static-v2/images/blog/data-driven-app/apr-year-over-year.png)

In sum, while we can see popular stations are getting more traffic than the early days of lockdown, so far turnstile activity in 2022 hovers around 50% of the same month in 2020.

The actual [dashboard](https://share.streamlit.io/splitgraph/nyc-subway-dashboard) has mouseover with specific #s.

## Recap
We started with a simple dataset, uploaded it to a Splitgraph repo so we could run SQL queries.
Then, by writing some basic Python, we powered a mini dashboard by utilizing Streamlit's rich UI features.
We didn't need to stand up any new infra, and our users are empowered to draw their own conclusions.

## Shoutouts & Acknowledgements
Thank you MTA for regularly publishing [turnstile data](http://web.mta.info/developers/turnstile.html)

Thank you [@je2538](https://observablehq.com/@je2538/bouncing-back-where-nyc-subway-riders-have-returned-and-whe), which is how I learned that the MTA offers this, and who (I believe) rolled up the data in a suitable format for this exercise.

Thank you [Streamlit](https://streamlit.io/about) for making it easier to serve a rich client UI from Python.

We demonstrate how combining Splitgraph and Streamlit lets devs and data scientists more easily build data-driven apps. In this example we plot NYC subway turnstile data to try and glean how NYC's Covid recovery is going.

Building a data-driven app with Splitgraph and Streamlit

While waiting for/sitting on various types of transportation during the recent
spike in demand for travel, I killed time by solving
[Sudoku](https://en.wikipedia.org/wiki/Sudoku) puzzles.

A Sudoku puzzle has a simple premise. A 9✕9 grid (split into 3✕3 squares) has to
be filled with numbers from 1 to 9 such that each column, row and square only
contains one of each digit.

After solving about a dozen boards, I had an idea. Sudoku is a classic
constraint satisfaction problem. Solving it with a computer has been done many
times before, including with something as unorthodox as
[SQL](https://wiki.postgresql.org/wiki/Sudoku_solver) (please don't run this on
Splitgraph).

I had a more disgusting method in mind.

Package managers like Yarn/Poetry/Cargo do version range and conflict resolution
when generating a lockfile. In essence, they are designed for solving constraint
satisfaction problems.

So, is it possible to get a dependency resolver to solve Sudoku for me?

Turns out, it is. I put the code up
[on GitHub](https://github.com/mildbyte/poetry-sudoku-solver) and what follows
is the explanation of how it works. This is using
[Poetry](https://python-poetry.org/), a package and dependency manager for
Python projects.

## Encoding the board constraints

First, we need to encode the rules of Sudoku in such a way that a dependency
resolver can understand them.

We can represent each Sudoku board cell as a Python package with the name
`sudoku-cell{row}{col}`. Each package has 9 versions `{value}.0.0`,
corresponding to the value of that cell. Since in Python, a resolved dependency
tree only has one version of each package, this means that every cell can only
have one value, which is what we're after.

In addition, every package also has dependencies on other "cell" packages, with
version constraints encoding which values those "cells" can have.

For example, version 3.0.0 of the `sudoku-cell25` package represents an
assertion that the cell at row 2, column 5 of the board has the number 3 in it.
The `pyproject.toml` for this package has a list of all "cell" packages in the
same row, column or a 3x3 square as this cell:

```toml
[tool.poetry.dependencies]
python = "^3.6"
sudoku-cell14 = "!= 3.0.0"
sudoku-cell15 = "!= 3.0.0"
sudoku-cell16 = "!= 3.0.0"
sudoku-cell21 = "!= 3.0.0"
sudoku-cell22 = "!= 3.0.0"
sudoku-cell23 = "!= 3.0.0"
sudoku-cell24 = "!= 3.0.0"
sudoku-cell26 = "!= 3.0.0"
sudoku-cell27 = "!= 3.0.0"
sudoku-cell28 = "!= 3.0.0"
sudoku-cell29 = "!= 3.0.0"
sudoku-cell34 = "!= 3.0.0"
sudoku-cell35 = "!= 3.0.0"
sudoku-cell36 = "!= 3.0.0"
sudoku-cell45 = "!= 3.0.0"
sudoku-cell55 = "!= 3.0.0"
sudoku-cell65 = "!= 3.0.0"
sudoku-cell75 = "!= 3.0.0"
sudoku-cell85 = "!= 3.0.0"
sudoku-cell95 = "!= 3.0.0"
```

To use this version of the package ("put 3 in this cell"), we can't use the same
version of any of the conflicting packages ("can't put 3 in cells in the same
row, column or square").

I then set up a devpi instance and uploaded all 9✕9✕9 = 729 package versions to
it. Theoretically, one could upload them to the public PyPI (these rules are
only ever uploaded once, not every time we need to solve a board), but that
feels abusive.

## Encoding the problem

Now, we can represent an unsolved Sudoku board as another Poetry package:

```toml
[tool.poetry.dependencies]
python = "^3.6"
sudoku-cell11 = "*"
sudoku-cell12 = "2.0.0"
sudoku-cell13 = "*"
sudoku-cell14 = "8.0.0"
sudoku-cell15 = "*"
sudoku-cell16 = "9.0.0"
sudoku-cell17 = "*"
sudoku-cell18 = "*"
sudoku-cell19 = "*"
sudoku-cell21 = "3.0.0"
sudoku-cell22 = "7.0.0"
sudoku-cell23 = "*"
sudoku-cell24 = "6.0.0"
...
```

This `pyproject.toml` depends on all 81 "cell" packages, pinning known cells to
their values.

## Solving the problem

We can now solve the problem by simply running `poetry update --lock`. In order
to generate a lockfile for this package, Poetry has to find a version (value)
for each of the 81 packages (cells) in such a way that they don't conflict with
each other. Since we encoded the rules of Sudoku as inter-package dependencies,
the lockfile will contain a solution to this Sudoku board.

Running `poetry update` with `-vvv` even outputs the internal assertions Poetry
is deriving about the constraints:

```
125: conflict: sudoku-cell52 (3.0.0) depends on sudoku-cell55 (!=3.0.0)
 125: ! sudoku-cell52 (3.0.0) is partially satisfied by not sudoku-cell52 (5.0.0)
 125: ! which is caused by "sudoku-cell52 (5.0.0) depends on sudoku-cell51 (!=5.0.0)"
 125: ! thus: sudoku-cell52 (3.0.0 || 5.0.0) requires sudoku-cell55 (!=3.0.0) or sudoku-cell51 (!=5.0.0)
 125: ! not sudoku-cell51 (!=5.0.0) is partially satisfied by sudoku-cell51 (!=8.0.0)
 125: ! which is caused by "sudoku-cell11 (8.0.0) depends on sudoku-cell51 (!=8.0.0)"
 125: ! thus: if sudoku-cell52 (3.0.0 || 5.0.0) and sudoku-cell11 (8.0.0) then sudoku-cell55 (!=3.0.0) or sudoku-cell51 (<5.0.0 || >5.0.0,<8.0.0 || >8.0.0)
...
131: selecting sudoku-cell14 (6.0.0)
 131: Version solving took 269.771 seconds.
 131: Tried 131 solutions.
```

## Success

Finally, we can parse the lockfile (read which version of each package Poetry
has chosen) and emit it as a solved Sudoku board:

```
 ORIGINAL SOLUTION


 . . . | 6 4 1 | 9 . 5 3 2 8 | 6 4 1 | 9 7 5
 . . . | . . 9 | 4 . . 1 6 5 | 8 7 9 | 4 3 2
 . . 7 | 2 . . | . . . 9 4 7 | 2 3 5 | 1 6 8
-------|-------|------- -----------------------
 2 . . | . 5 . | . . . 2 7 4 | 1 5 6 | 8 9 3
 . . 1 | . . 7 | 6 . 4 8 5 1 | 3 9 7 | 6 2 4
 . 9 . | . . . | 5 . 7 6 9 3 | 4 8 2 | 5 1 7
-------|-------|------- -----------------------
 . 8 . | . . . | . . . 4 8 9 | 7 6 3 | 2 5 1
 . . 2 | . . . | . 8 6 5 3 2 | 9 1 4 | 7 8 6
 . . 6 | 5 2 . | . . . 7 1 6 | 5 2 8 | 3 4 9
```

## Failed attempt: Yarn

In the beginning, I tried a similar idea with Yarn, with `package.json` files
for each individual "cell" package encoding dependencies on other packages:

```json
{
 "name":"sudoku-cell26",
 "version":"6.0.0",
 "main":"index.js",
 "license":"MIT",
 "dependencies":{
 "sudoku-cell14":"1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0",
 "sudoku-cell15":"1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0",
 "sudoku-cell16":"1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0",
...
```

However, Yarn resolved dependencies using multiple versions for some packages:

```yaml
# This file is generated by running "yarn install" inside your project.
# Manual changes might be lost - proceed with caution!

__metadata:
 version: 6
 cacheKey: 8

? "sudoku-cell11@npm:*, sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 ||
 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0, sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 ||
 4.0.0 || 5.0.0 || 6.0.0 || 8.0.0 || 9.0.0, sudoku-cell11@npm:1.0.0 || 2.0.0 ||
 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0, sudoku-cell11@npm:1.0.0 ||
 2.0.0 || 3.0.0 || 4.0.0 || 6.0.0 || 7.0.0 || 8.0.0 || 9.0.0,
 sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
 || 9.0.0, sudoku-cell11@npm:1.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0
 || 8.0.0 || 9.0.0, sudoku-cell11@npm:2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0
 || 7.0.0 || 8.0.0 || 9.0.0"
: version: 9.0.0
 resolution: "sudoku-cell11@npm:9.0.0"
 dependencies:
 sudoku-cell12:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
 sudoku-cell13:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
---
? "sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0
 || 8.0.0"
: version: 8.0.0
 resolution: "sudoku-cell11@npm:8.0.0"
 dependencies:
 sudoku-cell12:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0
 sudoku-cell13:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0
---
? "sudoku-cell12@npm:*, sudoku-cell12@npm:1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 ||
 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0, sudoku-cell12@npm:1.0.0 || 2.0.0 || 3.0.0 ||
 4.0.0 || 5.0.0 || 6.0.0 || 8.0.0 || 9.0.0, sudoku-cell12@npm:1.0.0 || 2.0.0 ||
 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0, sudoku-cell12@npm:1.0.0 ||
 2.0.0 || 3.0.0 || 4.0.0 || 6.0.0 || 7.0.0 || 8.0.0 || 9.0.0,
 sudoku-cell12@npm:1.0.0 || 2.0.0 || 3.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
 || 9.0.0, sudoku-cell12@npm:2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0
 || 8.0.0 || 9.0.0"
```

I tried running `yarn dedupe` which is supposed to deduplicate overlapping
version ranges in the lockfile, but it ran out of memory and crashed. Yarn also
comes with a constraints system, but that's just Prolog so it felt like
cheating.

## Conclusion and future work

The code I used to generate the packages and upload them to devpi is on
[GitHub](https://github.com/mildbyte/poetry-sudoku-solver/). You can install it
with Poetry and run this experiment locally, for better or for worse.

As for what else can be done with this, I was thinking of using something faster
than devpi to feed packages to Poetry (the package upload step takes about 10
minutes and the actual solving process takes a couple of minutes). Maybe it's
possible to transpile a Prolog program into a set of Poetry packages. The sky's
the limit.

An unexpected use case for one of Python's most popular package managers.

Solving Sudoku with Poetry's dependency resolver

## What is Splitgraph?

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

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

## Introduction

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

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

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

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

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

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

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

## Example `splitgraph.yml` file

Here is a basic example that builds two Splitgraph repositories:

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

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

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

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

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

## Writing `splitgraph.yml` files

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

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

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

Click the button to get started:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#### Miscellaneous `sgr cloud` commands

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

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

### `splitgraph.yml` reference

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

### In Alpha: Web UI

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

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

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

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

## Conclusion

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

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

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

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

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

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

Table partitioning and time travel queries: Seafowl case study

(Ab)using CDNs for SQL queries

Seafowl: a database for analytics at the edge

SELECT directly from the browser

Building a data-driven app with Splitgraph and Streamlit

Solving Sudoku with Poetry's dependency resolver

`splitgraph.yml`: Terraform for your data stack

Product

Support

Company

Splitgraph

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

Table partitioning and time travel queries: Seafowl case study

(Ab)using CDNs for SQL queries

Seafowl: a database for analytics at the edge

SELECT directly from the browser

Building a data-driven app with Splitgraph and Streamlit

Solving Sudoku with Poetry's dependency resolver

splitgraph.yml: Terraform for your data stack

Product

Support

Company

Community

Splitgraph

`splitgraph.yml`: Terraform for your data stack