topic Re: Databricks Data Ingestion in Get Started Discussions

Databricks Data Ingestion

anushnagesh — Wed, 02 Sep 2026 09:09:04 GMT

Hi all,

I am currently exploring the data ingestion feature offered by databricks, specifically connecting to a SQL server.
I have gone through the UI and configured an ingestion pipeline that connects to one specific server and can read tables into databricks.

I am currently venturing into understanding if we can make an ingestion pipeline dynamic.

What I mean by this is, I have created a yaml file that can be run to setup an ingestion pipeline. It accepts variables from a metadata config table that can be used to populate the YAML with information such as, connection server name, pipeline name, source catalog, source schema, source table, destination catalog, destination schema and destination table name.

So the question I have is, can we have a generic pipeline whose configurations can be altered?
Use cases:
1. Once a pipeline is created can it be edited in the future to ingest different tables? As in using pipeline_01, I ingested table_1, table_2 present in Server_01. Can I later pass different configs to the YAML such that I use the same pipeline_01 to ingest table_03, table_04?

2. Can I use pipeline_01 to connect to a different to a connection server and ingest tables from this new server?

Currently I am not finding a way to achieve this functionality. This will cause an issue in the future when I have say 50 servers. It will be difficult to maintain 50 different pipelines and I don't think this is desirable as well.

Can I get some insights into Data Ingestion feature and if this a viable option for my use case?

Thank you,
Anush

Re: Databricks Data Ingestion

balajij8 — Wed, 02 Sep 2026 09:45:02 GMT

@anushnagesh Updating an existing pipeline to ingest different tables from the same SQL Server instance is fully supported. You can dynamically modify the target tables by updating the objects block inside the pipeline's ingestion_definition. This change can be applied directly via the Pipelines API, the CLI (databricks pipelines update) or by updating and redeploying the Declarative Automation Bundles DAB YAML configuration. If you configure schema-level ingestion rather than specifying individual tables, the pipeline automatically detects and replicates new tables added to that schema without requiring any configuration updates.

Connecting a single pipeline across multiple SQL Server instances is generally not possible. In this ingestion architecture, each ingestion gateway is bound to a single Unity Catalog connection via gateway_definition.connection_name and each connection maps directly to one specific SQL Server instance. The ingestion pipeline inherits this connection via ingestion_definition.ingestion_gateway_id. Each ingestion pipeline is associated with exactly one ingestion gateway and gateways cannot be generally shared across pipelines. More details here

1:1 server-to-pipeline model scales cleanly using Declarative Automation Bundles (DABs). Your approach of driving parameterized YAML configurations from a metadata table fits this pattern well, allowing you to version-control and deploy all 50 pipelines across development, staging, and production environments with a single CLI command. If you want to streamline this footprint, you can check the new Integrated CDC architecture which merges the gateway and ingestion pipeline into a single pipeline per server eliminating the cost and maintenance of continuously running gateway compute. More details here

Re: Databricks Data Ingestion

Satyasai — Wed, 02 Sep 2026 10:01:57 GMT

Yes, having a generic, parameter-driven ingestion architecture is the standard enterprise design pattern in Databricks to avoid managing dozens of redundant pipelines.
However, how you achieve this depends on whether you are using Custom Spark/Delta Lake Pipelines or Databricks Lakeflow Connect (Managed Ingestion).
Use Case Breakdown & Feasibility
Use Case 1: Editing a pipeline to ingest new tables (table_3, table_4)
• Yes, this is fully supported. You do not need to build a new pipeline for new tables. You update your central configuration file (YAML/JSON) or configuration table to register table_3 and table_4.
• When the pipeline triggers, it dynamically reads the updated configuration, instantiates the flows for table_3 and table_4, and ingests them alongside or independently of existing tables.
Use Case 2: Using the same pipeline for 50 different Database Servers
• Yes, but with an architectural nuance:
o A single pipeline can loop through or accept dynamic connections if configured as a parameter-driven job.
o However, for scaling across 50 database servers, the best practice is One Generic Codebase/Template deployed to 50 Pipeline Jobs (via CI/CD or Databricks Asset Bundles) rather than forcing 50 servers sequentially into a single massive execution run.
Architecture Approaches in Databricks
Depending on how your ingestion is built, here are the two main ways to implement this:
Approach 1: Generic Databricks Declarative Pipeline / PySpark Framework (Best for Metadata-Driven Ingestion)
Rather than hardcoding table names or source servers in Python or SQL scripts, your code queries a central Metadata Config (YAML file or Delta Table).
1. Define your Configuration (ingestion_config.yaml):
YAML
sources:
- connection_name: "sql_server_east"
jdbc_url: "jdbc:sqlserver://server01.database.windows.net:1433;database=Sales"
secret_scope: "db_secrets"
secret_key: "server01_password"
tables:
- source_table: "dbo.Orders"
target_table: "bronze_orders"
primary_key: "OrderID"
- source_table: "dbo.Customers"
target_table: "bronze_customers"
primary_key: "CustomerID"
2. Dynamic Ingestion Engine (generic_ingest.py): Similar to below snap
Python
import yaml
from pyspark.sql import SparkSession

