Polars / Chapter 7 of 7

SQL, databases, and data quality

Query databases with Polars and SQLAlchemy, use SQLContext, and test data quality. Includes ConnectorX, ADBC, and Altair examples.

8 topicsUpdated 22 Oct 2026

43 / 50

Data quality checks and tests#

Test table meaning and types, not only whether a pipeline runs.

A data contract states required fields, types, ranges, uniqueness, and relationships. polars.testing compares values, column order, and types. Floating-point reports may need tolerances; exact integer or decimal money should use exact checks. Include empty tables, nulls, NaNs, duplicate keys, invalid conversions, and round trips. Sort when output order is part of your contract.

PythonTest empty input, duplicates, eager/lazy equality, and rounding rules

from decimal import Decimal
import polars as pl
from polars.testing import assert_frame_equal

schema = {"customer": pl.String, "cents": pl.Int64}
empty = pl.DataFrame(schema=schema)
report = empty.group_by("customer").agg(pl.col("cents").sum())
assert report.height == 0
assert dict(report.schema) == schema
frame = pl.DataFrame({"customer": ["A", "A"], "cents": [100, 200]})
assert_frame_equal(
    frame.group_by("customer").agg(pl.col("cents").sum()),
    frame.lazy().group_by("customer").agg(pl.col("cents").sum()).collect(),
)
assert frame.group_by("customer").len().filter(pl.col("len") > 1).height == 1
amounts = pl.Series(
    "amount", [Decimal("1.225"), Decimal("1.235")], dtype=pl.Decimal(18, 3)
)
print("half to even:", amounts.round(2, mode="half_to_even").to_list())
print("half away from zero:", amounts.round(2, mode="half_away_from_zero").to_list())
print("empty, duplicate, and execution checks: passed")

TextExpected output

half to even: [Decimal('1.220'), Decimal('1.240')]
half away from zero: [Decimal('1.230'), Decimal('1.240')]
empty, duplicate, and execution checks: passed

Empty aggregates and rounding

An empty grouped table has no groups and therefore no report rows; a global sum may return a single zero. round applies a policy but does not necessarily change decimal scale. Test positive and negative midpoint values plus overflow at your declared precision before using the policy for billing. A good reconciliation test deliberately includes an unmatched key on each side and a duplicate lookup key that must fail. For organizing test suites, see the pytest guide. For deeper connection and transaction patterns, see the SQLAlchemy guide.

Official reference: Data quality checks and tests.

44 / 50

Database workflows with SQLAlchemy#

Read a database query through an existing connection and keep filtering in the database when appropriate.

read_database loads query results into Polars; it is not a lazy database scan. Select the rows and columns in SQL, using bound parameters for external values. SQLAlchemy manages the connection.

ShellInstall SQLAlchemy; writing also uses pandas and PyArrow

python -m pip install "sqlalchemy[asyncio]==2.1.1" "pandas==3.0.6" "pyarrow==25.0.1"

PythonQuery a local database with a parameter and write a report table

import polars as pl
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///:memory:")
with engine.begin() as connection:
    connection.execute(text("CREATE TABLE sales (region TEXT, cents INTEGER)"))
    connection.execute(
        text("INSERT INTO sales VALUES (:region, :cents)"),
        [{"region": "Dhaka", "cents": 100}, {"region": "Sylhet", "cents": 200}],
    )
    df = pl.read_database(
        text("SELECT region, cents FROM sales WHERE cents >= :minimum"),
        connection=connection,
        execute_options={"parameters": {"minimum": 150}},
    )
    report = df.group_by("region").agg(pl.col("cents").sum())
    report.write_database("report", connection=connection, if_table_exists="fail")
    print(
        pl.read_database(
            "SELECT region, cents FROM report ORDER BY region", connection
        ).to_dicts()
    )
engine.dispose()

TextExpected output

[{'region': 'Sylhet', 'cents': 200}]

Batch reading and transactions

read_database can yield batches with iter_batches=True and batch_size, but a driver may still buffer the server result unless server-side cursor/streaming options are enabled. Write append/replace/fail behavior deliberately. A database transaction can protect database changes; it does not automatically include exported files. Database driver packages are separate from SQLAlchemy itself. With SQLAlchemy 2.1, install the asyncio extra even for this sync Polars workflow: Polars imports SQLAlchemy’s async-session class while recognizing the connection, and that import requires greenlet.

Official reference: Database workflows with SQLAlchemy.

45 / 50

