sql_database: FLOAT/REAL/DOUBLE columns lose data_type under SQLAlchemy 2.1 (Float no longer subclasses Numeric)

[sdebruyn]

dlt version

1.29.1 (also reproduces on devel — schema_types.py is unchanged there)

Describe the problem

In SQLAlchemy 2.1, Float is no longer a subclass of Numeric. Both now derive from a new common base class, NumericCommon:

sqlalchemy 2.0.51   ->  Float.__mro__ = Float -> Numeric -> ...          issubclass(Float, Numeric) is True
sqlalchemy 2.1.0b3  ->  Float.__mro__ = Float -> NumericCommon -> ...    issubclass(Float, Numeric) is False

sqla_col_to_column_schema dispatches on isinstance(sql_t, sqltypes.Numeric) (dlt/sources/sql_database/schema_types.py#L134 on devel). Under SQLAlchemy 2.1 that check no longer matches floating point types, so FLOAT, REAL and DOUBLE columns fall through to the final else branch. Two things follow:

1. A warning is emitted for every such column: A column with name <col> contains unknown data type FLOAT which cannot be mapped to 'dlt' data type.
2. The returned column schema has no data_type at all, where SQLAlchemy 2.0 produced data_type: "double".

This is silent on the surface because pyproject.toml declares sql_database = ["sqlalchemy>=1.4"] with no upper bound, so 2.1 installs cleanly. CI does not cover it either: the sources workflow tests against SQLAlchemy 1.4 and 2.0 (make install-sqlalchemy2), and 2.1 is currently in beta.

The impact varies by backend. With the sqlalchemy backend the column is passed to the normalizer and the type is inferred from the Python values, so the result is usually still correct but the declared schema is incomplete. Backends that build their own Arrow schema are largely unaffected in the data path. Either way the reflected schema loses type information it previously had, and the warning volume can be significant on wide schemas.

Filing this so it is tracked ahead of the SQLAlchemy 2.1 final release, since it will start affecting users as soon as 2.1 becomes the default resolution for sqlalchemy>=1.4.

Expected behavior

FLOAT, REAL and DOUBLE columns map to data_type: "double" under SQLAlchemy 2.1, as they do under 1.4 and 2.0, and no warning is emitted.

Steps to reproduce

uv run --no-project --with 'dlt[sql-database]' --with 'sqlalchemy==2.1.0b3' python repro.py

# repro.py
import dlt, sqlalchemy as sa
from sqlalchemy import Table, Column, MetaData, Float, Numeric, Integer
from dlt.sources.sql_database.schema_types import sqla_col_to_column_schema

print("dlt", dlt.__version__, "| sqlalchemy", sa.__version__)
md = MetaData()
t = Table("demo", md, Column("id", Integer), Column("f", Float), Column("n", Numeric(10, 2)))
for c in t.columns:
    print(f"  {c.name:<3} {str(c.type):<14} -> {sqla_col_to_column_schema(c, 'full')}")

Output on sqlalchemy 2.1.0b3 — note the missing data_type for f:

dlt 1.29.1 | sqlalchemy 2.1.0b3
  id  INTEGER        -> {'name': 'id', 'nullable': True, 'data_type': 'bigint'}
  f   FLOAT          -> {'name': 'f', 'nullable': True}
  n   NUMERIC(10, 2) -> {'name': 'n', 'nullable': True, 'data_type': 'decimal', 'precision': 10, 'scale': 2}

Output on sqlalchemy 2.0.51 — correct:

  f   FLOAT          -> {'name': 'f', 'nullable': True, 'data_type': 'double'}

Possible fix

Dispatching on NumericCommon when it exists keeps a single code path and stays backward compatible, since the existing asdecimal check already distinguishes the two cases:

# module level
_NUMERIC_BASE = getattr(sqltypes, "NumericCommon", sqltypes.Numeric)

# in sqla_col_to_column_schema
elif isinstance(sql_t, _NUMERIC_BASE):
    ...

Verified against both versions:

sqlalchemy 2.1.0b3 -> resolved base class: NumericCommon
  FLOAT            isinstance=True  asdecimal=False -> double
  NUMERIC(10, 2)   isinstance=True  asdecimal=True  -> decimal
  REAL             isinstance=True  asdecimal=False -> double
  DOUBLE           isinstance=True  asdecimal=False -> double

sqlalchemy 2.0.51  -> resolved base class: Numeric | Float matches: True

It may also be worth adding a SQLAlchemy 2.1 leg to the sources CI matrix once 2.1 is out of beta, and/or capping sqlalchemy until support is confirmed.

Operating system

macOS

Runtime environment

Local

Python version

3.12

dlt data source

sql_database

[sdebruyn]

FWIW we use the sqlalchemy beta version since that already supports mssql-python instead of pyODBC.

[rudolfix]

Note: add sqlalchemy 2.1 to test matrix when it is released