Question 1

What is Amazon Redshift?

Accepted Answer

Redshift is AWS's managed data warehouse: a columnar, massively parallel processing database where data is distributed across compute nodes and queries run in parallel across slices. It is optimised for analytical queries scanning large volumes rather than for transactional workloads. The columnar plus MPP combination is what makes it fast for aggregation over billions of rows and poor for single-row lookups. Naming RA3 nodes with managed storage, which separates compute from storage, is the detail that shows current knowledge.

Question 2

What is a distribution style in Redshift and why does it matter?

Accepted Answer

The distribution style determines how rows are spread across nodes. KEY distributes by the hash of a column, co-locating rows with the same value so joins on that column happen locally. ALL replicates the whole table to every node, suited to small dimensions. EVEN round-robins. AUTO lets Redshift choose and adapt. Choosing the join key as the distribution key for the two largest tables avoids redistribution, which is the single biggest Redshift performance lever. A poor choice causes data to be shuffled across the network on every join, which is what makes queries slow.

Question 3

What is a sort key in Redshift?

Accepted Answer

A sort key determines the physical order of rows within each slice, so Redshift can skip blocks that cannot match a predicate using zone maps. A compound sort key is efficient when queries filter on the leading columns; an interleaved sort key gives more even weight to several columns but is costlier to maintain. Sorting by the column you filter on most — usually a date — is the practical rule, and it is what makes range queries scan a fraction of the table. Naming zone maps as the mechanism, rather than describing sorting abstractly, is what shows understanding.

Question 4

What is Redshift Spectrum?

Accepted Answer

Spectrum lets Redshift query data in S3 directly using external tables defined in the Glue Data Catalog, without loading it. Compute for the S3 scan runs on a separate fleet, so it does not consume cluster capacity, and you pay per terabyte scanned. It is what makes the lakehouse pattern work: hot data in Redshift managed storage, cold historical data in S3 queried on demand, joined in one query. Partitioning and columnar formats in S3 are what keep Spectrum costs reasonable.

Question 5

What is Redshift Serverless?

Accepted Answer

Redshift Serverless provisions and scales compute automatically in Redshift Processing Units based on workload, with no cluster to size or manage, billing per second of compute used with a configurable base and maximum capacity. It suits variable or intermittent analytics where a provisioned cluster would sit idle. The consideration is that the base RPU setting establishes a cost floor and that very steady high-utilisation workloads may still be cheaper on a provisioned cluster with reserved instances.

Question 6

What is Amazon Athena?

Accepted Answer

Athena is a serverless interactive query service that runs SQL directly against data in S3 using table definitions in the Glue Data Catalog, with no infrastructure to manage. You pay per terabyte of data scanned, and there is nothing running when you are not querying. The cost model drives every optimisation: partitioning, columnar formats and compression all reduce bytes scanned and therefore cost. Naming that the price is per byte scanned rather than per query is the fact that makes the optimisations obvious.

Question 7

How do you reduce Athena query cost?

Accepted Answer

Partition the data on columns you filter by, usually date, and use partition projection or keep partitions registered; store in a columnar format such as Parquet or ORC so only referenced columns are read; compress with Snappy or ZSTD; compact small files; and select specific columns rather than star. Converting from CSV to partitioned Parquet routinely reduces scan volume by an order of magnitude or more, which is both a cost and a latency win. Partition projection is worth naming because it avoids the metadata bottleneck of tables with very many partitions.

Question 8

What is partition projection in Athena?

Accepted Answer

Partition projection calculates partition values from a configured pattern — a date range, an enumerated set, an integer range — instead of looking them up in the Glue catalog, so queries on tables with tens of thousands of partitions do not spend time on partition discovery and no MSCK REPAIR or crawler run is needed. It removes both the metadata latency and the operational burden of keeping partitions registered, which is the most common Athena pain point. Naming it is a strong signal that you have run Athena at scale rather than on a demo dataset.

Question 9

What is AWS Glue?

Accepted Answer

