Monitoring the Snowflake Connector for PostgreSQL¶

Note

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

The following sections describe how to monitor the connector by querying views and examining log files:

Viewing general information about the connector
Viewing data sources
Viewing the replication state of data sources
Viewing the replication state of source tables
Viewing table schema version history
Viewing connector metrics
Viewing aggregated connector metrics
Viewing experimental views
Viewing the connector audit log view
Viewing the agent audit log view
Viewing the connector logs
Viewing the agent logs

Viewing general information about the connector¶

To view general information about the connector, run DESCRIBE APPLICATION command:

DESCRIBE APPLICATION <app_db_name>;
Copy
Where:

app_db_name
Specifies the name of the connector database.

To view more specific information about the connector, query the PUBLIC.CONNECTOR_CONFIGURATION view:

SELECT * FROM PUBLIC.CONNECTOR_CONFIGURATION;

Copy

The PUBLIC.CONNECTOR_CONFIGURATION view displays a row for each parameter configured for the connector.

The following table describes these parameters:

Parameter	Description
alertingLogsView	If you enabled email notifications, this specifies the name of the view that provides access to the event table.
alertingNotificationIntegration	If you enabled email notifications, this specifies the name of the notification integration object used for email notifications.
alertingRecipients	If you enabled email notifications, this specifies the list of email addresses (separated by commas) that can receive email notifications from the connector.
alertingSchedule	If you enabled email notifications, this specifies the schedule or frequency at which the connector should check for errors and send a notification.
operational_warehouse	Name of the operational warehouse used by the connector.
warehouse	Name of the compute warehouse for merging data.

Viewing data sources¶

To view information about data sources, query the PUBLIC.DATA_SOURCES view:

SELECT * FROM PUBLIC.DATA_SOURCES;

Copy

The PUBLIC.DATA_SOURCES view displays a row for each data source configured for the connector. The view consists of the following columns:

Column Name	Data Type	Description
NAME	VARCHAR	Name of the data source.
SCHEDULE	VARCHAR	Schedule for running the replication. Displays NULL if scheduled replication of that data source is disabled.
DESTINATION_DB_NAME	VARCHAR	Name of the destination database.

Viewing the replication state of data sources¶

To view the current replication state of data sources, query the PUBLIC.DATA_SOURCE_REPLICATION_STATE view:

SELECT * FROM PUBLIC.DATA_SOURCE_REPLICATION_STATE;

Copy

The PUBLIC.DATA_SOURCE_REPLICATION_STATE view displays a row for each data source configured in the connector. The view consists of the following columns:

Column Name	Data Type	Description
NAME	VARCHAR	Name of the data source.
TABLES_ADDED_COUNT	NUMBER	Numbers of tables actively replicated in this data source. This number does not include tables for which the replication failed permanently.
CONNECTED_AGENT_ID	VARCHAR	ID of the agent application assigned to the data source.
SCHEDULE	VARCHAR	Schedule for running the replication. Displays NULL if scheduled replication of that data source is disabled.
REPLICATION_STATUS	VARCHAR	Replication status of the data source. Possible values: `WAITING` `ONGOING`
PREVIOUS_SCHEDULED_RUN_STATUS	VARCHAR	Status of previous scheduled replication. Displays NULL if scheduled replication of that data source is disabled. Possible values: `DONE` `WARNING`
PREVIOUS_RUN_FINISHED_AT	TIMESTAMP_NTZ	Timestamp of the end of last scheduled replication. Displays NULL if scheduled replication of that data source is disabled.

Viewing the replication state of source tables¶

To view the current replication state of each source table, query the PUBLIC.REPLICATION_STATE view:

SELECT * FROM PUBLIC.REPLICATION_STATE;

Copy

The PUBLIC.REPLICATION_STATE view displays a row for each source table. The view consists of the following columns:

Column Name	Data Type	Description
DATA_SOURCE_NAME	VARCHAR	Name of the data source that contains the source table
SCHEMA_NAME	VARCHAR	Name of the schema of the source table
TABLE_NAME	VARCHAR	Name of the source table
REPLICATION_PHASE	VARCHAR	Current replication phase. Possible values: `SCHEMA_INTROSPECTION` `INITIAL_LOAD` `INCREMENTAL_LOAD` For descriptions of each status, see Understanding replication phases.
SCHEMA_INTROSPECTION_STATUS	VARCHAR	Current schema introspection status. Possible values: `WAITING` `IN_PROGRESS` `DONE` `RETRYING` `FAILED`
SNAPSHOT_REPLICATION_STATUS	VARCHAR	Current snapshot replication status. Possible values: `WAITING` `IN_PROGRESS` `DONE` `RETRYING` `FAILED`
INCREMENTAL_REPLICATION_STATUS	VARCHAR	Current incremental replication status. Possible values: `WAITING` `IN_PROGRESS` `DONE` `RETRYING` `FAILED`

