SFTP Data Connector

The BigPanda Secure File Transfer Protocol (SFTP) data connector ingests file-based data from SFTP servers into the Unified Data Connector (UDC) pipeline. It supports configurable incremental sync strategies, streaming CSV reads with retries on transient failures, and chunked writes for large files. It can also stream matched CSV files directly to Amazon S3 as size-bounded JSON Lines (JSONL), keeping only the rows updated on or after each file's date within a configurable look-back window.

Key features

Scheduled file ingestion from SFTP servers via cron expressions.
Configurable fallback behavior for files with unparseable filename dates.
Streaming CSV reads with automatic retry on transient SFTP failures.
Progress logging and chunked writes for large files.
Direct-to-S3 streaming that filters each file to the rows updated on or after its filename date, within a configurable look-back window, and writes size-bounded JSONL objects to Amazon S3.

Read resilience and streaming

The SFTP connector streams CSV rows as it reads each file rather than buffering the entire file in memory, and automatically retries transient read failures.

Append mode

Because retries restart the file from the beginning, rows emitted before a mid-file failure may be re-emitted after a successful retry. In append-mode pipelines, this can introduce duplicate rows for the affected file. Use a downstream deduplication step or prefer replace-mode loads when duplicates are not acceptable.

Streaming CSV reads. CSV files are streamed row-by-row rather than fully loaded into memory before processing. During a read, the connector logs the file size before the read starts and a row-progress counter every 1,000 rows processed. Use these logs to monitor ingestion progress for large files.

Chunked writes. SFTP pipelines apply connector-scoped chunking limits so that load packages flush in smaller batches. For very large input files, this means the downstream loader can start processing data before the entire source file has been read, improving end-to-end throughput.

Resume after restart

Chunk resume state is persisted to S3 rather than local run disk, so progress survives a replaced run environment (for example, a pod restart) rather than restarting large files from the beginning.

By default, historical (mode: historical) runs clear chunk resume progress before reading files. Two options change this behavior:

Option	Description
`preserve_chunk_resume`	When `true`, keeps existing resume progress instead of clearing it at the start of a historical run, so that large CSVs continue from stored `next_offset`.
`chunk_resume_seed`	Seeds a starting byte offset for an interrupted historical CSV load. Remove this value after the file reaches EOF.

Plan changes during a maintenance window

Use preserve_chunk_resume or chunk_resume_seed when recovering an interrupted historical load. Validate the resumed byte offset and row counts after the run completes.

Authentication

The SFTP connector uses SSH key authentication. These details are required when configuring the connection:

