Troubleshoot

Need help deploying these sources or figuring out how to run them in your data stack?

Join our Slack community or Get in touch with the dltHub Customer Success team.

Timezone-aware and Non-aware Data Types

I see a UTC datetime column in my destination, but my data source has a naive datetime column

Use full or full_with_precision reflection level to get an explicit timezone hint in reflected table schemas. Without that hint, dlt will coerce all timestamps into timezone-aware UTC ones.

I have an incremental cursor on a datetime column and I see query errors

Queries used to query data in the sql_database are created from an Incremental instance attached to the table resource. Initial end and last values must match timezone-awareness of the cursor column because they will be used as parameters in the WHERE clause.

In rare cases where the last value is already stored in the pipeline state and has incorrect timezone-awareness, you may not be able to recover your pipeline automatically. You can modify the local pipeline state (after syncing with destination) to add/remove timezone information.

Troubleshooting incremental loading

I get a KeyError saying the cursor column doesn't exist

If dlt raises KeyError: 'Cursor column `...` does not exist in table `...`', the name you passed to dlt.sources.incremental(...) isn't present in the schema that SQLAlchemy reflected. Reflection returns whatever the database itself stores, and dialects normalize identifiers differently: PostgreSQL lowercases unquoted identifiers, Oracle uppercases them, MySQL, MSSQL, and SQLite preserve the case you declared.

Inspect the reflected column names first:

import sqlalchemy as sa

engine = sa.create_engine("postgresql://user:pass@host/db")
meta = sa.MetaData()
meta.reflect(bind=engine, only=["your_table"])
print(list(meta.tables["your_table"].c.keys()))

Copy the column name from that output, case included, and pass it to dlt.sources.incremental(...).

Troubleshooting connection

Pipeline state grows extremely large or I get deduplication state warnings when using incremental

If you set incremental column on low resolution column (i.e. of type date) then dlt will deduplicate such data by default. For low resolution column you may have many rows associated with a single cursor value and since hashes of such rows are stored in state - you will get large pipeline state. You can avoid that in many ways:

1. set the comparison to exclusive but make sure that rows are not added with the last cursor column value between runs (i.e. if you have column on a day column and between runs, rows are added to that day - with open range those rows will be skipped)
2. use high resolution cursor columns (i.e. datetime type) so not many rows are associated with single value.
3. disable deduplication explicitly

Connecting to MySQL with SSL

Here, we use the mysql and pymysql dialects to set up an SSL connection to a server, with all information taken from the SQLAlchemy docs.

1. To enforce SSL on the client without a client certificate, you may pass the following DSN:

   sources.sql_database.credentials="mysql+pymysql://root:<pass>@<host>:3306/mysql?ssl_ca="

2. You can also pass the server's public certificate (potentially bundled with your pipeline) and disable host name checks:

   sources.sql_database.credentials="mysql+pymysql://root:<pass>@<host>:3306/mysql?ssl_ca=server-ca.pem&ssl_check_hostname=false"

3. For servers requiring a client certificate, provide the client's private key (a secret value). In Airflow, this is usually saved as a variable and exported to a file before use. The server certificate is omitted in the example below:

   sources.sql_database.credentials="mysql+pymysql://root:<pass>@35.203.96.191:3306/mysql?ssl_ca=&ssl_cert=client-cert.pem&ssl_key=client-key.pem"

SQL Server connection options

To connect to an mssql server using Windows authentication, include trusted_connection=yes in the connection string.

sources.sql_database.credentials="mssql+pyodbc://loader.database.windows.net/dlt_data?trusted_connection=yes&driver=ODBC+Driver 17+for+SQL+Server"

To connect to a local SQL server instance running without SSL, pass the encrypt=no parameter:

sources.sql_database.credentials="mssql+pyodbc://loader:loader@localhost/dlt_data?encrypt=no&driver=ODBC+Driver 17+for+SQL+Server"

To allow a self-signed SSL certificate when you are getting certificate verify failed: unable to get local issuer certificate:

sources.sql_database.credentials="mssql+pyodbc://loader:loader@localhost/dlt_data?TrustServerCertificate=yes&driver=ODBC+Driver 17+for+SQL+Server"

To use long strings (>8k) and avoid collation errors:

sources.sql_database.credentials="mssql+pyodbc://loader:loader@localhost/dlt_data?LongAsMax=yes&driver=ODBC+Driver 17+for+SQL+Server"

To fix MS SQL Server connection issues with ConnectorX:

Some users have reported issues with MS SQL Server and Connector X. The problems are not caused by dlt, but by how connections are made. A big thanks to Mark-James M for suggesting a solution.

To fix connection issues with ConnectorX and MS SQL Server, include both Encrypt=yes and encrypt=true in your connection string:

sources.sql_database.credentials="mssql://user:password@server:1433/database?driver=ODBC+Driver+17+for+SQL+Server&Encrypt=yes&encrypt=true"

This approach can help resolve connection-related issues.

Troubleshooting backends

Notes on specific databases

Oracle

