Skip to content

Backends

Pivotal supports four execution backends. The same DSL source runs unchanged on any of them.

Comparison

pandas Polars DuckDB SQL CTE
Execution Python in-process Python in-process In-process SQL engine Generates SQL only
Speed Fast for small–medium data Fast — zero-copy columnar Fast for large data N/A — no execution
Memory Loads everything Columnar, efficient Columnar, lazy N/A
Python ops Full support Full support Supported (via pandas fallback) Skipped
Plots/tables Full support Full support Full support Skipped
Output Python objects Python objects Python objects .sql file
Best for Interactive exploration Performance-sensitive work Large datasets, production Sharing with SQL users

Pandas (default)

The default backend. Uses pandas DataFrames throughout. Best for interactive analysis and datasets that fit comfortably in memory.

# Python API
parser.execute(dsl, backend='pandas')
parser.generate_code(results, backend='pandas')

# Jupyter — set for the session
%pivotal_set backend=pandas

# Per-cell override
%%pivotal backend=pandas

Polars

Uses Polars DataFrames throughout. Polars is a fast, columnar DataFrame library with a Rust core. It is a good choice when you want pandas-style in-process execution but with better performance on larger datasets.

Requires pip install pivotal[polars].

parser.execute(dsl, backend='polars')
parser.generate_code(results, backend='polars')

%pivotal_set backend=polars

%%pivotal backend=polars

Plots and Great Tables output work identically to the pandas backend — Polars DataFrames are converted to pandas automatically at the plot/table boundary so the Pivotal viewer works without changes.

Notes:

  • Requires polars >= 0.20. On older CPUs without AVX2, install polars-lts-cpu instead of polars.
  • The Polars backend uses strict typing — fill_null only fills nulls whose column type matches the fill value.

DuckDB

Executes operations using DuckDB's in-process SQL engine. Significantly faster for large datasets (hundreds of millions of rows), uses columnar storage, and supports lazy evaluation.

Requires pip install pivotal[duckdb].

parser.execute(dsl, backend='duckdb')
parser.generate_code(results, backend='duckdb')

%pivotal_set backend=duckdb

%%pivotal backend=duckdb

Limitations with DuckDB:

  • Python-only operations (apply, multi-line python...end blocks, or user-defined Python functions that aren't DuckDB UDFs) fall back to pandas for that step
  • SQL dialect is DuckDB SQL — some edge cases may differ from pandas behaviour

SQL CTE

Generates a pure SQL WITH...AS chain. Does not execute the SQL — it writes it to a .sql file or returns it as a string.

Useful for: - Sharing queries with analysts using SQL tools (DBeaver, DataGrip, Tableau) - Producing dbt model files - Auditing what a notebook computes

parser.generate_code(results, backend='sql')

%pivotal_set backend=sql

Limitations with SQL CTE:

  • Python operations (apply, plot, table, show, python, python...end) are skipped and replaced with a -- [skipped: ...] comment
  • fillna is not yet supported (skipped)
  • Output targets DuckDB SQL dialect; minor adjustments may be needed for other databases

Example output:

-- Generated by Pivotal from: analysis.ipynb

WITH
_cte_0_sales AS (
  SELECT * FROM sales WHERE price > 100
),
_cte_1_sales AS (
  SELECT region, SUM(revenue) AS total FROM _cte_0_sales GROUP BY region
)
SELECT * FROM _cte_1_sales

Setting the backend

JupyterLab

Persistent for the session:

%pivotal_set backend=duckdb

Override for a single cell:

%%pivotal backend=duckdb
with sales as summary
    group by region
        agg sum revenue as total

Python API

from pivotal import DSLParser

parser = DSLParser()
results = parser.parse(dsl)

# generate code
code = parser.generate_code(results, backend='duckdb')

# or execute directly
parser.execute(dsl, backend='duckdb')

CLI — notebook export

pivotal --export-py notebook.ipynb           # pandas

Or use the Export to Code File menu in JupyterLab and choose the format from the dropdown.