
# Introducing SQLite file uploads

Splitgraph already had excellent support for [importing CSV files](csv-to-sql-upload-to-data-delivery-network-ddn), but now we can use the same interface to upload SQLite files as well. Try it out:

<EmbeddedUpload />

# Why use Splitgraph for SQLite data?

[SQLite](https://sqlite.org/index.html) is an incredibly versatile embedded database, used by apps on mobile devices, client code running in web browsers (via WebAssembly), and native applications.
The main difference compared to more traditional relational databases such as PostgreSQL is that an SQLite database is just a file, there's no need for a dedicated database server process.
This is an enormous advantage in resource-constrained environments and makes it possible to build cost-effective data-intensive applications using [Litestream](https://litestream.io/) for example.
At the same time, SQLite's database-as-a-file approach makes concurrent multi user access -and thus collaboration- difficult.

In addition to collaboration, Splitgraph offers countless features beyond what SQLite provides, for example:
- column-based data storage for efficient aggregation queries
- immutable data images
- built-in support for [`dbt` transformations](dbt)
- joining tables from other databases using [foreign data wrappers](foreign-data-wrappers)

# What about Datasette?

[Datasette](https://datasette.io/) is a versatile open source tool for data analysis and publishing built on SQLite.
It's a popular solution for making SQLite data available to the public, but doesn't have built-in support for controlling access to published data.
For those Datasette users who need [fine grained access control](data-sharing-settings-invites-private-repositories), Splitgraph offers a solution.

Similar to Datasette, Splitgraph also makes it possible to directly query database tables [from the browser](select-from-the-browser).
Unlike Datasette, authenticated Splitgraph-backed web apps can even write to database tables.

Datasette documentation includes two demo databases, [`global-power-plants.db`](https://global-power-plants.datasettes.com/global-power-plants.db
) and [`legislators.db`](https://congress-legislators.datasettes.com/legislators.db), both of which can be loaded into Splitgraph. You can also try the SQLite version of the [Chinook](https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite?raw=true) reference database. Try [uploading them here](/upload)!

# Key differences and limitations

SQLite [inherited](https://www.pgcon.org/2014/schedule/events/736.en.html) most of it's syntax from PostgreSQL, so the majority of SQLite queries should work on Splitgraph out of the box.
A major difference is that by default, SQLite allows any type of data to be written to any column, regardless of the column's declared type.
In fact, column type declarations are entirely optional in SQLite!

PostgreSQL is more strict when it comes to enforcing data types. Importing SQLite databases which store different types of data in a column than what was declared in the table schema may fail.
Tables containing columns without a declared type are ignored by the current implementation of SQLite import.

We'd love to hear from you! If you're excited about this new feature or if you're missing something from the current SQLite import functionality, [let us know](mailto:hello@splitgraph.com?subject=sqlite%20import)!


Importing SQLite data into Splitgraph is now as easy as drag-n-drop!

SQLite file uploads


## 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


## What is Splitgraph?

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

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

## Introduction

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

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

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

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

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

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

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

## Example `splitgraph.yml` file

Here is a basic example that builds two Splitgraph repositories:

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

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

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

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

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

## Writing `splitgraph.yml` files

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

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

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

Click the button to get started:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#### Miscellaneous `sgr cloud` commands

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

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

### `splitgraph.yml` reference

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

### In Alpha: Web UI

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

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

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

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

## Conclusion

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

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

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


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

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

`splitgraph.yml`: Terraform for your data stack

splitgraph.yml: Terraform for your data stack


## Splitgraph helps teams collaborate on data

At Splitgraph, we're convinced there's better ways to collaborate on data than
sharing CSV files. The ability to
[query other databases](/blog/postgresql-fdw-aggregation-pushdown-multicorn-part-1)
using Postgres queries, import data from hundreds of data sources,
[transform it with dbt](/blog/airbyte-dbt-splitgraph),
and travel back in time to any previous version all serve the same
purpose: to give teams working with data a place to collaborate effectively.
That said, if you're working with CSV files, [we have that covered as well 😉](/blog/csv-to-sql-upload-to-data-delivery-network-ddn).

The quickest way to try Splitgraph is to simply sign up to Splitgraph.com, the
public data portal open to everyone. You can import or connect your own data
into private or public repositories. And you can query over 40,000 public data
tables within 60 seconds, just by
[pointing your Postgres client to `data.splitgraph.com:5432`](https://www.splitgraph.com/connect/query).

## Dedicated Data Portals

For organizations, we also provide **Dedicated Data Portals**, which are private
deployments of Splitgraph to the cloud infrastructure of your choice. You get
all the same features of Splitgraph.com, except within a dedicated single-tenant
instance that you can invite your teammates to.

Dedicated Data Portals are currently in beta. Starting today, you can provision
a demo environment free for 7 days with no credit card required. If you want to
continue experimenting, just get in touch with us and we'll extend the trial.
Once you're ready to buy, schedule a meeting to discuss your requirements and pricing.

## Try Splitgraph in a single-tenant environment

In addition to the features available on the public [Splitgraph.com](https://www.splitgraph.com),
a Dedicated Data Portal enables you to:

- Store your data in a single-tenant environment.
- Get your own Splitgraph subdomain.
- Invite colleagues, collaborators and customers.
- Run Splitgraph in your cloud provider's region to minimize latency and transfer costs.
- Integrate with your company's OAuth/SSO (e.g. Okta).
- Gain in-depth control over sharing and unlimited namespaces.

## Deploy now

Launch your own demo Dedicated Data Portal with just a few
clicks (signup, but no credit card required).

<ButtonLink href="/demo">Deploy Demo</ButtonLink>

## What to expect

After you signup and deploy the demo, we'll send you an email in about 20 minutes,
once your deployment is ready. It will look like this:

<img src="/static-v2/images/blog/demo/demo-welcome-email.png" />

Just click the button in the email to get started with your Dedicated Data Portal.


Deploy a demo instance of a Dedicated Data Portal, so you can experiment with Splitgraph features in a single-tenant environment. Try it today, free for 7 days, no credit card required.

Get your own private Splitgraph data portal


### Create a dataset you wish to share

The easiest way to add data to Splitgraph is by uploading a CSV file. Go
to [the Upload page](/upload) or use this widget:

<EmbeddedUpload />

See
a [previous blog post](/blog/csv-to-sql-upload-to-data-delivery-network-ddn)
for more information.

You can also run a Splitgraph client locally. Check out
the [five minute demo](https://www.splitgraph.com/docs/sgr-advanced/getting-started/five-minute-demo)
that will show you how to create a dataset and publish it on Splitgraph.

In addition, we're working on a UI to add data to Splitgraph that will let
you ingest and query data from over 100 SaaS sources and databases.

### Open the Publication Status dialog

You can find the new Publication Status dialog to the right of the repository
name.

![Location of the publication status dialog](/static-v2/images/blog/data-sharing-settings-private-repositories/data-sharing-publication-button.png)

In here, you can:

- set a repository as private. This means only you (and users you add) will be
  able to read data from it or even find it.
- invite users to your repository
- see who can access your repository and manage their access settings

![Publication status dialog](/static-v2/images/blog/data-sharing-settings-private-repositories/data-sharing-publication-dialog.png)

### Invite a collaborator by username or email

If your collaborator is already registered on Splitgraph, you can add them to
the repository using their username:

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/add-user-demo.mp4" type="video/mp4" />
</video>

You can also add a contributor by their e-mail address. If they are already
registered on Splitgraph, they will be automatically added to your repository.

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/invite-user-demo.mp4" type="video/mp4" />
</video>

If they're not registered, they will receive an e-mail with an invitation to
register on Splitgraph.

![Sample invitation email](/static-v2/images/blog/data-sharing-settings-private-repositories/data-sharing-invite-email.png)

### Manage levels of access

We currently support three roles:

- Reader: Can view and query tables in a repository through the [DDN](/connect) or pull images with [`sgr`](https://www.splitgraph.com/docs/sgr-advanced/architecture/sgr-client)
- Writer: Can modify the repository's README and push data to it using `sgr`
- Admin: Has all permissions and can control sharing

If a repository is public, everyone on Splitgraph has an implicit Reader role on it.

## Next steps

- Query the dataset over the [DDN](/connect) or the [REST API](https://www.splitgraph.com/docs/query/postgrest)
- Check out the
  [`splitgraph.yml`](https://www.splitgraph.com/docs/splitgraph.yml/introduction)
  format for a sneak peek of how you will be able to add more data to Splitgraph

## Want a private Splitgraph instance?

If you want Splitgraph for your internal company data,
then [join the private beta](https://www.splitgraph.com/join-beta) for our cloud
SaaS product. We can deploy a white-labeled instance to your own infrastructure
and setup a modern data stack for you without any hassle.


Splitgraph now supports advanced data sharing settings. Make a repository private, invite a collaborator and control their level of access, all from a simple Web UI.

Share datasets like Notion pages


## Introduction

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

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

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

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

## Using the `splitgraph.yml` file

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

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

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

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

repositories:

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

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

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

This snippet:

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

### Running the file

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

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

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

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

### Result

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

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

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

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

Interested in seeing how this works? Read on!

## How it works

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

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

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

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

Roughly, here's what we do:

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

### Introspection

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

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

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

### Patching sources

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

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

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

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

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

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

### Running dbt

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

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

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

## Local development

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

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

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

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

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

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

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

Finally, run the model:

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

## Future plans

### Integrating the dbt docs site

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

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

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

### Cataloging dbt models built on other warehouses

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

### Autogenerating dbt models

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

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

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

### Writeable DDN

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

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

## Conclusion

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

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

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


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

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

showcase

SQLite file uploads

(Ab)using CDNs for SQL queries

Seafowl: a database for analytics at the edge

`splitgraph.yml`: Terraform for your data stack

Get your own private Splitgraph data portal

Share datasets like Notion pages

Scheduling, versioning and cataloging: introducing our dbt integration

Product

Support

Company

Splitgraph

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

showcase

SQLite file uploads

(Ab)using CDNs for SQL queries

Seafowl: a database for analytics at the edge

splitgraph.yml: Terraform for your data stack

Get your own private Splitgraph data portal

Share datasets like Notion pages

Scheduling, versioning and cataloging: introducing our dbt integration

Product

Support

Company

Community

Splitgraph

`splitgraph.yml`: Terraform for your data stack