Key Takeaways
- DuckDB 2.0 and the Quack protocol make a shared DuckDB server practical, offering an alternative to PostgreSQL for teams that prefer object storage.
- Medallion layers describe responsibilities, not a requirement to store the same data three times.
- A semantic layer on top of Gold gives dashboards and AI agents the same business definitions, so valid SQL does not return the wrong number.
- Parquet is a sensible default for storage, with Apache Iceberg reserved for tables that need transactions, schema evolution, and consistent snapshots.
- AI agents should only access approved datasets and metrics based on the permissions of the users they represent.
- Architecture decisions should follow the workload, not a row count or the popularity of a technology.
Previous articles on Lean Data Engineer have been extremely critical of distributed frameworks such as PySpark. Unfortunately, this article is not going to be any different. We previously proposed a lean data architecture using Python, DuckDB, and PostgreSQL, which remains a sensible option for many companies. However, with the introduction of DuckDB 2.0, there is an opportunity to explore an alternative architecture, particularly for companies that have outgrown PostgreSQL or prefer an object-storage-based approach. The Lean Data Platform uses Python and DuckDB for processing, Parquet and Apache Iceberg for storage, and a DuckDB server to handle analytical queries for both BI tools and AI agents. The objective remains the same: build what is needed and avoid engineering for problems that do not exist.
Medallion Architecture and the Semantic Layer
The medallion architecture provides a straightforward way to organize data from ingestion to reporting. Bronze preserves records from source systems, Silver handles cleaning, validation, and standardization, and Gold organizes the results into models that analysts and applications can use. Star and snowflake schemas are still useful here because they provide predictable relationships between facts and dimensions.
Not every pipeline needs three physical layers. If a transformation can move directly from raw data into a business-ready model without creating maintenance problems, there is little benefit in writing another intermediate table. The layers describe responsibilities, not a requirement to store the same data three times.
On top of Gold sits a semantic layer that defines business metrics and relationships. This becomes especially important when AI agents begin querying the platform. An agent might write valid SQL and still return the wrong number because it does not know the company’s definition. Shared definitions let dashboards and agents use the same calculations.
Storage and Processing
The storage layer uses Parquet files in S3 or another object store. Parquet is columnar, compresses efficiently, and allows analytical engines to read only the data needed for a query. Open formats also let us change engines without migrating into another warehouse.
Apache Iceberg is an option for tables that need transactions, schema evolution, and consistent snapshots. Iceberg does introduce additional table-management requirements, so it should not be the default for every dataset. For simple batch pipelines, plain Parquet files may be enough.
Python handles extraction, scheduling, retries, and validation, while DuckDB performs the SQL transformations. DuckDB can process data directly from Parquet, use multiple CPU cores, and work with datasets larger than the machine’s available memory. That does not make distributed processing obsolete, but it does mean there is little reason to start with Spark when the workload can run comfortably on one machine.
DuckDB 2.0 and Quack
Our previous architecture relied on PostgreSQL as the analytical warehouse, providing a familiar SQL interface and broad compatibility with reporting tools. That approach remains practical, particularly for predictable analytical workloads. However, organizations working with larger analytical datasets or looking to separate compute from storage may benefit from an alternative approach.
DuckDB has historically been an excellent processing engine, but its embedded architecture made it less convenient as a shared analytical service. With DuckDB 2.0 and the Quack protocol, DuckDB can operate in a client-server configuration, allowing remote DuckDB clients to execute queries through a shared instance. The new CONNECT statement further simplifies these connections.
For this data platform, this means a dedicated DuckDB server can query Gold tables stored in S3 and return results to connected applications. Rather than loading analytical datasets into a separate warehouse, we can query them directly using DuckDB. Compute resources remain independent from the underlying storage, and the same engine can support transformations and analytical queries.
Quack also supports concurrent clients, although the server still has finite memory and CPU resources. Existing BI tools may require compatible connectors or adapters, but the architecture provides another way to serve analytical workloads without introducing a distributed query engine.
Agentic Analytics and Governance
Agentic analytics is another reason to consider this architecture. Traditional dashboards are designed around predefined reports and metrics, while AI agents can investigate analytical questions dynamically. An agent could compare gross margins across products, investigate changes in customer behavior, or identify unexpected trends without requiring a dedicated dashboard for each question.
The semantic layer provides definitions of business metrics, dimensions, and relationships, allowing agents to query information without independently interpreting how each metric should be calculated. DuckDB handles the analytical queries while the agent interprets the results. DuckDB 2.0 also introduces an agent mode for its CLI, improving how coding agents interact with query results and errors.
Security and governance are equally important. AI agents should not have unrestricted access to analytical data simply because they can generate SQL. Storage permissions can be controlled through IAM, while the DuckDB server and semantic interface must enforce authentication and authorization. Agents should only access approved datasets and metrics based on the permissions of the users they represent. We will explore these considerations in greater detail in future articles.
Building for Actual Requirements
The Lean Data Platform is not intended to replace PostgreSQL, Spark, or every existing data architecture. PostgreSQL remains an excellent choice for many analytical workloads, while distributed frameworks provide capabilities that become necessary under certain requirements. The decision should depend on the workload rather than the popularity of a particular technology.
There is no fixed number of rows or terabytes at which a company must adopt Spark. Query patterns, concurrency, refresh schedules, and acceptable response times matter just as much as data volume. The objective is to start with infrastructure that meets current requirements and introduce additional complexity when there is a measurable reason to do so.
The Lean Data Platform provides an alternative for organizations that want to take advantage of open storage formats, lightweight analytical processing, and AI-driven analytics without committing to a distributed architecture.
Simple Platforms for AI-Driven Analytics
As AI agents become more involved in business analytics, data platforms will need to support new ways of accessing and interpreting information. However, the underlying principles remain unchanged. Reliable pipelines, well-structured datasets, consistent business definitions, and appropriate governance are still the foundation of a good data platform.
Whether the architecture uses PostgreSQL, DuckDB, or a distributed engine, the technology should be selected based on the requirements of the business. A modern data platform does not need to be complicated to be effective.
If your organization is evaluating its current data architecture, considering a move away from an existing warehouse, or looking to simplify its analytical infrastructure, this is the type of work we focus on at Constellation Systems. We help businesses design and implement practical data systems that align with their needs without introducing unnecessary complexity.