Lakeflow Connect in Azure Databricks: Managed Connectors for SaaS, Databases, and Cloud Storage - Setup, Incremental Ingestion, CDC, Scheduling, and Production Patterns

Lakeflow Connect in Azure Databricks: Managed Connectors for SaaS, Databases, and Cloud Storage — Setup, Incremental Ingestion, CDC, Scheduling, and Production Patterns

Lakeflow Connect in Azure Databricks: Managed Connectors for SaaS, Databases, and Cloud Storage — Setup, Incremental Ingestion, CDC, Scheduling, and Production Patterns
What Is Lakeflow Connect?
Setting Up a Managed SaaS Connector
Setting Up a Managed Database Connector
Incremental Ingestion
Scheduling and Orchestration
Unity Catalog Governance
Lakeflow Connect vs Other Ingestion Tools
Common Mistakes
Interview Questions
Wrapping Up

Our Lakeflow Declarative Pipelines post covered building transformation pipelines with streaming tables and materialized views. But before you can transform data, you need to ingest it. Lakeflow Connect is Databricks’ managed ingestion service — it provides point-and-click connectors for 100+ SaaS applications, databases, and cloud storage sources, with automatic incremental loading, CDC support, and Unity Catalog governance built in.

Analogy — A universal mail forwarding service. Imagine you subscribe to 30 different magazines (data sources: Salesforce, SQL Server, PostgreSQL, Snowflake, Google Analytics). Without a forwarding service, you check each magazine’s website individually, download PDFs, and organize them yourself. Lakeflow Connect is the forwarding service — it collects all your magazines automatically, delivers them to one mailbox (Unity Catalog), and only sends you new issues (incremental ingestion). You set it up once and it runs forever.

What Is Lakeflow Connect?

Lakeflow Connect provides three types of connectors for ingesting data into Databricks:

Connector Type	How It Works	Sources	Best For
Managed SaaS connectors	Connects directly to SaaS application APIs	Salesforce, HubSpot, Google Analytics, Workday, ServiceNow, Stripe, etc.	SaaS application data ingestion
Managed database connectors	Uses an ingestion gateway + staging volume for CDC	SQL Server, PostgreSQL, MySQL, Oracle, MongoDB	Database replication with CDC
Standard connectors	AutoLoader, Kafka, Event Hubs, cloud storage	S3, ADLS, GCS, Kafka, Event Hubs	File-based and streaming ingestion

Architecture

SaaS Connector:
  SaaS App API --> Lakeflow Connect (serverless) --> Delta Tables in Unity Catalog

Database Connector:
  Source DB --> Ingestion Gateway --> Staging Volume --> Lakeflow Pipeline --> Delta Tables
  (The gateway captures CDC changes continuously; the pipeline applies them)

Standard Connector:
  Cloud Storage / Message Bus --> AutoLoader / Structured Streaming --> Delta Tables
  (Covered in our AutoLoader and Streaming posts)

Setting Up a Managed SaaS Connector

Analogy — Installing an app on your phone. You go to the app store (Lakeflow Connect UI), find the app (Salesforce connector), authenticate (OAuth), select what data you want (tables/objects), and the app syncs automatically. No code, no infrastructure.

Step-by-step (UI):
  1. Workspace --> Lakeflow Connect --> Create Ingestion Pipeline
  2. Select source: e.g., "Salesforce"
  3. Authenticate: OAuth sign-in to your Salesforce org
  4. Select objects: Accounts, Contacts, Opportunities (pick the tables you need)
  5. Configure destination:
     - Catalog: prod_catalog
     - Schema: salesforce_raw
  6. Schedule: Every 1 hour / 6 hours / daily
  7. Click "Create" --> pipeline starts initial full load

What happens:
  - First run: Full snapshot of all selected objects
  - Subsequent runs: Only changed records (incremental)
  - Result: Delta tables in Unity Catalog, always fresh

Setting Up a Managed Database Connector

Database connectors are more complex than SaaS connectors because they require a gateway — a lightweight agent that runs inside your network (or VNet) and captures changes from the source database using CDC (Change Data Capture).

