-- Full process list (excludes sleeping connections)
SHOW FULL PROCESSLIST;

-- Better version with duration and host
SELECT
  id, user, host, db,
  command, time AS secs,
  state,
  LEFT(info, 120) AS query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Just the long-running ones (> 30 seconds)
SELECT id, user, db, time, state, LEFT(info,100) AS query
FROM information_schema.processlist
WHERE time > 30 AND command != 'Sleep'
ORDER BY time DESC;

-- Kill just the query (connection stays open)
KILL QUERY 12345;

-- Kill the entire connection
KILL 12345;

-- Kill ALL long-running queries at once (> 60s) — USE WITH CARE
SELECT CONCAT('KILL QUERY ', id, ';')
FROM information_schema.processlist
WHERE time > 60 AND command != 'Sleep' AND user != 'root';
-- Copy output and run manually

-- Current vs max connections
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- Connection history (peak ever reached)
SHOW STATUS LIKE 'Max_used_connections';

-- Connections by user (find who's using the most)
SELECT user, COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY user ORDER BY connections DESC;

-- Active InnoDB transactions
SELECT
  trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS secs_running,
  trx_mysql_thread_id AS connection_id,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- Who is blocking who (MySQL 8+ / Aurora 2+)
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_conn,
  LEFT(r.trx_query, 80) AS waiting_query,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_conn,
  LEFT(b.trx_query, 80) AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Full InnoDB engine status (look for LATEST DEADLOCK section)
SHOW ENGINE INNODB STATUS\G

-- Lock wait timeout setting
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Deadlock count since restart
SHOW STATUS LIKE 'Innodb_row_lock_waits';
SHOW STATUS LIKE 'Innodb_deadlocks';

-- Check current slow query settings
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable on-the-fly (EC2/self-managed only — use param group for RDS)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;        -- log queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Top slow queries from performance_schema (available on RDS)
SELECT
  LEFT(digest_text, 100) AS query_pattern,
  count_star AS exec_count,
  ROUND(avg_timer_wait/1000000000000, 3) AS avg_sec,
  ROUND(max_timer_wait/1000000000000, 3) AS max_sec,
  ROUND(sum_rows_examined / count_star) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name NOT IN ('performance_schema','information_schema','mysql')
ORDER BY avg_timer_wait DESC
LIMIT 15;

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Extended (MySQL 8+) — shows optimizer decisions
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- JSON format (most detailed)
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42\G

-- What to look for:
-- type = 'ALL'        → full table scan (BAD — needs index)
-- type = 'index'      → full index scan (better but still slow on large tables)
-- type = 'ref'/'eq_ref' → good, using index properly
-- rows > 100000       → scanning too many rows
-- Extra 'Using filesort' → sorting without index (expensive)

-- Buffer pool hit rate (should be > 99%)
SELECT
  ROUND(
    (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100, 2
  ) AS buffer_hit_rate_pct
FROM (
  SELECT
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_reads') + 0 AS innodb_buffer_pool_reads,
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_read_requests') + 0 AS innodb_buffer_pool_read_requests
) t;

-- Current buffer pool size vs configured
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_bytes_data';

-- All active queries with duration
SELECT
  pid,
  usename AS user,
  datname AS db,
  application_name AS app,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  EXTRACT(EPOCH FROM (now() - query_start))::int AS secs,
  LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start NULLS LAST;

-- Long-running queries only (> 30 seconds)
SELECT pid, usename, state, wait_event,
  EXTRACT(EPOCH FROM (now() - query_start))::int AS secs,
  LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - INTERVAL '30 seconds'
ORDER BY secs DESC;

-- Cancel just the query (connection stays — like KILL QUERY in MySQL)
SELECT pg_cancel_backend(12345);

-- Terminate the entire connection (like KILL in MySQL)
SELECT pg_terminate_backend(12345);

-- Terminate ALL long-running queries > 5 minutes — USE WITH CARE
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - INTERVAL '5 minutes'
  AND usename != 'rdsadmin';   -- don't kill internal RDS user

-- Current vs max connections
SELECT current_setting('max_connections') AS max,
       COUNT(*) AS current
FROM pg_stat_activity;

-- By user and state
SELECT usename, state, COUNT(*)
FROM pg_stat_activity
GROUP BY usename, state
ORDER BY count DESC;

-- % of max_connections used
SELECT
  MAX(conn) AS current_connections,
  MAX(setting)::int AS max_connections,
  ROUND(MAX(conn) * 100.0 / MAX(setting)::int, 1) AS pct_used
FROM
  (SELECT COUNT(*) AS conn FROM pg_stat_activity) c,
  pg_settings WHERE name = 'max_connections';

-- Check if pg_stat_statements is enabled
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- Top 15 slowest query patterns
SELECT
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND(max_exec_time::numeric, 2) AS max_ms,
  calls,
  ROUND(total_exec_time::numeric / 1000, 1) AS total_sec,
  rows,
  LEFT(query, 120) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
ORDER BY mean_exec_time DESC
LIMIT 15;

-- Most called queries (high frequency targets)
SELECT calls, ROUND(mean_exec_time::numeric,2) AS avg_ms, LEFT(query,100) AS query
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 10;

-- Reset stats (after fixing a query)
SELECT pg_stat_statements_reset();

-- Full analysis (BUFFERS shows cache hit/miss — critical for tuning)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2024-01-01';

-- What to look for:
-- Seq Scan on large table → missing index
-- Rows= vs actual rows → bad statistics (run ANALYZE)
-- Buffers: shared hit=X read=Y → Y > 0 means going to disk
-- Nested Loop with large outer → consider Hash Join
-- Sort → possible index optimization

-- Overall cache hit ratio (should be > 99%)
SELECT
  ROUND(
    blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0), 2
  ) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Per-table cache ratio
SELECT relname,
  ROUND(heap_blks_hit * 100.0 / NULLIF(heap_blks_hit + heap_blks_read, 0), 1) AS hit_pct,
  heap_blks_hit, heap_blks_read
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC LIMIT 15;

# Create manual snapshot before risky operations
aws rds create-db-snapshot \
  --db-instance-identifier my-rds \
  --db-snapshot-identifier pre-migration-$(date +%Y%m%d-%H%M)

# List available snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier my-rds \
  --query 'DBSnapshots[*].[DBSnapshotIdentifier,SnapshotCreateTime,Status,AllocatedStorage]' \
  --output table

# Restore snapshot to a NEW instance (never restore over existing prod)
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier my-rds-restored \
  --db-snapshot-identifier pre-migration-20240115-1430 \
  --db-instance-class db.r6g.large \
  --no-publicly-accessible

# Check restore progress
aws rds describe-db-instances \
  --db-instance-identifier my-rds-restored \
  --query 'DBInstances[0].[DBInstanceStatus,PercentProgress]' \
  --output text

# Aurora cluster snapshot
aws rds create-db-cluster-snapshot \
  --db-cluster-identifier my-aurora-cluster \
  --db-cluster-snapshot-identifier pre-migration-$(date +%Y%m%d-%H%M)

# Restore Aurora snapshot to new cluster
aws rds restore-db-cluster-from-snapshot \
  --db-cluster-identifier my-aurora-restored \
  --snapshot-identifier pre-migration-20240115-1430 \
  --engine aurora-mysql

# Dump single database (compressed, parallel)
pg_dump -h rds-endpoint.amazonaws.com -U masteruser \
  -Fc -j 4 -d mydb -f mydb_$(date +%Y%m%d).dump

# Dump single table only
pg_dump -h rds-endpoint.amazonaws.com -U masteruser \
  -Fc -t public.orders -d mydb -f orders_backup.dump

# Restore to target DB (--no-owner prevents ownership errors)
pg_restore -h target-rds.amazonaws.com -U masteruser \
  -d mydb --no-owner --no-acl -j 4 -Fc mydb_20240115.dump

# Restore single table into existing DB
pg_restore -h target-rds.amazonaws.com -U masteruser \
  -d mydb --no-owner -t orders -Fc orders_backup.dump

# Upload dump to S3 immediately after
aws s3 cp mydb_$(date +%Y%m%d).dump s3://my-backups-bucket/postgres/

# Quick schema-only export (structure without data)
pg_dump -h rds-endpoint.amazonaws.com -U masteruser \
  --schema-only -d mydb -f schema_$(date +%Y%m%d).sql

# Full database dump
mysqldump -h rds-endpoint.amazonaws.com -u admin -p \
  --single-transaction --routines --triggers \
  mydb > mydb_$(date +%Y%m%d).sql

# Single table
mysqldump -h rds-endpoint.amazonaws.com -u admin -p \
  --single-transaction mydb orders > orders_backup.sql

# Compressed dump piped directly to S3
mysqldump -h rds-endpoint.amazonaws.com -u admin -p \
  --single-transaction mydb \
  | gzip | aws s3 cp - s3://my-backups/mysql/mydb_$(date +%Y%m%d).sql.gz

# Restore
mysql -h target-rds.amazonaws.com -u admin -p mydb < mydb_20240115.sql

# Restore from S3 directly
aws s3 cp s3://my-backups/mysql/mydb_20240115.sql.gz - \
  | gunzip | mysql -h target-rds.amazonaws.com -u admin -p mydb

# Restore to specific point in time (e.g. just before a bad migration)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier my-rds \
  --target-db-instance-identifier my-rds-pitr \
  --restore-time 2024-01-15T14:30:00Z \
  --db-instance-class db.r6g.large \
  --no-publicly-accessible

# Check what the earliest restore point is
aws rds describe-db-instances \
  --db-instance-identifier my-rds \
  --query 'DBInstances[0].{Earliest:EarliestRestorableTime,Latest:LatestRestorableTime}' \
  --output table

-- Create index WITHOUT table lock (takes longer but safe in prod)
CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON public.orders (customer_id);

-- Create partial index (smaller, faster for filtered queries)
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON public.orders (created_at)
  WHERE status = 'pending';

-- Drop index without lock
DROP INDEX CONCURRENTLY idx_orders_old;

-- Rebuild bloated index without lock
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- Rebuild all indexes on a table (PostgreSQL 12+)
REINDEX TABLE CONCURRENTLY public.orders;

-- Check index bloat (indexes > 30% bloat are candidates for rebuild)
SELECT
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used,
  ROUND(pg_relation_size(indexrelid) * 100.0 /
    NULLIF(pg_total_relation_size(indrelid), 0), 1) AS pct_of_table
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- > 10MB
ORDER BY pg_relation_size(indexrelid) DESC;

-- Add index online (ALGORITHM=INPLACE avoids full table copy for most cases)
ALTER TABLE orders
  ADD INDEX idx_orders_customer_id (customer_id),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Drop unused index online
ALTER TABLE orders
  DROP INDEX idx_orders_old,
  ALGORITHM=INPLACE, LOCK=NONE;

-- Optimize table (reclaims space + rebuilds indexes — avoid on large tables in prod)
-- For InnoDB this is a full table rebuild. Schedule during maintenance window.
OPTIMIZE TABLE orders;

-- Check index fragmentation
SELECT table_name, index_name, stat_value AS pages,
  ROUND(stat_value * 16 / 1024, 1) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
  AND database_name = DATABASE()
ORDER BY stat_value DESC LIMIT 20;

-- Show all indexes on a table
SHOW INDEX FROM orders;

-- Force index usage for testing (remove in prod)
SELECT * FROM orders FORCE INDEX (idx_orders_customer_id)
WHERE customer_id = 42;

-- Check tables needing vacuum (unsorted rows & deleted rows)
SELECT "table", unsorted, stats_off, size AS size_mb,
       tbl_rows, estimated_visible_rows,
       vacuum_sort_benefit
FROM SVV_TABLE_INFO
WHERE unsorted > 5 OR stats_off > 20
ORDER BY vacuum_sort_benefit DESC
LIMIT 20;

-- Vacuum specific table (reclaim space + resort)
VACUUM orders;

-- Vacuum + update statistics
VACUUM orders TO 75 PERCENT;
ANALYZE orders;

-- Full vacuum all tables (run during off-hours)
VACUUM FULL;

-- Check distkey & sortkey configuration
SELECT tablename, "column", sortkey, distkey, encoding
FROM pg_table_def
WHERE schemaname = 'public'
  AND tablename = 'orders'
ORDER BY sortkey NULLS LAST;

-- Check clustering depth (DML_ratio > 0.5 or clustering_errors > 0 = re-cluster needed)
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date)');

