SAFETY GUIDE: DROP TABLE

DROP TABLE Safety Guide

Learn from costly mistakes! Master table deletion safety, recovery strategies, and production best practices. One wrong DROP = hours of downtime + thousands lost!

💥 The Story: The $380K Production Accident

March 22, 2022, 3:47 PM - FinTech Startup, 15-person engineering team

The Engineer: Senior Backend Developer, 4 years experience

The Task: Clean up old test tables from staging environment

The Terminal: Two tabs open - staging (green) and production (should've been red but wasn't)

💔 What Happened (Timeline)

47:12 - Connected to cluster
47:18 - Typed: DROP TABLE user_sessions;
47:21 - Hit Enter
47:22 - Table dropped (0.8 seconds)
47:25 - Realized mistake: "Wait... which terminal was I in?"
47:26 - Checked connection: PRODUCTION CLUSTER
47:27 - Heart stops

📊 What Was Lost

Data: 12.3 million active user sessions
Users Affected: Every single active user (47,392 people)
Immediate Impact: All users logged out instantly
Secondary Impact: 2FA tokens, shopping carts, session preferences

💰 The Damage

Immediate Revenue Loss: $87,000 (4.5 hours downtime during peak trading)
Recovery Costs: $45,000 (all-hands emergency, overtime)
Customer Credits: $128,000 (compensation for disruption)
Reputation Damage: $120,000 (42% drop in new signups for 2 weeks)
TOTAL: $380,000

🔧 Recovery Process

15:47-15:52 (5 min): Panic, verify damage, alert team
15:52-16:15 (23 min): Find last snapshot (taken 2 hours ago at 13:45)
16:15-17:30 (75 min): Restore user_sessions table from snapshot
17:30-19:45 (135 min): Reconcile missing 2 hours of sessions from application logs
19:45-20:15 (30 min): Verify data, run tests, monitor errors

Total Downtime: 4 hours 28 minutes

✅ What Saved Them

Automatic snapshots every 2 hours (lost only 2 hours of data)
Application logs (reconstructed missing sessions)
Quick reaction (started recovery within 5 minutes)
Experienced team (practiced recovery drills)

🛡️ Changes Made After

Terminal Colors: Production = red background (mandatory)
Custom Shell Prompt: Shows cluster name in BIG RED TEXT
Confirmation Script: DROP commands require typing table name twice
RBAC: Only 2 people can DROP in production (CEO approval)
Snapshots: Every 30 minutes (was every 2 hours)
Recovery Drills: Monthly practice (every engineer must participate)

"I had 4 years of experience and still made this mistake.
It took 3 seconds to type. It cost $380K to fix."

- The Engineer (still employed, now advocates for safety)

This could happen to YOU. Read on to learn how to prevent it. ⚠️

❓ What is DROP TABLE?

Understanding the command that deletes an entire table permanently.

Simple Definition

DROP TABLE: A CQL command that permanently deletes a table and ALL its data from the cluster.

What It Deletes:

All data: Every single row in the table
Table schema: Column definitions, data types
All indexes: Secondary indexes, materialized views
All replicas: Data on ALL nodes (RF copies)
Table metadata: Properties, settings, options

⚠️ Critical: NO UNDO BUTTON!

Once you hit Enter, the table is gone. Your only hope is backups/snapshots.

Basic Syntax

