cwind-tableau

---

Installation

pip install cwind-tableau

Requires Python >= 3.10.

Quick Start

import cwind_tableau as tab

# Reference columns and apply aggregations
total_sales = tab.col("amount").sum()
avg_sales = tab.col("amount").avg().alias("avg_amount")

# Create a view with dimensions and aggregations
view = tab.view(
    table="sales_table",
    dims=["product", "region"],
    aggs=[total_sales, avg_sales],
)

# Generate SQL
print(view.to_sql())
# SELECT product, region, SUM(amount) AS "SUM(amount)", AVG(amount) AS "avg_amount"
# FROM sales_table
# GROUP BY product, region

# Execute with DuckDB
result = view.to_query()

Features

Column Reference

tab.col("amount")  # Refer to a column
tab.col("order_date")  # Refer to a date column

Built-in Aggregations

tab.col("amount").sum()  # Sum
tab.col("amount").avg()  # Average
tab.col("amount").count()  # Count
tab.col("amount").count_distinct()  # Distinct count
tab.col("amount").min()  # Minimum
tab.col("amount").max()  # Maximum
tab.col("amount").median()  # Median
tab.col("amount").std()  # Standard deviation
tab.col("x").custom("MY_FUNC")  # Custom aggregation function (simple mode)
tab.col("x").custom("MY_FUNC({col}, {b}, {c})", b=1, c='xxx')  # Template mode: MY_FUNC(x, 1, 'xxx')

Custom Aliases

tab.col("amount").sum().alias("total_sales")  # Aggregation alias
tab.col("order_date").dt.year().alias("year")  # Dimension alias

Date Extraction (.dt accessor)

tab.col("order_date").dt.year()  # Year
tab.col("order_date").dt.month()  # Month
tab.col("order_date").dt.day()  # Day
tab.col("order_date").dt.quarter()  # Quarter
tab.col("order_date").dt.week()  # Week number
tab.col("order_date").dt.month_name()  # Month name (e.g. "January")

Window Dimensions (.fixed())

Turn an aggregation into a window function dimension:

# First order date per customer — used as a dimension, not an aggregation
tab.col("order_date").min().fixed("customer_id").alias("first_order_date")

# Multi-column partition
tab.col("order_date").min().fixed(["customer_id", "product"])

INCLUDE / EXCLUDE LOD Dimensions

Create dimensions based on Level of Detail (LOD) expressions:

# INCLUDE: partition by view_dimensions ∪ product
tab.col("amount").sum().include("product").alias("amount_by_product")

# EXCLUDE: partition by view_dimensions − region
tab.col("amount").sum().exclude("region").alias("amount_excl_region")

# Multi-column
# tab.col("amount").sum().include(["product", "city"])
# tab.col("amount").sum().exclude(["region", "category"])

Method	LOD Type	Partition Columns
.fixed(cols)	FIXED	cols only, ignoring view dimensions
.include(cols)	INCLUDE	view_dimensions ∪ cols
.exclude(cols)	EXCLUDE	view_dimensions − cols

LOD as Measure

Apply aggregations on LOD dimensions to create LOD measures (e.g. SUM({FIXED ...})):

# FIXED as measure — SUM({FIXED [product] : SUM([amount])})
tab.col("amount").sum().fixed("product").sum().alias("product_total")

# INCLUDE as measure — AVG({INCLUDE [product] : SUM([amount])})
tab.col("amount").sum().include("product").avg().alias("avg_by_product")

# EXCLUDE as measure — MAX({EXCLUDE [region] : SUM([amount])})
tab.col("amount").sum().exclude("region").max().alias("max_excl_region")

# Use in a View
view = tab.view(
    table="sales",
    dims=["region"],
    aggs=[
        tab.col("amount").sum().alias("region_sales"),
        tab.col("amount").sum().fixed("product").sum().alias("product_total"),
    ],
)

Arithmetic Expressions

# Calculate amount = price × quantity, then sum
(tab.col("price") * tab.col("quantity")).sum()

CASE WHEN Conditional Expressions

Build CASE WHEN conditional expressions via tab.if_(), usable as dimensions:

# Simple classification
c = tab.if_(tab.col("amount") > 500, "big").else_("small").alias("order_size")

# Multi-branch classification
c = (
    tab.if_(tab.col("amount") > 500, "high value")
    .else_if_(tab.col("amount") > 200, "medium value")
    .else_("low value")
    .alias("value_category")
)

# Use in a View
view = tab.view(
    table="sales",
    dims=["region", c],
    aggs=[tab.col("amount").sum().alias("total")],
)

# Combined with window dimensions (FixedDim)
daily_profit = tab.col("profit").sum().fixed("order_date").alias("daily_profit")
c2 = (
    tab.if_(daily_profit > 2000, "highly profitable")
    .else_if_(daily_profit < 0, "unprofitable")
    .else_("profitable")
    .alias("daily_category")
)

Supported operators: >, <, >=, <=. Values support int, float, str, Column.

API Reference

tab.col(name: str) -> Column

Create a column reference. Returns a Column object with aggregation and date extraction methods.

tab.view(table: str, dims: list, aggs: list) -> View

Create a pivot view.

Parameter	Type	Description
table	str	Source table name
dims	list[str | Dimension]	Dimension columns for GROUP BY
aggs	list[Aggregation]	Aggregation expressions for SELECT

View.to_sql() -> str

Generate the SQL string (DuckDB dialect).

View.to_query(con=None) -> duckdb.DuckDBPyResult

Execute the query via DuckDB.

• Without con: uses duckdb.query() on the default connection
• With con: uses con.query() on the provided connection

Architecture

cwind-tableau/
├── src/cwind_tableau/
│   ├── api/          # Facade: tab.col, tab.view
│   ├── core/         # Orchestration: View, SQL generation
│   └── models/       # Entities: Column, Aggregation, Dimension
├── tests/
├── docs/
└── examples/

Design Principles

• Reusable aggregations: Aggregation expressions can be defined as variables and reused across queries
• Default aliases: Auto-generated aliases (e.g. "SUM(amount)") when .alias() is not used
• Chainable API: All operations via method chaining — consistent and readable
• Expressions are objects: Any intermediate result (column, aggregation, dimension) is a Python object that can be stored, passed, and composed

Dependencies

• DuckDB — SQL execution engine
• sqlglot — SQL generation and future dialect transpilation

License

MIT