-- Manually trigger re-clustering (background, non-blocking)
ALTER TABLE orders CLUSTER BY (order_date, customer_id);

-- Enable search optimization (for equality/range lookups on large tables)
ALTER TABLE orders ADD SEARCH OPTIMIZATION;

-- Check search optimization status & cost
SELECT * FROM INFORMATION_SCHEMA.SEARCH_OPTIMIZATION_HISTORY
WHERE table_name = 'ORDERS'
ORDER BY start_time DESC LIMIT 10;

-- Show current clustering keys
SHOW TABLES LIKE 'orders';

-- Compare source vs target counts for today's load
SELECT
  'source'  AS layer, COUNT(*) AS rows FROM source_db.orders WHERE DATE(created_at) = CURRENT_DATE
UNION ALL
SELECT
  'target'  AS layer, COUNT(*) AS rows FROM target_db.orders WHERE DATE(created_at) = CURRENT_DATE;

-- Row count trend (detect sudden drops to 0 or 10x spikes)
SELECT
  DATE(created_at) AS day,
  COUNT(*)         AS rows,
  COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE(created_at)) AS day_over_day_delta
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY day;

-- Null rate for key columns (adjust column list to your table)
SELECT
  COUNT(*)                                          AS total_rows,
  ROUND(COUNT(*) FILTER (WHERE customer_id IS NULL)  * 100.0 / COUNT(*), 2) AS customer_id_null_pct,
  ROUND(COUNT(*) FILTER (WHERE order_total IS NULL)   * 100.0 / COUNT(*), 2) AS order_total_null_pct,
  ROUND(COUNT(*) FILTER (WHERE created_at IS NULL)    * 100.0 / COUNT(*), 2) AS created_at_null_pct,
  ROUND(COUNT(*) FILTER (WHERE status IS NULL)        * 100.0 / COUNT(*), 2) AS status_null_pct
FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

-- MySQL / Redshift / Snowflake version (no FILTER support):
SELECT
  COUNT(*) AS total,
  ROUND(SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS customer_id_null_pct,
  ROUND(SUM(CASE WHEN order_total IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS order_total_null_pct
FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

-- Find duplicate primary keys (should return 0 rows if pipeline is correct)
SELECT order_id, COUNT(*) AS occurrences
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY occurrences DESC
LIMIT 20;

-- Count of duplicates only (quick check)
SELECT COUNT(*) AS duplicate_keys FROM (
  SELECT order_id
  FROM orders
  WHERE DATE(created_at) = CURRENT_DATE
  GROUP BY order_id
  HAVING COUNT(*) > 1
) dupes;

-- When was the last record actually loaded? (staleness check)
SELECT
  MAX(created_at)                                    AS latest_record,
  NOW() - MAX(created_at)                            AS lag,
  CASE
    WHEN NOW() - MAX(created_at) > INTERVAL '2 hours' THEN '⚠ STALE'
    ELSE '✓ FRESH'
  END AS status
FROM orders;

-- Snowflake version
SELECT
  MAX(created_at)                          AS latest_record,
  DATEDIFF('minute', MAX(created_at), NOW()) AS lag_minutes,
  CASE WHEN DATEDIFF('minute', MAX(created_at), NOW()) > 120
       THEN 'STALE' ELSE 'FRESH' END        AS status
FROM orders;

-- Orders with no matching customer (orphaned foreign keys)
SELECT COUNT(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- Show sample orphans
SELECT o.order_id, o.customer_id, o.created_at
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL
LIMIT 10;

-- Distribution of status values (unexpected values = upstream change)
SELECT status, COUNT(*) AS count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY status ORDER BY count DESC;

-- Min / max / avg sanity check (detect negative prices, future dates, etc.)
SELECT
  MIN(order_total)    AS min_total,
  MAX(order_total)    AS max_total,
  ROUND(AVG(order_total), 2) AS avg_total,
  MIN(created_at)     AS earliest,
  MAX(created_at)     AS latest
FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

# Connect to PgBouncer admin console (port 6432 by default)
psql -h localhost -p 6432 -U pgbouncer pgbouncer

# Or from inside Docker
docker exec -it pgbouncer psql -h localhost -p 6432 -U pgbouncer pgbouncer

-- Pool status: cl_active = clients using connection, sv_idle = free server connections
SHOW POOLS;
-- Key columns:
-- cl_active   = clients currently executing a query
-- cl_waiting  = clients WAITING for a connection (THIS IS BAD — means pool is full)
-- sv_idle     = server connections idle (available)
-- sv_active   = server connections in use
-- maxwait     = seconds oldest waiting client has been waiting (> 5s = problem)

-- All active client connections
SHOW CLIENTS;

-- Server connections to the real DB
SHOW SERVERS;

-- Throughput and latency stats
SHOW STATS;
-- avg_query_time in microseconds — divide by 1000 for ms

-- Current configuration
SHOW CONFIG;

-- Reload config without restart (after editing pgbouncer.ini)
RELOAD;

-- Pause a database (graceful — finish current queries first)
PAUSE mydb;

-- Resume
RESUME mydb;

-- cl_waiting > 0 means clients are queuing for a DB connection
-- This query shows it clearly
SHOW POOLS;

-- Common causes and fixes:
-- 1. pool_size too small → increase pool_size in pgbouncer.ini
-- 2. Long-running transactions holding connections →
--    check pg_stat_activity for 'idle in transaction'
-- 3. Wrong pool mode → use 'transaction' mode for most apps, not 'session'
SHOW CONFIG;   -- check pool_mode

-- Current max connections consumed at the DB level
-- (run in PostgreSQL, not PgBouncer)
SELECT COUNT(*) AS pooler_connections,
       current_setting('max_connections') AS max_connections
FROM pg_stat_activity
WHERE application_name LIKE '%pgbouncer%'
   OR application_name LIKE '%bouncer%';

# List all RDS proxies
aws rds describe-db-proxies \
  --query 'DBProxies[*].[DBProxyName,Status,Endpoint,EngineFamily]' \
  --output table

# Check proxy target health (is it actually connected to the DB?)
aws rds describe-db-proxy-targets \
  --db-proxy-name my-rds-proxy \
  --query 'Targets[*].[Endpoint,Port,TargetHealth.State,TargetHealth.Reason]' \
  --output table
# TargetHealth.State should be AVAILABLE
# UNAVAILABLE with reason = shows what's wrong

# Connection pool metrics via CloudWatch
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ClientConnections \
  --dimensions Name=ProxyName,Value=my-rds-proxy \
  --start-time $(date -u -d '30 min ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 60 --statistics Average --output table

# Other key RDS Proxy metrics to watch:
# DatabaseConnectionsCurrentlyBorrowed  → connections in use
# DatabaseConnectionsBorrowLatency      → how long to get a connection (ms)
# QueryDatabaseResponseLatency          → DB response time through proxy
# ClientConnections                     → total clients connected to proxy

# Live resource monitor — shows which container eats RAM (Spark/JVM culprit)
docker stats
docker stats --no-stream            # snapshot, non-interactive

# See actual Linux processes inside the container
docker top <name>
docker top <name> aux              # full ps output

# Freeze container CPU without killing it (temporary relief)
docker pause <name>
docker unpause <name>

# Wait for container to finish and capture exit code (CI/CD scripts)
docker wait <name>
echo "Exit code: $?"

# Test TCP connectivity from inside a container (verbose output)
docker exec -it <etl_container> curl -v telnet://db-hostname:5432
docker exec -it <etl_container> curl -v telnet://rds-endpoint.amazonaws.com:3306

# Alternative: /dev/tcp method (no curl needed)
docker exec -it <name> timeout 3 bash -c '</dev/tcp/db-host/5432' \
  && echo "Port OPEN" || echo "Port CLOSED"

# nc (netcat) — test from EC2 / local shell
nc -zv rds-endpoint.amazonaws.com 5432    # PostgreSQL / Aurora PG
nc -zv rds-endpoint.amazonaws.com 3306    # MySQL / Aurora MySQL
nc -zv redshift-endpoint.amazonaws.com 5439  # Redshift

# Login to ECR (do this first — token expires in 12h)
aws ecr get-login-password --region us-east-1 | docker login \
  --username AWS --password-stdin \
  <account_id>.dkr.ecr.us-east-1.amazonaws.com

# Tag and push
docker tag my-image:latest <account_id>.dkr.ecr.us-east-1.amazonaws.com/my-repo:latest
docker push <account_id>.dkr.ecr.us-east-1.amazonaws.com/my-repo:latest

# Pull from ECR (e.g. on ECS/EC2 worker)
docker pull <account_id>.dkr.ecr.us-east-1.amazonaws.com/my-repo:latest

alias dps="docker ps --format 'table {{.Names}}\t{{.Status}}\t{{.Ports}}'"
alias dlogs="docker logs -f --tail 50"
alias dclean="docker system prune -f"
alias dstop="docker compose down --remove-orphans"

# Find the LATEST file in a prefix (root cause: "file not found")
aws s3 ls s3://my-bucket/prefix/ --recursive | sort | tail -n 5

# Check file size and metadata (root cause: "empty file / zero bytes")
aws s3api head-object --bucket my-bucket --key path/to/file.csv
# Returns: ContentLength, LastModified, ContentType, ETag

# Quick preview of a remote file WITHOUT downloading (root cause: "schema mismatch")
aws s3 cp s3://my-bucket/file.csv - | head -n 5

# Count objects in a prefix
aws s3 ls s3://my-bucket/prefix/ --recursive | wc -l

# Find files modified in the last 24h
aws s3 ls s3://my-bucket/prefix/ --recursive \
  | awk '{print $1, $2, $4}' \
  | awk -v d="$(date -d '1 day ago' '+%Y-%m-%d')" '$1 >= d'

-- COPY errors (essential when COPY command fails silently)
SELECT query, starttime, filename, line_number,
       colname, err_reason
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 10;

-- Detailed COPY history
SELECT query, trim(filename) AS file, lines_scanned,
       lines_loaded, rows_loaded, is_partial
FROM stl_load_commits
ORDER BY query DESC LIMIT 20;

-- Currently running queries (inflight)
SELECT pid, user_name, start_time, LEFT(query, 100) AS query
FROM stv_inflight
ORDER BY start_time;

-- Kill a stuck query
CANCEL <pid>;

-- Table locks
SELECT a.txn_owner, a.txn_db, a.table_id, b.relname,
       a.lock_mode, a.granted
FROM svv_transactions a
JOIN pg_class b ON a.table_id = b.oid
WHERE a.granted IS FALSE;

-- Long-running queries (> 5 minutes)
SELECT pid, user_name, duration, LEFT(query, 120) AS query
FROM SVL_QLOG
WHERE duration > 300000000    -- microseconds
ORDER BY duration DESC LIMIT 10;

nc -zv your-redshift-cluster.region.redshift.amazonaws.com 5439
# "Connection to ... succeeded" = network OK, check credentials
# "Connection refused / timed out" = Security Group or VPC blocking

# Get state of a specific task run (faster than opening the UI)
airflow tasks state <dag_id> <task_id> <execution_date>
# e.g.: airflow tasks state my_etl_dag extract_data 2024-01-15

# List task instances for a DAG run
airflow tasks list <dag_id> --tree

# Test a task without marking it in the metadata DB (safe for debugging)
airflow tasks test <dag_id> <task_id> <execution_date>

# Clear failed tasks to re-run (quick fix)
airflow tasks clear <dag_id> \
  --start-date 2024-01-15 \
  --end-date 2024-01-15

# Clear + confirm without prompt (-y)
airflow tasks clear -y <dag_id> -s 2024-01-15 -e 2024-01-15

# Trigger a DAG run manually
airflow dags trigger <dag_id>

# Pause / unpause a DAG
airflow dags pause <dag_id>
airflow dags unpause <dag_id>

# Check metadata DB connectivity (scheduler lag root cause)
airflow db check

# Show scheduler heartbeat (last seen time — if stale, scheduler is dead)
airflow jobs check --job-type SchedulerJob --limit 1

# Check Airflow version and config
airflow version
airflow config get-value core sql_alchemy_conn   # metadata DB connection

WITH WAREHOUSE_CREDITS_MAP AS (
    SELECT * FROM (VALUES
        ('X-Small',  'STANDARD',           1),
        ('Small',    'STANDARD',           2),
        ('Medium',   'STANDARD',           4),
        ('Large',    'STANDARD',           8),
        ('X-Large',  'STANDARD',          16),
        ('2X-Large', 'STANDARD',          32),
        ('3X-Large', 'STANDARD',          64),
        ('4X-Large', 'STANDARD',         128),
        ('5X-Large', 'STANDARD',         256),
        ('6X-Large', 'STANDARD',         512),
        ('Medium',   'SNOWPARK-OPTIMIZED',  6),
        ('Large',    'SNOWPARK-OPTIMIZED', 12),
        ('X-Large',  'SNOWPARK-OPTIMIZED', 24),
        ('2X-Large', 'SNOWPARK-OPTIMIZED', 48),
        ('3X-Large', 'SNOWPARK-OPTIMIZED', 96),
        ('4X-Large', 'SNOWPARK-OPTIMIZED',192),
        ('5X-Large', 'SNOWPARK-OPTIMIZED',384),
        ('6X-Large', 'SNOWPARK-OPTIMIZED',768)
    ) AS t (WAREHOUSE_SIZE, WAREHOUSE_TYPE, CREDITS_PER_HOUR)
),
JOINED AS (
    SELECT
        qh.QUERY_TEXT,
        qh.QUERY_ID,
        qh.EXECUTION_STATUS,
        qh.WAREHOUSE_SIZE,
        qh.WAREHOUSE_TYPE,
        qh.EXECUTION_TIME,
        qh.EXECUTION_TIME * wcm.CREDITS_PER_HOUR AS EFFECTIVE_EXECUTION_TIME,
        qh.USER_NAME
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh
    JOIN WAREHOUSE_CREDITS_MAP wcm
      ON wcm.WAREHOUSE_SIZE  = qh.WAREHOUSE_SIZE
     AND wcm.WAREHOUSE_TYPE  = qh.WAREHOUSE_TYPE
    WHERE qh.EXECUTION_STATUS = 'FAIL'
      AND qh.START_TIME BETWEEN DATEADD('day', -14, CURRENT_DATE()) AND CURRENT_DATE()
    ORDER BY EFFECTIVE_EXECUTION_TIME DESC
)
SELECT
    QUERY_TEXT,
    MAX(QUERY_ID)                                          AS LATEST_QUERY_ID,
    ANY_VALUE(WAREHOUSE_SIZE)                              AS WAREHOUSE_SIZE,
    ANY_VALUE(WAREHOUSE_TYPE)                              AS WAREHOUSE_TYPE,
    AVG(EFFECTIVE_EXECUTION_TIME::INT / 1000)::INT         AS AVG_COST_WEIGHTED_SECS,
    ANY_VALUE(USER_NAME)                                   AS USER_NAME
FROM JOINED
GROUP BY QUERY_TEXT
ORDER BY AVG_COST_WEIGHTED_SECS DESC;

-- Currently running queries
SELECT query_id, user_name, warehouse_name,
       execution_status,
       DATEDIFF('second', start_time, CURRENT_TIMESTAMP()) AS running_secs,
       LEFT(query_text, 120) AS query
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION())
WHERE execution_status = 'RUNNING'
ORDER BY running_secs DESC;

-- Kill a query
SELECT SYSTEM$CANCEL_QUERY('<query_id>');

-- Recent failures with error messages
SELECT query_id, start_time, user_name, warehouse_name,
       error_code, error_message,
       LEFT(query_text, 120) AS query
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'FAIL'
  AND start_time > DATEADD('hour', -24, CURRENT_TIMESTAMP())
ORDER BY start_time DESC
LIMIT 20;

-- Credits used per warehouse last 7 days
SELECT warehouse_name,
       SUM(credits_used)          AS total_credits,
       SUM(credits_used_compute)  AS compute_credits,
       COUNT(*)                   AS sessions
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time > DATEADD('day', -7, CURRENT_DATE())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

-- Warehouse queuing — are queries waiting?
SELECT warehouse_name, queued_overload_time, queued_provisioning_time,
       overloaded_time, AVG(execution_time) AS avg_exec_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time > DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY warehouse_name, queued_overload_time,
         queued_provisioning_time, overloaded_time
ORDER BY queued_overload_time DESC NULLS LAST;

-- Largest tables in current database
SELECT table_schema, table_name,
       ROUND(bytes / 1024 / 1024 / 1024, 2)          AS size_gb,
       row_count,
       ROUND(bytes / NULLIF(row_count, 0), 0)         AS bytes_per_row
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY bytes DESC NULLIF 20;

-- Query result cache hit rate (high = good, saves credits)
SELECT
    COUNT_IF(result_from_cache) AS cache_hits,
    COUNT(*) AS total_queries,
    ROUND(COUNT_IF(result_from_cache) * 100.0 / COUNT(*), 1) AS cache_hit_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time > DATEADD('day', -1, CURRENT_DATE());

-- Clone and time travel storage cost
SELECT table_name,
       ROUND(active_bytes/1024/1024/1024, 3)      AS active_gb,
       ROUND(time_travel_bytes/1024/1024/1024, 3) AS time_travel_gb,
       ROUND(failsafe_bytes/1024/1024/1024, 3)    AS failsafe_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE active_bytes > 0
ORDER BY (active_bytes + time_travel_bytes + failsafe_bytes) DESC
LIMIT 20;

# Check dbt version and connection to warehouse
dbt --version
dbt debug                             # tests connection, profiles.yml, dependencies

# Debug with specific profile / target
dbt debug --profile my_project --target prod

# List all models (check what's in the project)
dbt ls
dbt ls --select tag:daily             # models with a specific tag
dbt ls --select source:*              # all sources

# Run all models
dbt run

# Run a single model
dbt run --select orders

# Run a model + all its upstream dependencies
dbt run --select +orders

# Run a model + all downstream dependents
dbt run --select orders+

# Run only failed models from the last run
dbt run --select result:error --state ./target

# Run tests only
dbt test
dbt test --select orders              # test a specific model
dbt test --select source:raw_orders   # test a source

# Run source freshness check
dbt source freshness
dbt source freshness --select source:raw_orders

# See full error output (default truncates)
dbt run --select orders 2>&1 | cat

# Read the run results JSON after a failure
cat target/run_results.json | python3 -c "
import json, sys
r = json.load(sys.stdin)
for n in r['results']:
    if n['status'] not in ('success','pass'):
        print(f\"--- {n['unique_id']} [{n['status']}] ---\")
        print(n.get('message',''))
        print()
"

# Compile a model to see the rendered SQL (without running it)
dbt compile --select orders
cat target/compiled/my_project/models/orders.sql

# Run with --fail-fast (stop on first error instead of continuing)
dbt run --fail-fast

# Verbose logging
dbt run --select orders --log-level debug 2>&1 | grep -iE 'error|warn|fail'

# Check all sources for freshness (compares MAX(loaded_at) to warn/error thresholds)
dbt source freshness

# Output to file for logging
dbt source freshness -o target/sources.json

# Read freshness results
cat target/sources.json | python3 -c "
import json, sys
r = json.load(sys.stdin)
for s in r['results']:
    if s['status'] != 'pass':
        print(f\"{s['unique_id']}: {s['status']} — {s.get('warn_after') or s.get('error_after')}\")
"

# Re-run only tests that failed last time
dbt test --select result:fail --state ./target

# Check which warehouse dbt is using (Snowflake)
# In profiles.yml — confirm warehouse, role, database, schema
cat ~/.dbt/profiles.yml

# Override warehouse for a heavy model run (Snowflake)
dbt run --select large_model --vars '{"snowflake_warehouse": "TRANSFORM_XL"}'

# Check dbt model run times in Snowflake query history
SELECT
  query_text, start_time,
  ROUND(execution_time / 1000, 1) AS secs,
  warehouse_name, user_name
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text ILIKE '%/* {"app": "dbt"%'
  AND start_time > DATEADD('hour', -24, CURRENT_TIMESTAMP())
ORDER BY execution_time DESC
LIMIT 20;

# Redshift — find dbt queries by comment tag
SELECT pid, starttime,
  ROUND(datediff('second', starttime, endtime), 1) AS secs,
  LEFT(querytxt, 120) AS query
FROM STL_QUERY
WHERE querytxt LIKE '%dbt%'
  AND starttime > CURRENT_DATE - 1
ORDER BY starttime DESC LIMIT 20;

git log --since="2 days ago" --oneline
SELECT date, COUNT(*) FROM table WHERE date >= CURRENT_DATE - 7 GROUP BY date ORDER BY date;
aws s3 ls s3://bucket/prefix/ --recursive | wc -l

grep -i "error" logfile.log | head -10
aws logs filter-log-events --log-group-name /aws/glue/jobs/output \
  --filter-pattern "ERROR" --max-items 10 --query 'events[*].message' --output text

aws sts get-caller-identity
aws s3 ls s3://bucket/path/file.parquet
telnet hostname 5432

for i in {0..7}; do d=$(date -d "$i days ago" +%Y-%m-%d); echo "$d: $(aws s3 ls s3://bucket/$d/ | wc -l) files"; done

aws sts get-caller-identity
SELECT COUNT(*) FROM table WHERE date = CURRENT_DATE;
systemctl status service-name
nc -zv hostname 5432