SQL table incremental escape $ in column name for cursor_path

[mikevanloo]

dlt version

1.3.0

Describe the problem

Slack feed:
https://dlthub-community.slack.com/archives/C04DQA7JJN6/p1731449028965839

Performing incremental ingest of SQL Server table with cursor_path column set to __$start_lsn. When trying to use this column the following error occurs due to parse():

cursor_path="__$start_lsn"

Error:
JsonPathParserError: Parse error at 1:2 near token $ ($)

Attempted to escape the $ by applying single quotes around the column name:

cursor_path="'__$start_lsn'"

This results in the column not being able to be found:

ResourceExtractionError: In processing pipe dbo_t_eventData_CT: extraction of resource dbo_t_eventData_CT in generator table_rows caused an exception: "Cursor column ''__$start_lsn'' does not exist in table 'dbo_t_eventData_CT'"

Expected behavior

Able to escape $ for cursor_path within incremental pipeline for sql.

Steps to reproduce

source = sql_table(
credentials=connection,
schema="cdc",
table="table_a",
incremental=dlt.sources.incremental(
cursor_path="__$start_lsn"
)
)

Operating system

Linux

Runtime environment

Virtual Machine

Python version

3.11

dlt data source

SQL Server

dlt destination

Filesystem & buckets

Other deployment details

No response

Additional information

https://github.com/dlt-hub/dlt/blob/devel/dlt/sources/sql_database/helpers.py#L63-L69

Was able to get it to work by adding .replace("'", "") just to check that this is the source of the bug and the pipline worked once the ' were rermoved.

            try:
                self.cursor_column = table.c[incremental.cursor_path.replace("'","")]
            except KeyError as e:
                raise KeyError(
                    f"Cursor column '{incremental.cursor_path}' does not exist in table aaaaaaaaa"
                    f" '{table.name}'"
                ) from e

[burnash]

Thanks for reporting this @mikevanloo, I can confirm this is a bug on our end as https://github.com/h2non/jsonpath-ng handles escaping correctly when wrapping the path in quotes. Also it looks like this bug is specific to sql_database source. Will follow-up with a fix.

[mikevanloo]

@burnash Great thanks!