Glue is a serverless data integration service: the Data Catalog stores table metadata used by Athena, Redshift Spectrum and EMR; crawlers infer schema and partitions from data; ETL jobs run Spark or Python shell scripts; and Glue Studio provides a visual job builder with triggers and workflows. The Data Catalog is the piece that matters most architecturally, since it is the shared metastore across the analytics services — one table definition serves Athena, Spectrum, EMR and Lake Formation. That shared metadata is what makes a lakehouse coherent.

Question 10

What is a Glue crawler and when should you avoid it?

Accepted Answer

A crawler scans a data store, infers schema and partitions and writes them to the Data Catalog. Avoid it when the schema is known and stable, because inference can be wrong, changes schema unexpectedly, costs money per run and is slow on large partition counts — define the table explicitly instead, or use partition projection. The unexpected schema change is the real problem: a crawler re-inferring a column as string instead of int can silently break downstream queries. Explicit table definitions managed as code are more predictable, and saying so is a stronger answer than defaulting to crawlers.

Question 11

What is Amazon EMR and when would you use it?

Accepted Answer

EMR runs managed clusters of open-source big-data frameworks — Spark, Hive, Presto, HBase, Flink — on EC2, EKS or serverless. Use it when you have existing Spark or Hadoop workloads, need a specific library from that ecosystem, or need fine control over cluster configuration. The ephemeral cluster pattern is the cost answer: create a cluster for a job and terminate it afterwards, with data in S3 rather than HDFS. EMR Serverless removes cluster management entirely, which narrows the gap with Glue for Spark workloads.

Question 12

When would you choose Glue over EMR?

Accepted Answer

Glue when you want serverless Spark with no cluster to manage, tight Data Catalog integration and simple job scheduling. EMR when you need specific framework versions or libraries, fine-grained cluster tuning, non-Spark frameworks, or when very large sustained workloads make managed cluster costs favourable. The honest framing is that Glue is the easier default and EMR the more controllable option, with EMR Serverless sitting between them. Cost at scale often favours EMR because you can use Spot capacity aggressively, which is worth naming.

Question 13

What file formats would you use in an S3 data lake and why?

Accepted Answer

Parquet or ORC for analytical data because they are columnar, compressed and support predicate pushdown, so engines read only the columns and row groups they need. Avro for row-oriented streaming with schema evolution. Avoid CSV and JSON for large analytical datasets — they are uncompressed, untyped and force full scans. Apache Iceberg is the current direction worth naming: a table format layered over Parquet that adds ACID transactions, time travel, schema evolution and efficient row-level updates, and it is supported across Athena, EMR, Glue and Redshift.

Question 14

What is Apache Iceberg and why does it matter on AWS?

Accepted Answer

Iceberg is an open table format over files in S3 providing ACID transactions, snapshot isolation, time travel, schema and partition evolution, and efficient row-level updates and deletes. AWS supports it across Athena, Glue, EMR, Redshift and Lake Formation, and offers S3 Tables as a managed Iceberg storage option. It solves the two hardest data-lake problems: concurrent writes corrupting readers, and the inability to update or delete rows without rewriting partitions — which is what GDPR deletion requests demand. That compliance angle is often the deciding argument.

Question 15

What is AWS Lake Formation?

Accepted Answer

Lake Formation centralises governance for data lakes on S3: it manages permissions at database, table, column and row level across Athena, Redshift Spectrum, EMR and Glue, using the Data Catalog as the control point, with tag-based access control and cross-account sharing. The problem it solves is that S3 bucket policies are too coarse for analytical governance — you cannot express "this analyst may see all columns except salary" with an IAM policy on a bucket. Fine-grained permissions enforced at query time is what it adds.

Question 16

What is Amazon QuickSight?

Accepted Answer

QuickSight is AWS's serverless business intelligence service with dashboards, per-user or per-session pricing, SPICE in-memory acceleration, row-level security, embedding into applications, and natural-language querying with Q. SPICE is the detail worth naming: importing data into the in-memory engine makes dashboards fast and decouples them from the source, but introduces a refresh cadence and therefore staleness. Direct query keeps data live at the cost of load on the warehouse.

Question 17

What is Amazon OpenSearch Service and when is it the right choice?

Accepted Answer

