Viewing PostgreSQL data in Snowflake¶

Note

The Snowflake Connector for PostgreSQL is subject to the Connector Terms.

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.

Note

dest_db, schema_name and table_name needs to be double quoted in case their names are mixed-case.

The replicated tables contain the additional metadata columns:

Column name	Data type	Description
`_SNOWFLAKE_INSERTED_AT`	TIMESTAMP_NTZ	Timestamp of when the row was inserted into the destination table, in UTC.
`_SNOWFLAKE_UPDATED_AT`	TIMESTAMP_NTZ	Timestamp of when the row was last updated in the destination table, in UTC.
`_SNOWFLAKE_DELETED`	BOOLEAN	Value is `true` if the row has been deleted from the source table.

The replicated data types are mapped to match the Snowflake types. For more information, see PostgreSQL to Snowflake data type mapping.

Replicated data access control¶

To control access to replicated data use DATA_READER application role. More on connector application roles: Application roles in the Snowflake Connector for PostgreSQL For more granular control over specific destination objects, use ACCOUNTADMIN role to grant proper privileges or create database roles.

PostgreSQL to Snowflake data type mapping¶

In Snowflake, column names of replicated tables are capitalized and types are mapped to match the Snowflake types.

The following table shows the PostgreSQL to Snowflake types mapping.

PostgreSQL Type	Snowflake Type	Notes
BIGINT / INT8	INT
BIGSERIAL / SERIAL8	INT
BIT [(N)]	VARCHAR
BIT VARYING [(N)] / VARBIT [(n)]	VARCHAR
BOOLEAN / BOOL	BOOLEAN
BOX	VARCHAR
BYTEA	BINARY(N)	Supported up to the max datapoint size in Snowflake (16MB). Max length 1 GB.
CHARACTER [(N)] / CHAR [(N)]	VARCHAR [N]	Max length 10485760 ~= 10 MB
CHARACTER VARYING [(N)] / VARCHAR [(N)]	VARCHAR [N]	Max length 10485760 ~= 10 MB
CIDR	VARCHAR
CIRCLE	VARCHAR
DATE	DATE
DOUBLE PRECISION / FLOAT8	FLOAT
INET	VARCHAR
INTEGER / INT / INT4	INT
INTERVAL [FIELDS][(P)]	VARCHAR
JSON	VARIANT	Supported up to the max datapoint size in Snowflake (16MB).
JSONB	VARIANT	Supported up to the max datapoint size in Snowflake (16MB).
LINE	VARCHAR
LSEG	VARCHAR
MACADDR	VARCHAR
MACADDR8	VARCHAR
MONEY	VARIANT
NUMERIC [(P, S)] / DECIMAL [(P, S)]	DECIMAL(P, S)	Scale and precision are also recreated on the Snowflake side preserving Snowflake limitations.
PATH	VARCHAR
PG_LNS	VARCHAR
POINT	VARCHAR
POLYGON	VARCHAR
REAL / FLOAT4	FLOAT
SMALLINT / INT2	INT
SMALLSERIAL / SERIAL2	INT
SERIAL / SERIAL4	INT
TEXT	VARCHAR
TIME [(P)] [ without time zone ]	TIME
TIME [(P)] with time zone	TIME
TIMESTAMP [(P)] [ without time zone ]	DATETIME / TIMESTAMP_NTZ
TIMESTAMP [(P)] with time zone	TIMESTAMP_TZ
TSQUERY	VARCHAR
TSVECTOR	VARCHAR
UUID	VARCHAR
XML	VARCHAR

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.

PostgreSQL Type	Data in PostgreSQL	Column in Snowflake
ENUM	monday	“monday”
array of INTEGER	{1,2,3,5}	“{1,2,3,5}”
intrange	[6,31)	“[6,31)”
custom type (2 fields, INT4 and TEXT)	(text value,5432)	“(text value,5432)”

Viewing data from deleted columns¶

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>;

Copy

Viewing data from renamed columns¶

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>;

Copy

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

Next steps¶

After completing these procedures, review the processes in Snowflake Connector for PostgreSQL ongoing tasks