# Read YAML config from Workspace or DBFS/Volume
with open("/Volumes/main/default/configs/ingestion_config.yaml", "r") as f:
config = yaml.safe_load(f)

for source in config["sources"]:
db_password = dbutils.secrets.get(scope=source["secret_scope"], key=source["secret_key"])

for table_info in source["tables"]:
# Dynamic JDBC Read
df = (spark.read.format("jdbc")
.option("url", source["jdbc_url"])
.option("dbtable", table_info["source_table"])
.option("password", db_password)
.load())

# Write dynamically to Target Bronze Delta Table
df.write.format("delta").mode("append").saveAsTable(f"bronze.{table_info['target_table']}")
• To add table_3 and table_4: Simply append them to the YAML file. The next run will automatically pick them up without code changes.
• To handle multiple servers: Add a new entry under sources in the YAML, or pass the server_name as a Job Parameter (dbutils.widgets.get("server_name")) to reuse the single notebook for multiple runs.
Approach 2: Databricks Lakeflow Connect (Managed Ingestion)
If you are using Lakeflow Connect (Databricks' native managed change data capture / database connector feature):
1. Table Changes (Use Case 1): You can edit an existing Lakeflow managed ingestion pipeline directly via the UI, Databricks CLI, or Declarative Automation Bundles (DABs). Adding new tables to the pipeline config will trigger an initial snapshot for the new tables while keeping the existing tables running incrementally.
2. Multiple Servers (Use Case 2): Lakeflow managed ingestion requires a Unity Catalog Connection per database server.
o A single Lakeflow Ingestion Gateway & Pipeline is bound to one source connection (one database server).
o Recommendation: Use Databricks Asset Bundles (DABs) to define a single declarative template (in YAML). You then parameterize the connection name and deploy 50 small, isolated server pipelines automatically via CI/CD. This prevents one failing database server from crashing the ingestion of the other 49 servers.

Re: Databricks Data Ingestion

Satyasai — Wed, 02 Sep 2026 10:10:12 GMT

Just for additional information I hope Netflix is using similar way with metadata as below and created Generic frame work using python to read the metadata and execute the process

Table	purpose
Source_system	Source registration
Source_connection	Connection details
Tasble_metadata	Table Definitions
Column_Metadata	Column Definitions
FILE_FORMAT	File configuration
PIPELINE	Pipeline definitions
WORKFLOW	Execution order

Re: Databricks Data Ingestion

srini_ve — Wed, 02 Sep 2026 10:26:29 GMT

@anushnagesh

I think this is a very valid use case, especially if you are expecting to work with 50+ SQL Server instances. Maintaining a separate ingestion pipeline for every server and table would become difficult to manage over time.

In my experience, I would separate the ingestion logic from the configuration as much as possible. Instead of changing the YAML every time a new table needs to be onboarded, I would keep the ingestion logic reusable and maintain the changing information in a metadata/configuration table.

For example, the metadata could contain the source connection, database, schema, table, target catalog/schema/table, load type, and an active/inactive flag.

The overall approach would be:

Metadata → Reusable Lakeflow ingestion logic → Appropriate Databricks connection → SQL Server tables → Databricks target

For your first use case, moving from `table_1/table_2` to `table_3/table_4` is a good fit for a metadata-driven approach. The ingestion logic remains the same, while the metadata controls which tables are processed.

For the second use case, I would manage the SQL Server connections as Databricks connection resources and reference the appropriate connection from the ingestion configuration, rather than trying to dynamically change the server details inside the pipeline itself.

There is one important consideration with Lakeflow Connect: managed connectors have configuration boundaries. The source connection, ingestion objects, and other connector-specific settings are not necessarily designed to be changed dynamically at runtime through arbitrary YAML or metadata. So I wouldn't design the solution around dynamically rewriting an existing managed ingestion pipeline for every new server or table.

For a larger environment, I would instead use a metadata-driven Lakeflow Declarative Pipeline/orchestration approach, where the reusable ingestion logic is separated from the configuration that changes.

This gives you a clean separation:

What changes: server, connection, schema, table, target and load configuration
What stays the same: ingestion and transformation logic

With this model, onboarding a new SQL Server or a new table becomes primarily a configuration exercise rather than a new pipeline development exercise, while still working within the configuration limits of the managed Lakeflow Connect connectors.

Re: Databricks Data Ingestion

emma_s — Wed, 02 Sep 2026 16:32:03 GMT

Hi,

There are a few different approaches to this. And these will depend on what mechanism you intend to use to read the sql server tables.

The main way we recommend is to use Lakeflow Connect, this is usually the cheapest way especially if you can use incremental ingestion. Here are the docs- https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-pipeline?language=Databricks%C2%A0notebook

There is a strong example on how to set this up in the docs. If you then wanted to you can then programatically set up each pipeline using a loop. The downside of this is you aren't creating one pipeline and passing parameters you are creating a pipeline for each server. But each pipeline is doing whichever tables you want in the schema. To me this is probably the most efficient appraoch. You are getting the intelligence of Lakeflow connects change data capture plus you get a certian number of records ingested for free each day.

You could also use DABS to loop through your servers to deploy the pipelines. This is discussed in the docs.

The other posters have covered most of the other options.

I hope this helps.

Thanks,

Emma

Re: Databricks Data Ingestion

anushnagesh — Fri, 04 Sep 2026 09:11:59 GMT

Hi All,

Thank you all very much for your response to my queries.

Question 1:
It is now clear to me that once we have created an ingestion pipeline mapped to 1 ingestion gateway, we can dynamically play around with the number of tables we can ingest within this connection server by configuring the YAML appropriately.

Question 2:

On the other hand, I am now aware that once an ingestion pipeline is created it is mapped to a single ingestion gateway. And we can not update this ingestion pipeline to point to a different ingestion gateway. I see the benefits we get via lakeflow connect, but I am still failing to see how this can be beneficial if we have 20-30 connections. This would mean we will have 30 ingestion pipelines. Wouldn't this increase the maintenance overhead? We will need to oversee 30 pipelines and this would only grow as number of servers increases.

Re: Databricks Data Ingestion

anushnagesh — Fri, 04 Sep 2026 09:18:31 GMT

Hi emma_s,

Thank you for your response to my queries.

Question 1:
It is now clear to me that once we have created an ingestion pipeline mapped to 1 ingestion gateway, we can dynamically play around with the number of tables we can ingest within this connection server by configuring the YAML appropriately.

Question 2:

Re: Databricks Data Ingestion

srini_ve — Fri, 04 Sep 2026 09:37:15 GMT

I completely understand your concern, and I think this is a valid question.

If one ingestion pipeline is permanently associated with one ingestion gateway, then from an operational perspective, 20–30 source connections could indeed result in 20–30 ingestion pipelines. At first glance, that does sound like additional maintenance overhead, especially as the number of source systems continues to grow.

However, I think the key is to differentiate between having multiple pipeline instances and maintaining multiple independent pipeline implementations.

For example, I would try to manage this using a common YAML/template and metadata-driven configuration. The ingestion pattern can remain standard, while the gateway, connection, tables and target configuration are supplied per source.

Think of it as one reusable ingestion pattern, with each server or gateway having its own configuration and pipeline instance.

So you may still have 30 pipelines because of the Lakeflow Connect/gateway architecture, but ideally you should not have 30 different pipelines that need to be manually developed and maintained.

For example, the metadata/configuration could define:

Ingestion gateway
Source connection
Source database/schema
Tables to ingest
Target catalog/schema
Load configuration

A standard deployment process could then create and update the relevant pipeline configurations consistently.

I also think there are some advantages to having the pipelines separated by connection/gateway. For example, issues with one source connection are more isolated and do not necessarily impact ingestion from other source systems. It can also make monitoring, troubleshooting and access management more clearly scoped to a particular source.

That said, I agree that the number of pipeline resources can become an operational challenge at scale. In my view, this is where automation becomes important. I would avoid managing 30 pipelines manually through the UI and instead use a standardised configuration, source control and CI/CD approach to manage them.

So, the way I see it is, you may have multiple pipeline instances, but you should aim for a single reusable ingestion pattern and automate the creation and configuration of those pipelines.

That way, onboarding a new SQL Server becomes mainly a configuration/deployment exercise rather than creating and maintaining a completely new ingestion solution each time.