Integrate data from InterBase to Snowflake using Matillion

Creating a User in InterBase

This guide explains how to create a new user in an InterBase database, primarily through SQL statements or using the InterBase interactive command-line tools. Administrative privileges (as SYSDBA or a suitably privileged user) are required.

Using SQL: CREATE USER Statement

From InterBase 2020 onwards, you can create a new user by executing the SQL

CREATE USER

CREATE USER username
PASSWORD 'userpassword'
[ FIRSTNAME 'First' ]
[ MIDDLENAME 'Middle' ]
[ LASTNAME 'Last' ];

Example:

CREATE USER johndoe
PASSWORD 'SecretP@ssw0rd'
FIRSTNAME 'John'
MIDDLENAME 'J'
LASTNAME 'Doe';

Notes: - The fields

FIRSTNAME

MIDDLENAME

LASTNAME

Using gsec Command-Line Utility

Alternatively, you can use the

gsec

Steps:

1. Open your system’s command prompt or terminal.
2. Start

   gsec

   by connecting with a privileged account:

bash
   gsec -user sysdba -password masterkey

1. At the

   gsec>

   prompt, add a user:

add <username> -pw <password> [-fname First] [-mname Middle] [-lname Last]

Example:

add johndoe -pw SecretP@ssw0rd -fname John -lname Doe

1. To confirm the user has been added, list users:

display

1. Exit

   gsec

   :

quit

Assigning Privileges

After you have created a user, connect to your database as a privileged user and grant the necessary roles and access rights using the

GRANT

GRANT SELECT, INSERT ON tablename TO johndoe;

Replace

tablename

Installing the JDBC Driver

At the time of writing, the InterBase JDBC driver is not included by default in Matillion Data Productivity Cloud due to licensing or redistribution restrictions. To connect Matillion Data Productivity Cloud to an InterBase database, you must manually download and install the JDBC driver. Please follow the steps below to obtain and integrate the appropriate driver:

1. Download the InterBase JDBC Driver

1. Visit the official Embarcadero InterBase downloads page.
2. On the downloads page, locate and download the Type 4 JDBC driver. A Type 4 driver is preferable because it operates entirely in Java and is platform-independent, requiring no native library dependencies.
3. Save the downloaded

   JAR

   file to a location accessible for upload in later steps.

2. Install the InterBase JDBC Driver in Matillion Data Productivity Cloud

Since Matillion does not include the InterBase JDBC driver by default, it must be uploaded as an external driver via the Data Productivity Agent.

• Follow the official Matillion instructions here: Uploading external drivers.
  • These instructions cover how to upload a JDBC driver as a custom library for your agent environment.
  • Note any specific naming requirements or directory locations specified in the documentation to ensure Matillion can recognize and use the driver.

3. Configure and Use the Driver

Once the driver is successfully uploaded, you can proceed to configure InterBase connectivity in Matillion Data Productivity Cloud.

• Refer to the official guide: Database Query usage instructions.
  • This documentation will guide you through referencing the new driver when setting up your InterBase connection and performing database queries within Matillion.

Be sure to verify that your connection parameters match your InterBase database setup, and ensure the uploaded driver matches your database version for optimal compatibility.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your InterBase database, you must configure the database to allow incoming connections from the appropriate sources, depending on your deployment:

• Full SaaS Agent Configuration:
  You must allow incoming connections from the IP addresses used by Matillion’s Full SaaS agents. The complete list of required IP addresses can be found in the Matillion documentation. Ensure these IPs are permitted through any relevant firewalls or network security groups protecting your InterBase instance.

• Hybrid SaaS Deployment:
  In a Hybrid SaaS setup, Matillion agents run inside your own virtual private cloud (VPC). Therefore, you need to permit incoming connections from the subnets or IP addresses within your VPC that will be accessing the InterBase database. To verify connectivity from your VPC, you can use utilities provided at the Matillion Exchange.

Additionally, if the InterBase database is accessed using a DNS hostname rather than a static IP address, it is essential that the Full SaaS or Hybrid SaaS agent can resolve the DNS address to the correct IP. Ensure any DNS records or resolution mechanisms are properly configured and accessible from the environment running your Matillion agent.

Querying Data from an InterBase Database

Below are step-by-step instructions for querying data from an InterBase database, with clear examples and best practices for integrating with destinations such as Snowflake using Matillion.

Example InterBase Query Statements

InterBase SQL syntax is ANSI-compliant. To retrieve data, use the

SELECT

``` -- Select all columns from the Employees table SELECT * FROM Employees;

-- Select specific columns SELECT EmployeeID, LastName, FirstName FROM Employees;

-- Filter with WHERE clause SELECT * FROM Sales WHERE SaleDate >= '2023-01-01';

-- Order results SELECT * FROM Products ORDER BY ProductName ASC; ```

Considerations: InterBase to Snowflake Data Type Conversion

When migrating or replicating data from InterBase to Snowflake, be aware of potential differences in data types:

Some transformation or casting may be needed where types do not directly match.

Query Pattern: Initial Load vs. Incremental Load

The recommended data replication pattern is:

1. Initial Load: A one-off full extraction.
2. Incremental Load: Subsequent loads pulling only new or changed data.

Both runs should use the same Database Query component, adjusting the presence of filters.

Initial Load

• No filter in the SQL statement.
• Typically extracts the entire table.

Example:

SELECT * FROM Customers;

Incremental Load

• Add a filter to select only new or changed records since the last load.
• Frequently uses a high-watermark column, such as a timestamp or auto-incrementing ID.

Example:

SELECT * FROM Customers
WHERE LastModified > '2024-06-01 00:00:00';

Replace

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

with the actual high-watermark value captured during your last successful load.

For more details on this pattern, see Incremental Load Data Replication Strategy (Matillion Exchange).

Tip: Keep your database queries as efficient as possible, and use proper indexes on columns involved in filters for efficient incremental loading.

Data Integration Architecture

An important advantage of the Extract, Load, and Transform (ELT) architecture is that it allows organizations to tackle data integration in a modular fashion by first loading raw data into Snowflake before performing any transformations. This "divide and conquer" approach splits the process into two manageable steps: loading the source data, then applying the integration logic. Data integration inherently requires substantial data transformation, and the most effective strategy for this is to leverage transformation pipelines—automated workflows that systematically refine and combine data as needed. With ELT, these pipelines execute directly within Snowflake, meaning all transformation and integration activities occur inside the target database itself. This design brings significant benefits: processing is fast and can be scaled for any workload, transformations can be performed on demand, and there is no need for additional infrastructure to handle data processing, reducing both operational complexity and costs.