Video summary

Build an ELT Pipeline: Postgres → Snowflake (Meltano Project Demo) .Part2

Main summary

Key takeaways

Technology

Overview

This video (Part 2) demonstrates building an ELT pipeline with Meltano to move data from Postgres (source) to Snowflake (target). It focuses on:

  • Project setup
  • Connector/plugin installation
  • Credential configuration
  • Incremental table replication
  • Running the pipeline

1) Create and inspect a Meltano project

  • Uses the Meltano CLI to initialize a project (example name like Postgres Snowflake).
  • The generated project structure includes folders such as:
    • analyze
    • extract
    • load
  • The primary configuration file is meltano.yml.

2) Install required Meltano plugins (connectors)

The video installs:

  • Postgres extractor (e.g., tap-postgres) to extract from Postgres
  • Snowflake loader (e.g., target-snowflake) to load into Snowflake

After installation, the plugins are automatically added to the project meltano.yml.

3) Prepare source data in Postgres

Assumes Part 1 already created:

  • Postgres + Meltano DB
  • A Snowflake trial account

In a SQL tool (e.g., DBeaver), the video creates and inserts sample tables:

  • customer
  • order
  • sales

These three tables are intended for replication to Snowflake.

4) Configure Snowflake credentials for the target

Required Snowflake credentials include:

  • account
  • user
  • role
  • warehouse
  • schema (uses public, generated automatically)
  • password (the same password used to connect to Snowflake)

Configuration is done using an interactive Meltano command (meltano config). A note is included about a command issue: the video moves set into config set so the interactive flow works.

Sensitive values are stored in .env (not in meltano.yml), so they appear redacted.

5) Configure Postgres credentials for the extractor

Also set via interactive Meltano config using the Postgres extractor requirements:

  • host (e.g., localhost)
  • port
  • user
  • password
  • database (referenced as the Meltano DB)
  • replication configuration is mentioned, with full details deferred to a later point

6) Configure incremental replication for specific tables

In meltano.yml, the pipeline uses an incremental strategy/replication instead of a full load.

Key points:

  • A select/table list restricts replication to only:
    • customer
    • order
    • sales
  • Incremental “tracking” is configured via replication keys (tracking columns):
    • customer: created_at (or a similar created-date column)
    • order: order_date (or a similar date column)
    • sales: another date column (video implies a created-date variant)
  • The goal: the pipeline will detect changes “from yesterday” and load only new/updated rows.

7) Run the ELT pipeline

Runs the pipeline with a command like:

meltano run tap-postgres target-snowflake

The logs indicate:

  • The extractor runs in incremental mode
  • It detects and processes the three tables
  • The run completes successfully

8) Verify results in Snowflake

After completion, Snowflake shows:

  • The public schema
  • Tables: customer, order, sales
  • Preview confirms records were loaded

The expected future behavior is also described:

When new data is added in Postgres tomorrow, Meltano should pull only rows newer than the tracked incremental column values, then append/update in Snowflake.

Main speakers / sources

  • Speaker: unnamed presenter (narrates commands and edits meltano.yml)
  • Sources referenced:
    • Meltano documentation (for required configuration fields)
    • Meltano Hub (for plugins/connectors like tap-postgres and target-snowflake)

Original video