Step-by-step (UI):
  1. Workspace --> Lakeflow Connect --> Create Ingestion Pipeline
  2. Select source: e.g., "SQL Server"
  3. Configure gateway:
     - Deploy ingestion gateway (runs as a Databricks job in your workspace)
     - Configure network access to source database (VNet peering or private endpoint)
  4. Configure connection:
     - Server: mydb.database.windows.net
     - Database: production_db
     - Authentication: SQL auth or Managed Identity
  5. Select tables: dbo.orders, dbo.customers, dbo.products
  6. Configure destination:
     - Catalog: prod_catalog
     - Schema: sqlserver_raw
     - Staging volume: /Volumes/staging/sqlserver/
  7. Enable CDC: tracks INSERT, UPDATE, DELETE from source
  8. Schedule: Continuous or triggered

How CDC works:
  - Gateway reads the source database's change log (CT or CDC)
  - Changes land in the staging volume as files
  - Lakeflow Pipeline applies changes to Delta tables (MERGE)
  - Result: Delta tables mirror the source database in near real-time

Incremental Ingestion

Lakeflow Connect uses incremental ingestion by default — it tracks what has already been loaded and only processes new or changed data on subsequent runs.

Ingestion Type	First Run	Subsequent Runs	How It Tracks Changes
SaaS connectors	Full snapshot	Cursor-based (e.g., last modified timestamp)	API query parameters
Database CDC	Full snapshot	Change log (INSERT/UPDATE/DELETE)	SQL Server CT, PostgreSQL logical replication
Database query-based	Full query	Re-query with watermark filter	Timestamp or ID column

# Example: Query-based incremental ingestion (conceptual)
# Lakeflow Connect does this automatically, but here is the logic:

# First run: SELECT * FROM orders (full load)
# Tracks: max(updated_at) = '2026-07-19 15:30:00'

# Second run: SELECT * FROM orders WHERE updated_at > '2026-07-19 15:30:00'
# Tracks: max(updated_at) = '2026-07-20 08:45:00'

# Third run: SELECT * FROM orders WHERE updated_at > '2026-07-20 08:45:00'
# ... and so on

Scheduling and Orchestration

Lakeflow Connect creates a Lakeflow Job for each ingestion pipeline. You can schedule it on a cron expression, trigger it on file arrival, or run it continuously.

Scheduling options:
  - Cron: "0 */6 * * *" (every 6 hours)
  - Continuous: Pipeline runs forever, processing changes as they arrive
  - Manual: Trigger via UI or REST API

Adding downstream tasks:
  Each ingestion pipeline runs as a task within a Lakeflow Job.
  You can add additional tasks after ingestion:
  1. Task 1: Lakeflow Connect (ingest from Salesforce)
  2. Task 2: Lakeflow Declarative Pipeline (bronze -> silver -> gold transforms)
  3. Task 3: Notebook (custom post-processing, notifications)

Unity Catalog Governance

Every table created by Lakeflow Connect is a Unity Catalog managed table. This means automatic lineage tracking, access control, audit logging, and discoverability.

What you get automatically:
  - Lineage: See that gold_revenue came from silver_orders came from bronze_salesforce
  - Access control: GRANT SELECT to downstream consumers, REVOKE from unauthorized users
  - Audit: Who queried the data, when, from where
  - Discovery: Tables appear in Catalog Explorer with descriptions and tags
  - Schema: Column types, nullability, constraints tracked in Unity Catalog

Lakeflow Connect vs Other Ingestion Tools

Feature	Lakeflow Connect	Azure Data Factory	Custom Notebooks
Setup	Point-and-click, no code	Pipeline design canvas	Write PySpark code
CDC support	Built-in (database connectors)	Via Change Tracking or watermark	Manual MERGE logic
Incremental	Automatic	Configure with watermarks	Manual tracking
Compute	Serverless (managed)	Integration Runtime	Job clusters
Governance	Unity Catalog native	No UC integration	Manual registration
Cost	DBU-based (free tier: 100 DBU/day)	Pipeline activity runs	Job cluster cost
Best for	Databricks-native pipelines	Multi-cloud orchestration	Custom/complex logic

Common Mistakes

1. Using ADF when Lakeflow Connect supports the source. If your source is SQL Server, PostgreSQL, Salesforce, or another supported connector, Lakeflow Connect is simpler (no pipeline canvas), faster (serverless), and better integrated (Unity Catalog native). Use ADF for sources Lakeflow Connect does not support or for multi-cloud orchestration.

2. Not enabling CDC for database connectors. Without CDC, database connectors must re-query the entire table on each run (full load). With CDC enabled, only changed rows are captured. For tables with millions of rows, the difference is 10x faster and 10x cheaper.