-- Basic DROP (throws error if table doesn't exist)
DROP TABLE table_name;

-- Safe DROP (no error if table doesn't exist)
DROP TABLE IF EXISTS table_name;

-- With keyspace name
DROP TABLE keyspace_name.table_name;
                        

Speed of Execution

How fast does DROP TABLE execute?

Schema deletion: ~50-200ms (instant)
After schema deleted: Table is immediately INACCESSIBLE
Physical deletion: Hours to days (background process)

The danger: Table disappears before you realize the mistake!

⚖️ DROP TABLE vs DROP KEYSPACE

Understanding the difference can save your job!

Aspect	DROP TABLE	DROP KEYSPACE
Scope	Deletes ONE table	Deletes ALL tables in keyspace
Severity	⚠️ Warning (recoverable)	🔥 Critical (catastrophic)
Recovery Time	30 min - 2 hours	4 - 12 hours
Business Impact	Partial outage (1 feature)	Complete outage (all features)
Typical Cost	$50K - $500K	$1M - $50M+
Job Risk	Warning/probation	Often termination

The Good News

DROP TABLE is MORE recoverable than DROP KEYSPACE:

✅ Smaller scope: Only one table affected (not entire application)
✅ Faster recovery: Restore single table snapshot (not whole keyspace)
✅ Partial service: Other features may still work
✅ Less data: Easier to reconstruct from logs/other sources

But still dangerous! Don't get comfortable!

📝 Syntax & Usage Examples

Complete syntax guide with safe practices.

Basic Forms

-- Form 1: Basic DROP (throws error if doesn't exist)
DROP TABLE users;

-- Form 2: Safe DROP (no error if doesn't exist)
DROP TABLE IF EXISTS users;

-- Form 3: With keyspace (explicit)
DROP TABLE my_app.users;

-- Form 4: With keyspace (safe)
DROP TABLE IF EXISTS my_app.users;
                        

Safe Workflow

-- Step 1: VERIFY you're on correct cluster
SELECT cluster_name FROM system.local;
-- Output: "production_cluster" or "staging_cluster"

-- Step 2: VERIFY the table exists and check row count
DESCRIBE TABLE user_sessions;
SELECT COUNT(*) FROM user_sessions LIMIT 100000;

-- Step 3: CHECK if any apps depend on it
-- (Check application code, ask team)

-- Step 4: TAKE SNAPSHOT (critical!)
-- In terminal (not CQL):
-- nodetool snapshot my_keyspace -t before_drop_user_sessions

-- Step 5: GET APPROVAL (production only)
-- (JIRA ticket, manager approval, team notification)

-- Step 6: DROP (use IF EXISTS for safety)
DROP TABLE IF EXISTS user_sessions;

-- Step 7: VERIFY it's gone
DESCRIBE TABLES;
                        

What NOT To Do

-- ❌ NEVER do this (too dangerous!)
DROP TABLE user_sessions;  -- No verification, no snapshot!

-- ❌ NEVER do this on Friday
-- (What if recovery takes 8 hours? You'll work all weekend!)

-- ❌ NEVER do this when tired
-- (Mistakes happen when brain is foggy)

-- ❌ NEVER do this without telling anyone
-- (Team can catch mistakes, help with recovery)
                            

⚙️ What Happens Internally

Understanding the deletion process helps with recovery!

The 5-Minute Rule

If you realize the mistake within 5 minutes:

IMMEDIATELY stop compaction: nodetool stop COMPACTION
Find SSTable files on disk (they might still exist!)
Copy them to safe location
Follow SSTable resurrection procedure (see Recovery section)

Success rate: 60-80% if you act within 5 minutes!

⚠️ The Dangers of DROP TABLE

Why DROP TABLE accidents happen to experienced engineers.

⚡

Instant & Irreversible

Executes in 100ms - Too fast to stop
No confirmation dialog - One Enter key
No undo button - Can't reverse it
Immediate effect - Table gone instantly

You have 0.1 seconds to realize the mistake!

💥

Cascading Failures

App crashes - Queries fail immediately
User errors - 500 errors everywhere
Alerts flood - PagerDuty goes crazy
Team panic - All hands on deck

One table down can break entire features!

🎯

Easy to Make Mistakes

Wrong terminal tab - Prod vs staging
Copy-paste error - Wrong table name
Typo - user_sessions vs user_session
Mental fatigue - 3 AM bug fixes

Even experts make these mistakes!

Most Common Scenarios

Wrong Environment: Thought staging, was production (40% of incidents)
Wrong Table: Similar names (user_sessions vs user_session_temp) (25%)
Cleanup Script: Automation gone wrong (20%)
Copy-Paste: Pasted wrong command from chat/docs (10%)
Other: Fatigue, distraction, confusion (5%)

✅ Pre-DROP Safety Checklist

Complete this checklist BEFORE dropping any table!

📋 Mandatory Checklist

Click each item to mark complete (practice good habits!)

1. Verify Cluster Connection
Run: SELECT cluster_name FROM system.local;
2. Confirm Table Name & Keyspace
Run: DESCRIBE TABLE keyspace.table_name;
3. Check Data/Row Count
Run: SELECT COUNT(*) FROM table LIMIT 100000;
4. Verify Application Dependencies
Check code, ask team if any apps use this table
5. Take Snapshot (CRITICAL!)
Run: nodetool snapshot keyspace -t before_drop_tablename
6. Get Approval (Production Only)
JIRA ticket + manager approval + change request
7. Notify Team
Slack announcement, wait 5 min for objections
8. Verify Backup Status
Check last backup < 24 hours old
9. Have Recovery Plan Ready
Know how to restore, have commands ready
10. Use IF EXISTS for Safety
DROP TABLE IF EXISTS (not just DROP TABLE)
11. Triple-Check Before Enter
Read command out loud, verify everything
12. Have Colleague Review (Production)
Screen share with senior engineer for verification

⚠️ If you skip ANY of these steps, you're accepting the risk! ⚠️

💾 Backup Strategies: Your Safety Net

Backups are your ONLY recovery option after DROP TABLE!

Strategy 1: Snapshots (Instant Recovery)

What are Snapshots?

Snapshots: Hard-link copies of SSTable files (instant, minimal space initially)

✅ Instant creation: Takes seconds (hardlinks, not copies)
✅ Minimal initial space: Uses hardlinks until data changes
✅ Fast recovery: 15-60 minutes for most tables
⚠️ Limitation: Stored on same node (not offsite)

-- Take snapshot of specific table
$ nodetool snapshot my_keyspace -t before_drop_user_sessions

-- Output:
-- Requested creating snapshot(s) for [my_keyspace]
-- Snapshot directory: before_drop_user_sessions

-- Take snapshot of entire keyspace
$ nodetool snapshot my_keyspace -t daily_backup_20240115

-- List all snapshots
$ nodetool listsnapshots

-- Example output:
-- Snapshot name    Keyspace   Table           Size    Created
-- before_drop      my_app     user_sessions   2.1 GB  2024-01-15 14:47:00

-- Delete old snapshots (to free space)
$ nodetool clearsnapshot my_keyspace -t old_snapshot_name

-- Delete ALL snapshots (DANGEROUS!)
$ nodetool clearsnapshot
                        

Strategy 2: Automated Snapshots (Cron Jobs)

#!/bin/bash
# save as: /usr/local/bin/cassandra_snapshot.sh

KEYSPACE="production_app"
DATE=$(date +"%Y%m%d_%H%M")
SNAPSHOT_NAME="auto_${DATE}"

# Take snapshot
/usr/bin/nodetool snapshot "$KEYSPACE" -t "$SNAPSHOT_NAME"

# Delete snapshots older than 7 days
find /var/lib/cassandra/data/"$KEYSPACE"/*/snapshots/* \
  -type d -mtime +7 -exec rm -rf {} \;

# Log result
echo "$(date): Snapshot $SNAPSHOT_NAME created" >> /var/log/cassandra_snapshots.log

# Add to crontab:
# Run every 6 hours
# 0 */6 * * * /usr/local/bin/cassandra_snapshot.sh

# Run every 2 hours (more frequent for critical data)
# 0 */2 * * * /usr/local/bin/cassandra_snapshot.sh
                        

Strategy 3: Off-Site Backups (S3/Cloud)

#!/bin/bash
# Upload snapshots to S3 for disaster recovery

KEYSPACE="production_app"
DATE=$(date +"%Y%m%d")
BUCKET="s3://my-company-cassandra-backups"

# Take snapshot first
nodetool snapshot "$KEYSPACE" -t "s3_backup_${DATE}"

# Find snapshot directory
SNAPSHOT_DIR=$(find /var/lib/cassandra/data/"$KEYSPACE" \
  -name "s3_backup_${DATE}" -type d)

# Upload to S3 (compress first)
tar -czf /tmp/cassandra_backup_"${DATE}".tar.gz "$SNAPSHOT_DIR"
aws s3 cp /tmp/cassandra_backup_"${DATE}".tar.gz \
  "$BUCKET"/"$KEYSPACE"/

# Clean up local compressed file
rm /tmp/cassandra_backup_"${DATE}".tar.gz

# Schedule: Daily at 2 AM
# 0 2 * * * /usr/local/bin/s3_backup.sh
                        

⚡

Snapshots (Local)

Pros:

✅ Instant creation (seconds)
✅ Fast recovery (15-60 min)
✅ Minimal initial space
✅ Free (built-in)

Cons:

❌ Same node (not offsite)
❌ Lost if disk fails
❌ Manual cleanup needed

Best for: Quick recovery, immediate undo

☁️

S3 Backups (Cloud)

Pros:

✅ Off-site protection
✅ Datacenter failure safe
✅ Long-term retention
✅ Automatic lifecycle

Cons:

❌ Slower recovery (hours)
❌ Cost ($0.023/GB/month)
❌ Network transfer time

Best for: Disaster recovery, compliance

🔄

Multi-DC Replication

Pros:

✅ Instant failover
✅ Always synced
✅ Geographic distribution
✅ Best protection

Cons:

❌ Expensive (2-3x cost)
❌ Complex setup
❌ Network latency

Best for: Mission-critical data

🏢 Production Strategy (Netflix Example)

Netflix's Multi-Layer Backup Strategy:

Local Snapshots: Every 6 hours (48-hour retention)
S3 Daily Backups: Full backup to S3 (30-day retention)
Glacier Monthly: Archive to Glacier (7-year retention)
Multi-DC: RF=3 in 4 datacenters (real-time replication)

Monthly Cost: ~$150,000

ROI: Saved $50M+ in 2022 datacenter outage

Lesson: Multiple backup layers = Sleep well at night! 😴

🖥️ Interactive DROP TABLE Simulator

Practice safely - see what happens when you DROP a table!

Safe DROP TABLE Simulator

⚠️ Enter DROP TABLE Command:

🎮 Safe DROP TABLE Simulator Ready!

This simulator shows you what happens when you execute DROP TABLE.
No real data will be deleted!

Try the examples:
• Safe Drop: Test table with snapshot
• Dangerous Drop: Production table simulation
• Wrong Table: Accidental production drop

🔧 Emergency Recovery Guide

What to do when disaster strikes!

🚨 IMMEDIATE ACTIONS (First 60 Seconds)

DON'T PANIC - Panic makes mistakes worse
Alert Team IMMEDIATELY - Post in Slack: "URGENT: Accidentally dropped production table [name]"
Stop Compaction - Run: nodetool stop COMPACTION
Stop Applications - Prevent failed queries from flooding logs
Document Timeline - Write down: Time dropped, table name, keyspace, cluster

Recovery Option 1: Snapshot Restore (15-60 minutes)

When to Use

Best option if: You have recent snapshot (< 24 hours old)

Success rate: 95%+ if snapshot exists

Data loss: Only data added since snapshot

-- Step 1: Find available snapshots
$ nodetool listsnapshots | grep user_sessions

-- Output example:
-- before_drop  my_keyspace  user_sessions  2.1 GB  2024-01-15 14:47:00

-- Step 2: Recreate table schema (CRITICAL!)
-- Get schema from backup or memory
cqlsh> CREATE TABLE user_sessions (
    user_id UUID,
    session_id TEXT,
    created_at TIMESTAMP,
    PRIMARY KEY (user_id, created_at)
);

-- Step 3: Find snapshot location on each node
$ find /var/lib/cassandra/data/my_keyspace/user_sessions-*/snapshots/before_drop

-- Example path:
-- /var/lib/cassandra/data/my_keyspace/user_sessions-a1b2c3d4/snapshots/before_drop/

-- Step 4: Copy snapshot data back to table directory
$ cp -r /var/lib/cassandra/data/my_keyspace/user_sessions-*/snapshots/before_drop/* \
       /var/lib/cassandra/data/my_keyspace/user_sessions-*/

-- Step 5: Refresh table (make Cassandra aware of new files)
$ nodetool refresh my_keyspace user_sessions

-- Step 6: Verify data is back
cqlsh> SELECT COUNT(*) FROM user_sessions LIMIT 100000;
cqlsh> SELECT * FROM user_sessions LIMIT 10;

-- Step 7: Run repair to ensure consistency
$ nodetool repair my_keyspace user_sessions
                        

Recovery Option 2: SSTable Resurrection (5-30 minutes)

When to Use

Emergency option if: NO snapshot, but DROP was < 5 minutes ago

Success rate: 60-80% if caught within 5 minutes

Requirements: Act IMMEDIATELY, files still on disk

-- EMERGENCY PROCEDURE (5-minute window!)

-- Step 1: IMMEDIATELY stop compaction (first 30 seconds!)
$ nodetool stop COMPACTION

-- Step 2: Find SSTable files (they might still exist!)
$ find /var/lib/cassandra/data/my_keyspace -name "*user_sessions*" -type f -mmin -5

-- Example output (if lucky!):
-- /var/lib/cassandra/data/my_keyspace/user_sessions.../na-1-big-Data.db
-- /var/lib/cassandra/data/my_keyspace/user_sessions.../na-1-big-Index.db

-- Step 3: Copy files to SAFE LOCATION immediately
$ mkdir /tmp/emergency_recovery
$ cp /var/lib/cassandra/data/my_keyspace/user_sessions.*/* /tmp/emergency_recovery/

-- Step 4: Recreate table schema
cqlsh> CREATE TABLE user_sessions (...);

-- Step 5: Copy files back to new table directory
$ cp /tmp/emergency_recovery/* \
     /var/lib/cassandra/data/my_keyspace/user_sessions-/

-- Step 6: Refresh and verify
$ nodetool refresh my_keyspace user_sessions
cqlsh> SELECT COUNT(*) FROM user_sessions LIMIT 100000;
                        

Recovery Option 3: S3/Backup Restore (2-8 hours)

When to Use

Last resort if: No snapshot, SSTables gone, but have S3 backup

Success rate: 100% (if backup exists and is recent)

Data loss: Everything since last backup

-- Step 1: Find most recent backup in S3
$ aws s3 ls s3://my-backups/cassandra/my_keyspace/ --recursive | grep user_sessions

-- Step 2: Download backup
$ aws s3 cp s3://my-backups/cassandra/my_keyspace/user_sessions_20240115.tar.gz /tmp/

-- Step 3: Extract backup
$ tar -xzf /tmp/user_sessions_20240115.tar.gz -C /tmp/restore/

-- Step 4: Recreate table schema
cqlsh> CREATE TABLE user_sessions (...);

-- Step 5: Use sstableloader to restore data
$ sstableloader -d 10.0.0.1 /tmp/restore/my_keyspace/user_sessions/

-- Step 6: Run repair across all nodes
$ nodetool repair -full my_keyspace user_sessions

-- Step 7: Verify data
cqlsh> SELECT COUNT(*) FROM user_sessions LIMIT 100000;
                        

📋 Post-Recovery Checklist

Verify Data Completeness
- Check row count matches expected
- Verify recent data exists (or note gap)
- Test critical queries
Check Data Consistency
- Run: nodetool repair my_keyspace user_sessions
- Verify all replicas have data
Resume Applications
- Start services one by one
- Monitor error logs closely
- Test critical user journeys
Document Incident
- What happened, when, why
- Impact: users affected, downtime duration
- Recovery steps taken
- Data loss (if any)
Implement Preventive Measures
- Add terminal color coding
- Create confirmation wrapper script
- Increase snapshot frequency
- Schedule recovery drill
Notify Stakeholders
- Send post-mortem report
- Apologize to affected users
- Share lessons learned with team

🔄 Safer Alternatives to DROP TABLE

Think twice - do you REALLY need to DROP the table?

🔒

1. Disable Access

-- Revoke all permissions
REVOKE ALL ON TABLE old_table
FROM app_user;

-- Rename to "deprecated"
-- (can't rename in Cassandra,
-- but document as deprecated)
                                

Benefits:

✅ Reversible instantly
✅ No data loss
✅ Can restore if needed

Best for: Uncertain if table is still used

📦

2. Archive First

-- Export to CSV
COPY old_table 
TO '/backup/old_table.csv';

-- Or use sstable2json
$ sstable2json data.db > backup.json

-- Upload to S3
$ aws s3 cp backup.csv \
  s3://archives/old_table/
                                

Benefits:

✅ Historical record preserved
✅ Compliance requirements met
✅ Can analyze later

Best for: Legal/compliance retention

⏰

3. TTL Cleanup

-- Set TTL on all existing data
UPDATE old_table 
USING TTL 2592000  
-- 30 days
SET dummy = dummy;

-- Data expires automatically
-- After 30 days, DROP safely
                                

Benefits:

✅ Gradual expiration
✅ 30-day grace period
✅ Automatic cleanup

Best for: Cautious deprecation

🗑️

4. TRUNCATE Instead

-- Remove all data, keep schema
TRUNCATE TABLE old_table;

-- Schema still exists!
-- Can insert data again later
                                

Benefits:

✅ Schema preserved
✅ Fast (marks for deletion)
✅ Can repopulate later

Best for: Clearing test data

⚠️ Warning: TRUNCATE is also irreversible! Take snapshot first!

🏆 The 30-Day Rule (Professional Approach)

Week 1 (Days 1-7):

Document table as "DEPRECATED" in wiki
Revoke write permissions (read-only)
Announce in team channels
Monitor access logs

Week 2 (Days 8-14):

Remove from application configs
Update documentation (mark as deprecated)
Check if any queries still accessing

Week 3 (Days 15-21):

Take final snapshot/archive
Export data to CSV/S3 (if needed)
Final team notification

Week 4 (Days 22-30):

Monitor for unexpected access
Verify no one objects
If all clear: DROP on Day 30

Advantage: If someone objects, simply keep the table!
No emergency recovery needed! 🎉

⭐ Best Practices: Production Safety

Learn from teams that have DROP TABLE policies that work!

✅

DO's

ALWAYS take snapshot first (no exceptions!)
Use IF EXISTS for safety
Verify cluster name before ANY DROP
Get approval for production (manager + change request)
Notify team in Slack before dropping
Use terminal colors (red=prod, green=dev)
Test in staging first with same command
Have recovery plan ready before dropping
Document the action (why, when, approval)
Drop during maintenance windows (not peak hours)

❌

DON'Ts

Never DROP without snapshot (EVER!)
Don't DROP on Friday (or before vacation)
Don't DROP when tired (3 AM = danger zone)
Don't rely on memory (verify table name)
Don't skip checklist (even if "just a test table")
Don't use automation for DROP (manual only!)
Don't give everyone DROP rights (limit to 2-3 people)
Don't DROP without telling anyone (team awareness)
Don't assume backup works (verify before DROP)
Don't work alone on production (pair for DROP)

🛡️

Safety Features

Custom shell function with confirmation
RBAC (role-based access control)
Audit logging (who dropped what when)
Monitoring alerts on schema changes
Change management (JIRA tickets required)
Peer review (screen share for prod)
Automated snapshots (every 2-6 hours)
Off-site backups (S3 daily)
Recovery drills (practice quarterly)
Terminal indicators (clear prod warning)

Enterprise DROP TABLE Safety Script

#!/bin/bash # safe_drop_table.sh - Production-grade DROP TABLE wrapper # Usage: ./safe_drop_table.sh keyspace_name table_name KEYSPACE=$1 TABLE=$2 DATE=$(date +"%Y%m%d_%H%M%S") # Validate arguments if [ -z "$KEYSPACE" ] || [ -z "$TABLE" ]; then echo "Usage: $0 "exit 1 fi# Check cluster nameCLUSTER=$(cqlsh -e "SELECT cluster_name FROM system.local;" | grep -v cluster_name | tr -d ' ') echo"⚠️ Cluster: $CLUSTER"# Production checkif [[ "$CLUSTER" == *"prod"* ]]; thenecho"🚨 WARNING: PRODUCTION CLUSTER!"echo"━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"# Confirmation 1: Type keyspace.tableecho -n "Type keyspace.table to confirm [$KEYSPACE.$TABLE]: "read CONFIRM1 if [ "$CONFIRM1" != "$KEYSPACE.$TABLE" ]; thenecho"❌ Confirmation failed. Aborting."exit 1 fi# Show table infoecho""echo"📊 Table Information:" cqlsh -e "DESCRIBE TABLE $KEYSPACE.$TABLE;"ROW_COUNT=$(cqlsh -e "SELECT COUNT(*) FROM $KEYSPACE.$TABLE LIMIT 100000;" | grep -v count | tr -d ' ') echo"📈 Approximate row count: $ROW_COUNT"echo""# Take snapshotecho"📸 Taking snapshot..."SNAPSHOT_NAME="before_drop_${TABLE}_${DATE}" nodetool snapshot "$KEYSPACE" -t "$SNAPSHOT_NAME"echo"✅ Snapshot created: $SNAPSHOT_NAME"echo""# Confirmation 2: Type "YES I AM ABSOLUTELY SURE"echo"⚠️ FINAL CONFIRMATION"echo"This will PERMANENTLY delete table: $KEYSPACE.$TABLE"echo -n "Type 'YES I AM ABSOLUTELY SURE': "read CONFIRM2 if [ "$CONFIRM2" != "YES I AM ABSOLUTELY SURE" ]; thenecho"❌ Final confirmation failed. Aborting."exit 1 fi# Log the actionecho"$(date): User $USER dropped table $KEYSPACE.$TABLE on cluster $CLUSTER (snapshot: $SNAPSHOT_NAME)" \ >> /var/log/cassandra_drops.log fi# Execute DROPecho""echo"🗑️ Dropping table..." cqlsh -e "DROP TABLE IF EXISTS $KEYSPACE.$TABLE;"if [ $? -eq 0 ]; thenecho"✅ Table dropped successfully"echo""echo"📝 Recovery information:"echo" Snapshot: $SNAPSHOT_NAME"echo" Command: nodetool listsnapshots | grep $SNAPSHOT_NAME"elseecho"❌ DROP failed"exit 1 fi

How Top Companies Do It

Netflix Approach:

Only 2 people have DROP rights in production (VP Engineering + Lead DBA)
Requires JIRA ticket + approval from 2 managers
Must announce in #data-ops channel 24 hours in advance
Automated snapshot taken immediately before DROP
Change window: Saturday 2-6 AM only

Stripe Approach:

Custom wrapper script (similar to above) required for all DROPs
Two-person authorization (initiator + reviewer)
Screen recording mandatory for production DROPs
Quarterly recovery drill (randomly drop table, restore from backup)
Post-DROP verification checklist (15 items)

Airbnb Approach:

30-day deprecation period mandatory (no exceptions)
Table marked as "DEPRECATED" in schema
Access logs monitored for 30 days
If no access → safe to drop
If unexpected access → keep table, investigate

💥 More Real Disaster Stories

Learn from others' expensive mistakes!

Disaster #1: The Copy-Paste Catastrophe

Company: Healthcare SaaS Startup (Series B, $15M funding)

Date: August 8, 2021, 4:23 PM

What Happened:

Engineer was helping a colleague via Slack. Colleague asked: "What's the syntax to drop the test_patients table?"

Engineer typed: DROP TABLE patient_records; as an example (but used REAL table name instead of test name)

Colleague copy-pasted and ran it... IN PRODUCTION

What Was Lost:

Data: 2.8 million patient medical records
Severity: HIPAA violation (protected health information)
Recovery: 7 hours (snapshot from 4 hours ago + reconstruction)
Data Gap: 4 hours of appointments/prescriptions lost permanently

The Cost:

HIPAA Fines: $280,000
Lost Revenue: $95,000 (7 hours downtime)
Recovery Costs: $62,000 (all-hands, forensics)
Legal Fees: $140,000 (patient notifications, lawsuits)
Reputation: $320,000 (67 customers left)
TOTAL: $897,000

What Changed:

Policy: Never share DROP commands in Slack (use pseudo-code only)
All DROP examples must use "example_table" (never real names)
Production access requires VPN + 2FA + approval
Snapshots every 1 hour (was every 4 hours)
Both engineers received training, kept jobs (company blamed process, not people)

Disaster #2: The Cleanup Script Gone Wrong

Company: AdTech Platform (Public, 1200 employees)

Date: November 14, 2022, 11:47 PM

What Happened:

Automated cleanup script runs nightly to drop old test tables (pattern: test_*)

Engineer had created table: latest_campaign_metrics (production table)

Script regex bug: Matched "latest" thinking it was "test" (fuzzy matching enabled)

Script dropped: latest_campaign_metrics at 11:47 PM

What Was Lost:

Data: Real-time campaign metrics for 8,400 advertisers
Impact: Dashboard showed zero metrics (advertisers panicked)
Discovery: 6 AM next morning (6+ hours later)
Recovery: 9 hours (reconstruct from logs + Kafka streams)

The Cost:

Lost Revenue: $520,000 (advertisers paused campaigns)
Recovery: $89,000 (engineering team overtime)
Credits: $340,000 (compensation for downtime)
Customer Churn: $1.2M (142 customers left permanently)
TOTAL: $2.15 Million

What Changed:

Policy: NEVER automate DROP commands (manual only)
Naming convention enforced: prod_* (production), test_* (test), dev_* (development)
Cleanup scripts use whitelist (not pattern matching)
Dry-run mode mandatory for all automation
Alerts on schema changes (immediate Slack notification)
Cleanup only during business hours (not overnight)

Disaster #3: The Distracted Engineer

Company: E-learning Platform (50 employees)

Date: March 3, 2023, 2:18 PM

What Happened:

Engineer working from home, toddler crying in background

Meant to drop: user_session_temp (test table)

Typed: DROP TABLE user_sessions; (forgot "_temp")

Hit Enter while checking on child, didn't notice mistake

What Was Lost:

Data: 890,000 active learning sessions
Impact: All students kicked out mid-lesson
Discovery: Immediate (support tickets flooded in)
Recovery: 3 hours (snapshot from 2 hours ago)

The Cost:

Lost Revenue: $28,000 (3 hours downtime during peak)
Recovery: $12,000 (team overtime)
Credits: $45,000 (refunds for affected students)
Reputation: $67,000 (bad reviews, trust damage)
TOTAL: $152,000

What Changed:

Policy: No production work during distractions
Custom shell function with confirmation (type table name twice)
Table auto-complete shows all similar names
Production access only allowed during "focus hours" (no meetings/distractions)
Engineer received support (company understood WFH challenges)
Better work-life balance policies implemented

📊 Common Patterns in All Disasters

Human Factors: Fatigue, distraction, stress, mental overload
No Safety Nets: Missing snapshots, weak confirmations, no peer review
Similar Names: user_sessions vs user_session_temp, latest vs test
Process Gaps: No approval workflow, no change management
Automation Risk: Scripts with bugs, pattern matching failures

Total cost across 4 stories: $3.58 Million
Prevention cost: ~$50K/year in tools & processes

Prevention is 70x cheaper! 🎯

💼 Interview Questions & Answers

Master these production-focused questions!

1 You accidentally dropped a production table 2 minutes ago. Walk me through your immediate recovery steps. ▼

Answer (Structured Approach):

First 60 seconds (Critical Window):

DON'T PANIC - Stay calm, think clearly
Alert team immediately - Post in Slack: "URGENT: Dropped table [name] 2min ago"
Stop compaction - Run: nodetool stop COMPACTION
Stop applications - Prevent query flood

Next 5 minutes (Assessment):

Check for snapshots: nodetool listsnapshots | grep table_name
Check SSTable files: find /var/lib/cassandra/data -name "*table_name*" -mmin -5
If files exist: Copy to safe location immediately
Document: Time dropped, table name, row count, dependencies

Next 30 minutes (Recovery):

Best case (snapshot exists):
- Recreate table schema
- Copy snapshot data to table directory
- Run: nodetool refresh keyspace table
- Verify data count
Good case (SSTables found):
- Resurrect SSTables from safe copy
- Same process as snapshot
Last resort (S3 backup):
- Download latest backup
- Use sstableloader to restore
- Run full repair

After Recovery:

Verify data completeness
Run consistency check
Resume applications gradually
Document incident & lessons learned
Implement preventive measures

Key Point: Speed matters! The first 5 minutes determine success rate (80% if fast, 20% if slow).

2 What's the difference between DROP TABLE, TRUNCATE, and DELETE FROM in terms of recovery? ▼

Answer:

DROP TABLE:

What it does: Deletes schema + all data
Reversible? NO (only from backup/snapshot)
Schema preserved? NO (must recreate)
Speed: ~100ms (instant)
Recovery: Snapshot restore (15-60 min)
Use case: Removing table permanently

TRUNCATE:

What it does: Deletes all data, keeps schema
Reversible? NO (only from backup/snapshot)
Schema preserved? YES (can insert again)
Speed: ~200ms (marks for deletion)
Recovery: Snapshot restore (10-30 min)
Use case: Clearing all data quickly

DELETE FROM:

What it does: Deletes specific rows (with WHERE)
Reversible? YES (tombstones, before compaction)
Schema preserved? YES
Speed: Varies (depends on row count)
Recovery: Possible within gc_grace_seconds (10 days default)
Use case: Removing specific data

Recovery Comparison:

Command	Recovery Window	Method
DROP TABLE	0-5 min (SSTables)	Snapshot/Backup
TRUNCATE	0-10 min (SSTables)	Snapshot/Backup
DELETE FROM	10 days (gc_grace)	Tombstone recovery

Key Takeaway: DELETE is MUCH safer than TRUNCATE or DROP because you have 10-day grace period!

3 Design a production-safe workflow for dropping tables. What safeguards would you implement? ▼

Answer (Multi-Layer Defense):

Layer 1: Access Control

RBAC: Only 2-3 senior engineers have DROP privileges
Separate roles: app_user (read/write), admin_user (schema changes)
VPN + 2FA: Required for production access
Bastion host: Single controlled entry point

Layer 2: Technical Safeguards

Custom wrapper script: Requires confirmation, takes snapshot
Terminal colors: Red background for production clusters
Shell prompt modification: Shows [PROD] in large red text
Audit logging: Every command logged with user, timestamp, cluster

Layer 3: Process Safeguards

Change management: JIRA ticket required with justification
Approval workflow: Manager + DBA must approve
Team notification: Announce in Slack 24 hours before
Maintenance window: Only Saturday 2-6 AM
Peer review: Screen share with senior engineer

Layer 4: Backup & Recovery

Automated snapshots: Every 2 hours (48-hour retention)
S3 backups: Daily off-site backups (30-day retention)
Multi-DC replication: RF=3 in 3 datacenters
Recovery drills: Quarterly practice (every engineer participates)

Layer 5: Monitoring & Alerts

Schema change alerts: Instant Slack notification on DROP
Application monitoring: Alert on sudden query failures
Audit trail review: Weekly review of all schema changes

Example Workflow:

Engineer creates JIRA ticket explaining why DROP needed
Manager reviews and approves (or rejects)
DBA reviews and confirms table is safe to drop
24-hour announcement in Slack (team can object)
Wait for maintenance window (Saturday 2 AM)
Take manual snapshot just before DROP
Screen share with senior engineer
Execute using custom wrapper script (with confirmations)
Verify DROP completed successfully
Monitor applications for 1 hour
Document in runbook

Defense-in-depth principle: If one layer fails, others catch the mistake!

4 You need to drop 50 old test tables. How would you approach this safely? ▼

Answer (Safe Bulk Operation):

Step 1: Identify Tables (Carefully!)

-- Get list of all test tables
cqlsh> DESCRIBE TABLES;

-- Filter for test tables (manually verify!)
-- Look for patterns: test_*, temp_*, old_*, dev_*

-- Create explicit list (NOT pattern matching!)
test_user_data
test_orders_jan
test_sessions_old
...
                                        

Step 2: Verify EACH Table

-- For EACH table, check:
DESCRIBE TABLE test_user_data;
SELECT COUNT(*) FROM test_user_data LIMIT 1000;

-- Verify it's actually a test table:
-- 1. Check creation date (recent?)
-- 2. Check data volume (small?)
-- 3. Ask team (anyone using this?)
                                        

Step 3: Create Whitelist Script (SAFE)

#!/bin/bash
# drop_test_tables.sh - WHITELIST approach (explicit list)

# WHITELIST of tables to drop (explicit, no patterns!)
TABLES_TO_DROP="
test_user_data
test_orders_jan
test_sessions_old
"

KEYSPACE="test_app"

# Dry run first (show what would be dropped)
DRY_RUN=true

for table in $TABLES_TO_DROP; do
    if [ "$DRY_RUN" = true ]; then
        echo "[DRY RUN] Would drop: $KEYSPACE.$table"
        
        # Show table info
        cqlsh -e "DESCRIBE TABLE $KEYSPACE.$table;"
        cqlsh -e "SELECT COUNT(*) FROM $KEYSPACE.$table LIMIT 1000;"
    else
        echo "Dropping: $KEYSPACE.$table"
        
        # Take snapshot
        nodetool snapshot "$KEYSPACE" -t "bulk_cleanup_$(date +%Y%m%d)"
        
        # Drop table
        cqlsh -e "DROP TABLE IF EXISTS $KEYSPACE.$table;"
        
        echo "✓ Dropped $table"
        
        # Pause between drops (be gentle on cluster)
        sleep 5
    fi
done

echo "Done!"
                                        

Step 4: Execute Safely

Run dry-run first: DRY_RUN=true ./drop_test_tables.sh
Review output carefully: Check each table listed
Get team approval: Share dry-run output in Slack
Take full snapshot: nodetool snapshot test_app
Execute during off-hours: Saturday 2 AM
Run actual script: DRY_RUN=false ./drop_test_tables.sh
Monitor cluster health: Watch nodetool status
Verify tables gone: DESCRIBE TABLES;

What NOT to Do:

❌ Pattern matching: DROP TABLE test_* (DANGEROUS!)
❌ Regex/wildcards: Can match unintended tables
❌ No verification: Drop without checking each table
❌ All at once: Drop 50 tables simultaneously (cluster stress)
❌ Peak hours: Drop during business hours

Key principle: Whitelist (explicit) is MUCH safer than blacklist (pattern)!

5 Explain the trade-offs between different backup strategies for DROP TABLE recovery. ▼

Answer (Comprehensive Analysis):

Strategy 1: Local Snapshots

Aspect	Details
Recovery Time	15-60 minutes (FAST)
Cost	Free (built-in)
Protection	Local only (same disk)
Data Loss	Since last snapshot (hours)
Disk Usage	Grows over time (manual cleanup)

Best for: Quick recovery from human error (accidental DROP)

Strategy 2: S3/Cloud Backups

Aspect	Details
Recovery Time	2-8 hours (depends on size)
Cost	$0.023/GB/month (S3)
Protection	Off-site (datacenter failure safe)
Data Loss	Since last backup (1 day typically)
Disk Usage	Off-site (no local impact)

Best for: Disaster recovery, compliance, long-term retention

Strategy 3: Multi-DC Replication

Aspect	Details
Recovery Time	Instant (failover)
Cost	High (2-3x infrastructure)
Protection	Geographic distribution
Data Loss	Zero (always synced)
Complexity	High (cross-DC latency)

Best for: Mission-critical data, zero-downtime requirements

Hybrid Strategy (Recommended):

Tier 1 (Immediate): Local snapshots every 2 hours
- Fast recovery for accidental drops
- Max data loss: 2 hours
Tier 2 (Daily): S3 backups at 2 AM
- Disaster recovery capability
- 30-day retention for compliance
Tier 3 (Real-time): Multi-DC for critical tables only
- User accounts, payment data, etc.
- Zero data loss for critical operations

Example: E-commerce Platform (1TB data)

Strategy	Monthly Cost	Covers
Snapshots (2hr)	$0 (disk space)	Accidental DROP
S3 Daily	~$23 (1TB × $0.023)	Datacenter failure
Multi-DC (critical)	~$5000 (100GB critical × 2 DCs)	Zero downtime
Total	~$5,023/month	Comprehensive coverage

Key Insight: Combine strategies for cost-effective, comprehensive protection!

Responsive Ad