Cloud Data Warehouses

-- Create a Snowpipe for auto-loading S3 data
CREATE PIPE sales_pipe AUTO_INGEST = TRUE AS
COPY INTO sales_table
FROM @s3_sales_stage
FILE_FORMAT = (TYPE = 'JSON');

-- Snowpipe continuously monitors S3 for new files
-- and loads them within seconds, no manual intervention

-- Clone production database instantly for testing
CREATE DATABASE dev_db CLONE production_db;

-- Clone a table at a specific point in time
CREATE TABLE orders_clone CLONE orders AT(TIMESTAMP => '2026-01-01 00:00:00');

-- No data duplication, shares underlying storage until modified

-- Query table as it existed 1 hour ago (3600 seconds)
SELECT * FROM orders AT(OFFSET => -3600);

-- Restore accidentally deleted data
CREATE TABLE orders_restored CLONE orders AT(OFFSET => -7200);

-- Time Travel retention: Standard = 1 day, Enterprise = 90 days
-- Fail-Safe: Additional 7 days for disaster recovery

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum

# Create Snowpark session
session = Session.builder.configs(connection_params).create()

# DataFrame operations execute in Snowflake, not locally
df = session.table("orders") \
    .filter(col("amount") > 1000) \
    .group_by("customer_id") \
    .agg(sum("amount").alias("total_spent")) \
    .sort("total_spent", ascending=False)

# Write results back to Snowflake
df.write.save_as_table("high_value_customers")

-- Create a share
CREATE SHARE sales_share;
GRANT USAGE ON DATABASE sales_db TO SHARE sales_share;
GRANT SELECT ON TABLE sales_db.public.transactions TO SHARE sales_share;

-- Share with another Snowflake account
ALTER SHARE sales_share ADD ACCOUNTS = partner_account;

-- Consumer accesses data without copying (zero data movement)

-- Query data in S3 without importing
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG DATABASE 'my_database'
IAM_ROLE 'arn:aws:iam::account:role/SpectrumRole';

-- Join Redshift table with S3 data
SELECT r.customer_id, r.name, SUM(s.amount) as total
FROM redshift_customers r
JOIN spectrum_schema.s3_transactions s ON r.id = s.customer_id
GROUP BY r.customer_id, r.name;

-- Spectrum auto-scales compute for S3 queries

-- Enable concurrency scaling
ALTER WORKLOAD MANAGEMENT CONFIGURATION
ADD CONCURRENCY SCALING MODE AUTO;

-- Redshift automatically provisions additional clusters
-- when query queue exceeds threshold
-- Users don't notice any latency increase

-- Free concurrency scaling credits provided daily

CREATE MATERIALIZED VIEW customer_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as lifetime_value,
    MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id;

-- Auto-refresh when orders table changes
ALTER MATERIALIZED VIEW customer_summary AUTO REFRESH YES;

-- Create external schema pointing to RDS
CREATE EXTERNAL SCHEMA rds_schema
FROM POSTGRES
DATABASE 'production_db'
URI 'mydb.rds.amazonaws.com'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftFederatedRole'
SECRET_ARN 'arn:aws:secretsmanager:region:account:secret:rds-creds';

-- Join live RDS data with Redshift warehouse
SELECT w.product_name, SUM(r.quantity) as total_sold
FROM warehouse.sales w
JOIN rds_schema.live_inventory r ON w.product_id = r.id
WHERE r.last_updated > CURRENT_DATE - 1;

-- Create and train a linear regression model
CREATE OR REPLACE MODEL `project.dataset.customer_ltv_model`
OPTIONS(
  model_type='linear_reg',
  input_label_cols=['lifetime_value']
) AS
SELECT
  customer_age,
  purchase_frequency,
  avg_order_value,
  lifetime_value
FROM `project.dataset.customer_features`;

-- Make predictions with SQL
SELECT customer_id, predicted_lifetime_value
FROM ML.PREDICT(MODEL `project.dataset.customer_ltv_model`,
  (SELECT * FROM `project.dataset.new_customers`));

-- Create BI Engine reservation (managed memory)
bq mk --reservation   --project_id=my-project   --location=US   --bi_engine_capacity=100GB

-- BigQuery automatically caches frequently accessed data
-- Dashboard queries hit memory instead of storage
-- 100x faster response times for interactive exploration

-- Schedule daily Google Ads data import
bq mk --transfer_config   --data_source=google_ads   --display_name='Daily Ads Data'   --target_dataset=marketing_data   --schedule='every day 02:00'   --params='{
    "customer_id": "123-456-7890",
    "start_date": "2026-01-01"
  }'

-- Supports: Google Ads, YouTube, S3, Teradata, Redshift, and more

-- Create partitioned and clustered table
CREATE TABLE `project.dataset.events`
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
AS SELECT * FROM source_table;

-- Queries only scan relevant partitions
SELECT COUNT(*) FROM `project.dataset.events`
WHERE DATE(event_timestamp) = '2026-01-15'
  AND user_id = 'user123'
-- Scans only 1 day of data, 1 cluster, not entire table