How to Find Who Uses Snowflake in PostgreSQL

How do I see who is accessing Snowflake data in PostgreSQL?

Quickly list every user, role, and session touching your Snowflake-linked PostgreSQL instance.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “who uses Snowflake” mean in a mixed Postgres + Snowflake stack?

Teams often replicate Snowflake data into PostgreSQL for downstream services. "Who uses Snowflake" usually translates to which Postgres users, roles, or applications are querying Snowflake-sourced tables.

How do I list all database users in PostgreSQL?

Run \du in psql or query pg_catalog.pg_user. This shows every role—even service accounts—capable of accessing Snowflake-backed schemas.

How can I pinpoint sessions actively hitting Snowflake tables?

Join pg_stat_activity with catalog metadata. Filter by schema or table names synced from Snowflake. The example below targets the snowflake schema.

Which customers, orders, or products are most queried from Snowflake replicas?

Aggregate pg_stat_statements to learn which business entities the Postgres layer pulls most. This helps size Snowflake credits and cache hot data locally.

Best practices for monitoring Snowflake usage through Postgres

Enable pg_stat_statements, tag application names via the connection string, and create a scheduled report that emails a usage dashboard to the data team.

What are common mistakes when checking Snowflake usage?

Don’t forget to exclude idle sessions; they skew counts. Also ensure you’re inspecting the correct replica schema; many shops use multiple Snowflake imports.

Why How to Find Who Uses Snowflake in PostgreSQL is important

How to Find Who Uses Snowflake in PostgreSQL Example Usage


-- Who is currently querying the Snowflake-replicated "Orders" table?
SELECT pid,
       usename,
       query
FROM   pg_stat_activity
WHERE  query ILIKE '%FROM snowflake.orders%';

How to Find Who Uses Snowflake in PostgreSQL Syntax


-- List every Postgres role that could query Snowflake-sourced tables
\du

-- SQL alternative
SELECT usename AS role,
       usesuper AS is_superuser,
       usecreatedb AS can_create_db
FROM   pg_catalog.pg_user;

-- Active sessions touching the "snowflake" schema
SELECT pid,
       usename,
       query,
       state,
       query_start
FROM   pg_stat_activity
WHERE  query ILIKE '%snowflake.%';

-- Most-accessed Snowflake tables in an ecommerce context
SELECT relname   AS table,
       calls     AS total_hits,
       total_time/1000 AS minutes_spent
FROM   pg_stat_statements pss
JOIN   pg_class pc ON pss.relid = pc.oid
WHERE  relname IN ('customers','orders','products','orderitems')
ORDER  BY total_hits DESC;

Common Mistakes

Relying only on connection counts. A single long-running ETL connection can mask dozens of BI users routed through it. Always inspect individual SQL statements.
Filtering on the wrong schema name. If your replica lives in "snflk" instead of "snowflake", you’ll get zero rows. Confirm schema names with \dn first.

Frequently Asked Questions (FAQs)

Does this require the pg_stat_statements extension?

Yes, you need pg_stat_statements to aggregate query text, counts, and timing. Enable it in shared_preload_libraries and restart the server.

Will these queries impact performance?

Listing roles is instant. Querying pg_stat_activity is lightweight. Aggregating pg_stat_statements can be slower on very busy systems; run it off-peak or with LIMIT.