ConnectorX and ADBC database transfers#

Use an Arrow-oriented transfer engine when large query results make row-wise conversion expensive.

ConnectorX reads supported database queries into Arrow-compatible columns. read_database_uri takes a connection URI and defaults to ConnectorX. ADBC means Arrow Database Connectivity; it offers database-specific drivers and an Arrow-oriented interface. Choose a supported database/driver and test its type mapping, nulls, decimals, and time zones. A returned DataFrame still occupies memory.

ShellInstall ConnectorX and PyArrow

python -m pip install "connectorx==0.4.6" "pyarrow==25.0.1"

PythonRead a complete local SQLite query with ConnectorX

from pathlib import Path
from tempfile import TemporaryDirectory
import sqlite3
import polars as pl

with TemporaryDirectory() as folder:
    path = Path(folder) / "sales.db"
    with sqlite3.connect(path) as connection:
        connection.execute("CREATE TABLE sales (region TEXT, cents INTEGER)")
        connection.executemany(
            "INSERT INTO sales VALUES (?, ?)", [("Dhaka", 100), ("Dhaka", 200)]
        )
    result = pl.read_database_uri(
        "SELECT region, SUM(cents) AS total FROM sales GROUP BY region",
        uri="sqlite://" + str(path),
        engine="connectorx",
    )
    print(result.to_dicts())

TextExpected output

[{'region': 'Dhaka', 'total': 300}]

ADBC alternative

For SQLite install adbc-driver-sqlite, then pass engine="adbc" to read_database_uri with the appropriate URI. Other databases require their matching driver. Do not concatenate untrusted values into a SQL query; use the parameterized connection route when supported parameters are needed. Parallel partitioned database reads need a consistent snapshot and a suitable partition key; they are not guaranteed to see one stable state by default.

Official reference: ConnectorX and ADBC database transfers.

46 / 50

SQL over Polars frames#

Use SQLContext when SQL is clearer for a table query; execution still belongs to Polars.

SQLContext registers frames under table names and translates supported SQL into a Polars query. It is not a persistent database or a complete implementation of another SQL dialect. SELECT chooses output fields, GROUP BY defines groups, and SUM aggregates them. The default context returns a LazyFrame for collect.

PythonBuild a region report in SQL and compare it to expressions

import polars as pl
from polars.testing import assert_frame_equal

sales = pl.DataFrame({"region": ["Dhaka", "Dhaka", "Sylhet"], "cents": [100, 200, 300]})
with pl.SQLContext(sales=sales) as context:
    result = context.execute(
        "SELECT region, SUM(cents) AS total FROM sales GROUP BY region ORDER BY region"
    ).collect()
native = (
    sales.group_by("region").agg(pl.col("cents").sum().alias("total")).sort("region")
)
assert_frame_equal(result, native)
print(result.to_dicts())

TextExpected output

[{'region': 'Dhaka', 'total': 300}, {'region': 'Sylhet', 'total': 300}]

Official reference: SQL over Polars frames.

47 / 50

Range joins and row combinations#

Sometimes a match means a value falls within a range, rather than equal IDs.

A non-equi join uses conditions such as less-than and greater-than. join_where returns all row pairs satisfying its predicates. A cross join generates every possible pair before later filtering; it can be enormous. Range joins need checks for overlapping ranges and missing coverage because one input row may match several bands.

PythonAssign orders to nonoverlapping shipping weight bands

import polars as pl

orders = pl.DataFrame({"order": [1, 2, 3], "grams": [500, 1500, 5000]})
bands = pl.DataFrame(
    {"band": ["small", "medium"], "low": [0, 1000], "high": [1000, 3000]}
)
matched = orders.join_where(
    bands, pl.col("grams") >= pl.col("low"), pl.col("grams") < pl.col("high")
)
print("matched:", matched.select("order", "band").sort("order").to_dicts())
print(
    "uncovered orders:",
    orders.join(matched.select("order").unique(), on="order", how="anti")[
        "order"
    ].to_list(),
)
assert matched["order"].n_unique() == matched.height

TextExpected output

matched: [{'order': 1, 'band': 'small'}, {'order': 2, 'band': 'medium'}]
uncovered orders: [3]

Range boundaries and experimental behavior

The lower boundary is inclusive and the upper boundary is exclusive, so 1000 grams matches only medium. The 5000-gram order remains uncovered. Overlapping bands could produce multiple matches; assert uniqueness or report them explicitly. join_where is an experimental API and does not preserve input order.

