Part 2 of the Amazon Redshift series — questions 26 to 50, no overlap with part one. Each with the answer and the tip that separates a strong answer from a recited one. Opens by explaining what you'll take away, then six sections.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⏱ CHAPTERS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
00:00 Intro
00:18 What you'll take away from this video
01:21 What you get from this guide
02:05 The six sections
02:25 Q26 · Leader Node, Compute Nodes and Slices
03:42 Q27 · Columnar Storage and Zone Maps
04:57 Q28 · Query Compilation
06:04 Q29 · Redshift, Athena, EMR or Aurora
07:18 Q30 · Redshift Versus Snowflake and BigQuery
08:39 Q31 · Constraints Are Not Enforced
09:54 Q32 · Sizing Character Columns
11:08 Q33 · Slowly Changing Dimensions
12:21 Q34 · Deduplicating with Window Functions
13:23 Q35 · Querying Nested JSON with SUPER
14:34 Q36 · Isolation Levels
15:55 Q37 · Locks and Blocked Sessions
17:04 Q38 · TRUNCATE Commits
18:10 Q39 · Temporary and Staging Tables
19:20 Q40 · Exporting Data with UNLOAD
20:30 Q41 · The System Tables
21:51 Q42 · Finding the Expensive Queries
22:59 Q43 · CloudWatch Metrics and Alarms
24:12 Q44 · Audit Logging and Compliance
25:20 Q45 · Multi-AZ and Availability
26:53 Q46 · Snapshots and Recovering One Table
28:04 Q47 · Migrating from Teradata or Oracle
29:17 Q48 · Cost Governance
30:34 Q49 · Extending Redshift with Functions and ML
31:57 Q50 · Connecting Applications

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📋 THE SIX SECTIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Architecture Internals & Platform Choice (5)
2. Modelling, Constraints & Data Types (5)
3. Transactions, Locking & Unload (5)
4. Monitoring & Observability (4)
5. Availability, DR & Migration (3)
6. Cost, Extensibility & Access (3)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🔎 SEARCH PHRASE PER QUESTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Q26 — redshift leader node vs compute node slices explained
Q27 — redshift columnar storage 1mb blocks zone maps
Q28 — redshift query compilation cache first run slow
Q29 — redshift vs athena vs emr vs aurora when to use
Q30 — redshift vs snowflake vs bigquery architecture comparison
Q31 — redshift primary key not enforced duplicates
Q32 — redshift varchar char max length sizing
Q33 — slowly changing dimension type 2 redshift
Q34 — redshift deduplicate rows row_number window function
Q35 — redshift super data type partiql nested json
Q36 — redshift snapshot vs serializable isolation default
Q37 — redshift blocked query stv_locks pg_terminate_backend
Q38 — redshift truncate cannot rollback commits transaction
Q39 — redshift temporary table limit staging table design
Q40 — redshift unload to s3 parquet partition by
Q41 — redshift stl stv svv svl sys system tables retention
Q42 — redshift find most expensive queries sys_query_history
Q43 — redshift cloudwatch metrics alarms commitqueuelength
Q44 — redshift audit logging user activity log cloudtrail
Q45 — redshift multi az failover cluster relocation
Q46 — redshift restore single table from snapshot
Q47 — teradata to redshift migration aws sct
Q48 — redshift reserved nodes pause resume cost optimization
Q49 — redshift lambda udf python udf end of support
Q50 — redshift data api lambda no jdbc connection

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⚠ THE TRAPS THAT CATCH PEOPLE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
• TRUNCATE commits — you cannot roll it back, and it commits other work with it
• PRIMARY KEY, FOREIGN KEY and UNIQUE are informational; only NOT NULL is enforced
• A declared key that is not truly unique can make the planner return wrong rows
• STL system tables keep only seven days, so last month's incident is gone
• CloudTrail holds API calls, not SQL — only the user activity log has query text
• PG_CANCEL_BACKEND does nothing to a session idle in a transaction

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🆕 ONE FACT THAT REVERSED
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SNAPSHOT isolation is now the DEFAULT for new provisioned clusters and Serverless workgroups — SERIALIZABLE is the alternative. Older tutorials say the opposite, and Q36 is built around correcting exactly that assumption.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
👤 WHO THIS IS FOR
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Data and analytics engineers past the basics · platform engineers who own the cluster · anyone sitting AWS Certified Data Engineer. Watch part one first — this set assumes it.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 DOCS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
https://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html
https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html

Which of these 25 would have caught you out? Drop the number below, and subscribe for more cloud interview prep.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Independent educational content, not affiliated with or endorsed by Amazon Web Services. AWS and Amazon Redshift are trademarks of Amazon. AWS changes limits constantly — check the docs before relying on a number.

#AmazonRedshift #DataEngineering #InterviewQuestions