3. Skipping the staging volume for database connectors. Database connectors require a staging volume (Unity Catalog volume) to land CDC files before applying them. Not configuring this correctly causes ingestion failures.

4. Ingesting all tables from a source. Just because you can ingest 200 tables from Salesforce does not mean you should. Ingest only the tables your downstream pipelines and dashboards actually use. More tables = more cost + more governance overhead.

5. Not monitoring ingestion freshness. Lakeflow Connect can silently fall behind if the source API rate-limits you or the gateway loses connectivity. Monitor the ingestion lag and set up alerts for stale data.

6. Running continuous mode for low-frequency sources. If your Salesforce data changes once per day, running the ingestion continuously wastes compute. Use a scheduled trigger (every 6 hours or daily) instead.

7. Not using the free tier. Every Databricks workspace gets 100 free DBUs per day for Lakeflow Connect. This is enough to ingest up to 100 million records per day. Do not ignore free resources.

8. Hardcoding credentials in connections. Always use Unity Catalog connections with OAuth or Managed Identity. Never store passwords in notebook variables or job parameters.

Interview Questions

Q: What is Lakeflow Connect and how does it differ from Azure Data Factory? A: Lakeflow Connect is Databricks’ managed ingestion service that provides point-and-click connectors for 100+ data sources (SaaS apps, databases, cloud storage). Unlike ADF, it is Databricks-native (runs on serverless compute, publishes to Unity Catalog, provides built-in CDC). ADF is better for multi-cloud orchestration and sources Lakeflow Connect does not support. Lakeflow Connect is simpler for Databricks-to-Databricks pipelines.

Q: What is the difference between a managed SaaS connector and a managed database connector? A: SaaS connectors call the source application’s API directly (e.g., Salesforce REST API) and do not require a gateway. Database connectors require an ingestion gateway that runs inside your network to capture CDC changes from the source database’s change log. SaaS connectors are simpler to set up; database connectors provide real-time CDC replication.

Q: How does incremental ingestion work in Lakeflow Connect? A: On the first run, Lakeflow Connect performs a full snapshot (loads all data). It then tracks changes using either cursor-based tracking (SaaS — query records modified after last run), CDC (databases — read the change log for inserts, updates, deletes), or watermark-based (query with a timestamp filter). Subsequent runs process only changed data, making them faster and cheaper.

Q: How does Lakeflow Connect integrate with Unity Catalog? A: All tables created by Lakeflow Connect are Unity Catalog managed tables. This provides automatic data lineage (trace data from source to gold layer), access control (GRANT/REVOKE permissions), audit logging (who accessed what), and discoverability (tables appear in Catalog Explorer with metadata). Connections are also Unity Catalog securables with managed credentials.

Q: What is the ingestion gateway and when do you need it? A: The ingestion gateway is a lightweight agent deployed as a Databricks job that runs inside your network (VNet). It is required only for managed database connectors (SQL Server, PostgreSQL, MySQL, Oracle) because it needs direct network access to the source database to read CDC change logs. SaaS connectors do not need a gateway because they call public APIs.

Q: How would you build an end-to-end pipeline with Lakeflow Connect and Lakeflow Declarative Pipelines? A: Step 1: Set up Lakeflow Connect to ingest source data into Unity Catalog as bronze Delta tables. Step 2: Create a Lakeflow Declarative Pipeline that reads from the bronze tables, applies expectations (data quality), transforms to silver (clean, typed, deduplicated), and creates gold materialized views (aggregations). Step 3: Schedule both as tasks in a single Lakeflow Job — ingestion first, then transformation. The result is a fully managed, end-to-end pipeline with zero custom infrastructure code.

Wrapping Up

Lakeflow Connect eliminates the boilerplate of data ingestion. Instead of writing custom PySpark code to call APIs, manage watermarks, handle retries, and register tables in Unity Catalog, you configure a connector in the UI and let Databricks handle the infrastructure. Start with the free tier (100 DBUs/day), connect your most important data sources, and let Lakeflow Connect deliver fresh data to your bronze layer automatically.

← Previous: Lakeflow Declarative Pipelines Databricks (20/23) Next: Delta Lake Advanced →

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.

Lakeflow Connect in Azure Databricks: Managed Connectors for SaaS, Databases, and Cloud Storage — Setup, Incremental Ingestion, CDC, Scheduling, and Production Patterns