I keep seeing teams reach for a full data warehouse when all they need is fast, in-process analytics next to their Spring AI code. DuckDB fills that gap better than I expected, and I wrote up the patterns with real Spring Boot samples. If you're wiring analytics into an AI-driven app, this one's worth a look.
I ran into DuckDB almost by accident, and it took me a while to see its real use case. We were building out the analytics module for the ATS console at ItJobOpportunities, the one that shows recruiters funnel numbers: applications per job, time-to-hire, skill match distributions. Nothing fancy at first, just aggregate queries over data that already lived in our PostgreSQL job schema. But every time I ran one of those "give me a monthly breakdown of applications by skill category" queries directly against production Postgres, I could feel it. The query planner doing scans it didn't love, the connection pool under a bit more pressure than I wanted during business hours. What actually got me paying attention to DuckDB wasn't the reporting problem, though. It was a different question I kept running into once I started wiring Spring AI into the platform: how do you give an LLM structured access to your data without either dumping your whole schema into a prompt or building a bespoke query layer for every AI feature. DuckDB turned out to be a surprisingly good answer to that, and that's the angle I want to focus on here. What DuckDB actually is DuckDB is an embedded, in-process analytical database. No server, no daemon, no separate deployment to manage. You add a JAR (or a Python package, or a CLI binary), and you get a full SQL engine that runs inside your application's process, backed by a columnar storage format optimized for scans and aggregations rather than row-by-row lookups. If you've used SQLite, the mental model is close: it's a file (or an in-memory instance) your app talks to directly. But where SQLite is built for transactional row access, DuckDB is built for analytical queries. Think GROUP BY , window functions, joins across large tables, aggregations over millions of rows. It's OLAP where SQLite is OLTP, running in the same "no server" spirit. A few things about it that took me longer to appreciate than they should have: Vectorized execution. DuckDB processes data in column batches rather than row by row, which is why aggregations over millions of rows finish in a blink compared to a row-store doing the same work. Zero-copy file access. It reads Parquet , CSV, and JSON directly off disk or over HTTP, without an import step. Point a query at a file and it behaves like a table. Extension model. Features like httpfs (remote file access), tpch (benchmark data generation), and json ship as optional extensions you load on demand, so the core engine stays small. Single-file portability. A .duckdb file is a self-contained database. You can hand someone a file and they have the whole thing, schema and data included. That combination, an in-process engine plus direct file querying, is what made me think of it as an AI data layer rather than just a faster reporting tool. An LLM tool call that runs SQL against DuckDB doesn't need network access to a database server, doesn't need credentials to anything sensitive, and doesn't need an ETL step before it can answer a question. Getting comfortable with it using a plain CSV Before I trusted DuckDB anywhere near exported application data, I wanted to see how it behaved against something simple and inspectable. I keep a small CSV of fictional job application records for exactly this kind of dry run: job_id,candidate_id,company_size,skill_category,applied_at,relevance_score 1001,c-204,startup,backend,2026-01-04,82 1002,c-311,enterprise,frontend,2026-01-06,74 1003,c-118,startup,backend,2026-01-09,91 1004,c-457,midsize,devops,2026-01-12,68 1005,c-204,enterprise,backend,2026-01-15,88 DuckDB reads that file directly, no import, no schema definition, with read_csv_auto : SELECT skill_category, count(*) AS total_applications, avg(relevance_score) AS avg_relevance FROM read_csv_auto('data/sample_applications.csv') GROUP BY skill_category ORDER BY total_applications DESC; That's the exact query shape I later handed to a Spring AI tool. Once I trusted the SQL against a CSV I could read in a text editor, moving to real exported Parquet snapshots was a matter of swapping the file path, not rethinking the approach. Using application.yaml to keep the sandbox configurable Once I moved from a one-off script to an actual Spring Boot service, I didn't want file paths hardcoded anywhere. I externalized the DuckDB source location into application.yaml , the same way I'd configure a datasource URL: duckdb: analytics: mode: csv csv-path: classpath:data/sample_applications.csv parquet-glob: exports/job_application_*.parquet read-only: true And a small properties class to bind it: @ConfigurationProperties(prefix = "duckdb.analytics") public record DuckDbAnalyticsProperties( String mode, String csvPath, String parquetGlob, boolean readOnly ) { } @Configuration @EnableConfigurationProperties(DuckDbAnalyticsProperties.class) public class DuckDbConfig { @Bean(name = "duckDbJdbcTemplate") public JdbcTemplate duckDbJdbcTemplate() { HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:duckdb:"); config.setMaximumPoolSize(1); config.setReadOnly(true); return new JdbcTemplate(new HikariDataSource(config)); } } The tool itself reads the active source from properties, so I can flip between the sample CSV in development and the real Parquet exports in production without touching a line of Java: @Component public class ApplicationAnalyticsTool { private final JdbcTemplate duckDbJdbcTemplate; private final DuckDbAnalyticsProperties properties; public ApplicationAnalyticsTool( @Qualifier("duckDbJdbcTemplate") JdbcTemplate duckDbJdbcTemplate, DuckDbAnalyticsProperties properties) { this.duckDbJdbcTemplate = duckDbJdbcTemplate; this.properties = properties; } @Tool(description = "Runs a read-only analytical SQL query against job application " + "data. Source depends on environment: a sample CSV in development, " + "nightly Parquet snapshots in production. Use this for aggregate " + "questions about applications, skills, and hiring trends. Do not use " + "for live or per-candidate lookups.") public List<Map<String, Object>> runAnalyticsQuery(String sql) { if (!isReadOnlySelect(sql)) { throw new IllegalArgumentException("Only SELECT statements are permitted."); } return duckDbJdbcTemplate.queryForList(sql); } public String sourceExpression() { return "csv".equals(properties.mode()) ? "read_csv_auto('%s')".formatted(properties.csvPath()) : "read_parquet('%s')".formatted(properties.parquetGlob()); } private boolean isReadOnlySelect(String sql) { String normalized = sql.trim().toUpperCase(); return normalized.startsWith("SELECT") && !normalized.contains("INSERT") && !normalized.contains("UPDATE") && !normalized.contains("DELETE") && !normalized.contains("DROP") && !normalized.contains("ATTACH"); } } That isReadOnlySelect check is crude on purpose. I don't rely on it as my only line of defense. The real safety comes from the fact that this DuckDB instance never sees a write-capable connection to anything that matters, whether it's pointed at a CSV in development or a Parquet snapshot in production. Why Spring AI needs a data layer like this Spring AI gives you a clean abstraction over chat models, embeddings, and tool calling. Once you start building anything past a basic chatbot, you run into the same problem I did: your LLM needs to answer questions grounded in real data, and you have to decide how that grounding happens. The common patterns are: Stuff everything into the prompt. Works for small, static datasets. Falls apart fast once you're talking about application history or skill distributions with tens o...