Analytical Databases: BigQuery, ClickHouse & DuckDB
Relevant source files
The following files were used as context for generating this wiki page:
Forze provides specialized integrations for analytical workloads through BigQuery, ClickHouse, and DuckDB. These integrations allow developers to define analytical queries and ingestion pipelines using a consistent, engine-agnostic API while leveraging the specific strengths of each database backend. The system is designed around logical specifications that separate the query definition from the underlying infrastructure configuration.
Sources: examples/recipes/analytics_duckdb/app.py:23-28, skills/forze-analytics-bigquery/SKILL.md:14-16, skills/forze-analytics-clickhouse/SKILL.md:14-16
Core Analytics Architecture
The analytics system in Forze utilizes an abstraction layer where AnalyticsSpec defines the logical structure of data and queries, while specific infrastructure modules (e.g., BigQueryDepsModule, ClickHouseDepsModule) provide the physical mapping. This allows handlers to remain agnostic of whether they are querying a cloud data warehouse like BigQuery or an embedded engine like DuckDB.
Interaction Flow
The following diagram illustrates how an application interacts with the analytical ports to execute queries or ingest data.
Analytical ports are resolved from the ExecutionContext using logical names, allowing for seamless swapping of backends without changing business logic.
Sources: skills/forze-framework-usage/SKILL.md:43-55, examples/recipes/analytics_duckdb/app.py:84-89
Common Configuration Elements
Each analytical integration shares a common pattern for defining queries and ingestion targets.
| Component | Description |
|---|---|
AnalyticsSpec | Defines the logical route, read model, query parameters, and ingest model. |
AnalyticsQueryDefinition | Used within a spec to define specific named queries and their parameter models. |
AnalyticsQueryPort | The runtime interface used by handlers to execute queries (via run, run_page, or run_cursor). |
AnalyticsIngestPort | The runtime interface used to append data to analytical tables. |
Sources: skills/forze-analytics-bigquery/SKILL.md:20-25, skills/forze-analytics-clickhouse/SKILL.md:20-25, examples/recipes/analytics_duckdb/app.py:42-47
ClickHouse Integration
ClickHouse integration focuses on high-performance warehouse queries and batch ingestion. It supports tenant-aware configurations where queries can be automatically routed to specific databases based on the active tenant.
ClickHouse Query Configuration
Queries in ClickHouse use server-side placeholders in the format {field:Type}. The configuration also supports keyset pagination via a cursor_column.
@attrs.define(slots=True, kw_only=True, frozen=True)
class ClickHouseQueryConfig:
sql: str # ClickHouse SQL with {field:Type}
skip_total: bool = False # Skips COUNT wrapper
cursor_column: str | None = None # For keyset cursors
Sources: src/forze_clickhouse/execution/deps/configs.py:38-54
Tenant Awareness
The ClickHouseAnalyticsConfig can be marked as tenant_aware. In this mode, the adapter automatically binds the current tenant ID as a {tenant:UUID} parameter. Developers can also resolve databases per-tenant using the query_database field.
BigQuery Integration
BigQuery integration is designed for cloud data warehousing and supports streaming appends. It utilizes gcloud-aio-bigquery for asynchronous communication.
BigQuery Operations
BigQuery queries use @params syntax for SQL templates. The AnalyticsIngestPort provides an append method that returns details on accepted and rejected rows, allowing for robust error handling during partial streaming failures.
| Feature | implementation |
|---|---|
| Query Parameter Format | @param_name |
| Ingestion Method | Streaming Append |
| Local Testing | bigquery-emulator |
Sources: skills/forze-analytics-bigquery/SKILL.md:20-41
DuckDB Integration
DuckDB is utilized as an embedded analytical engine, often querying data lakes (Parquet, CSV, Iceberg) directly from the application process without requiring a separate server or warehouse.
Data Lake Querying
DuckDB configurations typically involve registering a Source (such as ParquetSource) as a view during the lifecycle step. The AnalyticsSpec remains engine-agnostic, while the DuckDbAnalyticsConfig contains the specific SQL.
# Mapping DuckDB SQL to a logical query
SALES_CONFIG = DuckDbAnalyticsConfig(
queries={
"by_region": DuckDbQueryConfig(
sql=(
"SELECT region, sum(total) AS total FROM sales "
"GROUP BY region HAVING sum(total) >= $min_total ORDER BY region"
),
),
},
)
Sources: examples/recipes/analytics_duckdb/app.py:53-65, examples/recipes/analytics_duckdb/app.py:84-95
Ingestion Patterns
Ingestion across these databases follows a batch-oriented approach via the AnalyticsIngestPort.
Summary
The analytical database module in Forze provides a unified interface for complex data processing. By using AnalyticsSpec to define logical requirements and specialized DepsModules for infrastructure, Forze enables developers to switch between embedded local processing (DuckDB), high-performance columnar storage (ClickHouse), and managed cloud warehousing (BigQuery) while maintaining consistent application code.
Sources: examples/recipes/analytics_duckdb/app.py:11-18, skills/forze-analytics-bigquery/SKILL.md:8-12, skills/forze-analytics-clickhouse/SKILL.md:8-12