Understanding replication phases¶

Replication of each of the source tables can be in the following replication phases:

Replication Phase	Description
`SCHEMA_INTROSPECTION`	Schema of the source table is being checked. Once this phase is done the destination table is created.
`INITIAL_LOAD`	The connector is processing the snapshot load for the source table.
`INCREMENTAL_LOAD`	Initial load is done, data is being replicated using change data capture process.

Note

You can start FAILED replications from the beginning by removing table from replication and adding it again as described in Configuring replication for the Snowflake Connector for PostgreSQL.

Viewing table schema version history¶

To view the history of table schema changes, query the PUBLIC.SCHEMA_CHANGE_HISTORY view using a command similar to:

SELECT * FROM PUBLIC.SCHEMA_CHANGE_HISTORY;

Copy

The PUBLIC.SCHEMA_CHANGE_HISTORY view displays one or two rows for each table’s valid schema version.

The view consists of the following columns:

Column Name	Data Type	Description
DATA_SOURCE_NAME	VARCHAR	Source table data source name.
SCHEMA_NAME	VARCHAR	Source table schema name.
TABLE_NAME	VARCHAR	Source table name.
VERSION	INTEGER	Schema version identifier, initially 0, and incremented by 1 with each schema change. Numbering restarts at zero if the table is removed and later re-added.
STATE	VARCHAR	one of: ACCEPTED: schema change is valid, but has yet to be applied to the destination table. APPLIED: schema change has already been applied to the destination table. Initially, at the start of the replication, contains only a single row with the value APPLIED. After subsequent valid schema changes will include two rows - one with state=ACCEPTED and one with state=APPLIED.
SOURCE_SCHEMA	VARIANT	JSON describing the schema of the source table.
DESTINATION_TABLE_SCHEMA	VARIANT	JSON describing the schema of the destination table after this schema version is applied.
INSERTED_AT	TIMESTAMP_NTZ	UTC timestamp when this record was inserted.

Viewing connector metrics¶

To view the connector replication metrics, query the PUBLIC.CONNECTOR_STATS view:

SELECT * FROM PUBLIC.CONNECTOR_STATS;

Copy

The PUBLIC.CONNECTOR_STATS view displays a row for each periodic merge of data into destination table during incremental load replication phase.

Note

The first run for a given table in this view will be longer and larger than a typical later run. This is due to the fact that the connector gathers incremental updates to tables during the initial load phase, but processes them only after the whole table has been replicated.

The view consists of the following columns:

Column Name	Data Type	Description
RESOURCE_INGESTION_DEFINITION_ID	VARCHAR	Identifier of a replicated table constructed from data source name, schema name and table name.
INGESTION_CONFIGURATION_ID	VARCHAR	Internal column for future integrations.
INGESTION_PROCESS_ID	VARCHAR	ID of the merge process.
INGESTION_DEFINITION_NAME	VARCHAR	Internal column for future integrations.
DATA_SOURCE_NAME	VARCHAR	Name of the data source to which the table belongs.
SCHEMA_NAME	VARCHAR	Name of the table’s schema.
RESOURCE_NAME	VARCHAR	Table name.
STARTED_AT	TIMESTAMP_NTZ	Time when the first record of the batch of records merged to the destination table was read from source database.
STATUS	VARCHAR	Merge process status. Possible values: `FINISHED` `FAILED`
INGESTED_ROWS	NUMBER	Number of rows merged in the batch
INGESTION_DURATION_S	NUMBER	Batch processing time in seconds calculated as difference between first record being observed and the batch of records being merged into the destination table.
NATIVE_APP_PROCESSING_DURATION_S	NUMBER	Duration in seconds of data processing on Snowflake side.
AGENT_PROCESSING_DURATION_S	NUMBER	Duration in seconds of data processing on agent side.
THROUGHPUT_RPS	NUMBER	Connector throughput in records per second (RPS). Takes into account the overall processing time.
NATIVE_APP_THROUGHPUT_RPS	NUMBER	Throughput of the data processing on Snowflake side in records per second (RPS).

