Integrate data from Greenplum Database to Snowflake using Matillion

Creating a User in Greenplum Database

To create a user (also known as a role) in Greenplum Database, ensure you have the necessary superuser or CREATEROLE privilege. This guide provides the steps and SQL scripts required.

1. Connect to the Database

Connect to your Greenplum database using the

psql

psql -h <host> -p <port> -U <admin_user> <database_name>

Replace

<host>

<port>

<admin_user>

<database_name>

2. Create a New User

Use the

CREATE ROLE

LOGIN

Example:

CREATE ROLE new_user WITH LOGIN PASSWORD 'secure_password';

• Replace

  new_user

  with the desired username.
• Replace

  'secure_password'

  with a strong password.

3. Grant Privileges (Optional)

To allow the new user to connect to specific databases or perform operations, grant the appropriate privileges.

Example:

``` -- Grant CONNECT privilege on a database: GRANT CONNECT ON DATABASE example_db TO new_user;

-- Grant usage on schema: GRANT USAGE ON SCHEMA public TO new_user;

-- Grant permissions on tables: GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO new_user; ```

4. Verify the New User

Query the catalog view to confirm the user’s creation:

\du                   -- in psql, lists all roles

SELECT rolname FROM pg_roles WHERE rolname = 'new_user';

Notes

• The user will not have superuser privileges by default. Only grant elevated permissions if absolutely necessary.
• You may customize the user further using options such as

  CREATEDB

  ,

  CREATEROLE

  , or resource group assignments:

CREATE ROLE new_user WITH LOGIN PASSWORD 'secure_password' CREATEDB;

Refer to the Greenplum documentation for advanced user management features.

Installing the JDBC driver

The Greenplum database leverages a JDBC driver to facilitate connectivity from third-party applications such as Matillion Data Productivity Cloud. At the time of writing, this driver is not packaged with Matillion Data Productivity Cloud by default, primarily due to licensing or redistribution restrictions. As such, users must manually obtain and install the driver.

Download the JDBC Driver

1. Navigate to the official PostgreSQL JDBC Driver website: https://jdbc.postgresql.org/
2. Locate the latest release of the Type 4 JDBC driver (also known as the “pgJDBC” driver), which is preferred for its platform independence and direct communication with the Greenplum database.
3. Download the appropriate JAR file to your local workstation.

Upload the JDBC Driver to Matillion Data Productivity Cloud

To make the JDBC driver available for use within your Matillion environment, follow these steps:

1. Log into your Matillion Data Productivity Cloud account and access the environment where you wish to install the driver.
2. Consult the official instructions for uploading and installing external drivers:
3. Uploading external drivers into Matillion Data Productivity Cloud
4. Using the documented procedure, upload the downloaded JDBC JAR file to your Matillion agent or workspace, ensuring the file is correctly placed according to Matillion’s requirements.

Configuring and Using the Greenplum Connection

For guidance on configuring and utilizing a database connection within Matillion Data Productivity Cloud, refer to the official usage documentation: - Database Query component documentation

This documentation provides detailed instructions on specifying the correct driver and parameters to establish a connection to your Greenplum instance.

Checking network connectivity

To ensure successful communication between Matillion Data Productivity Cloud and your Greenplum database, you must configure your network to allow incoming connections to Greenplum, based on your Matillion deployment type:

• Full SaaS agent configuration:
  Allow incoming connections from the IP addresses published by Matillion. You can find the current list at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS deployment:
  Configure your Greenplum database to accept incoming connections from your own virtual private cloud (VPC) where your Hybrid SaaS agent runs. If you need to check or troubleshoot network accessibility, utilities are available at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if the Greenplum database is referenced using a DNS hostname, ensure that the relevant Matillion Data Productivity Cloud agent (whether Full SaaS or Hybrid SaaS) has DNS resolution enabled and is able to resolve the provided address.

Querying Data from a Greenplum Database

The process for querying and extracting data from a Greenplum database typically involves using SQL

SELECT

Example Greenplum Queries

A standard query targeting a Greenplum table, for example:

SELECT
  id,
  user_name,
  email_address,
  created_at
FROM
  public.users;

Filtering data, for example targeting users created after a specific date:

SELECT
  id,
  user_name,
  email_address,
  created_at
FROM
  public.users
WHERE
  created_at >= '2024-01-01';

Aggregating data:

SELECT
  status,
  COUNT(*) AS order_count
FROM
  public.orders
GROUP BY
  status;

Joining tables:

SELECT
  u.id,
  u.user_name,
  o.order_id,
  o.amount
FROM
  public.users u
INNER JOIN
  public.orders o
ON
  u.id = o.user_id;

Note on Datatype Conversion

If you are transferring data between Greenplum and Snowflake, be aware that datatype conversion may occur. For instance, Greenplum’s

timestamp without time zone

TIMESTAMP_NTZ

Recommended Load Pattern: Initial Load & Incremental Load

The best data extraction practice is to perform a once-off initial load followed by incremental loads for ongoing sync. Both processes use the same Database Query component, configured differently as follows:

Initial Load

• The Database Query component is configured using a basic

  SELECT

  statement without a filter clause.
• All available records are extracted.

Example (no filter):

SELECT
  id,
  user_name,
  email_address,
  created_at
FROM
  public.users;

Incremental Loads

• For subsequent loads, the same database query is updated to include a filter (usually on a monotonically increasing column like

  created_at

  or

  last_updated

  ).
• This filter allows the extraction of only new or changed records since the last load.

Example (with filter):

SELECT
  id,
  user_name,
  email_address,
  created_at
FROM
  public.users
WHERE
  created_at > '${last_loaded_at}';

${last_loaded_at}

For detailed guidance, see the article: Incremental Load Data Replication Strategy.

Summary Table:

SELECT ... FROM table;

SELECT ... FROM table WHERE updated > ?;

Following this pattern ensures efficient and reliable data extraction from Greenplum for downstream processing or loading into Snowflake.

Data Integration Architecture

A key advantage of the ELT (Extract, Load, Transform) architecture is its “divide and conquer” approach to data integration. By first loading data into Snowflake and then performing integration and transformation as a separate, subsequent step, organizations simplify the problem and gain greater flexibility. Data integration itself inherently depends on data transformation—whether for data cleansing, harmonization, or enrichment—and the most effective method for achieving this is through well-designed data transformation pipelines. With ELT, all transformation and integration tasks take place entirely within the target Snowflake database. This strategy delivers exceptional benefits: processing is fast, scalable on demand, and leverages Snowflake’s robust compute resources, so there is no need to invest in or maintain separate data processing infrastructure. This not only reduces operational overhead and cost, but also allows teams to focus on optimizing transformation logic directly in the analytics environment.