The Humble Data Warehouse

Warehouses, Minus the Enterprise Baggage

The phrase “data warehouse” tends to evoke images of heavy enterprise infrastructure: XML-based ETL pipelines, business intelligence suites, and OLAP cubes. But the core idea doesn't require any of that. A data warehouse is simply a local, structured copy of data from an external source, organized for flexible querying and analysis. On a modern stack, that concept scales down to something remarkably small and agile.

Consider an internal tool built at Heroku to archive commit data from the GitHub API. Over a few days, the app backfilled the entire commit history of the company's projects into a Postgres database, then left a scheduled job running to keep it current. The GitHub REST API is not designed for ad-hoc analysis; it presents data as resources with rigidly consistent interfaces. But by pulling that data into a relational database, the full expressiveness of SQL becomes available for filtering, joining, aggregating, and exploring it in any way imaginable.

The key is that the data has been reduced to exactly the subset that matters. Even though GitHub's commit history is vast, the local copy is small enough that any query runs in negligible time, even on a tiny database. This is a data warehouse in miniature, built without a single line of XML or a 200k-line framework. Just Postgres, an HTTP client, and a scheduled task.

Archiving Tweets

The same pattern works well on a personal scale. One developer has been archiving his own tweets for years using a tool that pulls them down from Twitter's API and stores them in Postgres. The advantage becomes clear the moment you want to find a link you posted months ago. Instead of manually paging back through your timeline, you can run a single targeted SQL query. The tool's ETL logic is written in Ruby, using community gems to keep the code concise.

A File Warehouse, End to End

The pattern can even be applied to your own filesystem. A common annoyance is a nearly full disk where the operating system does a poor job of highlighting the best deletion candidates. A small Postgres warehouse can solve this, and the full setup takes minutes.

First, create a database:

$ createdb home-warehouse

Second, create a table to hold the file information:

$ psql home-warehouse -c \
  'create table files (size bigint, name text, dir boolean)'

Finally, run a simple ETL script that uses du to populate the table. Note that the script multiplies the block counts by 512 to convert them into bytes:

$ du -a . | \
  ruby -n -a -e 'puts "#{$F[0].to_i * 512}\t#{$F[1]}\t#{File.directory?($F[1])}"' | \
  psql home-warehouse -c '\COPY files FROM STDIN'

After indexing a directory like ~/Downloads, the contents can quickly be inspected. In a real-world example, the directory is bloated with a serious amount of garbage:

=> select count(*) from files where dir = false;
 count
-------
 14819
(1 row)

=> select pg_size_pretty(sum(size)) from files where dir = false;
 pg_size_pretty
----------------
 18 GB
(1 row)

Deleting everything outright is risky, since the directory can occasionally be useful for digging up old files. So the first step is to query for the top candidates for deletion:

=> select name, pg_size_pretty(size) from files where dir = false order by size desc limit 30;

                             name                             | pg_size_pretty
--------------------------------------------------------------+----------------
 ./dchha39_Death_Throes_of_the_Republic_VI.mp3                | 300 MB
 ./GCC-10.7-v2.pkg                                            | 273 MB
 ./eclipse-standard-kepler-R-macosx-cocoa-x86_64.tar.gz       | 198 MB
 ./jdk-7u45-macosx-x64.dmg                                    | 184 MB
 ./andean.zip                                                 | 155 MB
 ./command_line_tools_for_xcode_june_2012.dmg                 | 147 MB
 ./dads_gift/musical_evenings_with_the_captain_1996.zip       | 126 MB
 ./ideaIC-12.1.4.dmg                                          | 117 MB
 ./complete/nzbget.log                                        | 116 MB
 ./dads_gift/musical_evenings_with_the_captain_ii_1997.zip    | 108 MB

Once identified, they can be removed:

$ psql home-warehouse -c \
  '\COPY (select name from files where dir = false order by size desc limit 30) TO STDOUT' | \
  xargs rm

This example is simple, but it demonstrates two important principles. First, data can be transformed on its way into the warehouse to make it easier to work with later. The blocks-to-bytes conversion in the Ruby script is a trivial example. Second, by copying only a slice of a larger dataset — just the ~/Downloads directory — the data volume is reduced to something trivial to query. A dataset that would otherwise be slow to analyze becomes instantly accessible.

The humble data warehouse is a pattern worth keeping in your toolbox. Postgres is freely available, and the supporting libraries for pulling data from APIs, RSS feeds, or any other source are a gem install or npm install away. You can build one of these for yourself today.