mssql: use native Arrow bulk copy for Parquet load jobs

[sdebruyn]

Context

The current MSSQL Parquet path is implemented through ADBC:

• MssqlParquetCopyJob subclasses the generic AdbcParquetCopyJob, opens a separate Go MSSQL connection, and translates ODBC DSN values into that driver's format:
  

  dlt/dlt/destinations/impl/mssql/mssql.py

  Lines 93 to 148 in a831096

  	class MssqlParquetCopyJob(AdbcParquetCopyJob):
  	_config: MsSqlClientConfiguration
  	# mssql ADBC driver buffers the full input stream in memory; flush per row-group
  	# to bound peak memory and avoid OOM on large parquet files
  	_ingest_per_rowgroup: bool = True
  	if TYPE_CHECKING:
  	from adbc_driver_manager.dbapi import Connection
  	def _connect(self) -> "Connection":
  	from adbc_driver_manager import dbapi
  	self._config = self._job_client.config # type: ignore[assignment]
  	conn_dsn = self.odbc_to_go_mssql_dsn(self._config.credentials.get_odbc_dsn_dict())
  	conn_str = build_odbc_dsn(conn_dsn)
  	return dbapi.connect(driver="mssql", db_kwargs={"uri": conn_str})
  	@staticmethod
  	def odbc_to_go_mssql_dsn(dsn: Dict[str, Any]) -> Dict[str, Any]:
  	"""Converts odbc connection string to go connection string used by ADBC"""
  	# DSN keys are already normalized to upper case
  	result: Dict[str, Any] = {}
  	for upper, value in dsn.items():
  	if value is None:
  	continue
  	v = str(value)
  	if upper == "ENCRYPT":
  	v = v.strip().lower()
  	# ODBC: yes/mandatory/true/1 → go-mssqldb: true (TLS on)
  	if v in {"yes", "true", "1", "mandatory"}:
  	v = "true"
  	# ODBC: strict → go-mssqldb strict (if supported by the driver)
  	elif v in {"strict"}:
  	v = "strict"
  	# ODBC: optional → go-mssqldb optional (login only)
  	elif v in {"optional"}:
  	v = "optional"
  	# ODBC: no/false/0/disabled → go-mssqldb disable (no TLS at all)
  	# This mirrors your previous string hack:
  	# .replace("=yes", "=1").replace("=no", "=disable")
  	elif v in {"no", "false", "0", "disabled", "disable"}:
  	v = "disable"
  	elif upper == "TRUSTSERVERCERTIFICATE":
  	v = v.strip().lower()
  	# ODBC uses yes/no; go-mssqldb expects true/false (but is lenient);
  	# we normalize explicitly.
  	if v in {"yes", "true", "1"}:

• The job is selected for Parquet files here:
  

  dlt/dlt/destinations/impl/mssql/mssql.py

  Lines 180 to 188 in a831096

  	def create_load_job(
  	self, table: PreparedTableSchema, file_path: str, load_id: str, restore: bool = False
  	) -> LoadJob:
  	job = super().create_load_job(table, file_path, load_id, restore)
  	if not job:
  	parsed_file = ParsedLoadJobFileName.parse(file_path)
  	if parsed_file.file_format == "parquet":
  	job = MssqlParquetCopyJob(file_path)
  	return job

• Parquet availability and preference depend on a separately installed ADBC driver:
  

  dlt/dlt/destinations/impl/mssql/factory.py

  Lines 120 to 128 in a831096

  	def _raw_capabilities(self) -> DestinationCapabilitiesContext:
  	caps = DestinationCapabilitiesContext()
  	caps.preferred_loader_file_format = "insert_values"
  	caps.supported_loader_file_formats = ["insert_values", "parquet", "model"]
  	caps.loader_file_format_selector = make_adbc_parquet_file_format_selector(
  	"mssql",
  	"https://dlthub.com/docs/dlt-ecosystem/destinations/mssql#data-loading",
  	prefer_parquet=True,
  	)

