To transfer data from an on-premise system to Databricks, you need to establish secure connectivity between your on-premise infrastructure and the Databricks environment. After setting up the connection, you can use Databricks notebooks and available connectors to extract, transform (if required), and load the data into Databricks tables. Tools like Auto Loader can assist with streamlined ingestion, particularly for large or incremental data loads.

Key Steps:

1. Establish Network Access

Set up a secure communication channel such as a VPN or private link between your on-premise network and the Databricks workspace. The exact setup may vary based on your cloud provider (AWS, Azure, or GCP).

2. Configure a Connection in Databricks

Within your Databricks environment, go to the Connections panel and add a new data source. Enter the necessary details like:

  • Hostname or IP address
  • Database name
  • Authentication credentials (username/password)

3. Select a Data Ingestion Approach

JDBC Connector:
Use JDBC to directly read from your relational database (such as MySQL, SQL Server, Oracle, etc.) into Databricks.

File-Based Upload:
Export data from your source system as CSV, JSON, or Parquet files. Upload these files to **DBFS** (Databricks File System) and then import them into a table.

Auto Loader (Recommended for Large or Incremental Loads):
Utilize Databricks Auto Loader for continuous or efficient batch ingestion, especially when handling large datasets or changes over time.

4. Build a Notebook Workflow

Connect to Source: Use Spark or JDBC to connect to the on-premise database using credentials and network settings.
Extract Data: Write SQL queries or use DataFrame API to fetch the required data.
Transform (Optional): Perform any data transformation using PySpark or SQL for cleaning, joining, or enriching the data.
Load into Databricks Table: Save the transformed data using `CREATE TABLE AS SELECT` or `write.format(…).save(…)` to persist it in Delta or Parquet format.

Security Best Practices
Implement strong authentication and role-based access control on both ends (source system and Databricks).
Use encrypted connections (e.g., TLS/SSL) during data transmission.
Limit access to sensitive credentials via Databricks secrets management.

Performance Considerations
Use optimized data formats like Parquet or Delta for better performance.
Minimize data transfer volumes by filtering at source.
Enable partitioning and leverage Spark’s distributed computing for faster processing.

Optional: Use Advanced Data Integration Tools
For more complex ingestion pipelines, explore tools like:
LakeFlow Connect
Azure Data Factory (for Azure users)
Informatica / Talend / Apache NiFi
These tools help automate, schedule, and monitor pipelines across hybrid environments.