How to Connect to Snowflake on AWS in PostgreSQL

Galaxy Glossary

How do I connect, load data, and query Snowflake on AWS quickly?

Connect, query, and manage Snowflake running on AWS from PostgreSQL-style SQL tools.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is Snowflake on AWS?

Snowflake on AWS is the cloud-hosted data warehouse deployed in Amazon Web Services regions. It stores data in S3 and provides scalable compute clusters called warehouses.

How do I connect to Snowflake on AWS?

Use the SnowSQL CLI, JDBC/ODBC drivers, or a PostgreSQL-compatible editor like Galaxy. Supply account, region, user, role, warehouse, database, and schema.

snowsql -a myco.us-east-1 -u alice -r ANALYST_ROLE \ -w BI_WH -d ecommerce -s public

What is the basic query workflow?

1) USE the correct context. 2) CREATE or SELECT objects. 3) COPY data from S3. 4) ANALYZE with familiar SQL. Snowflake auto-scales compute and separates storage.

How do I create an e-commerce schema?

CREATE SCHEMA IF NOT EXISTS ecommerce; USE SCHEMA ecommerce; CREATE TABLE Customers ( id INT, name STRING, email STRING, created_at TIMESTAMP ); -- Repeat for Orders, Products, OrderItems

How can I load CSV orders from S3?

CREATE OR REPLACE STAGE s3_stage URL='s3://my-bucket/orders/' CREDENTIALS=(AWS_KEY_ID='$AWS_KEY' AWS_SECRET_KEY='$AWS_SECRET'); COPY INTO Orders FROM @s3_stage/orders_2023.csv FILE_FORMAT=(TYPE=CSV FIELD_DELIMITER=',' SKIP_HEADER=1);

How do I join product and order data?

SELECT o.id AS order_id, c.name AS customer, SUM(oi.quantity * p.price) AS total FROM Orders o JOIN Customers c ON c.id = o.customer_id JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id GROUP BY o.id, c.name ORDER BY total DESC;

Best practices for Snowflake on AWS?

Keep warehouses small and auto-suspend to cut costs. Use clustering keys on large fact tables. Leverage Snowflake Time-Travel and Fail-Safe for backups.

What are common mistakes?

1) Forgetting to set USE context: leads to "Object does not exist" errors. Always run USE ROLE, USE WAREHOUSE, USE DATABASE, and USE SCHEMA up front.
2) Oversizing warehouses: burning credits while idle. Enable AUTO_SUSPEND and choose the smallest size that meets SLAs.

Can I query S3 data without loading?

Yes. Use CREATE EXTERNAL TABLE on an external stage pointing to S3, then query it like any table.

Is Snowflake ACID compliant?

Snowflake ensures ACID transactions even on AWS object storage, making multi-statement workloads reliable.

How does pricing work?

Storage is charged per terabyte per month. Compute is charged per second of warehouse usage, rounded to the nearest minute.

Why How to Connect to Snowflake on AWS in PostgreSQL is important

How to Connect to Snowflake on AWS in PostgreSQL Example Usage


-- Calculate customer lifetime value (CLV)
WITH order_totals AS (
  SELECT customer_id,
         SUM(total_amount) AS lifetime_spend
  FROM   Orders
  GROUP  BY customer_id
)
SELECT c.id,
       c.name,
       c.email,
       o.lifetime_spend
FROM   Customers c
JOIN   order_totals o ON o.customer_id = c.id
ORDER  BY o.lifetime_spend DESC
LIMIT 100;

How to Connect to Snowflake on AWS in PostgreSQL Syntax


snowsql -a <account_name>.<aws_region> \
        -u <user_name> \
        -p <password | -f key_file> \
        -r <role_name> \
        -w <warehouse_name> \
        -d <database_name> \
        -s <schema_name>

-- In-database context
USE ROLE <role_name>;
USE WAREHOUSE <warehouse_name> [WITH {AUTO_SUSPEND = <sec> | AUTO_RESUME = TRUE}];
USE DATABASE <database_name>;
USE SCHEMA <schema_name>;

-- Data loading from S3
CREATE OR REPLACE STAGE <stage_name>
  URL='s3://<bucket>/<path>/'
  [CREDENTIALS=(AWS_KEY_ID='<key>' AWS_SECRET_KEY='<secret>')];
COPY INTO <table_name>
FROM @<stage_name>/<file>
FILE_FORMAT=(TYPE=CSV FIELD_DELIMITER=',' SKIP_HEADER=1)
[ON_ERROR = 'CONTINUE'];

Common Mistakes

Using a large, always-on warehouse. This drains credits. Fix by setting AUTO_SUSPEND=60 and AUTO_RESUME=TRUE.
Omitting USE statements before queries, causing object not found errors. Always set role, warehouse, database, and schema first.