How do I move data from PostgreSQL into Snowflake on a schedule?

A common way to do this is:

Recommended approach

Use an ELT pipeline:

1. Extract data from PostgreSQL
2. Load it into Snowflake
3. Schedule the job to run automatically

Practical options

1) Use an ETL/ELT tool

Tools like:

• Fivetran
• Airbyte
• Matillion
• Talend
• Stitch

These are usually the easiest because they handle:

• incremental syncs
• schema changes
• retries
• scheduling
• monitoring

Best if: you want the simplest setup and don’t mind using a managed tool.

---

2) Build your own scheduled pipeline

A common DIY pattern is:

• A script extracts rows from PostgreSQL
• Writes them to files like CSV or JSON
• Uploads files to a Snowflake stage or cloud storage (S3 / GCS / Azure Blob)
• Runs COPY INTO in Snowflake
• Schedule via:
  • cron
  • Airflow
  • Prefect
  • Dagster
  • dbt Cloud jobs
  • AWS Lambda + EventBridge
  • Cloud Scheduler

Best if: you want full control and lower tooling cost.

---

3) Use CDC (change data capture)

If you need near-real-time or low-latency sync:

• capture inserts/updates/deletes from PostgreSQL
• stream them into Snowflake

You can use:

• Debezium
• Fivetran
• Airbyte CDC
• Kafka-based pipelines

Best if: you need frequent syncs and want minimal data loss or lag.

---

A simple schedule-based architecture

For batch loads, this is a common flow:

PostgreSQL → Extract job → S3/GCS/Azure Blob → Snowflake COPY INTO → Scheduler

Example schedule:

• every hour
• every night at 2 AM
• every 15 minutes

---

Snowflake loading methods

Snowflake typically ingests via:

COPY INTO

Good for batch file loads.

Example:

COPY INTO my_table
FROM @my_stage/path/
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"');

Snowpipe

Good for automatic loading when new files arrive in cloud storage.

If your pipeline writes files to S3/GCS/Azure Blob, Snowpipe can automatically ingest them.

---

If you want a straightforward recommendation

• For easiest setup: Fivetran or Airbyte
• For custom control: Python script + cron/Airflow + Snowflake COPY INTO
• For near-real-time: CDC with Debezium or managed CDC tool

---

Things to think about

• full refresh vs incremental sync
• primary keys / updated_at columns
• handling deletes
• deduplication
• schema changes
• data validation
• timezone and type mapping

If you want, I can give you:

1. a step-by-step DIY Python example,
2. an Airbyte/Fivetran setup, or
3. a Snowflake + cron + S3 architecture.