Skip to main content

How to Build a Real-Time Pipeline from PostgreSQL to Snowflake

Selected Destination PostgreSQLSelected Destination Snowflake

Meroxa is a real-time data orchestration platform. We remove the time and overhead associated with configuring and managing brokers, connectors, transforms, functions, and streaming infrastructure. All you have to do is add your resources and construct your pipelines. Meroxa takes care of the rest.

This tutorial will guide you in building a real-time data pipeline using the Meroxa CLI. It is a powerful way to build data pipelines from your terminal. With the CLI, you can automate or even script your pipelines.

If desired, you can also build pipelines using the Meroxa Dashboard or Terraform.

Step One: Create an Account and Install CLI

To begin, you'll need to have an account, and the Meroxa CLI installed.

If you're using Homebrew:

brew tap meroxa/taps && brew install meroxa

For other environments, see CLI Installation Guide.

Once installed, you can create an account or login with:

meroxa login

You can also view all available commands with:

meroxa help

Step Two: Add Resources

Resources represent your data sources (e.g., databases, data warehouse, etc.). They represent the source or destination of the data you will use in your pipeline.

Depending on your environment, additional configuration may be needed to provide Meroxa with proper access to your resources. For example, you may need to whitelist Meroxa's IPs or use SSH Tunneling.

Add Source Resource: PostgreSQL

You may add a resource using the following command:

 meroxa resource add my-postgres --type postgres -u postgres://$PG_USER:$PG_PASS@$PG_URL:$PG_PORT/$PG_DB

You can view the PostgreSQL Connector Documentation to learn more about changing behavior or advanced configuration.

Add Destination Resource: Snowflake

Next, add your destination resource:

meroxa resource add snowflake --type snowflakedb --url snowflake://$SNOWFLAKE_URL/meroxa_db/stream_data --username meroxa_user --password $SNOWFLAKE_PRIVATE_KEY

You can view the Snowflake Connector Documentation to learn more about changing behavior or advanced configuration.

Step Three: Create Pipeline

Pipelines are a collection resources, connections, and streams.

To create a new pipeline use the meroxa pipeline create command.

meroxa pipeline create getting-started-pipeline

Step Four: Create Source Connector

Next, use the meroxa connector create command to create a new source connector:

meroxa connector create from-my-postgres --from postgres --input $INPUT --pipeline getting-started-pipeline

To create a source connector, provide the --from and an --input parameters.

  • --from - The name of the source resource.
  • --input - For PostgreSQL, the input is a Postgres Table. In general, input describes what table, index, collection, etc., in the source you would like to pull from.

Step Five: Create Destination Connector

To add a destination, you need a Stream. Streams are created when you create connectors. Source Connectors produce output streams, and Destination Connectors produce input streams.

To see the available Streams for your Connectors, you can use the list connectors command:

meroxa connectors list
Pipeline GIF

Now, use the meroxa connector create command to create a new destination connector:

meroxa connector create to-my-postgres  --to snowflakedb --input $STREAM_NAME --pipeline getting-started-pipeline

To create a destination connector, provide the --to and an --input parameters.

  • --to is the name of the destination resource.
  • --input describes the $STREAM_NAME of the source connector.

Step Six: Verify

After creating the destination, your pipeline will begin to run. Data from PostgreSQL will now flow to a table within Snowflake.

You can view the status of your pipelines with:

meroxa connectors list

You can view the logs for your connectors with:

meroxa connectors logs my-postgres

If everything went well, your pipeline is complete! You are now streaming real-time to your destination.

What's Next?

Here are some helpful resources:

Staying in touch: