Get Your Ducks in a Row with Databases
summary
Small team replaces repeated live SQL Server queries with a local DuckDB cache layer written from R (DBI + duckdb) via a script named create_duckdb_database.R, precomputing aggregations and storing raw and result tables on each user PC. Performance improves from 8s per query → near-instant with a ~+2s app boot cost (net ~6s gain); spatial functions appear and work; risks are dataset drift between users and local-permissions exposure of row‑level data.
AI-written summary · about
chapters (7)
- 0:00DuckDB and Shiny apps
- 1:45Excel to R tools
- 3:32Shiny app bottlenecks
- 5:41DuckDB performance fix
- 8:14DuckDB in Shiny workflow
- 10:02Local DuckDB caveats
- 11:28About the speaker and resources
related talks
19mIn-Process Analytical Data Management with DuckDB
34mData Wrangling with DuckDB
18mduckplyr: Tight Integration of duckdb with R and the tidyverse
38mFrom Big Data to DuckDB: Ryan Boyd (MotherDuck) on Hyper-Tenancy, Duck Lake, and Making Data Fun.
27mIn Process Analytical Data Management with DuckDB
30mDuckDB: Bringing analytical SQL directly to your Python shell
33mData preparation and analytics on your laptop with DuckDB
25mA quack of all trades: DuckDB, a versatile analytical database to keep in your toolkit