Viewing PostgreSQL data in Snowflake¶

The connector replicates data to the destination database, which was defined while setting up the connector and calling PUBLIC.ADD_DATA_SOURCE('data_source_name', 'dest_db').

Data tables contain the replicated data and are available under identifier dest_db.schema_name.table_name where:

• dest_db is the name of the destination database.
• schema_name is the schema name in which the original PostgreSQL table resides.
• table_name is the name of the original PostgreSQL table.

The replicated tables contain the additional metadata columns:

The following table shows the PostgreSQL to Snowflake types mapping.

All other types, including arrays, ENUMs, custom types and ranges are mapped to VARCHAR values in Snowflake. The following table illustrates how types not explicitly mentioned in the table above are handled.

If the connection between the database agent and the connector is lost during snapshot load, because of time and cost optimisation, the connector will continue to load the snapshot from the point where it was stopped before. This happens regardless of whether the agent was restarted or if there was an issue with the connections between the source database and the database agent, and the database agent and the connector.

This feature works for primary key columns of the following types:

• SMALLINT/INT2
• INTEGER/INT/INT4
• BIGINT/INT8
• UUID
• NUMERIC
• TEXT
• VARCHAR
• BOOL

If the primary key is of any other type, the snapshot load after the connection failure for a particular column will start from the beginning.

If a column is deleted in the source table, it will not be deleted in the destination table. Instead, a soft-delete approach is followed, and the column will be renamed to <previous name>__SNOWFLAKE_DELETED so that historical values can still be queried.

For example, if a column A is deleted, it will be renamed to A__SNOWFLAKE_DELETED in the destination table and can be queried as

SELECT A__SNOWFLAKE_DELETED FROM <TABLE_NAME>;

Renaming a column is equal to deleting the column and creating a new one with the new name. The deletion follows the soft-delete approach explained in the previous section.

For example, if column A was renamed to B - in the destination table A was renamed to A__SNOWFLAKE_DELETED and a new column B is added. All rows existing before the change keep the values of the column in the A__SNOWFLAKE_DELETED column while new rows added after the change have the values in the B column. Values from the renamed column can be viewed as a single column with a simple query:

SELECT
     CASE WHEN B IS NULL THEN A__SNOWFLAKE_DELETED ELSE B END AS A_RENAMED_TO_B
FROM <TABLE_WITH_RENAMED_COLUMN>;

A view can be created to simplify the usage after a column is renamed.