I'm building a small analytics stack and need to move data from Postgres, Salesforce, and S3 into one warehouse

A solid way to do this is to standardize on an ELT pipeline into a warehouse, then transform there.

Recommended architecture

Sources

• Postgres
• Salesforce
• S3

Ingestion layer

• Use a connector/orchestrator tool such as:
  • Airbyte or Fivetran for managed replication
  • Meltano / Singer taps if you want open source and more control
  • AWS Glue / Lambda / custom jobs if you want to build it yourself

Warehouse

• Common choices:
  • Snowflake
  • BigQuery
  • Redshift
  • Databricks SQL

Transformations

• Use dbt to model and clean the data once it lands in the warehouse

Practical setup

1) Postgres

• Use CDC/incremental sync if possible
• Capture tables into raw schemas in the warehouse

2) Salesforce

• Sync standard and custom objects
• Watch out for API limits and deleted/changed records
• Prefer incremental sync on SystemModstamp or equivalent

3) S3

• If files are CSV/JSON/Parquet, land them as raw external or staging tables
• Normalize schema differences early
• Partition by date if possible

Suggested pattern

1. Raw layer: one schema per source
2. Staging layer: cleaned/typed tables
3. Mart layer: business-ready tables for analytics
4. BI/Reporting: Looker, Metabase, Mode, Tableau, etc.

If you want the simplest low-maintenance option

• Fivetran + Snowflake + dbt
• Very common, reliable, and quick to stand up

If you want more budget-friendly / open source

• Airbyte + Postgres/BigQuery/Redshift + dbt
• More hands-on, but cheaper and flexible

Key things to plan for

• Incremental loads and deduplication
• Schema drift, especially from Salesforce and S3
• Data freshness requirements
• Identity matching across systems
• Monitoring and alerting for failed syncs

If you want, I can propose:

1. a cheap open-source stack,
2. a managed production stack, or
3. a step-by-step implementation plan for your exact volume and budget.