What you will be able to do
- Describe what a Lakeflow Connect managed connector builds and how Unity Catalog governs it
- Choose between the managed and standard SharePoint connectors for a given requirement
- Configure a file connector for unstructured (BINARYFILE) or structured (JSON and similar) ingestion and predict the resulting storage mode
- Explain how managed connectors ingest incrementally and get scheduled
1.What a Lakeflow Connect managed connector builds
Not every semi-structured or unstructured source is a bucket you can point Auto Loader at. Documents live in SharePoint and Google Drive, and records live in SaaS applications. Lakeflow Connect groups its connectors by source type: database, SaaS, file, streaming, query-based, plus community and custom connectors. For files, the relevant group is the file source connectors, which ingest both unstructured and structured files from enterprise file storage services.
A managed connector produces an ingestion pipeline that is governed by Unity Catalog and runs on serverless compute and Lakeflow pipelines. The source credentials are kept in a Unity Catalog connection. The pipeline writes to destination tables in Unity Catalog. Database connectors have extra parts, an ingestion gateway and staging storage, to support continuous change capture. File and SaaS connectors do not need them. If you need more control, standard connectors give up some of the automation in exchange for wider source support and more room to customise.
SaaS connectors are the managed route for enterprise applications such as Salesforce, HubSpot, Jira and Workday. They have the same three components as file connectors: a Unity Catalog connection, an ingestion pipeline and destination tables. They differ from file connectors in how they find changes. After the first full run, a SaaS connector reads only what changed, and how it detects change depends on the source. The Salesforce connector, for example, selects a cursor column from a set list of options. Databricks notes that some sources or specific tables do not support incremental ingestion yet. For those, you cannot assume each run reads only the changes.
Checkpoint 1 of 5· Check yourself
Which pair of components do database (CDC) connectors have that file connectors do not?
Every managed connector has a connection, a pipeline and destination tables. Only database connectors add a gateway and staging storage, which they need for continuous change capture.
“Database connectors also include an ingestion gateway and staging storage to support continuous change capture.”Source: docs.databricks.com
Sources1
2.Managed or standard: the SharePoint example
SharePoint shows the trade-off most clearly, because Lakeflow Connect offers two connectors for it. Both reach the same data and both store their credentials in a Unity Catalog connection. What separates them is how much of the pipeline you build and maintain yourself.
| Aspect | Managed SharePoint connector | Standard SharePoint connector |
|---|---|---|
| What you build | A fully managed pipeline that keeps Delta tables in sync with the source | Your own pipelines in SQL, PySpark or Lakeflow pipelines |
| APIs | Configured through UI or API pipeline authoring | read_files, spark.read, COPY INTO and Auto Loader |
| Transformations during ingestion | Limited (no API-based column selection or row filtering) | Complex transformations are possible |
| Maintenance | Low | You are responsible for managing and maintaining the pipelines |
| Databricks guidance | Recommended for most use cases | Choose it when you need the flexibility |
The standard connector reuses the tools from file ingestion generally. Auto Loader reads SharePoint once you pass the Unity Catalog connection in the databricks.connection option. In the example below it streams only PDFs, as binary files, and narrows the selection with pathGlobFilter.
# Incrementally ingest new PDF files
df = (spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "binaryFile")
.option("databricks.connection", "my_sharepoint_conn")
.option("cloudFiles.schemaLocation", "<path-to-schema-location>")
.option("pathGlobFilter", "*.pdf")
.load("https://mytenant.sharepoint.com/sites/Marketing/Shared%20Documents")
)Checkpoint 2 of 5· Check yourself
A team needs to apply complex transformations while the SharePoint files are being ingested and accepts running the pipelines itself. Which connector fits?
The standard connector trades automation for flexibility and puts maintenance on you. The managed connector is the default recommendation, but it does not offer API-based column selection or row filtering.
“Offers the flexibility to perform complex transformations during ingestion”Source: docs.databricks.com
Sources2
3.Unstructured or structured: format, storage mode and schema evolution
The managed SharePoint connector can take a file in three ways. It can store the file as binary content, parse a structured format such as JSON into rows, or capture only the file's metadata. You choose with entity_type, which is FILE or FILE_METADATA, and with format inside file_ingestion_options. That block applies to both entity types, and format is required in it. BINARYFILE is the unstructured choice for PDFs, Office files and images. Each file becomes one row, with a content column and metadata columns, ready for downstream processing. JSON, CSV, XML, EXCEL, PARQUET, AVRO and ORC are parsed instead, so each row inside a file becomes a row in the table. Setting entity_type to FILE_METADATA ingests metadata without downloading the file contents.
| Format choice | Row shape | storage_mode (only supported value) | Effect of changed files |
|---|---|---|---|
| BINARYFILE | One row per file: content column plus metadata columns | SCD_TYPE_1 | Records are overwritten when files change or are deleted |
| CSV, JSON, XML, EXCEL, PARQUET, AVRO, ORC | One row per record inside the file | APPEND_ONLY | Only newly added files are captured; updates and deletes are not ingested (limitations page) |
One caveat on the last row. The limitations page says APPEND_ONLY captures only newly added files. The reference page describes the same mode as appending rows from new or updated files, and the FAQ says only files updated since the last run are re-ingested. These wordings are not identical, and the docs do not reconcile them. This lesson follows the limitations page, which states the restriction explicitly. Either way, the connector works at the file level and does not update incrementally inside a file.
SCD type 2 is not supported, and setting the scd_type field throws an error. The default for each format is also its only option, so you never need to set storage_mode explicitly. For parsed formats, schema_evolution_mode uses the same modes as Auto Loader, written in upper case. Its default is ADD_NEW_COLUMNS_WITH_TYPE_WIDENING. With binary ingestion, each file's content is loaded into memory as a single record. Files over 100 MB can therefore make an update fail, either with an out-of-memory error or by exceeding the 2 GB limit on binary columns in Delta. The documented fix is a row_filter on the length column in table_configuration, for example length <= 104857600, which skips files larger than 100 MB.
Parsed formats also have a type question. For JSON, CSV and XML, Auto Loader infers every column as a string by default, including nested fields in JSON files. The managed connector's schema_hints option overrides inferred column types. You give it a comma-separated list of column_name TYPE pairs, for example order_id INT, amount DOUBLE. Use a hint when a field that arrives as a string should be stored with a real type.
Checkpoint 3 of 5· Exam question
A data engineer is building an Auto Loader stream that reads deeply nested JSON files. Downstream consumers require that a nested `user_info.dob` field always be materialized as a `DATE` column rather than whatever type Auto Loader would otherwise infer for it. Which mechanism lets the engineer force that specific nested field to a chosen type while still letting Auto Loader infer the rest of the schema automatically?
Correct answer: A — A schema hint such as `"user_info.dob DATE"` passed as a reader option to the stream
- A. Schema hints let you override the inferred type of a specific column path, including nested fields addressed with dot notation, while Auto Loader continues to infer the remaining schema automatically.
- B. Post-read casting on the full DataFrame changes the value's type only after inference has already happened for every field, so it does not target a single nested field during schema inference itself.
- C. A Lakeflow Spark Declarative Pipelines expectation validates or drops rows based on a condition at write time; it enforces a rule about the data rather than overriding the inferred column type during Auto Loader's schema inference step.
- D. `COPY INTO` is a separate batch-loading SQL command with its own `FORCE` option for reloading files, and it does not apply to an Auto Loader streaming read or provide per-field type overrides.
Checkpoint 4 of 5· Match them up
Match each managed SharePoint setting to its effect
Tap a term, then the definition that fits it.
The format choice determines both the row shape and the storage mode. The schema evolution default differs from Auto Loader's own default of addNewColumns.
“Supported values: ADD_NEW_COLUMNS_WITH_TYPE_WIDENING (default), ADD_NEW_COLUMNS, RESCUE, FAIL_ON_NEW_COLUMNS, NONE.”Source: docs.databricks.com
4.Incremental runs and scheduling
Managed connectors ingest incrementally. The first run ingests all of the selected data and starts tracking changes at the same time. Each later run uses that tracking to ingest only what has changed, where the source allows it. How changes are tracked depends on the source. SQL Server can use change tracking or CDC, while Salesforce uses a cursor column. Some sources or tables do not support incremental ingestion yet. For SharePoint, the unit of change is the file, never a cell or a row inside a file. The FAQ says only files updated since the last run are re-ingested, while the limitations page says structured formats in APPEND_ONLY mode capture only newly added files. The previous section explains that difference. Each schedule you add to a pipeline gets its own job, and the ingestion pipeline is a task in that job, so you can add downstream tasks after it.
Checkpoint 5 of 5· Check yourself
One cell changes in a large Excel file that a managed SharePoint pipeline has already ingested. Which statement about change tracking do the SharePoint docs support?
The connector works at the file level. It does not update incrementally within a file, for example only the Excel data that changed. Whether an edit to an already-ingested structured file is picked up at all depends on the storage mode, and the limitations page says APPEND_ONLY ignores updates to existing files.
“It does not incrementally update within those files”Source: docs.databricks.com
One reasonable answer: the managed connector cannot give you this for JSON. APPEND_ONLY is the only supported storage mode for structured formats, and SCD type 2 is not supported. The standard SharePoint connector is the customisable option, because you build the pipeline yourself with Auto Loader or spark.read and take on its maintenance. That lets you add your own handling for corrected files. A simpler alternative is to have the exports arrive as new files, since new files are what APPEND_ONLY captures. This is reasoning from the documented limits, not a documented recipe.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A managed SharePoint pipeline that parses JSON keeps the table in sync when existing files are edited or deleted.Why is that wrong?
Structured formats support only APPEND_ONLY, which captures newly added files. Updates and deletes to existing files are not ingested.
Covered in Unstructured or structured: format, storage mode and schema evolution
2.The managed SharePoint connector uses Auto Loader's default schema evolution mode, addNewColumns.Why is that wrong?
The modes are the same as Auto Loader's, but the connector's default is ADD_NEW_COLUMNS_WITH_TYPE_WIDENING.
Covered in Unstructured or structured: format, storage mode and schema evolution
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Managed connectors produce ingestion pipelines that are governed by Unity Catalog and powered by serverless compute and Lakeflow pipelines”
↩︎ What a Lakeflow Connect managed connector builds“Ingest unstructured and structured files from enterprise file storage services including Google Drive and SharePoint.”
↩︎ What a Lakeflow Connect managed connector builds“standard connectors, which trade some automation for broader source support and customization”
↩︎ What a Lakeflow Connect managed connector builds“Ingest data from enterprise SaaS applications including Salesforce, HubSpot, Jira, Workday, and more.”
↩︎ What a Lakeflow Connect managed connector builds“the Salesforce connector selects a cursor column from a set list of options”
↩︎ What a Lakeflow Connect managed connector builds“On the first run of your pipeline, it ingests all of the selected data from the source.”
↩︎ Incremental runs and scheduling“For each schedule that you add to a pipeline, Lakeflow Connect automatically creates a job for it.”
↩︎ Incremental runs and scheduling“Some sources or specific tables don't support incremental ingestion at this time.”
↩︎ Incremental runs and scheduling“SaaS and file connectors use a connection, an ingestion pipeline, and destination tables.”
↩︎ Prediction“Database connectors also include an ingestion gateway and staging storage to support continuous change capture.”
↩︎ Checkpoint - 2.
“Databricks recommends the managed SharePoint connector for most use cases.”
↩︎ Managed or standard: the SharePoint example“Offers the flexibility to perform complex transformations during ingestion, while giving you greater responsibility for managing and maintaining your pipelines.”
↩︎ Managed or standard: the SharePoint example“Offers the flexibility to perform complex transformations during ingestion”
↩︎ Checkpoint - 3.
“Use BINARYFILE for unstructured ingestion (PDFs, Office files, images).”
↩︎ Unstructured or structured: format, storage mode and schema evolution“Overrides inferred column types.”
↩︎ Unstructured or structured: format, storage mode and schema evolution“Appends new rows from new or updated files.”
↩︎ Unstructured or structured: format, storage mode and schema evolution“Modes match Auto Loader schema evolution modes.”
↩︎ Exam trap 2“Supported values: ADD_NEW_COLUMNS_WITH_TYPE_WIDENING (default), ADD_NEW_COLUMNS, RESCUE, FAIL_ON_NEW_COLUMNS, NONE.”
↩︎ Checkpoint - 4.
“Auto Loader infers all columns as strings (including nested fields in JSON files).”
↩︎ Unstructured or structured: format, storage mode and schema evolution - 5.
“Files are ingested as rows with a content column plus metadata columns.”
↩︎ Unstructured or structured: format, storage mode and schema evolution“the connector only re-ingests files that were updated since the last run”
↩︎ Incremental runs and scheduling“It does not incrementally update within those files”
↩︎ Checkpoint - 6.
“Setting the scd_type field throws an error.”
↩︎ Unstructured or structured: format, storage mode and schema evolution“files larger than 100 MB can cause the update to fail”
↩︎ Unstructured or structured: format, storage mode and schema evolution“exceeding the 2 GB limit on binary columns in Delta”
↩︎ Unstructured or structured: format, storage mode and schema evolution“APPEND_ONLY storage mode captures only newly added files. Updates and deletes to existing files are not ingested.”
↩︎ Exam trap 1“Updates and deletes to existing files are not ingested.”
↩︎ Prediction