Integrate data from IBM Netezza to Snowflake using Matillion

Creating an Identity in IBM Netezza Database

The following instructions explain how to create a user (identity) in an IBM Netezza database environment. You can execute SQL commands using 

nzsql

1. Prerequisites

• You must have administrative privileges or be granted appropriate authority to create users. Generally, this requires access as an admin user (such as 

  nz

  ).

2. Syntax Overview

To create a user in IBM Netezza, use the 

CREATE USER

CREATE USER username WITH PASSWORD 'yourStrongPassword';

- 

username

yourStrongPassword

3. Example: Creating a User

The example below creates a user named 

data_analyst

S3cur3P@ssw0rd

CREATE USER data_analyst WITH PASSWORD 'S3cur3P@ssw0rd';

4. Additional Options

You can assign additional options, such as administrative permissions, or restrict the user:

CREATEDB

WITHOUT CREATEDB

SYSADMIN

NOSYSADMIN

VALID UNTIL

Example:

CREATE USER data_analyst
  WITH PASSWORD 'S3cur3P@ssw0rd'
  NOSYSADMIN
  DEFAULT_SCHEMA my_schema
  VALID UNTIL '2024-12-31';

5. Assigning the User to a Group

To manage permissions efficiently, add the new user to a group:

CREATE GROUP analysts;
ALTER GROUP analysts ADD USER data_analyst;

6. Using

nzsql

Command-Line Tool

You can also create a user interactively at the command-line:

bash
nzsql -u admin_user -d system

Then run your 

CREATE USER

nzsql

Note: Ensure passwords comply with your organization's password policy and Netezza's password requirements. Always use secure channels and best practices for user management.

Installing the JDBC driver

The IBM Netezza JDBC driver enables connectivity between Matillion Data Productivity Cloud and Netezza databases. Due to licensing or redistribution restrictions, the Netezza JDBC driver is not included by default in Matillion’s data integrations. You must download and install this driver manually before you can establish a connection. Follow the steps below to complete this process.

1. Download the Netezza JDBC Driver

1. Go to the official IBM download page for the Netezza JDBC driver:
   https://www.ibm.com/support/pages/ibm-netezza-jdbc-driver
2. On the download page, locate and obtain the Type 4 JDBC driver (this is the most suitable type for use with Matillion).
3. You will typically need to accept IBM’s license agreement before proceeding with the download.

2. Locate the Driver File

After downloading, you should have a

.jar

nzjdbc3.jar

3. Upload the JDBC Driver to Matillion

Since Matillion Data Productivity Cloud does not bundle the Netezza driver, you need to upload it manually. Detailed upload instructions are available here:
https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

In summary:

• Login to the Matillion Data Productivity Cloud user interface.
• Navigate to the Agent environment where you want the driver to be available.
• Use the options provided to upload the

  .jar

  file as a new external JDBC driver.

Please refer to the link above for the step-by-step process and requirements (for example, permissions, or supported agent versions).

4. Configure and Use the Driver

Once the driver is installed, Matillion will recognize it in the list of available database connectors. Follow these instructions to create your first database query or connection, incorporating the newly added driver:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

The documentation provides guidance on specifying connection parameters and integrating Netezza data sources using the Product Cloud Designer interface.

Following these steps, you will have access to the IBM Netezza database from your Matillion platform. Always ensure to consult official documentation for any recent changes or driver-specific instructions.

Checking network connectivity

To connect Matillion Data Productivity Cloud to your IBM Netezza database, you must ensure that the database accepts incoming network connections according to your deployment model:

• Full SaaS agent configuration:
  The firewall of your IBM Netezza database must allow inbound connections from the static IP addresses used by Matillion’s SaaS platform. You can find the list of required IP addresses at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS deployment:
  In this configuration, Matillion connects from within your own environment. Therefore, you must allow incoming connections from your own virtual private cloud (VPC) network where the Matillion agent is running. To help verify network access in this scenario, you can use the connectivity utilities available at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if your IBM Netezza database is referenced using a DNS name, the Full SaaS or Hybrid SaaS agent must be able to resolve the DNS address correctly. Make sure that any applicable DNS records are accessible from the relevant network.

Querying Data from an IBM Netezza Database

This guide provides instructions and examples on querying data from an IBM Netezza database, with considerations for ETL practices and data integration (e.g., migrating to Snowflake). The examples use standard SQL SELECT statements, demonstrate the handling of incremental versus initial loads, and touch on datatype conversions.

Example IBM Netezza Queries

Below are some typical sample queries you might run against a Netezza database.

``` -- Fetch all columns from a table SELECT * FROM sales.orders;

-- Select specific columns and apply a WHERE filter SELECT order_id, customer_id, order_date FROM sales.orders WHERE order_date >= '2024-01-01';

-- Aggregate query: total sales amount per customer SELECT customer_id, SUM(sale_amount) AS total_sales FROM sales.transactions GROUP BY customer_id;

-- Join between two tables SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.status = 'SHIPPED'; ```

Datatype Conversion: IBM Netezza vs. Snowflake

When moving data between IBM Netezza and Snowflake, some datatype conversion is often necessary. Common considerations include:

Note: Review your schema and test thoroughly to ensure datatype compatibility and preservation of precision.

Data Extraction Best Practice: Initial and Incremental Loads

The recommended load pattern is:

1. One-time Initial Load: Extracts the entire dataset without filters.
2. Incremental Loads: Extracts only new or changed records by applying a filter such as a modified timestamp or ID.

Use the same Database Query component/tool in both phases—simply adjust the SQL filter.

Initial Load: No Filter Clause

-- Load all rows (initial load)
SELECT * FROM sales.transactions;

Incremental Load: With Filter Clause

-- Load only rows where 'last_updated' is newer than the last load time (incremental load)
SELECT *
FROM sales.transactions
WHERE last_updated >= '2024-06-01 00:00:00';

Adjust the value

'2024-06-01 00:00:00'

For a detailed strategy including high-water mark management, see Matillion Incremental Load Strategy.

Tip: Always validate query results when switching between IBM Netezza and Snowflake to ensure accuracy after data type conversions and filter applications.

Data Integration Architecture

Loading data into Snowflake in advance of integration embodies the "divide and conquer" approach by breaking the overall process into two manageable steps. In the context of ELT (Extract, Load, Transform) architecture, this allows organizations to first load raw source data into the target environment, and then perform data integration using separate, optimized transformation steps. Effective data integration invariably requires transformation of the loaded data, and the most efficient way to achieve this within Snowflake is through data transformation pipelines, which can automate and orchestrate the various transformation tasks. A key advantage of the ELT approach is that all data transformations and integrations are performed inside the Snowflake database itself. This means the process is fast, scalable, and available on demand, without the need to invest in or maintain separate data processing infrastructure, ultimately streamlining operations and reducing costs.