Column by your name: The analytics database that skips the rows

<p>These days, every company looking at analyzing their data for insights has a data pipeline setup. Many companies have a fast production database, often a NoSQL or key-value store, that goes through a data pipeline.The pipeline process performs some sort of extract-transform-load process on it, then routes it to a larger data store that the analytics tools can access. But what if you could skip some steps and speed up the process with a database purpose-built for analytics?</p><p>On this sponsored episode of the podcast, we chat with Rohit (Ro) Amarnath, the CTO at <a href="https://www.vertica.com/">Vertica</a>, to find out how your analytics engine can speed up your workflow. After a humble beginning with a ZX Spectrum 128, he’s now in charge of Vertica Accelerator, a SaaS version of the Vertica database. </p><p>Vertica was founded by database researcher Dr. Michael Stonebreaker and Andrew Palmer. Dr. Stonebreaker helped develop several databases, including Postgres, Streambase, and VoltDB. Vertica was born out of research into purpose-built databases. Stonebreaker’s <a href="http://www.csail.mit.edu/user/1547">research</a> found that columnar database storage was faster for data warehouses because there were fewer read/writes per request. </p><p>Here’s a quick example that shows how columnar databases work. Suppose that you want all the records from a specific US state or territory. There are 52 possible values here (depending on how you count territories). To find all instances of a single state in a row-based DB, the search must check every row for the value of the state column. However, searching by column is faster by an order of magnitude: it just runs down the column to find matching values, then retrieves row data for the matches. </p><p>The Vertica database was designed specifically for analytics as opposed to transactional databases. Ro spent some time at a Wall Street firm building reports—P&L, performance, profitability, etc. Transactions were important to day-to-day operations, but the real value