Feature: sql_table Source Leverage Literal Query String <> pyodbc #3475
Description
Feature description
Support for literal (non-parameterized) SQL query execution in dlt SQL resources, allowing users to bypass parameterized query execution and send fully inlined SQL statements directly to the database (while still supporting incremental pipeline automation between source and destination).
Are you a dlt user?
Yes, I'm already a dlt user.
Use case
When extracting large volumes of data from SQL Server (or similar databases), parameterized queries generated by SQLAlchemy or other connectors can result in suboptimal query plans and slow performance due to parameter sniffing and plan caching issues. Users need a way to execute literal SQL queries (with values inlined) to leverage database-specific optimizations, especially for backfill or batch extraction scenarios.
Proposed solution
Add an option to dlt SQL resources (or introduce a new resource type) that allows users to provide and execute literal SQL queries, bypassing parameterization. This could be implemented by exposing a flag or method for users to supply a fully formatted SQL string, which dlt will execute as-is using a low-level connector (e.g., pyodbc for SQL Server). The feature should integrate with dlt’s incremental state management, allowing for query construction with the current cursor/watermark value inlined, and should document the trade-offs and safety considerations of using literal queries.
Tradeoffs being this may be dangerous if the query contains untrusted input. SQL injection vulnerabilities can occur if user input is interpolated into the query.
Related issues
N/A