OpenSearch is a managed search and analytics engine for full-text search, log analytics and observability, with dashboards. It is right for keyword and relevance search, log exploration and near-real-time analytics on semi-structured data, and wrong as a primary transactional store or a data warehouse. The anti-pattern to name is using OpenSearch as the system of record — it is not designed for durability guarantees or transactions in that role. Replicating from a durable store into OpenSearch for search is the correct architecture.

Question 18

How would you design a data lake on AWS?

Accepted Answer

Land raw immutable data in S3 partitioned by source and date; process into curated and aggregate zones in Parquet or Iceberg with compaction; register tables in the Glue Data Catalog; govern access with Lake Formation using column and row level permissions; query with Athena for ad hoc and Redshift for heavy BI; orchestrate with Step Functions or Managed Workflows for Apache Airflow; and apply lifecycle policies for cost. The three-zone structure — raw, curated, aggregate — is what makes reprocessing possible when requirements change, because the raw data is never mutated. Naming compaction and lifecycle tiering shows you have operated a lake rather than designed one on paper.

Question 19

What is the small-files problem and how do you address it?

Accepted Answer

Query engines pay per-file overhead for listing, opening and task scheduling, so millions of small files make a job spend most of its time on metadata. Fix by compacting into files of a few hundred megabytes, controlling the writer's output partition count, and using Iceberg's compaction maintenance operations. It is acute on object storage because every file open is a network request. Controlling output shard count at write time is more efficient than compacting afterwards, and naming that distinction is the practical insight.

Question 20

What is Amazon Kinesis Data Firehose in an analytics context?

Accepted Answer

Firehose buffers streaming data by size or time and delivers it to S3, Redshift, OpenSearch or third-party destinations, with optional Lambda transformation, format conversion to Parquet or ORC, and dynamic partitioning based on record content. Format conversion and dynamic partitioning are the features that matter for a data lake, because they mean raw JSON events land as partitioned Parquet without a separate ETL job. That removes an entire pipeline stage from most designs.

Question 21

How do you handle GDPR deletion requests in a data lake?

Accepted Answer

Use a table format supporting row-level deletes such as Iceberg or Hudi so a record can be removed without rewriting partitions; or crypto-shred by encrypting per subject with a key you destroy. Track where every copy lives — raw zone, curated tables, warehouse, backups, logs and exports — because deletion must reach all of them. Crypto-shredding is the technique that solves the immutable-backup problem, since you cannot selectively delete one record from an archived snapshot. Naming backups and logs as places data hides is what makes the answer complete rather than aspirational.

Question 22

What is the difference between Athena and Redshift?

Accepted Answer

Athena is serverless, queries S3 directly, costs per byte scanned and has no infrastructure — ideal for ad hoc and intermittent analysis. Redshift is a provisioned or serverless warehouse with its own optimised storage, sort and distribution keys, materialised views and workload management — better for high-concurrency BI with predictable performance. The concurrency point is the practical divider: Athena has query concurrency limits and variable latency, so a dashboard serving hundreds of analysts usually belongs on Redshift. Using both — Redshift for BI, Athena for exploration over the same S3 data — is the common architecture.

Question 23

What is workload management in Redshift?

Accepted Answer

WLM allocates cluster resources across query queues so a long-running ETL job cannot starve short interactive dashboards. Automatic WLM manages concurrency and memory dynamically, with query priority and short query acceleration; manual WLM lets you define queues and memory shares explicitly. The reason it exists is queue contention: without it, one enormous query consumes memory and everything else waits. Query monitoring rules that abort or demote queries exceeding thresholds are the protective mechanism worth naming.

Question 24

What is a materialised view in Redshift?

Accepted Answer

A materialised view precomputes and stores a query result, refreshed incrementally where possible, and Redshift can automatically rewrite queries against the base tables to use it. It removes repeated computation for dashboards and common aggregations. Automatic query rewrite is the feature to highlight, because existing dashboards get faster with no query changes. The trade-off is storage and refresh cost, so materialised views should be created for genuinely repeated aggregations rather than every query.

Question 25

How do you load data into Redshift efficiently?

