Day 01 of the WClickHouse Open-Source Engineering Series.

Why are engineers still writing manual SQL DDL strings in Python? WClickHouse binds Pydantic v2 models directly to ClickHouse columnar storage.

The Pain Points We Faced

• Writing 40-line CREATE TABLE DDLs by hand for every analytical event
• Mismatches between Python types and ClickHouse columnar storage engines
• Runtime data corruption from unvalidated dictionaries inserted into tables

The Implementation

from pydantic import BaseModel
from wclickhouse import WClickHouse
from datetime import datetime
from typing import List

class AnalyticsEvent(BaseModel):
    event_id: int
    event_name: str
    properties: List[str]
    created_at: datetime = datetime.now()

# Auto-creates table with MergeTree engine
db = WClickHouse(AnalyticsEvent, db_config)
db.insert(AnalyticsEvent(event_id=1, event_name="click", properties=["web", "cta"]))

Why This Architecture Wins

• Pydantic v2 Native: Model fields map 1:1 to ClickHouse columns automatically.
• Auto DDL Generation: Executes CREATE TABLE IF NOT EXISTS with optimal engines.
• Strict Validation: Validates types in memory before sending bytes to the server.

Verification & Status

Tested and verified against live ClickHouse server instances with 95%+ test coverage. Built for Python 3.9 through 3.14 with Apache Arrow and Pydantic v2.

• GitHub: https://github.com/wisrovi/wclickhouse
• PyPI: https://pypi.org/project/wclickhouse

ClickHouse #Python #DataEngineering #OLAP #BigData #Wisrovi