The video clarified my initial confusion. DuckDB is an embedded column store library and borrows the SQLite C API and some of the SQLite philosophy (single cpp/hpp amalgamation file, no external dependencies, and resource sharing) but it uses PostgreSQL syntax and parser, and a column oriented memory/storage engine based on their experience with MonetDB/VectorWise.

DuckDB borrows code from HyPer DB which looks like a in-memory hybrid OLTP/OLAP clone of SAP HANA. They did not discuss any hybrid OLTP/OLAP implementation details in the video so the borrowed HyPer code may only be the wrapper for the PostgreSQL parser.

Desktop column stores do have a precedent in the enterprise market. Microsoft's first attempt at a column store started with embedded integration with PowerPivot and eventually made its way into SQL Server. Sybase IQ, built on zero-admin SQL Anywhere, has been used as a local column store for Pivot Table applications (think BusinessObjects, Cognos, Tableau, QlikView, and Excel Pivot Tables). Pivot Table integrations are typically custom-built for each data warehouse vendor or MDX/XMLA driver.

DuckDB is focused on integrating with the desktop data science platforms R and Python (dbplyr and pandas). Analytics/OLAP in SQL means lightning fast GROUP BY queries and more recently SQL OLAP Windows. "SQLite for Analytics" confused me but that is probably due to my own historical mental clutter. The name DuckDB refers to a pet duck and not the duck typing system in SQLite.

The fact that it’s column oriented is a massive improvement for analytics over SQLite, I don’t understand why they don’t mention this more explicitly on their website and GitHub pages.

I usually just extend SQLite with virtual columns and try to do the filtering/aggregate before sending tuples to the engine. This does the trick most of the time and it's relatively straightforward to implement.

I'm need to move an excel sheet to something like SQLite, hadn't heard of virtual columns, fixes most of the issues I have, thanks!