Accepted Answer

Use the COPY command from S3, which loads in parallel across slices — ideally with the input split into a number of files that is a multiple of the slice count, compressed, and in a columnar or delimited format. Avoid row-by-row INSERT, which is orders of magnitude slower. Run ANALYZE and VACUUM or rely on automatic maintenance afterwards. The file-count-to-slice-count guidance is the specific optimisation that shows real experience, because a single large file cannot be loaded in parallel. Auto-copy and zero-ETL integrations from Aurora are the newer alternatives worth naming.

Question 26

What is a zero-ETL integration?

Accepted Answer

Zero-ETL integrations replicate data from a source such as Aurora, RDS or DynamoDB into Redshift continuously and automatically, with no pipeline to build or operate, so transactional data is available for analytics within seconds. It removes an entire class of custom CDC pipeline. The consideration is that it replicates rather than transforms, so modelling and curation still happen in the warehouse — it replaces the extract and load, not the transform.

Question 27

What is Amazon MWAA?

Accepted Answer

Managed Workflows for Apache Airflow runs Airflow as a managed service, so you author DAGs in Python with dependencies, retries, backfills, sensors and the operator ecosystem, while AWS handles the scheduler, workers and web server with VPC integration and IAM. The positioning is that it orchestrates rather than processes — the heavy work is done by Glue, EMR, Athena or Redshift and Airflow triggers and monitors it. Using Airflow workers to process large data is a well-known anti-pattern worth calling out.

Question 28

When would you use Step Functions instead of Airflow for data pipelines?

Accepted Answer

Step Functions when the pipeline is a modest sequence of AWS service calls, you want serverless with no always-on cost, and you value native integration and execution history. Airflow when you have many interdependent DAGs, need backfills and sensors, rely on the operator ecosystem, or the team already knows it. The cost distinction matters: MWAA has a meaningful always-on charge while Step Functions costs nothing when idle. For a handful of daily jobs, Airflow is over-provisioned, and saying so shows cost awareness rather than tool preference.

Question 29

How do you handle late-arriving data in a batch pipeline?

Accepted Answer

Process by event time rather than arrival time, and design jobs to be idempotent and re-runnable for a window — reprocessing the last N days each run so late records are picked up. Use a table format supporting upserts so restatements replace rather than duplicate, and monitor the lateness distribution to size the window. Idempotent, partition-keyed writes are what make reprocessing safe, and Iceberg or Hudi upserts are what make restatement efficient. Measuring actual lateness rather than guessing the window is the part that turns it from a rule of thumb into a design.

Question 30

What is data skew in a Spark job and how do you fix it?

Accepted Answer

Skew is when one key holds a disproportionate share of data, so one task does most of the work while others idle. Fix by salting the key and aggregating in two stages, broadcasting the small side of a join, filtering the dominant key into a separate path, or enabling adaptive query execution which handles some skew automatically. Diagnosing it from the Spark UI — a huge gap between median and maximum task duration in a stage — is the practical half. Adaptive query execution in recent Spark versions handles many cases automatically, which is worth naming as the first thing to check.

Question 31

How do you monitor a data pipeline?

Accepted Answer

Data freshness — how old the newest processed record is — as the primary user-facing signal; record counts against expected volume; reconciliation between source and destination totals; rejected-record counts; and job duration trends. Alert on freshness exceeding the business tolerance rather than only on job failure. Reconciliation is what catches silent data loss, which is the characteristic pipeline failure — everything reports success while records are quietly dropped. Alerting on absence, so a job that never starts is noticed, is the other essential control.

Question 32

What is AWS Glue DataBrew and Glue Data Quality?

Accepted Answer

DataBrew is a visual data preparation tool with prebuilt transformations for cleaning and normalising data without writing code. Glue Data Quality evaluates rulesets — completeness, uniqueness, freshness, referential integrity — against datasets and can fail a pipeline or raise findings when quality degrades. Data quality gates are the underused control: a pipeline that publishes bad data silently is worse than one that fails loudly. Naming that you would fail the job on a quality rule violation rather than only reporting it is the stronger position.

Question 33

