Consecutive batches become incompatible and silently break the pipeline when `type_adapter_callback` returns `None`
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.9k
- Forks
- 605
- Avg merge
- 1d 14h
- Merged PRs (30d)
- 38
Description
dlt version
1.26.0 (also reproduces on 1.27.2 and devel).
Describe the problem
The sql_database source's type_adapter_callback is designed for
selective type overrides. The natural pattern is:
def adapt(sql_type):
if needs_override(sql_type):
return SomeNewType()
return None # keep the reflected type
But in
dlt/sources/sql_database/schema_types.py
in sqla_col_to_column_schema:
if type_adapter_callback:
sql_t = type_adapter_callback(sql_t)
...
if sql_t is None:
# Column ignored by callback
return col
Returning None makes the function exit before setting data_type on
the column. So every column that the callback "passes through" ends
up with a dlt column schema of just {"name": ..., "nullable": ...}
— no data_type, no precision, no scale.
Downstream, convert_numpy_to_arrow sees data_type=None, falls back
to pa.array(values, type=None), and pyarrow infers the decimal
precision from each batch's values. For a NUMERIC(7,2) column where
batch 1 happens to contain only small values and batch 2 contains
larger values, the two batches get incompatible decimal128(P, S)
types. ArrowToParquetWriter opens the file with batch 1's schema and
the next batch fails with:
ValueError: Table schema does not match schema used to create file
The extract terminates mid-run.
Expected behavior
type_adapter_callback returning None should mean "no override, use
the reflected type". Either:
- Read
Noneas "keepsql_tunchanged" and continue the reflection
path that fills indata_type, precision and scale. - Or update the docstring to make absolutely explicit that
None
discards the type information and users mustreturn sql_typefor
the "no change" case.
Today the API silently turns a documented "selective override" pattern
into "drop type info on every column the callback didn't touch", which
is a footgun.
Steps to reproduce
Empty Postgres on localhost:55432, e.g.:
docker run --rm -d -p 55432:5432 \
-e POSTGRES_DB=repro -e POSTGRES_USER=repro -e POSTGRES_PASSWORD=repro \
postgres:17-alpine
repro.py:
# /// script
# requires-python = ">=3.11"
# dependencies = [
# "dlt[duckdb,postgres,parquet,sql_database]==1.26.0",
# "psycopg2-binary", "numpy", "pandas",
# ]
# ///
import os
os.environ["DATA_WRITER__BUFFER_MAX_ITEMS"] = "1"
from sqlalchemy import Text
from sqlalchemy.sql.sqltypes import Numeric
import dlt, psycopg2
from dlt.sources.sql_database import sql_database
PG_URL = "postgresql://repro:repro@localhost:55432/repro"
def adapt(sql_type):
# Override one specific case, pass through everything else.
if isinstance(sql_type, Numeric) and sql_type.precision is None:
return Text()
return None # "no change" — but dlt reads this as "drop the type"
c = psycopg2.connect(PG_URL); c.autocommit = True; cur = c.cursor()
cur.execute("DROP TABLE IF EXISTS t")
cur.execute("CREATE TABLE t (id INT PRIMARY KEY, price NUMERIC(7,2) NOT NULL)")
cur.executemany("INSERT INTO t VALUES (%s, %s)", [(i, i/100) for i in range(1, 100)])
cur.executemany("INSERT INTO t VALUES (%s, %s)", [(i, i) for i in range(100, 200)])
c.close()
pipe = dlt.pipeline(pipeline_name="mwe",
destination=dlt.destinations.duckdb("/tmp/mwe.duckdb"))
pipe.run(sql_database(
credentials=PG_URL, table_names=["t"],
backend="pyarrow", chunk_size=99,
type_adapter_callback=adapt,
))
Output:
ValueError: Table schema does not match schema used to create file:
table:
id: int64 not null
price: decimal128(5, 2) not null vs.
file:
id: int64 not null
price: decimal128(2, 2) not null
Returning sql_type from the callback instead of None (i.e. the
"no override" path explicitly returns the original type) makes the
extract succeed and the destination column is DECIMAL(7, 2) as
declared by Postgres.
Operating system
macOS
Runtime environment
Local
Python version
3.12
dlt data source
dlt.sources.sql_database against Postgres 17 with backend="pyarrow"
and a type_adapter_callback.
dlt destination
DuckDB
Other deployment details
No response
Additional information
Hit in production on NUMERIC(7,2) and INT columns where the
callback was designed to coerce unbounded NUMERIC and JSONB to
TEXT and pass everything else through with return None. The pattern
matches the dlt docs example for type_adapter_callback. Workaround
in our codebase was a one-line change:
- return None
+ return sql_type
Either docs change or behaviour change would prevent the next user
from rediscovering this.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start in dlt/sources/sql_database/schema_types.py at sqla_col_to_column_schema and inspect how type_adapter_callback results flow into the reflected column schema. Reproduce the issue with the provided repro.py and a selective callback returning None. Done means the no-override path preserves reflected data_type, precision, and scale so consecutive batches use a compatible schema.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sqlalchemy
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100