This is an open-source, open-data data-platform-in-a-box¹ based on DuckDB + dbt + Evidence. It offers a simple script to extract and load (EL) data from the GitHub Archive, a dbt project built on top of this data inside a DuckDB database, and BI tooling via Evidence to analyze and present the data.

It runs completely local or inside of a devcontainer, but can also run on MotherDuck as a production target. Some (me) call it the Quack Stack.

Most of the below setup will be done for you automatically if you choose one of the devcontainer options above, so feel free to skip to the Extract and Load section if you're using one of those. Please note that while devcontainers are very neat and probably the future, they also add some mental overhead and complexity at their present stage of development that somewhat offsets the ease of use and reproducibility they bring to the table. I personally prefer local development still for most things.

There are a few steps to get started with this project if you want to develop locally. We'll need to:

1. Clone the project locally.
2. Set up Python, then install the dependencies and other tooling.
3. Extract and load the data locally.
4. Transform the data with dbt.
5. Build the BI platform with Evidence.

We encourage to to run the setup steps for the sake of understanding them more deeply and learning, but if they feel overwhelming or, conversely, you're experienced with this stack and want to go faster, we've included a setup.sh bash script that will do everything to get you to baseline functioning automatically. Just source setup.sh and have at.

1. Install the GitHub CLI.
2. cd path/to/where/you/keep/projects
3. gh repo clone gwenwindflower/octocatalog
4. cd octocatalog
5. Next steps!

1. Set up SSH keys for GitHub.
2. Grab the SSH link from the green Code button in the top-right of the repo. It will be under Local > SSH.
3. cd path/to/where/you/keep/projects
4. git clone [ssh-link-you-copied]
5. cd octocatalog
6. Next steps!

You likely already have relatively recent version of Python 3 installed on your system. If you use the devcontainer options above it will be installed for you. If not, we recommend using pyenv to manage your python versions. You should be fine with anything between 3.7 and 3.11.

I highly recommnend aliasing python3 to just python in your shell. This will ensure you're using the right version of python and save you some thinking and typing. There's generally no practical reason the majority of data folks would ever need to use Python 2 at this point, and if you do, you probably know what you're doing an don't need this guide 😅. To alias python you can add this to your .bashrc or .zshrc:

alias python=python3

The rest of this guide will assume you've got python3 aliased to python, but if you don't you'll need to replace python with python3 in the commands below.

Once you have python installed you'll want to set up a virtual environment in the project directory. This will ensure the dependencies that we install are scoped to this project, and not globally on your system. I like to call my virtual environments .venv but you can call them whatever you want. You can do this with:

python -m venv .venv

Once we've got a Python virtual environment set up we'll need to activate it. You can do this with:

source .venv/bin/activate

Now that we're in an isolated virtual environment we can install the dependencies for this project. You can do this with:

python -m pip install -r requirements.txt

Now you know getting a typical Python project set up is as easy as 1-2-3:

python -m venv .venv # Create a virtual environment
source .venv/bin/activate # Activate the virtual environment
python -m pip install -r requirements.txt # Install the dependencies into the virtual environment

 alias python="python3"
 alias venv="python -m venv .venv"
 alias va="source .venv/bin/activate"
 alias venva="venv && va"
 alias pi="python -m pip"
 alias pir="python -m pip install -r"
 alias pirr="python -m pip install -r requirements.txt"
 alias piup="python -m pip install --upgrade pip"
 alias vpi="venva && piup && pirr"

This project used pre-commit to run basic checks for structure, style, and consistentcy. It's installed with the Python dependencies, but you'll need to run pre-commit install in the virutal environment to install the speciefic hooks defined by the checks in the .pre-commit-config.yaml. After that it will run all the checks on each commit automatically.

Extract and load is the process of taking data from one source, like an API, and loading it into another source, typically a data warehouse. In our case our source is the GitHub Archive, and our load targets are either: local, MotherDuck, or (soon S3).

You've go two options here: you can run the el scripts directly or you can use the configured task runner to make things a little easier. We recommend the latter, but it's up to you. If you're using one of the devcontainer options above Task is already installed for you.