What is the difference between ETL and ELT and which suits AWS?

Accepted Answer

ETL transforms before loading; ELT loads raw data first and transforms inside the warehouse or lake with SQL. ELT suits AWS because Redshift and Athena compute is elastic and cheap enough to transform in place, and it preserves the raw data so you can reprocess when requirements change. Reprocessability is the strongest argument for ELT: transform-before-load destroys the original, so a bug found later is unrecoverable. ETL still wins where transformation is required before storage for compliance, such as tokenising PII at ingestion.

Question 34

How do you secure a data lake on AWS?

Accepted Answer

Bucket policies denying non-TLS access and blocking public access; encryption with SSE-KMS and bucket keys; Lake Formation for column and row level permissions across engines; separate accounts or prefixes per zone with least-privilege roles; CloudTrail data events on sensitive buckets; and VPC endpoints so traffic stays off the internet. The point that distinguishes a lake from a bucket is Lake Formation: without it, anyone with S3 read access bypasses column-level security entirely, because the file is the unit of access. Enforcing at query time is what makes governance real.

Question 35

What is a star schema and does it apply on AWS?

Accepted Answer

A star schema has a central fact table of measures surrounded by dimension tables, joined on keys. It applies well in Redshift, where dimensions can use ALL distribution and facts use KEY distribution on the join column. In a lake queried by Athena, denormalisation is often preferable because joins are more expensive. The nuance is engine-dependent modelling: what is optimal in Redshift is not optimal in Athena over Parquet, where a wider denormalised table avoids shuffles. Recognising that the same logical model has different physical implementations is a senior insight.

Question 36

How do you handle slowly changing dimensions?

Accepted Answer

Type 1 overwrites the value; Type 2 preserves history with valid-from and valid-to columns and a current flag. In Redshift use MERGE; in a lake use an Iceberg or Hudi upsert. Partition by effective date and index or sort on the business key for efficient matching. The lake-specific point is that Type 2 was historically painful on immutable object storage because updates meant rewriting partitions, which is exactly the problem Iceberg and Hudi solve. Naming that connection shows you understand why table formats emerged.

Question 37

What is Amazon DataZone?

Accepted Answer

DataZone is a data management service providing a business catalogue, data discovery, governed publishing and subscription workflows across accounts, so data producers publish assets and consumers request access through an approval flow rather than by asking in a chat channel. It addresses the organisational problem rather than the technical one: in a large estate, finding data and getting access is usually slower than querying it. A catalogue with a subscription workflow is what makes a data mesh operationally viable.

Question 38

How would you migrate an on-premises data warehouse to AWS?

Accepted Answer

Assess schemas, queries and downstream consumers; use the Schema Conversion Tool and Redshift migration tooling for schema and code translation; move data with DataSync, DMS or Snowball depending on volume; run both warehouses in parallel and reconcile results; migrate consumers progressively; then decommission. Redesign for Redshift rather than porting the schema unchanged. Parallel running with result reconciliation is what gives stakeholders confidence to switch, and it is the step most often cut for schedule reasons. The redesign point matters because distribution and sort keys have no equivalent in most source systems.

Question 39

Design an analytics platform for a retailer with 500 stores and real-time sales data.

Accepted Answer

Point-of-sale events into Kinesis Data Streams, with Firehose landing raw events as partitioned Parquet in S3 and a Managed Flink or Lambda consumer computing near-real-time aggregates into DynamoDB for operational dashboards. Glue jobs build curated and aggregate zones as Iceberg tables, catalogued in Glue and governed by Lake Formation with column-level PII protection. Redshift serves BI with materialised views and separate WLM queues for ETL and dashboards; Athena serves ad hoc exploration over the same S3 data. MWAA or Step Functions orchestrates, with data-quality gates, freshness alarms and daily reconciliation between the streaming and batch paths. The closing scenario. The senior markers are separating the real-time serving path from the analytical path, reconciling streaming aggregates with a batch recomputation because streaming numbers drift, and isolating ETL from BI so a heavy load job cannot slow the morning dashboards.

AWS Redshift, Athena, Glue & Analytics Interview Questions and Answers

How to use this set