Viewing aggregated connector metrics¶

To view the connector replication metrics, query the PUBLIC.AGGREGATED_CONNECTOR_STATS view:

SELECT * FROM PUBLIC.AGGREGATED_CONNECTOR_STATS;

Copy

The PUBLIC.AGGREGATED_CONNECTOR_STATS view shows the metrics of the connector aggregated hourly. Additional columns with data source name, schema name and table name are provided for further aggregations and analysis.

The view consists of the following columns:

Column Name	Data Type	Description
DATE	DATE	Date of the aggregate, hourly.
PROCESSED_ROWS_COUNT	NUMBER	Sum of rows ingested for the table during the aggregate time.
THROUGHPUT_RPS	NUMBER	Throughput for the table for the aggregate time in records per second (RPS).
DATA_SOURCE_NAME	VARCHAR	Name of the data source to which the table belongs.
SCHEMA_NAME	VARCHAR	Name of the table’s schema.
SOURCE_TABLE_NAME	VARCHAR	Table name.

Viewing experimental views¶

The connector comes with a several additional views containing low-level information about the state of the connector and support state change history tracking. These views are found in the PUBLIC schema with names that begin with the prefix EXPERIMENTAL.

The following table summarizes the currently available experimental views:

View Name	Description
EXPERIMENTAL_TABLE_REPLICATION_HISTORY	A history of state changes for all enabled source tables in the connector.
EXPERIMENTAL_DATA_SOURCE_REPLICATION_HISTORY	A history of state changes for all configured data sources in the connector.
EXPERIMENTAL_EVENTS_HISTORY	A history of all events that occurred in the connector.

Note

Experimental views are subject to change and can be modified or removed in future connector releases.

Viewing the connector audit log view¶

To view the audit log of user actions in the connector, query the PUBLIC.AUDIT_LOG view:

SELECT * FROM PUBLIC.AUDIT_LOG;

Copy

The PUBLIC.AUDIT_LOG view displays a row for each user-initiated action recorded by the connector.

The view consists of the following columns:

Column Name	Data Type	Description
ACTION_TIME	TIMESTAMP_NTZ	Time when the action happened.
ACTION_TYPE	VARCHAR	Action type.
PARAMETERS	VARIANT	Additional parameters of the action.

Actions recorded in this view are:

Data source added

Table replication enabled

Table replication disabled

Scheduled replication enabled for data source

Scheduled replication disabled for data source

Viewing the agent audit log view¶

To view the audit log of agent actions in the connector, query the PUBLIC.AGENT_AUDIT_LOG view:

SELECT * FROM PUBLIC.AGENT_AUDIT_LOG;

Copy

The PUBLIC.AGENT_AUDIT_LOG view displays a row for each agent-reported action registered by the connector.

The view consists of the following columns:

Column Name	Data Type	Description
ACTION_TIME	TIMESTAMP_NTZ	Time when the action happened.
ACTION_TYPE	VARCHAR	Action type.
PARAMETERS	VARIANT	Additional parameters of the action.

Actions shown in this view are:

Agent assigned to data source

Agent unassigned from data source

Agent registered

Agent unregistered

Snapshot load started

Snapshot load finished

Snapshot load failed

Snapshot load terminated

Schema introspection succeeded

Schema introspection failed

Incremental load started

Incremental load stopped

Incremental load failed

Incremental load terminated

Schema change reported

Viewing the connector logs¶

To view the connector logs, query the event table that you created while setting up the connector log view.

SELECT * FROM <fully_qualified_event_table_name>
   WHERE RECORD_TYPE = 'LOG'
   AND RESOURCE_ATTRIBUTES:"snow.database.name" = '<app_db_name>';

Copy

Where:

fully_qualified_event_table_name: Specifies the fully qualified name of the event table.
app_db_name: Specifies the name of the connector database.

Viewing the agent logs¶

When the agent is running, it periodically sends logs to Snowflake. These logs are available in the AGENT_LOGS view and can be retrieved using the following query:
SELECT * FROM PUBLIC.AGENT_LOGS;
Copy

Next steps¶

If required, and after completing these procedures, review the steps in Troubleshooting the Snowflake Connector for PostgreSQL.