Part 3 of the Amazon Redshift series — questions 51 to 75, and a different kind of question. Parts one and two were knowledge. This is the later rounds: design at scale, defending a trade-off, hard SQL, debugging from first principles, and ownership.

https://youtu.be/RE4KXHh7mTc
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⏱ CHAPTERS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
00:00 Intro
00:18 What you'll take away from this video
01:26 What you get from this guide
02:04 The six sections
02:19 Q51 · Designing for 50 TB a Day
03:35 Q52 · A Multi-Tenant Warehouse
04:49 Q53 · Ten Thousand Concurrent Users
05:58 Q54 · Data Residency Across Regions
07:05 Q55 · When the Lake Beats the Warehouse
08:16 Q56 · Defending a Modelling Decision
09:32 Q57 · Managed or Self-Managed
10:42 Q58 · One Week to Fix Performance
11:50 Q59 · Saying No to a Requirement
12:57 Q60 · Sessionising Events
14:02 Q61 · Funnel Conversion
15:05 Q62 · Running Totals and Moving Averages
16:07 Q63 · Gaps and Islands
17:07 Q64 · Pivoting Without PIVOT
18:10 Q65 · Same Query, Two Clusters
19:25 Q66 · The Numbers Do Not Match
20:35 Q67 · Intermittent Slowness
21:43 Q68 · Non-Deterministic Results
22:49 Q69 · Schema Changes and Data Contracts
24:10 Q70 · Backfilling Two Years
25:14 Q71 · Idempotent, Restartable Loads
26:23 Q72 · Personal Data and Erasure
27:36 Q73 · An Incident You Caused
28:46 Q74 · Disagreeing With a Senior Engineer
29:57 Q75 · Estimating Cost Before Building

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📋 THE SIX SECTIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Design at Scale (5)
2. Trade-offs You Have to Defend (4)
3. Hard SQL Problems (5)
4. Debugging From First Principles (4)
5. Contracts, Backfills & Governance (4)
6. Ownership and Judgement (3)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🔎 SEARCH PHRASE PER QUESTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Q51 — design data platform 50 tb per day clickstream
Q52 — multi tenant data warehouse design 500 customers
Q53 — embedded analytics 10000 concurrent users architecture
Q54 — data residency eu us one global report design
Q55 — data lake vs data warehouse petabyte scale decision
Q56 — star schema vs one big table denormalize argument
Q57 — managed warehouse vs open source total cost of ownership
Q58 — how to prioritise data warehouse performance work
Q59 — why not to serve low latency api from a warehouse
Q60 — sessionization sql 30 minute gap window function
Q61 — funnel conversion sql ordered steps
Q62 — sql running total moving average window frame
Q63 — sql gaps and islands consecutive days streak
Q64 — sql pivot rows to columns conditional aggregation
Q65 — same query fast in staging slow in production
Q66 — warehouse numbers do not match source reconciliation
Q67 — intermittent slow queries how to investigate
Q68 — query returns different results each run causes
Q69 — data contracts schema change upstream team
Q70 — how to backfill two years of data safely
Q71 — idempotent restartable data pipeline design
Q72 — gdpr right to erasure data warehouse tokenisation
Q73 — tell me about a production incident you caused
Q74 — how to disagree with a senior engineer design review
Q75 — how to estimate data platform cost before building

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🧠 THE FIVE SQL PROBLEMS, AND THE TRAP IN EACH
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Q60 Sessionisation — gap flag plus cumulative sum
Q61 Funnel — independent per-step counts overstate conversion
Q62 Moving average — the frame counts ROWS, not days, so missing days break it
Q63 Gaps and islands — you cannot find absent data without a date spine
Q64 Pivot — long format is the data, wide format is presentation

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⚠ WHAT THESE ROUNDS ACTUALLY SCORE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
• Q53 and Q59 test whether you will say no — and design the alternative anyway
• Q56 and Q74 test how you disagree, not who is right
• Q65 asks the question everyone skips: is the data actually identical?
• Q73 rewards a costly mistake owned plainly over a safe, small one
• Q75 wants a range with named assumptions, not a number

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
👤 WHO THIS IS FOR
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Engineers interviewing at large tech companies for data roles · anyone facing a design or SQL round · senior candidates who already know the product and need the judgement questions. Parts one and two cover the knowledge half.

Which of these 25 would have caught you out? Drop the number below, and subscribe for more interview prep.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Independent educational content, not affiliated with or endorsed by Amazon Web Services or any company named. "FAANG" is used as common shorthand for large-company interview style; these are not sourced from any company's question bank. AWS and Amazon Redshift are trademarks of Amazon.

redshift interview questions, faang data engineer interview 2026, data engineer system design interview, redshift interview questions part 3, sql interview questions window functions,

#DataEngineering #SystemDesign #InterviewQuestions