Official reference: Range joins and row combinations.

48 / 50

Altair for charts from Polars summaries#

Pass a small summary to a charting library after checking the values.

Altair describes charts with encodings that map fields to visual properties. A nominal field is a category, an ordinal field has category order, a quantitative field is numeric, and a temporal field is time. Altair can accept Polars tables through its dataframe interchange support. Aggregate first so the chart does not embed an unnecessarily large dataset.

ShellInstall Altair

python -m pip install "altair>=6,<7"

PythonCreate a chart specification from region totals

import altair as alt
import polars as pl

sales = pl.DataFrame({"region": ["Dhaka", "Dhaka", "Sylhet"], "cents": [100, 200, 400]})
report = sales.group_by("region").agg(pl.col("cents").sum()).sort("region")
chart = (
    alt.Chart(report)
    .mark_bar()
    .encode(x="region:N", y="cents:Q", tooltip=["region:N", "cents:Q"])
)
spec = chart.to_dict()
print("mark:", spec["mark"]["type"])
print("axes:", spec["encoding"]["x"]["field"], spec["encoding"]["y"]["field"])
print("checked data:", report.to_dicts())
# In a notebook display chart; to save an interactive file use chart.save("sales.html").

TextExpected output

mark: bar
axes: region cents
checked data: [{'region': 'Dhaka', 'cents': 300}, {'region': 'Sylhet', 'cents': 400}]

Rendering charts

to_dict() creates a Vega-Lite specification. Display it with a compatible renderer. HTML export normally loads chart libraries; inline exports require the relevant companion packages.

Official reference: Altair for charts from Polars summaries.

49 / 50

Common mistakes and a practical decision guide#

Choose the simplest reliable route that fits the data and result size.

Problem	Fix
ColumnNotFoundError after adding a column	Use another with_columns call before referencing the new alias.
A text value is mistaken for a column	Wrap the constant in pl.lit.
A filter keeps no unknown values	Null predicates are discarded; use is_null or fill_null with a deliberate policy.
Totals grow after a lookup join	Check duplicate keys and enforce the expected cardinality.
ID 001 becomes 1	Read ID fields as String; lost leading zeros cannot be reconstructed from the number alone.
NaN remains after fill_null	Use fill_nan; decide if invalid numeric values should become missing.
collect runs out of memory	Scan, reduce the result, and write with a sink; inspect large stateful operations.
Multiple CSV files fail under one glob	Normalize separate scans to one explicit schema before concat.
Time grouping gives incorrect local days	Parse source offsets, choose the reporting zone, then derive dates.
A latest record varies across runs	Add stable tie-breaker fields and an explicit invalid-record policy.
Slow processing despite Polars	Measure I/O, UDF calls, unnecessary conversions, join expansion, and repeated collects.
Empty report has wrong dtypes	Create empty input with a declared schema and test aggregate shape.

For exploratory data already in memory, start with DataFrame. For a file pipeline, start with a native scan and compose expressions. For a large row-level output, prefer a sink. For a small report, collect after reducing rows. When a Python-only library must run, minimize the boundary and give the UDF an honest type and independence contract.

Compiled plugins, cudf-polars GPU execution, and Polars Cloud have separate requirements. For multiprocessing, use a spawn-based start method: forking a multithreaded Polars process can deadlock. Measure contention before adding processes to native threads.

Official reference: Common mistakes and a practical decision guide.

50 / 50

Official references and version checks#

Use the documentation for your installed version when a behavior or API changes.

Check pl.__version__ and pl.show_versions() when diagnosing version differences. Older API names include groupby, dtypes in CSV options, melt, join_nulls, and streaming=True; current equivalents are group_by, schema_overrides, unpivot, nulls_equal, and engine="streaming".

Topic	Official source
Core concepts	Polars concepts
Expressions	Expressions guide
Lazy plans	Optimizations
Streaming	Streaming execution
Types, joins, and files	Python API reference
Release changes	Polars releases
Arrow	PyArrow documentation
Excel writing	XlsxWriter documentation
Filesystems	fsspec documentation
Database connections	SQLAlchemy documentation
Charting	Altair documentation

Data quality checks and tests#

Database workflows with SQLAlchemy#

ConnectorX and ADBC database transfers#

SQL over Polars frames#

Range joins and row combinations#

Altair for charts from Polars summaries#

Common mistakes and a practical decision guide#

Official references and version checks#

Help improve CodeRef