If you run the script directly, it takes two arguments: a start and end datetime string, both formatted as 'YYYY-MM-DD-HH'. It is inclusive of both, so for example running python el.py '2023-09-01-01' '2023-09-01-02' will load two hours: 1am and 2am on September 9th 2023. Pass the same argument for both to pull just that hour.

This functionality is still cooking!

If you're comfortable with S3 and want to pull a larger amount of data, we've got you covered there as well. The el-modal.py script leverages the incredible Modal platform to pull data and upload it to S3 in parallelized, performant cloud containers. It works pretty much like the regular el.py script, you supply it with start and end datetime string in 'YYYY-MM-DD-HH' format, and it goes to town. Modal currently gives you $30 of free credits a month, which is more than enough to pull quite a bit of data.

There are some basic tasks included using my preferred task runner Task. This is optional for your convenience, you can also run the el.py script directly with Python. You can install it with most package managers:

brew install go-task

choco install go-task

scoop install task

yay -S go-task-bin

More install methods are detailed in the Task docs.

Tasks included are:

You can also manually run the el.py script with python3 el.py [args] to pull a custom date range, run on small test data file, and isolate the extract or load steps. Please note that the GitHub Archive is available from 2011-02-12 to the present day and that being event data it is very large. Running more than a few days or weeks will push the limits of DuckDB (that's part of the interest and goal of this project though so have at).

The args are:

python el.py [start_date in YYYY-MM-DD format, defaults to yesterday] [end_date in YYYY-MM-DD format, defaults to today] [-e --extract Run the extract part only] [-l --load Run the load part only] [-p --prod Run in production mode against MotherDuck]

Running the the el.py script without an -e or -l flag is a no-op as all flags default to false. Combine the flags to create the commands you want to run. For example:

python el.py -e # extract the data for the past day
python el.py -lp # load any data into the production database
python el.py 2023-09-20 2023-09-23 -elp # extract and load 3 days of data into the production database

In order for Evidence to work the DuckDB file needs to be built into the ./reports/ directory. If you're looking to access it via the DuckDB CLI you can find it at ./reports/github_archive.db.

dbt is the industry-standard control plane for data transformations. We use it to get our data in the shape we want for analysis.

The task runner is configured to run dbt for you task transform, but if you'd like to run it manually you can do so by running these commands in the virtual environment:

dbt deps # install the dependencies
dbt build # build and test the models
dbt run # just build the models
dbt test # just test the models
dbt run -s marts # just build the models int the marts folder

Evidence is an open-source, code-first BI platform. It integrates beautifully with dbt and DuckDB, and lets analysts author version-controlled, literate data products with Markdown and SQL. Like the other steps, it's configured to run via the task runner with task bi, but you can also run it manually with:

npm install --prefix ./reports # install the dependencies
npm run sources --prefix ./reports # build fresh data from the sources
npm run dev --prefix ./reports # run the development server

Evidence uses Markdown and SQL to create beautiful data products. It's powerful and simple, focusing on what matters: the information. You can add and edit markdown pages in the ./reports/pages/ directory, and SQL queries those pages can reference in the ./reports/queries/ directory. You can also put queries inline in the Markdown files inside of code fences, although stylistically this project prefers queries go in SQL files in the queries directory for reusability and clarity. Because Evidence uses a WASM DuckDB implementation to make pages dynamic, you can even chain queries together, referencing other queries as the input to your new query. We recommend you utilize this to keep queries tight and super readable. CTEs in the BI section's queries are a sign that you might want to chunk your query up into a chain for flexibility and clarity. Sources point to the raw tables, either in your local DuckDB database file or in MotherDuck if you're running prod mode. You add a select * [model] query to the ./reports/sources/ directory and re-run npm run sources --prefix ./reports and you're good to go.

Evidence's dev server uses hot reloading, so you can see your changes in real time as you develop. It's a really neat tool, and I'm excited to see what you build with it.

Schemas for the event data are documented here.

So far we've modeled:

• Issues
• Pull Requests
• Users
• Repos
• Stars
• Forks
• Comments
• Pushes