Instance URL: SFTP host (for example, sftp://sftp.example.com or sftp.example.com).
Username: SFTP username.
Private key: PEM-encoded private key (RSA, Ed25519, or ECDSA).
Passphrase: Optional; for encrypted private keys.

Sync preferences

Provide the following information about your sync preferences to BigPanda:

Required Configuration

Option	Description
`cron_schedule`	Cron expression for scheduling the connector (for example, `0 /4 * *` for every 4 hours).

Optional Pipeline Configuration

Option	Description
`start_date`	Start date for data collection in `YYYY-MM-DD` format. This is only for historical syncs. We recommend syncing 12 months of data.
`incremental_by`	Controls how the SFTP connector tracks progress during incremental syncs. You can choose one of three strategies. `file_mtime` - Uses the file modification time to determine which files are new or updated since the last sync. This is the default behavior and works well when files are written once and not modified after initial creation. `row_column` - Uses a date column within each row of the file data to track incremental progress. This is useful when individual rows within a file represent time-series data and you want to track progress at the row level rather than the file level. `filename_date` - Extracts dates in `YYYYMMDD` format from filenames to determine which files to include in each sync. Files are filtered and counted based on the parsed filename date rather than the file modification time. Useful when files are named with date stamps (for example, `transactions_20250415.csv`), when file modification times are unreliable, or when date-based filtering must align with business date boundaries. Tip Remapping and incremental sync When you set `row_format`, cursor state follows the effective destination resource name, `incident`, `cmdb_ci`, or the value of `dlt_resource_name` , rather than `sftp_files`. Row-level filters and the `row_column` incremental strategy continue to use the source CSV column names from before remapping, not the remapped field names.
`filename_date_fallback`	Fallback behavior when filename dates are unparseable. Only applies when `incremental_by` is `filename_date`: `mtime_fallback` - Falls back to the file modification time for filtering and date-range assignment. The file is still included in the sync. `skip` - Ignores the file entirely. It will not be processed or counted. `fail` - Stops the pipeline with an error. Use this when all files are expected to have date-stamped names and a missing date indicates a problem.
`row_format`	Remaps incoming file rows before they are loaded. When unset, rows are loaded in their raw source-file shape as source columns into the `sftp_files` resource. `row_format=itmapp_servicenow` or `row_format=itmapp_servicenow_incident` creates ServiceNow-style incident-shaped rows. `row_format=itmapp_servicenow_cmdb_ci` creates configuration-item-shaped rows. `row_format=itmapp_servicenow_change_request` creates change-request-shaped rows. `row_format=itmapp_servicenow_problem` creates problem-shaped rows. `row_format=itmapp_servicenow_kb_knowledge` creates knowledge-article-shaped rows. `row_format=itmapp_flat` creates a flat ITMAPP passthrough: every source column is kept as plain text with no `{value, display_value}` objects, and both `_guid` and `_name` columns are preserved rather than collapsed. `sys_id` is aliased from `flat_sys_id_source` so the records suit text-first vector indexing. `itmapp_flat` requires CSV parsing and cannot be used with raw-copy loads. For all ServiceNow-style shapes, choice mappings accept either the `<field>_id` / `<field>_description` pair or a bare `<field>` value when `<field>_id` is absent. `wf_guid` maps to `sys_id` for configuration items, change requests, problems, and knowledge articles.
`flat_sys_id_source`	Applies only when `row_format=itmapp_flat`. Source column aliased to the primary key `sys_id` when a row has no populated `sys_id`. Defaults to `wf_guid`. The column must be stable and unique per row, or re-indexing duplicates records.
`flat_sys_id_on_missing`	Applies only when `row_format=itmapp_flat`. Controls what happens when a row has neither a populated `sys_id` nor a populated `flat_sys_id_source` value. `keep` (default) emits the row without a `sys_id` and never drops data, `skip` drops the row, and `fail` stops the pipeline with an error.
`dlt_resource_name`	Overrides the destination resource name that the connector loads into. When unset, the destination defaults to `sftp_files`, or to the remapped resource (`incident` or `cmdb_ci`) when `row_format` is set.
`upload_mode`	Selects how matched files are ingested. `parse` (default) loads files through the normal pipeline, `raw_copy` copies matched files byte-for-byte, and `direct_s3` streams matched CSV files to S3 as JSONL, keeping only the rows updated on or after each file's filename date. See Direct-to-S3 streaming below. Connectors that omit `upload_mode` keep `parse` behavior.

Fallback strategy best practice

Start with mtime_fallback during initial setup to avoid pipeline failures from unexpected filenames. Switch to skip or fail once you have validated that all source files follow a consistent naming convention.

Use caution during this process. Switching between incremental strategies on an existing pipeline may cause files to be reprocessed or skipped, depending on the difference between filename dates and file modification times. Plan strategy changes during a maintenance window.

Output remapping and destination resources

By default, the SFTP connector loads each file's rows in their raw source-file shape as source columns into the sftp_files resource. Set row_format to remap exports before they are loaded:

itmapp_servicenow or itmapp_servicenow_incident maps incident-shaped rows.
itmapp_servicenow_cmdb_ci maps configuration-item-shaped rows.
itmapp_servicenow_change_request maps change-request-shaped rows.
itmapp_servicenow_problem maps problem-shaped rows.
itmapp_servicenow_kb_knowledge maps knowledge-article-shaped rows.
itmapp_flat maps a flat ITMAPP passthrough, keeping every column as plain text with sys_id aliased from flat_sys_id_source.

The destination resource the connector loads into depends on your configuration:

No row_format: Loads target the sftp_files resource.
Incident remapping: Loads default to the incident resource.
Configuration-item remapping: Loads default to the cmdb_ci resource.
Change-request remapping: Loads default to the change_request resource.
Problem remapping: Loads default to the problem resource.
Knowledge-article remapping: Loads default to the kb_knowledge resource.
dlt_resource_name set: Loads target the resource name you provide, overriding the defaults above.

Remapped SFTP loads

If you enable row_format on an existing SFTP pipeline, the destination resource changes from sftp_files to the remapped resource (incident, cmdb_ci, or the itmapp_flat destination), or to the value of dlt_resource_name. Because cursor state follows the effective resource name, an existing pipeline's tracked progress does not carry over to the new resource.

Plan changes during a maintenance window

Enabling or changing row_format or dlt_resource_name on an existing pipeline changes the destination resource and the resource that cursor state is tracked against. This may cause data to load into a different resource than before, or progress to be re-tracked from the start. Plan these changes during a maintenance window, and validate the destination resource and incremental progress after the change.

Direct-to-S3 streaming

Set upload_mode to direct_s3 to stream a matched CSV file directly to Amazon S3 as JSON Lines (JSONL), keeping only rows updated on or after the date in the filename.

This mode is recommended when you receive a full CSV snapshot each day and want to land only that day's updates in object storage instead of loading the entire snapshot through the pipeline.

upload_mode accepts three values:

parse: Loads matched files through the normal pipeline. This is the default, and connectors that omit upload_mode keep this behavior.
raw_copy: Copies matched files byte-for-byte.
direct_s3: Streams matched CSV files to S3 as JSONL with date-based row filtering.

Defaults changed for existing direct_s3 connectors

A direct_s3 connector that does not set direct_row_lookback_days or direct_shard_max_rows adopts the one-day look-back window and the 100-row shard default on its next run. This can keep more rows and produce more, smaller output objects than before. Set these keys explicitly to keep the earlier behavior. parse and raw_copy connectors are unchanged.

Direct-to-S3 requirements

When you set upload_mode to direct_s3, the connector validates the rest of the configuration before a run and stops with an error if any of the following is not true:

file_format is csv.
incremental_by is filename_date.
sys_updated_column is set and non-empty.
The destination is S3-backed.
row_date_column is cleared, because the filename-date filter replaces it.
mode is historical or incremental.
If filter_date_override is set, it is a full ISO date in YYYY-MM-DD format. Trailing characters are rejected.

Row filtering

direct_s3 keeps a row when the value in sys_updated_column is on or after the start of the file's filename date in UTC (or the filter_date_override date, when one is set), minus the look-back window set by direct_row_lookback_days. sys_updated_column defaults to sys_updated_on. Rows with a missing or unparseable value in that column are dropped.

direct_row_lookback_days defaults to 1. By default, the connector also keeps prior-day updates that arrived after that day's export, for example, late updates that missed an early-morning snapshot cutoff. Set direct_row_lookback_days to 0 for a strict same-day cutoff, or to a higher value to widen the window.

Pin a date with filter_date_override

Without an override, direct_s3 tracks progress by filename date. It keeps that filename-date cursor in a dedicated resource separate from your data, so the cursor does not appear in the {dataset}/{resource} output folder.

Set filter_date_override to a full ISO date (YYYY-MM-DD) to ingest exactly the one file whose filename date matches that day, with the row cutoff pinned to the same date. After that file is ingested successfully, the override advances to the next day. If the file for that day is not present, nothing is ingested, and the override stays in place, so the next run retries the same day.

When filter_date_override is set, it is the only watermark the connector uses. The filename-date cursor is neither read nor written while an override is in effect.

If filter_date_override is two or more days ahead of today in UTC, the run fails with an error prompting you to reset the override to a valid date, rather than waiting for a file that does not yet exist.

Re-ingesting a day is safe in override mode

When filter_date_override is set, output object keys are pinned to the filter date, so re-ingesting a day overwrites the same objects instead of creating duplicates. Without an override, object keys use the run start time, so a retry writes new objects.

Output layout and sharding

Each JSONL object is finalized when it reaches direct_shard_max_rows (default 100 rows) or direct_shard_max_bytes (default 10,485,760 bytes, 10 MiB), whichever comes first. Objects follow the same filesystem and S3 JSONL layout used elsewhere:

{dataset}/{resource}/{YYYY}/{MM}/{DD}/{HH}-{mm}/{load_id}.{shard}.jsonl

Tune shard size

The default of 100 rows per object produces many small JSONL objects, allowing downstream jobs to start processing before the file ends. If your consumers prefer fewer, larger files, raise direct_shard_max_rows. The 10 MiB byte cap always applies.

Remapping in direct_s3

Within direct_s3, row_format accepts raw (the default passthrough) or itmapp_flat. Other ServiceNow object-shape remaps are rejected in this mode. As on the parse path, itmapp_flat still requires flat_sys_id_source.

Pre-load redaction is not supported

direct_s3 bypasses the pipeline data load and cannot apply pre-load redaction. If pre-load redaction is configured for the connector, direct_s3 fails to start. Use upload_mode: parse when redaction is required.

direct_s3 Configuration options

Option	Description
`sys_updated_column`	Names the CSV column that holds each row's last-updated timestamp. `direct_s3` keeps a row when this value is on or after the start of the file's filename date in UTC, and drops rows with a missing or unparseable value. Defaults to `sys_updated_on`. Must be set and non-empty for `direct_s3`.
`filter_date_override`	Optional. A full ISO date (`YYYY-MM-DD`) that pins the run to the single file whose filename date matches, with the row cutoff pinned to the same date. After a successful ingest, the override advances to the next day; if that day's file is absent, nothing is ingested and the override stays so the next run retries the same day. If the value is two or more days ahead of today (UTC), the run fails. When set, it is the connector's only watermark.
`direct_row_lookback_days`	Number of days before the file's filename date (or `filter_date_override`) to extend the row cutoff, in UTC. Must be 0 or greater. Defaults to 1, which keeps prior-day updates that arrived after that day's export. Set 0 for a strict same-day cutoff.
`direct_shard_max_rows`	Maximum number of rows per output JSONL object before it is finalized. Defaults to 100. Raise this for fewer, larger objects.
`direct_shard_max_bytes`	Maximum size in bytes per output JSONL object before it is finalized. Defaults to 10,485,760 (10 MiB).

Validation and troubleshooting

If a direct_s3 run stops before ingesting, check configuration settings. Common causes:

CSV required: direct_s3 only reads CSV files. Confirm file_format is csv.
Filename-date tracking required: Confirm incremental_by is filename_date and that row_date_column is cleared.
Missing timestamp column: Confirm sys_updated_column is set and names a real column in the file. Rows with a missing or unparseable value in that column are dropped.
Bad override value: filter_date_override must be a full ISO date (YYYY-MM-DD) with no trailing characters.
Override too far ahead: If filter_date_override is two or more days ahead of today (UTC), the run fails. Reset it to a valid date, typically today or yesterday.
Redaction configured: direct_s3 fails to start when pre-load redaction is configured. Use upload_mode: parse instead.

A large number of small JSONL objects is expected with the default direct_shard_max_rows of 100, and it is not an error. Raise direct_shard_max_rows if you want fewer, larger objects.

In this section: