How columnar storage serves dashboards

A dashboard query usually touches many rows but only a few columns: total revenue by region for this year. A row-oriented database stores each record together, which suits fetching or updating one order, so answering that question means reading whole records. A columnar engine stores each column together, so the query reads only the columns it needs and skips the rest of the table.

Values in one column tend to resemble each other: regions repeat, dates arrive in order, amounts fall in familiar ranges. Columnar formats use this with encodings such as dictionaries and run lengths, so a column often takes far less space than the same data stored row by row.

Less to read means less time waiting on storage, and columnar engines then process values in batches that suit modern processors.

Columnar formats keep small summaries for each block of data, such as its minimum and maximum values. A filter on this month can skip every block whose dates cannot match, and data that is sorted or partitioned by date makes the skipping more effective. Much of what makes these engines suit analytics is reading less, not reading faster.

How a dashboard feels depends mostly on where its queries run, not on how its charts are drawn. A transactional database tuned for the application it serves is not built for wide aggregations, and analytical queries compete with that application for the same resources. Serving analytics from a columnar engine, whether your own warehouse or a managed one, separates the two workloads.

In DataSquares, a dashboard can query your own database or warehouse live, or read a table imported into the managed Warehouse, which stores data in columnar form. Reading the copy keeps analytical queries off the database your application depends on.