dlt-hub / dlt-hub/dlt

Consecutive batches become incompatible and silently break the pipeline when `type_adapter_callback` returns `None`

Open
#4,032 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
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:

  1. Read None as "keep sql_t unchanged" and continue the reflection
    path that fills in data_type, precision and scale.
  2. Or update the docstring to make absolutely explicit that None
    discards the type information and users must return sql_type for
    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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.