1. We recommend to use the oracledb dialect in thin mode instead of the cx_oracle (old) client. It is the setup that we test and support. Note that oracledb is not supported in SQLAlchemy below 2.0.
2. Mind that SQLAlchemy translates Oracle identifiers into lower case! Keep the default dlt naming convention (snake_case) when loading data. We'll support more naming conventions soon.
3. Connectorx is not compatible with oracledb in thin mode. In thick mode with cx_oracle it is for some reason slower for Oracle than the PyArrow backend, so it is not recommended for Oracle.
4. To preserve the original DB type semantics and avoid data loss, NUMBER is always treated as decimal, with sqlalchemy backend keeping the original DB precision and scale and pyarrow setting the default ones. pandas is generally discouraged when it comes to decimals, not only for Oracle
5. Since dlt 1.32.0, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE load as correct instants, also incrementally. See Oracle timestamps with a time zone below.

See here for information and code on setting up and benchmarking on Oracle.

DB2

1. Mind that SQLAlchemy translates DB2 identifiers into lower case! Keep the default dlt naming convention (snake_case) when loading data. We'll support more naming conventions soon.
2. The DB2 type DOUBLE gets incorrectly mapped to the Python type float (instead of the SQLAlchemy type Numeric with default precision). This requires dlt to perform additional casts. The cost of the cast, however, is minuscule compared to the cost of reading rows from the database.

See here for information and code on setting up and benchmarking on DB2.

MySQL

1. The SQLAlchemy dialect converts doubles to decimals. (This can be disabled via the table adapter argument as shown in the code example here)

Postgres / MSSQL

No issues were found for these databases, except DATETIMEOFFSET with ConnectorX on MS SQL Server. Postgres is the only backend where we observed a 2x speedup with ConnectorX (see here for the benchmarking code). On other db systems, it performs the same as (or sometimes worse than) the PyArrow backend.

Notes on specific data types

Oracle timestamps with a time zone

Since dlt 1.32.0, Oracle timestamps load as correct instants in any database, session or dlt context time zone, in both thin and thick oracledb mode:

• TIMESTAMP WITH TIME ZONE: loaded as a tz-aware instant with the timezone hint. Values stored with a region name such as Europe/Warsaw load too. The original offset is not kept.
• TIMESTAMP WITH LOCAL TIME ZONE: loaded as a tz-aware instant with the timezone hint.
• TIMESTAMP and DATE: loaded as stored, without the timezone hint. DATE keeps its time of day.

Incremental loading works on all of them. A cursor on a zoned column is compared as an instant, so no rows are skipped or loaded twice whatever the session time zone is. A naive cursor value, such as an initial_value without a time zone, is read in the dlt context time zone. dlt does not change the session of its connections, including those of an Engine you pass as credentials.

caution

Versions before 1.32.0 loaded TIMESTAMP WITH TIME ZONE values as their wall clock labeled as UTC, so values stored with a non-zero offset were shifted, and region names failed with DPY-3022. TIMESTAMP WITH LOCAL TIME ZONE columns lost the timezone hint and were shifted by the database time zone. After upgrading, such rows load as different (correct) instants, and the destination column type may change. If you load these columns incrementally, check the stored cursor value before the first run.

note

oracledb returns zoned timestamps as naive datetimes that are not UTC. dlt selects such columns as SYS_EXTRACT_UTC(column) and binds the cursor value as FROM_TZ(CAST(:value AS TIMESTAMP), '+00:00') (cast to TIMESTAMP WITH LOCAL TIME ZONE for such columns), so Oracle does both conversions. Filters and ordering use the column itself, so its index still works.

MS SQL Server DATETIMEOFFSET with ConnectorX

ConnectorX returns wrong DATETIMEOFFSET values because it applies the offset twice. Only values stored at +00:00 are correct. Use the pyarrow or sqlalchemy backend instead, or select the column converted to UTC:

import sqlalchemy as sa
from dlt.sources.sql_database import sql_table

def utc_offsets(query, table):
    columns = [
        sa.func.switchoffset(col, "+00:00").label(col.name)
        if col.name == "updated_at"
        else col
        for col in table.c
    ]
    return query.with_only_columns(*columns)

orders = sql_table(table="orders", backend="connectorx", query_adapter_callback=utc_offsets)

Decimals and money types with ConnectorX

ConnectorX returns decimals with 10 decimal places: values with more are rounded, and values with more than ~18 integer digits fail to load. MS SQL Server MONEY and SMALLMONEY are returned as floats. dlt warns when a column is rounded or returned as a float. Use the pyarrow backend for such tables.

JSON

In the SQLAlchemy backend, the JSON data type is represented as a Python object, and in the PyArrow backend, it is represented as a JSON string. At present, it does not work correctly with pandas and ConnectorX, which cast Python objects to str, generating invalid JSON strings that cannot be loaded into the destination.

UUID

UUID columns (PostgreSQL UUID, MSSQL UNIQUEIDENTIFIER, and SQLAlchemy 2.0 generic Uuid) are mapped to dlt data type text and always yielded as strings. This ensures consistent casing across initial and incremental loads regardless of the backend used.

You can switch this behavior by using table_adapter_callback to modify properties of the UUID type for a particular column. (See the code example here for how to modify the data type properties of a particular column.)

MS SQL Server MONEY

MONEY and SMALLMONEY columns are loaded as decimal(19, 4) and decimal(10, 4).

Notes on data loading

Arrow loading

Using the configuration sql_database(backend="pyarrow") improves performance by pivoting all records (row-major) into a arrow table (column-major). The library pyarrow doesn't have a utility for this pivot operation, which is why numpy or pandas is necessary. Either library is required for this pivot operation only, then pyarrow handles the rest of type conversion and applying dlt constraints.