• ADBC buffers the full input stream, so dlt works around that by invoking ingest once per Parquet row group:
  

  dlt/dlt/destinations/impl/mssql/mssql.py

  Lines 93 to 108 in a831096

  	class MssqlParquetCopyJob(AdbcParquetCopyJob):
  	_config: MsSqlClientConfiguration
  	# mssql ADBC driver buffers the full input stream in memory; flush per row-group
  	# to bound peak memory and avoid OOM on large parquet files
  	_ingest_per_rowgroup: bool = True
  	if TYPE_CHECKING:
  	from adbc_driver_manager.dbapi import Connection
  	def _connect(self) -> "Connection":
  	from adbc_driver_manager import dbapi
  	self._config = self._job_client.config # type: ignore[assignment]
  	conn_dsn = self.odbc_to_go_mssql_dsn(self._config.credentials.get_odbc_dsn_dict())
  	conn_str = build_odbc_dsn(conn_dsn)
  	return dbapi.connect(driver="mssql", db_kwargs={"uri": conn_str})

mssql-python v1.13.0 / PR #665 adds Cursor.bulkcopy_arrow(table_name, source). It accepts pyarrow.Table, RecordBatch, RecordBatchReader, iterables of record batches, and Arrow C Data Interface producers. It preserves Arrow memory, returns copied-row statistics, supports column mappings, and shares the driver's SQL/Entra authentication setup.

This gives us a path to remove an MSSQL-only second driver stack and its DSN conversion code while retaining streamed Parquet loading.

This depends on #10. It should integrate with #11 so azure_credential remains available to bulk copy for fresh token acquisition.

Proposed change

1. Replace MssqlParquetCopyJob(AdbcParquetCopyJob) with a native mssql-python runnable job.
2. Stream the existing dlt Parquet batches into bulkcopy_arrow; do not materialize the full file. A RecordBatchReader or bounded iterable built from pq_stream_with_new_columns is appropriate.
3. Pass a fully qualified target table and explicit source-to-destination column mappings so schema order and identifier casing are not implicit.
4. Replace ADBC-driver detection in the MSSQL loader-file-format selector with detection of the optional Arrow dependency/native API. Keep Arrow optional for users who only use insert_values; do not make a heavy PyArrow install mandatory without a separate justification.
5. Remove MSSQL-specific ODBC-to-Go DSN conversion and the row-group buffering workaround when the native path is proven equivalent.
6. Keep an ADBC fallback only if the validation matrix finds a concrete native-driver gap; otherwise remove it from the MSSQL path.

Required validation before making it the preferred path

• Verify commit/rollback and partial-failure semantics. bulkcopy_arrow creates its own native bulk-copy context, so a failed/retried dlt load job must not silently leave a committed prefix that is duplicated on retry.
• Verify append, merge staging, and both replace strategies, including restore/retry behavior.
• Cover SQL login, built-in Authentication=, explicit access_token, azure_credential/token_provider, and Fabric NotebookUtils credentials.
• Cover dlt's type matrix: nullable numerics, decimal/wei, UUID, binary/LOB, JSON/text, date/time/timestamp, timezone behavior, and empty files.
• Verify table/schema quoting, case folding, reordered columns, and destination column mappings.
• Compare peak RSS and throughput against both current ADBC Parquet loading and insert_values on representative row groups and large varbinary(max) data.
• Prove memory remains bounded across many row groups.

Acceptance criteria

• A Parquet load can run with mssql-python>=1.13.0 plus PyArrow and without adbc_driver_mssql/adbc_driver_manager.
• The preferred format becomes Parquet only when its optional runtime requirements are present; existing insert-values-only installs remain lightweight.
• Load jobs report copied row counts and surface native failures through dlt's load-job exception handling.
• Retry behavior is demonstrated to be idempotent or the job is explicitly made so.
• MSSQL-specific ADBC connection and DSN conversion code is removed unless a tested fallback is retained and documented.
• End-to-end MSSQL load tests pass for normal, staged merge, and replace flows.

[sdebruyn]

Branch, upstream PR, integration, and release plan

Where this change belongs

This issue needs a new focused branch and upstream PR. Create:

feat/13-mssql-python-arrow-bulk-copy

from the updated tip of feat/mssql-python-driver after #10 and #11 are complete.

