What you will learn
- OLTP vs OLAP — why your production database is the wrong tool for analytics
- Amazon Redshift — columnar storage and why it makes analytical queries fast
- Redshift Serverless vs provisioned clusters — when each makes sense
- Loading data into Redshift — the COPY command and why it beats INSERT
- Redshift Spectrum — querying S3 data directly without loading it
- Amazon EMR — managed Hadoop and Spark for big data processing
- EMR node types — Master, Core, and Task nodes and their roles
- Spot Instances with EMR for 90% cost savings on Task nodes
- Amazon QuickSight — serverless BI dashboards without managing BI servers
- SPICE — QuickSight's in-memory engine for fast dashboard queries
- QuickSight Column-Level Security for data access control
Why this matters
Swiggy generates millions of order events, delivery events, and user interactions every day. Running complex analytics on the production RDS database would slow it to a crawl. Redshift is a separate analytical database built for exactly this — query billions of rows in seconds. Razorpay processes billions of transactions — EMR Spark jobs aggregate them into daily summary tables. At Hotstar, the business team builds content performance dashboards in QuickSight without writing a line of code or waiting for the engineering team. These three services together form the analytics layer that turns raw data into business decisions.
OLTP vs OLAP — Why You Need a Separate Database
OLTP (Online Transaction Processing) — your production database: Handles thousands of small transactions per second INSERT, UPDATE, DELETE individual records Optimised for write performance and data integrity Examples: RDS, Aurora, DynamoDB Row-based storage — reads entire rows OLAP (Online Analytical Processing) — your analytics database: Handles a few very large queries SELECT with GROUP BY, SUM, AVG across millions of rows Optimised for read performance on large datasets Example: Redshift Columnar storage — reads only the columns the query needsRunning analytics on your production OLTP database:
SELECT city, COUNT(*), SUM(amount) FROM orders GROUP BY cityOn RDS with 500 million rows → full table scan → minutes to completeDuring this scan → production writes slow down → users experience lagSolution: copy data to Redshift. Run analytics there. Production database untouched.
Amazon Redshift — Columnar Data Warehouse
Redshift stores data in columns instead of rows. This makes analytical queries dramatically faster.
Row storage (how RDS stores data): Row 1: Rahul | Mumbai | 450 | 2024-01-15 | delivered Row 2: Priya | Pune | 120 | 2024-01-15 | pending Row 3: Arjun | Chennai | 890 | 2024-01-16 | delivered Query: SELECT SUM(amount) FROM orders Must read: all columns of all rows (even city, date, status you don't need) Columnar storage (how Redshift stores data): Column: Rahul, Priya, Arjun... (names — not needed for this query) Column: Mumbai, Pune, Chennai... (cities — not needed) Column: 450, 120, 890... (amounts — ONLY this column is read) Column: 2024-01-15, ... Query: SELECT SUM(amount) FROM orders Reads: ONLY the amount column → 10-100x less data scanned → 10-100x fasterRedshift cluster architecture:
Leader Node: Receives SQL queries from clients Develops query execution plan Coordinates Compute Nodes Returns results to client Compute Nodes (1 to 128): Store the actual data Execute query fragments in parallel Each node stores a slice of the data More Compute Nodes = more parallelism = faster queriesRedshift Serverless:
No cluster to manage. Redshift scales automatically based on query demand.
Provisioned cluster: You choose node type and count upfront Pay 24/7 for the cluster whether or not you are running queries Good for: predictable, continuous analytics workloads Serverless: No cluster — just a namespace and workgroup Scales automatically Pay per second of compute used Good for: intermittent analytics, dev/test, unknown workloadLoading Data Into Redshift — COPY vs INSERT
Never use INSERT for bulk loading.
INSERT one row at a time → 1 million rows → 1 million round trips → very slow COPY command → bulk loads from S3 in parallel across all Compute NodesAll nodes download their slice simultaneously → orders of magnitude faster-- Load data from S3 into Redshift using COPYCOPY ordersFROM 's3://devops-analytics/orders/2024/01/'IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Role'FORMAT AS PARQUET;-- Parquet is the best format for Redshift loading-- All Compute Nodes load their slice in parallelRedshift Spectrum — query S3 without loading:
Create an external table in Redshift that points to S3. Query it with SQL. Data never moves into Redshift.
-- Create external schema pointing to Glue Data CatalogCREATE EXTERNAL SCHEMA spectrum_schemaFROM DATA CATALOGDATABASE 'devops_analytics'IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'CREATE EXTERNAL DATABASE IF NOT EXISTS; -- Query S3 data directly from RedshiftSELECT city, COUNT(*) as ordersFROM spectrum_schema.ordersWHERE year = '2024' AND month = '01'GROUP BY city;Spectrum queries S3 data at $5.00 per TB scanned — same as Athena. Useful when you want S3 as your data lake but Redshift as your query interface.
Amazon EMR — Big Data Processing
EMR (Elastic MapReduce) is managed Hadoop, Spark, Hive, Presto, and other big data frameworks. Instead of provisioning and managing a Hadoop cluster yourself, EMR does it for you.
Without EMR: Provision EC2 instances Install Java, Hadoop, configure HDFS Configure Spark, Yarn, Hive Manage updates, failures, scaling Lots of infrastructure work before writing any data code With EMR: Select framework (Spark, Hadoop, etc) Choose instance types and count Submit your Spark job EMR handles everything elseThree EMR node types:
Master Node (1 required): Manages the cluster Coordinates job distribution Tracks task progress Do NOT terminate — losing it terminates the cluster Core Nodes (1+ required): Run tasks AND store data in HDFS Can be scaled — but shrinking risks data loss if not careful Task Nodes (optional, 0+): Run tasks ONLY — no HDFS storage Can be added or removed safely at any time Perfect for Spot Instances — if Spot is interrupted, no data is lostSpot Instances on Task Nodes:
Master Node: On-Demand (critical — cannot be interrupted)Core Nodes: On-Demand or Reserved (store data — interruption is risky)Task Nodes: Spot Instances (no data storage — interruption is safe) Spot Instances: up to 90% cheaper than On-DemandA Spark job using 20 Task Nodes on Spot saves 80-90% of compute costEMR with S3 as storage:
Instead of HDFS, store data in S3. EMR reads from and writes to S3. Cluster can be terminated when the job finishes — data persists in S3.
Transient cluster pattern: Job arrives → spin up EMR cluster → process data in S3 → write results to S3 Job finishes → terminate cluster → pay only for job durationThis is the most cost-effective EMR pattern for batch workloads.
Amazon QuickSight — Serverless BI Dashboards
QuickSight is AWS's business intelligence service. Non-technical business users can build interactive charts and dashboards from data in S3, Redshift, RDS, Athena, and other sources — without writing code or waiting for the engineering team.
No servers to manage. Scales automatically. Pay per user per month.
Data sources QuickSight connects to:
AWS: Athena, Redshift, S3, RDS, Aurora, DynamoDB, OpenSearchSaaS: Salesforce, ServiceNow, TwitterOn-premises: any database via JDBCSPICE — in-memory analytics engine:
SPICE stands for Super-fast, Parallel, In-memory Calculation Engine. When you import data into QuickSight, it loads into SPICE.
Without SPICE: Every dashboard interaction → query Redshift → wait for response Dashboard is slow for large datasets With SPICE: Data loaded into memory once Every dashboard interaction → query SPICE → sub-second response Redshift is not touched during normal dashboard useSPICE capacity: 10 GB per user included, can purchase more.
Column-Level Security:
Control which columns different users can see in a dataset.
Finance team dashboard: sees all columns including revenue and marginSales team dashboard: same dataset, revenue visible, margin column hiddenOne dataset. Different views for different teams. Enforced by QuickSight — no separate datasets needed.
Row-Level Security:
Each user sees only rows relevant to them.
Arjun (Mumbai region manager): only sees Mumbai dataPriya (Bangalore region manager): only sees Bangalore dataSame dashboard. Filtered automatically by user identity.Hands-on Lab — Redshift Serverless and QuickSight
Step 1 — Create Redshift Serverless
Redshift → Serverless → Get startedNamespace name: devops-namespaceWorkgroup name: devops-workgroupAdmin username: adminAdmin password: set a strong passwordVPC: your VPC Subnets: select private subnetsCreate Wait for Status: Available (3-5 minutes)Step 2 — Connect and create a table
Redshift → Query editor v2 → Connect to devops-workgroup-- Create a sample tableCREATE TABLE orders ( order_id VARCHAR(20), user_id VARCHAR(20), amount DECIMAL(10,2), city VARCHAR(50), status VARCHAR(20), order_date DATE); -- Insert sample dataINSERT INTO orders VALUES('ORD-001', 'user-101', 450.00, 'Mumbai', 'delivered', '2024-01-15'),('ORD-002', 'user-102', 1200.00, 'Pune', 'delivered', '2024-01-15'),('ORD-003', 'user-101', 89.00, 'Mumbai', 'pending', '2024-01-16'),('ORD-004', 'user-103', 2500.00, 'Bangalore', 'delivered', '2024-01-16'),('ORD-005', 'user-102', 340.00, 'Pune', 'cancelled', '2024-01-17'); -- Run analytical querySELECT city, COUNT(*) as orders, SUM(amount) as revenueFROM ordersGROUP BY cityORDER BY revenue DESC;Step 3 — Connect QuickSight to Redshift
QuickSight → Sign up for QuickSight (Standard edition — free trial)Datasets → New dataset → Redshift (Manual connect)Enter: workgroup endpoint, database name, credentialsConnect → select orders table → Import to SPICE → VisualizeStep 4 — Create a dashboard
QuickSight visual editor:Visual type: Bar chartX axis: cityValue: SUM(amount)Create → see revenue by city chart Add another visual:Donut chart → orders by statusPublish dashboard → share with your teamStep 5 — Cleanup
QuickSight → Datasets → delete datasetRedshift → Serverless → delete workgroup and namespaceCommon Mistakes to Avoid
Common MistakeRunning analytical GROUP BY queries on production RDS. A query scanning 500 million rows locks I/O and slows down every user on the production database. Analytical queries belong in Redshift. Move data there using AWS Glue or COPY from S3.
Common MistakeUsing INSERT to load data into Redshift. INSERT loads one row at a time. For thousands of rows this is acceptable. For millions of rows it takes hours. Always use COPY from S3 for bulk loading — it parallelises across all Compute Nodes and is orders of magnitude faster.
TipThe most cost-effective EMR pattern for batch analytics is transient clusters. Spin up the cluster when a job arrives, process data reading from S3 and writing results back to S3, then terminate the cluster when done. You pay only for the minutes the cluster ran. Use Spot Instances for Task Nodes to reduce that cost by 70-80% further.
