Overview Quickstart dbt Compatibility The Style Guide profiles.yml sources.yml Configuration Supported Databases

SFTP

Any SSH-reachable server as a data endpoint — exchange folders, partner drop zones, legacy hosts. Files move through the same Sling lane as the cloud buckets.

PREREQUISITES

SDK: (none — built-in via Sling) — installed automatically by:

dvt sync

CONFIGURATION FIELDS

FIELD	REQUIRED	DEFAULT	DESCRIPTION
type	yes	—	Must be `sftp`
host	yes	—	SFTP server hostname or IP
user	yes	—	SSH user
port	no	22	SSH port
password	no	—	Password auth. Omit when using a private key
private_key	no	—	Path to the SSH private key (or the inline PEM). One of password / private_key is required
passphrase	no	—	Private key passphrase, when the key has one
path	no	—	Base directory on the server (e.g. /exports)
format	no	parquet	Default file format: csv, parquet, json, jsonl

PROFILES.YML EXAMPLE

my_project:
  target: prod_pg
  outputs:
    prod_pg:
      type: postgres
      host: db.internal
      user: dvt
      password: "{{ env_var('PG_PASSWORD') }}"
      dbname: analytics
      schema: public

    partner_drop:
      type: sftp
      host: files.partner.example
      user: exchange
      private_key: ~/.ssh/partner_ed25519
      path: /exports
      format: csv

SOURCES.YML EXAMPLE

sources:
  - name: partner_files
    meta:
      connection: partner_drop         # must match profiles.yml output name
    tables:
      - name: daily_positions          # /exports/daily_positions.csv
      - name: reference_rates

MODEL EXAMPLE — BUCKET AS SOURCE

Sling extracts files from the bucket into the DuckDB cache. Model SQL executes in DuckDB (Postgres-like dialect).

-- models/staging/stg_positions.sql
-- Read a partner's SFTP drop as a federated source
{{ config(materialized='f_table') }}

select
    position_id,
    account_code,
    cast(notional as decimal(18,2)) as notional
from {{ source('partner_files', 'daily_positions') }}

MODEL EXAMPLE — BUCKET AS TARGET

Model executes on the default target. Sling streams the result to the bucket in the specified format. Use config(format='...') to override the default format set in profiles.yml.

-- models/exports/positions_out.sql
-- Land results ON the SFTP server (bucket-style target)
{{ config(materialized='f_table', target='partner_drop', format='csv') }}

select account_code, sum(notional) as total_notional
from {{ ref('stg_positions') }}
group by account_code

FILE FORMAT CONFIGURATION

The output format is resolved in order of priority:

1 in model file— highest priority

2 in profiles.yml output— project default

3— built-in default

FORMAT	VALUE	NOTES
Parquet	parquet	Columnar, compressed. Best for analytics. Default.
Delta Lake	delta	Schema evolution, time travel, ACID transactions.
CSV	csv	Universal compatibility. No schema enforcement.
JSON	json	Nested structures, human-readable.
JSON Lines	jsonl	One record per line. Streamable.
Avro	avro	Schema embedded, compact binary. Kafka-friendly.
IPC / Arrow	ipc	Arrow IPC format. Zero-copy reads.

INCREMENTAL EXTRACTION

Incremental models work with cloud storage sources. DVT resolves the watermark from the target database, extracts only changed rows, and merges them in the DuckDB cache.

-- models/exports/positions_incr.sql
{{ config(
    materialized='f_incremental',
    target='partner_drop',
    format='parquet',
    unique_key='position_id',
    watermark_column='updated_at'
) }}

select position_id, account_code, notional, updated_at
from {{ ref('stg_positions') }}

NOTES

⚠No SQL engine: SFTP connections hold files, so they cannot be a default target, run hooks, or answer dvt exec — DVT refuses those honestly.
⚠Key-based auth supports a key path or inline PEM (+ optional passphrase); agent-forwarding setups should use a key file.
⚠Retraction is manual for now — dvt retract skips SFTP targets and tells you so.

DEBUGGING

Use dvt debug to test bucket connectivity. DVT verifies read/write access for each cloud storage connection.

dvt debug