Do not branch from devel: the load job requires the driver migration from dlt-hub#4141. Do not branch from feat/2-mssql-python-arrow-batches: native destination bulk loading does not depend on the sql_database Arrow extraction backend. They are sibling features with a shared driver parent:

upstream/devel
└── feat/mssql-access-token-credential          #4147
    └── feat/mssql-python-driver                 #4141 + #10 + #11
        ├── feat/2-mssql-python-arrow-batches   #4258 + #12
        └── feat/13-mssql-python-arrow-bulk-copy  <-- implement #13 here

Use a separate worktree, commits, tests, and review history for #13. If dlt-hub#4141 changes during implementation, rebase this branch onto its new tip instead of copying driver/authentication changes into the bulk-copy branch.

How it reaches upstream

Push the new branch to the fork and open a new pull request against dlt-hub/dlt:devel. Link this fork issue and explicitly state that the PR is stacked on dlt-hub#4141, which is itself stacked on dlt-hub#4147. GitHub cannot use those fork-only branches as the cross-fork base, so the upstream PR will temporarily show the parent commits.

The upstream PR should contain only the bulk-copy commits after the shared parent and should explain why it is a sibling of dlt-hub#4258 rather than a continuation of it.

When dlt-hub#4147 and dlt-hub#4141 land upstream:

1. Rebase feat/13-mssql-python-arrow-bulk-copy onto the then-current upstream/devel.
2. Have an authorized maintainer update the fork branch with an explicit force-with-lease.
3. Keep the same upstream PR; its diff should then narrow to the native bulk-load implementation only.
4. Update the dlt-fabric README branch tree and "What this fork carries" list with the assigned upstream PR number.

If dlt-hub#4258 lands first or changes independently, no rebase onto dlt-hub#4258 is required; only the shared dlt-hub#4141/devel ancestry matters.

How it reaches integrated-all-prs

Never implement or cherry-pick #13 directly onto the integration branch. Rebuild integrated-all-prs from a current upstream/devel and merge both driver leaves:

git merge --no-ff feat/2-mssql-python-arrow-batches
git merge --no-ff feat/13-mssql-python-arrow-bulk-copy

The first leaf brings dlt-hub#4147, dlt-hub#4141, and the extraction work; the second shares dlt-hub#4147/dlt-hub#4141 and adds only native destination bulk loading. Git should deduplicate the common ancestry. Then merge the independent branches from the README and run the complete combined test matrix.

Do not merge feat/mssql-python-driver separately: both leaves already contain it. Conflicts must be resolved by retaining both independent feature changes, especially where tests, dependencies, or the MSSQL factory are touched.

How it reaches dlt-fabric

The published branch starts from stable upstream/master; it must never receive a merge from the devel-based integration branch.

After integrated-all-prs passes:

1. Rebuild dlt-fabric from current upstream/master.
2. Enumerate the non-merge commits in devel..integrated-all-prs and cherry-pick them in dependency order. The mssql: use native Arrow bulk copy for Parquet load jobs #13 commits must follow feat(mssql,fabric): add Entra ID authentication and NotebookUtils credential dlt-hub/dlt#4147, feat(mssql): replace pyODBC + ODBC installation dependency with Microsoft's mssql-python dlt-hub/dlt#4141, mssql: raise the mssql-python baseline to 1.13 #10, and mssql: delegate TokenCredential authentication to mssql-python #11, but they do not need to follow sql_database: close mssql-python Arrow readers explicitly #12 except where conflict resolution makes that convenient.
3. Apply the separate dlt-fabric package-name and next-.postN version commits.
4. Validate native Parquet bulk loading without ADBC across SQL login and every supported token path, plus retry/partial-failure semantics, on the actual release branch.
5. Run the README check that rejects any commit already contained in devel; it must print nothing.
6. Build and publish only after the fork branch, tag/version, wheel, and sdist all reflect the same validated commit set.

The new topic branch/upstream PR is the long-lived source of truth. integrated-all-prs is the combined test projection; dlt-fabric is the stable-release projection. Any later fix starts on the topic branch, then both projections are rebuilt again in that order.