Reading on-prem SQL Server / Oracle from a Fabric notebook: is the gateway really not an option?

Hi vedant_9​ , We came across a similar architectural consideration while working with Microsoft Fabric ingestion patterns at DataToBiz. The key distinction is that a gateway connection configured for pipelines and Dataflows does not automatically extend to Spark executors running JDBC reads. I’d separate the connectivity problem from the ingestion design before changing the existing framework.

1. Direct JDBC connectivity through Managed Private Endpoints

If preserving the metadata-driven PySpark framework is important, I’d first validate whether direct connectivity can be established through a supported Managed Private Endpoint and Azure Private Link Service configuration.

With ExpressRoute already in place, verify private DNS resolution, routing, firewall rules, load-balancer configuration, endpoint approval and TLS requirements. ExpressRoute alone does not guarantee that Fabric Spark can reach an on-premises database.

If connectivity works reliably, you can retain the existing JDBC extraction, Key Vault credentials, watermark logic and Delta writes without introducing an additional staging step.

1. Gateway-based ingestion for approximately 200 tables

If direct connectivity isn't viable, I'd use a metadata-driven Data Factory pipeline with a Lookup activity followed by a parameterised Copy activity inside a ForEach. Each iteration receives the source connection, schema, table name and incremental boundaries.

The source query would follow this pattern:

SELECT * 
FROM sales.Orders 
WHERE RowVer > <lower_watermark> 
  AND RowVer <= <upper_watermark>;

The placeholders are illustrative, not executable SQL. Use supported parameterisation and validate identifiers and values. For SQL Server, rowversion is a binary version value, not a timestamp.

The pipeline copies each table through the gateway into a table-specific staging location in the lakehouse Files area. A subsequent notebook validates and processes the staged data into bronze Delta tables.

The control flow should be:

• Read the last successful watermark from the control table.
• Capture and persist the upper watermark for the current run.
• Execute the Copy activity through the gateway.
• Validate the extracted data and write it to Delta.
• Advance the watermark only after successful processing.
• Log row counts, execution duration, errors and retries.

Retries must be idempotent to prevent duplicate rows, and overlapping runs for the same table should be prevented. Watermark-based extraction also does not automatically capture deletes; use CDC or a separate deletion-tracking strategy when required.

1. Keep the notebook focused on Delta processing

Once the Copy activity succeeds, the notebook can read the staged Parquet files and write them into the bronze lakehouse:

from pyspark.sql import functions as F 

staging_path = "Files/staging/Orders/current_run/" 
target_table = "bronze.Orders" 

df = spark.read.parquet(staging_path) 

df = ( df.withColumn("_ingested_at", F.current_timestamp()) 
             .withColumn("_run_id", F.lit("current_run")) 
) 

df.write.format("delta").mode("append").saveAsTable(target_table)

This is illustrative code; the path and run ID should come from pipeline parameters. A plain append is not retry-safe, so production implementations should use a run-level commit strategy or an appropriate Delta MERGE to prevent duplicates after failures.

1. Copy activities versus Copy jobs

I'd start with a parameterised Copy activity inside a ForEach because it offers explicit control over table-specific queries, watermark boundaries, staging and notebook dependencies. Copy jobs are also worth evaluating, but verify connector support, incremental-state management and custom-query capabilities first.

At 200 tables, avoid running every extraction concurrently by default. Tune the ForEach batch count according to gateway throughput, source connection limits and database load.

1. Performance and CU consumption

I wouldn't assume either approach is cheaper on an F64 capacity without benchmarking. Test representative small, medium and large tables and compare end-to-end duration, source database load, gateway throughput, Spark startup latency, retry overhead and Fabric capacity consumption.

For direct JDBC, excessive partitioning can overload source databases. For gateway-based ingestion, excessive pipeline concurrency can saturate the gateway cluster. Measure both under realistic workloads before committing to an architecture.