Amazon Redshift and Redshift Serverless
Decide when Harbourline Bank needs a warehouse rather than Athena, load curated Parquet into Redshift with COPY, choose distribution and sort keys, query the lake with Spectrum, and predict the bill.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.RedshiftDataAPIService 4.1.x) · AWS CLI v2 · Redshift Serverless · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain what a columnar, massively parallel warehouse does differently from SQL Server and from Athena, and decide which one a reporting need deserves.
- Load curated Parquet from S3 into Redshift with
COPYand an IAM role, and reconcile the load. - Choose a distribution style (
KEY,EVEN,ALL,AUTO) and a sort key for a transactions fact table, and explain the trade-offs. - Query lake data in place with Redshift Spectrum, and know when materialised views pay off.
- Estimate Redshift Serverless cost (RPU-hours, base capacity, minimum billing) and keep a lab within a free trial or a design-only budget.
- Run SQL from C# through the Redshift Data API without managing database connections or passwords.
Prerequisites
- Module 7 overview, especially the lake zones in S3 data lake design and the comparison in Data lake vs warehouse vs lakehouse
- Module 6: Databases for relational basics on AWS
- Tools: AWS CLI v2 with the
learn-sandboxSSO profile; Docker for running C# without a local SDK
Why this matters in banking
Harbourline's finance team closes the month on the fifth working day. They need: balances by product and branch, fee income by month, interest accruals, and a reconciliation between the ledger and the data lake. Every query joins hundreds of millions of transaction rows to accounts, customers and exchange rates, and the same 40 dashboards are refreshed by 120 people between 08:00 and 10:00.
Athena can answer these questions, and for small or ad hoc work it is the right first tool. But when dozens of people run heavy joins at the same moment, every query re-reads Parquet from S3, and both latency and cost grow with each refresh. A warehouse stores the data in its own optimised columnar format, keeps statistics, caches results and co-locates the rows that join together. When the month-end pack is late, the bank misses regulatory deadlines; when a warehouse is badly designed, it is both slow and expensive. This lesson gives you the vocabulary to make that call and to design the tables well.
The idea in plain language
A data warehouse is a database built for reading lots of rows and summarising them, not for updating one row at a time. Amazon Redshift is AWS's warehouse. It stores each column separately (columnar storage), compresses it, and splits the table across many workers (compute nodes, each with several slices) that scan their share in parallel and then combine the results. That design is called MPP (massively parallel processing).
Redshift Serverless is the same engine without you choosing node types. You create a namespace (the database objects, users and storage) and a workgroup (the compute). The workgroup scales between a base capacity and a maximum, measured in Redshift Processing Units (RPUs), and you pay per RPU-second only while queries run.
Analogy: SQL Server is a bank branch: one counter, very good at serving one customer's request quickly. Redshift is the cash-processing centre: sixty counting machines, each counting its own pile of notes, and a supervisor adding up the totals. You would never send a single customer there, but for counting the whole day's takings it is far faster.
What problem the AWS service solves
| Problem | How Redshift addresses it | Limitation to know |
|---|---|---|
| Heavy joins and aggregates over billions of rows | Columnar storage, compression, zone maps and MPP execution | Single-row updates and deletes are slow compared with OLTP databases; design for batch loads |
| Many concurrent dashboard users | Result caching, concurrency scaling, workload management (WLM) | Concurrency scaling and large bases cost money; idle provisioned clusters still bill |
| Joining warehouse tables with lake data | Redshift Spectrum reads S3 data through external schemas over the Glue Data Catalog | Spectrum scans are billed per TB on provisioned clusters, and performance depends on file layout |
| Keeping summaries fresh | Materialised views, with incremental refresh where supported | Not every SQL construct is incrementally refreshable |
| Running SQL without drivers and passwords | Redshift Data API (HTTPS, IAM, asynchronous) | Results are fetched separately and retained only for a limited time |
Redshift is not a replacement for the curated data lake. In this module's design, S3 stays the system of record for analytics data. Redshift holds a copy shaped for fast reporting, and can be rebuilt from S3 at any time.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Azure Synapse dedicated SQL pool | Redshift provisioned cluster (RA3 nodes) | Similar MPP idea; Redshift uses distribution styles and sort keys instead of hash/round-robin/replicated tables plus clustered columnstore indexes |
| Synapse serverless SQL pool | Amazon Athena (and Redshift Spectrum) | Synapse serverless is pay-per-TB over the lake, like Athena; Redshift Serverless is a full warehouse that scales its compute |
HASH(AccountId) distribution | DISTSTYLE KEY DISTKEY(account_id) | Same idea: rows with the same key land together, so joins on that key avoid data movement |
REPLICATE distribution | DISTSTYLE ALL | A full copy of a small dimension on every node |
| Clustered columnstore index ordering | SORTKEY | Redshift uses zone maps (min/max per block) to skip blocks, not B-tree seeks |
COPY INTO / PolyBase | COPY ... FROM 's3://...' IAM_ROLE ... FORMAT AS PARQUET | Authentication is an IAM role attached to the namespace, not a database-scoped credential |
| Pause/resume a dedicated pool | Serverless auto-scales to zero compute; provisioned clusters can pause | Serverless still bills managed storage while idle |
| SQL Server Agent jobs | Redshift scheduled queries (via EventBridge Scheduler) or Step Functions + Data API | Orchestration lives outside the database |
Architecture
Security-boundary view. The workgroup runs in your VPC subnets with a security group, so only resources you allow can reach port 5439. Loading uses an IAM role that the namespace assumes; that role reads only the curated bucket and can decrypt only with alias/hl-m07-data. People and applications authenticate with IAM (Data API or temporary credentials), not with a shared admin password. Audit logging goes to CloudWatch Logs or S3.
Distribution and sort keys
This is the part interviewers and the DEA-C01 exam ask about most.
Distribution decides which slice stores each row.
| Style | What happens | Use for |
|---|---|---|
AUTO (default) | Redshift picks and may change the style as the table grows | Start here when unsure; Redshift Advisor and automatic table optimisation adjust it |
KEY | Rows with the same key value go to the same slice | Large fact tables joined on one dominant key, e.g. transactions.account_id joining accounts.account_id |
ALL | A full copy on every node | Small, slowly changing dimensions: currencies, products, merchant categories |
EVEN | Round-robin | Tables with no dominant join key, staging tables |
Two pitfalls: a skewed key (one account with millions of rows, or NULL in many rows) overloads one slice; and a key that is never used in joins buys nothing. Check skew in SVV_TABLE_INFO.skew_rows.
Sort keys decide the order of rows on disk. Each 1 MB block records the minimum and maximum value of each column (a zone map). If the table is sorted by booking_date and your query asks for one month, Redshift skips every block whose range misses that month. A compound sort key (the default) helps most when filters use the leading column; an interleaved sort key gives equal weight to several columns but costs more to maintain and is rarely the right choice today. SORTKEY AUTO lets Redshift decide.
For Harbourline's curated.transactions: DISTKEY(account_id) because statements and balances join on account, and SORTKEY(booking_date) because nearly every report filters on a date range.
Step-by-step implementation
Decide whether you need Redshift at all
Write down the workload: data size, number of concurrent users, latency target, how often data changes. If it is "a few analysts, ad hoc, under a terabyte", stay on Athena with Parquet and partitions (Lab 7.3). If it is "100 users refresh the same heavy dashboards every morning and finance needs sub-second drill-downs", a warehouse is justified.
Create a namespace and workgroup (or plan them)
Choose base capacity (4 RPUs is the lowest), set a maximum RPU-hours usage limit per day, place the workgroup in private subnets, and turn on enhanced VPC routing so COPY traffic to S3 goes through your VPC endpoint.
Create the load role
An IAM role trusted by redshift.amazonaws.com (and redshift-serverless.amazonaws.com), allowed s3:GetObject/s3:ListBucket on the curated bucket and kms:Decrypt on the data key. Associate it with the namespace.
Create tables and load with COPY
Run the DDL from labs/m07-transaction-pipeline/sql/redshift/01_load_curated.sql. COPY ... FORMAT AS PARQUET loads files in parallel, one slice per file, so many medium files (roughly equal sizes) load faster than one huge file.
Reconcile
Compare row counts and sums per currency with hl_m07_gold.reconciliation or with an Athena query on the curated table. A load is not finished until it reconciles.
Expose the lake with Spectrum
Create an external schema over the Glue database so analysts can join warehouse tables with recent lake partitions that you have not loaded yet.
AWS Console instructions
- Console → Amazon Redshift → Redshift Serverless → Create workgroup. Name
hl-m07-wg, base capacity 4 RPU, choose private subnets and a security group that allows inbound 5439 only from your own resources. - On the same wizard choose Create a new namespace
hl-m07. Under Permissions, associate the IAM rolehl-m07-redshift-copy. Under Encryption, choose Customize encryption settings and selectalias/hl-m07-data. - Workgroup → Limits → Manage usage limits → add a limit such as 4 RPU-hours per day with action Turn off user queries. This is your cost brake.
- Query editor v2 → connect with Federated user (IAM identity) → run the DDL and
COPY. - Namespace → Security and encryption → Audit logging → turn on connection, user and user-activity logs to CloudWatch.
AWS CLI
# Create a namespace with a customer managed key and the COPY role (no admin password: use IAM auth).
aws redshift-serverless create-namespace \
--namespace-name hl-m07 \
--kms-key-id alias/hl-m07-data \
--iam-roles arn:aws:iam::111122223333:role/hl-m07-redshift-copy \
--log-exports userlog connectionlog useractivitylog \
--tags key=Project,value=cea-lab key=Lab,value=m07-redshift key=Environment,value=learn key=Owner,value=learner \
--profile learn-sandbox
# Create the workgroup: smallest base capacity, private, enhanced VPC routing on.
aws redshift-serverless create-workgroup \
--workgroup-name hl-m07-wg --namespace-name hl-m07 \
--base-capacity 4 --enhanced-vpc-routing --no-publicly-accessible \
--subnet-ids subnet-0aaa subnet-0bbb subnet-0ccc --security-group-ids sg-0123 \
--profile learn-sandbox
# Cost brake: at most 4 RPU-hours per day, then stop user queries.
aws redshift-serverless create-usage-limit \
--resource-arn "$(aws redshift-serverless get-workgroup --workgroup-name hl-m07-wg --query workgroup.workgroupArn --output text --profile learn-sandbox)" \
--usage-type serverless-compute --amount 4 --period daily --breach-action deactivate \
--profile learn-sandbox
# Run SQL through the Data API (asynchronous) and fetch the result.
ID=$(aws redshift-data execute-statement --workgroup-name hl-m07-wg --database dev \
--sql "select currency, count(*), sum(amount) from curated.transactions group by currency" \
--query Id --output text --profile learn-sandbox)
aws redshift-data describe-statement --id "$ID" --profile learn-sandbox --query Status
aws redshift-data get-statement-result --id "$ID" --profile learn-sandboxcreate-usage-limit breach actions are log, emit-metric and deactivate ⚠ VERIFY the exact enum spelling for Serverless in your CLI version (aws redshift-serverless create-usage-limit help).
Infrastructure as Code
using Amazon.CDK;
using Amazon.CDK.AWS.IAM;
using Amazon.CDK.AWS.RedshiftServerless;
using Constructs;
namespace Harbourline.DataPipeline.Infra;
/// <summary>Redshift Serverless for month-end reporting (L1 constructs: there is no stable L2 yet).</summary>
public sealed class WarehouseStack : Stack
{
public WarehouseStack(Construct scope, string id, IStackProps props, string[] subnetIds, string securityGroupId, string kmsKeyArn, string curatedBucketArn)
: base(scope, id, props)
{
var copyRole = new Role(this, "CopyRole", new RoleProps
{
RoleName = "hl-m07-redshift-copy",
AssumedBy = new CompositePrincipal(
new ServicePrincipal("redshift.amazonaws.com"),
new ServicePrincipal("redshift-serverless.amazonaws.com")),
});
copyRole.AddToPolicy(new PolicyStatement(new PolicyStatementProps
{
Actions = ["s3:GetObject", "s3:ListBucket", "s3:GetBucketLocation"],
Resources = [curatedBucketArn, $"{curatedBucketArn}/*"],
}));
copyRole.AddToPolicy(new PolicyStatement(new PolicyStatementProps { Actions = ["kms:Decrypt"], Resources = [kmsKeyArn] }));
var ns = new CfnNamespace(this, "Namespace", new CfnNamespaceProps
{
NamespaceName = "hl-m07",
KmsKeyId = kmsKeyArn,
IamRoles = [copyRole.RoleArn],
DefaultIamRoleArn = copyRole.RoleArn,
LogExports = ["userlog", "connectionlog", "useractivitylog"],
});
var wg = new CfnWorkgroup(this, "Workgroup", new CfnWorkgroupProps
{
WorkgroupName = "hl-m07-wg",
NamespaceName = ns.NamespaceName,
BaseCapacity = 4,
EnhancedVpcRouting = true,
PubliclyAccessible = false,
SubnetIds = subnetIds,
SecurityGroupIds = [securityGroupId],
});
wg.AddDependency(ns);
}
}Complete code
The SQL that creates and loads the table is in labs/m07-transaction-pipeline/sql/redshift/01_load_curated.sql. Below are the Spectrum and materialised-view parts, then a C# month-end report that uses the Data API.
-- External schema: Redshift reads the Glue Data Catalog database in place (Spectrum).
CREATE EXTERNAL SCHEMA IF NOT EXISTS lake
FROM DATA CATALOG DATABASE 'hl_m07_curated'
IAM_ROLE 'arn:aws:iam::111122223333:role/hl-m07-redshift-copy';
-- Small dimension, copied to every node.
CREATE TABLE IF NOT EXISTS ref.currencies (code char(3) PRIMARY KEY, name varchar(40)) DISTSTYLE ALL;
-- Month-end summary kept up to date by the warehouse.
CREATE MATERIALIZED VIEW mv_monthly_fees
AUTO REFRESH YES
AS
SELECT date_trunc('month', booking_date) AS month, currency, count(*) AS n, sum(amount) AS fee_total
FROM curated.transactions
WHERE type = 'fee' AND status = 'booked'
GROUP BY 1, 2;
-- Join loaded history with lake partitions that are not loaded yet (today's files).
SELECT currency, sum(amount) AS total
FROM (
SELECT currency, amount FROM curated.transactions WHERE booking_date >= '2026-11-01'
UNION ALL
SELECT currency, amount FROM lake.transactions WHERE year = '2026' AND month = '11' AND day = '17'
) t
GROUP BY currency;// dotnet add package AWSSDK.RedshiftDataAPIService (v4) and AWSSDK.SSO + AWSSDK.SSOOIDC for the SSO profile
using System.Globalization;
using Amazon.RedshiftDataAPIService;
using Amazon.RedshiftDataAPIService.Model;
// Credentials: AWS_PROFILE=learn-sandbox after `aws sso login`. No database password anywhere.
using var client = new AmazonRedshiftDataAPIServiceClient();
var start = await client.ExecuteStatementAsync(new ExecuteStatementRequest
{
WorkgroupName = "hl-m07-wg",
Database = "dev",
Sql = "select currency, count(*) as n, sum(amount) as total from curated.transactions " +
"where booking_date between :from and :to group by currency order by currency",
Parameters =
[
new SqlParameter { Name = "from", Value = "2026-10-01" },
new SqlParameter { Name = "to", Value = "2026-10-31" },
],
StatementName = "hl-m07-month-end",
});
// The Data API is asynchronous: poll until the statement finishes.
DescribeStatementResponse status;
do
{
await Task.Delay(TimeSpan.FromMilliseconds(500));
status = await client.DescribeStatementAsync(new DescribeStatementRequest { Id = start.Id });
}
while (status.Status is "SUBMITTED" or "PICKED" or "STARTED");
if (status.Status != "FINISHED")
{
Console.Error.WriteLine($"Statement {status.Status}: {status.Error}");
return 1;
}
var result = await client.GetStatementResultAsync(new GetStatementResultRequest { Id = start.Id });
foreach (var row in result.Records)
{
var currency = row[0].StringValue;
var count = row[1].LongValue;
// DECIMAL arrives as a string: parse it as decimal, never double.
var total = decimal.Parse(row[2].StringValue, CultureInfo.InvariantCulture);
Console.WriteLine($"{currency} {count,8} {total,16:N2}");
}
return 0;Code explained line by line
CREATE EXTERNAL SCHEMA ... FROM DATA CATALOG— maps a Glue database into Redshift. No data is copied; queries againstlake.*are Spectrum queries that read S3.DISTSTYLE ALLonref.currencies— a tiny table copied to every node, so joins to it never move data.CREATE MATERIALIZED VIEW ... AUTO REFRESH YES— Redshift stores the result and refreshes it after base-table changes when the cluster is not busy. Simple aggregates like this one can be refreshed incrementally.- The
UNION ALLquery — history from the loaded table plus today's partition from the lake, with partition columns in theWHEREso Spectrum reads only one folder. new AmazonRedshiftDataAPIServiceClient()— uses the default credential chain. The Data API authenticates with IAM; when you pass aWorkgroupNamewithout a secret, it uses temporary database credentials mapped from your IAM identity.Parameterswith:fromand:to— named parameters, so values are never concatenated into SQL (no injection).- The
do ... whileloop —ExecuteStatementreturns immediately with an id; you pollDescribeStatementuntilFINISHED,FAILEDorABORTED. GetStatementResultAsync— pages of records; each field is a typed union (StringValue,LongValue, …). Large results need theNextTokenloop, omitted here because the result has two rows.decimal.Parse(row[2].StringValue, ...)— the Data API returnsDECIMALcolumns as strings, which keeps every penny exact.
Security considerations
⚠ Learning shortcut — not for production
The lab grants the analyst role direct SELECT on curated.transactions. In production, analysts should get views or role-based grants on a reporting schema, column-level grants that exclude free-text fields (reference, counterparty_name), and row-level security policies (CREATE RLS POLICY) where regional teams must see only their own book. Use Redshift roles mapped from IAM Identity Center groups rather than individual database users.
- Encryption at rest with the customer managed key; snapshots inherit it.
- No public access, private subnets, and enhanced VPC routing so
COPY/UNLOADtraffic uses your S3 gateway endpoint and bucket policies can require it. - IAM authentication (Data API,
GetCredentials) instead of a shared admin password. If you must create an admin password, let Redshift manage it in Secrets Manager. - Audit logging on (connection, user, user activity). User activity logs contain SQL text, so treat them as sensitive.
- Data minimisation: load only curated, masked data. Raw IBANs never enter the warehouse; tokens allow joins without exposing the number.
Compliance review required
A warehouse copy is another place personal data lives. Your record of processing, retention schedule and subject-access process must include it. Decide with the data-protection officer how long history is kept and how erasure requests reach this copy.
Banking use case
Harbourline loads curated transactions nightly after the Glue job reconciles. Finance runs month-end on Redshift Serverless with a base of 8 RPUs; the warehouse scales up between 08:00 and 10:00 and back to zero compute at night. A materialised view summarises fees and interest by product. Regulators' returns pull from a reporting schema with column-level grants. Anything older than 25 months is unloaded to S3 as Parquet and queried through Spectrum when an auditor asks, which keeps the warehouse small.
Common mistakes
- Using Redshift as an OLTP database → single-row inserts in a loop are slow. Load in batches with
COPY, or stage andMERGE. - Loading one giant file → only one slice works. Split into many files of similar size (Glue/Spark writes them naturally).
DISTKEYon a skewed column → one slice does all the work. Check skew; fall back toAUTOorEVEN.- Forgetting the usage limit on Serverless → a badly written dashboard can keep RPUs busy all day. Set daily limits and query monitoring rules.
- Copying raw PII into the warehouse "for convenience" → widens the breach surface. Load curated, masked data only.
- Comparing prices without the workload → Serverless is cheap for spiky use and expensive when busy 24×7; a reserved provisioned cluster can win for constant load.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
COPY fails with S3ServiceException: Access Denied | Role not associated with the namespace, missing kms:Decrypt, or bucket policy requires VPC endpoint | Associate the role; add KMS permission; turn on enhanced VPC routing |
COPY error mentions column count or type mismatch | Parquet column order or types differ from the table | COPY from Parquet maps by position; align DDL with the file schema |
| Spectrum query returns no rows | External table location or partitions wrong | Check the Glue table; partition projection or MSCK REPAIR in Athena |
| Queries suddenly fail with "usage limit" | Daily RPU-hour limit reached | Raise the limit deliberately, or fix the expensive query |
Joins slow, DS_BCAST_INNER or DS_DIST_BOTH in EXPLAIN | Data redistributed at query time | Align DISTKEYs of joined tables, or DISTSTYLE ALL for small tables |
Data API: ValidationException: workgroup not found | Wrong Region or name | Pass --region us-east-1; check list-workgroups |
Cost and cleanup
Prices for us-east-1, checked on 2026-11-18 (confirm on the Redshift pricing page):
| Item | Price |
|---|---|
| Redshift Serverless compute | USD 0.375 per RPU-hour, billed per second, 60-second minimum |
| Base capacity | from 4 RPU (so 4 × 0.375 = USD 1.50 per hour while queries run) |
| Managed storage | USD 0.024 per GB-month |
| Spectrum (provisioned clusters) | USD 5 per TB scanned, 10 MB minimum per query |
| Free trial | USD 300 credit for first-time Serverless users, expiring after 90 days |
A lab session that runs about 20 short queries spread over an hour might bill a few minutes of RPU time — well under a dollar — but a dashboard refreshing every minute keeps the workgroup warm. Free alternative: do everything in this lesson design-only and run the same SQL logic in Athena (Lab 7.3); Athena costs fractions of a cent on lab data.
Cleanup:
aws redshift-serverless delete-workgroup --workgroup-name hl-m07-wg --profile learn-sandbox
# wait until the workgroup is gone, then:
aws redshift-serverless delete-namespace --namespace-name hl-m07 --profile learn-sandbox
aws iam delete-role-policy --role-name hl-m07-redshift-copy --policy-name copy || true
aws iam delete-role --role-name hl-m07-redshift-copy || true
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab --profile learn-sandbox \
--query "ResourceTagMappingList[?contains(ResourceARN, 'redshift')].ResourceARN"Deleting the namespace offers a final snapshot; snapshots bill storage until you delete them.
Exercises
- 🟢 Guided: Write the DDL for
curated.accountsandref.productswith sensible distribution styles, and explain each choice in one sentence. - 🟡 Challenge: Given
EXPLAINoutput showingDS_DIST_BOTHon a join betweentransactionsandpaymentsondebtor_account_id, redesign the tables so the join is collocated. - 🔴 Live problem: Finance reports that the month-end dashboard "got slow and the Redshift bill tripled" after a new team onboarded. You have the usage, query monitoring and
SYS_QUERY_HISTORYviews. Write the investigation plan and three likely fixes.
Assignment
Write a one-page architecture decision record (ADR) titled "Athena or Redshift for Harbourline month-end reporting". Include workload assumptions, a cost estimate for both options using today's prices, security implications, and the decision. Commit it to your portfolio repository next to the lab.
Solutions
CREATE TABLE curated.accounts (
account_id varchar(14) NOT NULL, customer_id varchar(12) NOT NULL, iban_token varchar(40),
currency char(3) NOT NULL, product varchar(12) NOT NULL, status varchar(8) NOT NULL,
balance decimal(18,2) NOT NULL, opened_at timestamp NOT NULL
) DISTKEY (account_id) SORTKEY (account_id);
CREATE TABLE ref.products (product varchar(12) PRIMARY KEY, description varchar(60)) DISTSTYLE ALL;accounts shares account_id with transactions, so a KEY distribution on the same column collocates the join: matching rows are on the same slice. products has four rows, so ALL costs nothing and removes broadcast steps. The raw IBAN is replaced by its token, keeping the warehouse free of the clear value.
DS_DIST_BOTH means both tables are redistributed for the join. Distribute payments on debtor_account_id and transactions on account_id (the same domain of values), so matching rows share a slice. If payments is small (thousands of rows), DISTSTYLE ALL is an alternative. Re-run EXPLAIN and expect DS_DIST_NONE. Check SVV_TABLE_INFO.skew_rows for both tables afterwards, because a business account with many payments could skew the key.
Plan: (1) query SYS_QUERY_HISTORY for the new team's queries by user, elapsed time and volume scanned; (2) check SYS_SERVERLESS_USAGE for RPU consumption by hour; (3) look for dashboards refreshing every minute, SELECT * on wide tables and Spectrum scans of unpartitioned data. Likely fixes: move their refresh to a schedule and SPICE (cache) in Quick Sight; give them a materialised view for their aggregate; add a daily usage limit and query monitoring rules that log or abort scans over a threshold. Communicate the cost per team using query labels or separate workgroups.
Knowledge check
Answer: Each block stores min/max values (zone maps). Sorted by date, most blocks fall entirely outside the month and are skipped without being read. An index seek (B-tree) is not involved — that is the SQL Server mental model, not Redshift's.
Answer: ALL. A copy on every node means the join never moves data. KEY would not help because the fact table is distributed on a different column; EVEN would force redistribution.
Answer: Managed storage (and snapshots), not compute. Compute is billed per RPU-second only while work runs, with a 60-second minimum per period of activity.
Answer: Ad hoc or infrequent queries, small-to-medium data, few concurrent users, and when you want zero infrastructure. Athena charges per TB scanned with nothing to manage; Redshift wins for heavy, repeated, concurrent workloads.
Interview questions
- "How do you choose a distribution key?" — Pick the column used in the largest, most frequent joins, with high cardinality and even spread; use
ALLfor small dimensions; verify withEXPLAINand skew metrics; start withAUTOif unsure. - "Serverless or provisioned?" — Serverless for spiky or unpredictable use and small teams; provisioned RA3 with reserved pricing when the warehouse is busy most hours. Always model both with real usage.
- "How would you load data from the lake into Redshift safely?" —
COPYfrom curated Parquet with an IAM role limited to that bucket and key, enhanced VPC routing, then reconcile counts and totals against the lake before publishing. - "What is Spectrum for?" — Querying S3 data in place from Redshift, for cold history or very recent partitions not yet loaded, joined with warehouse tables.
Certification practice
A bank loads 2 billion card transactions into Amazon Redshift. Most queries join transactions to a 50-million-row accounts table on account_id and filter by the last 30 days. Analysts report high data redistribution in query plans. What should the data engineer do?
A. Use DISTSTYLE ALL on both tables.
B. Distribute both tables with KEY on account_id, and use a sort key on the transaction date.
C. Use DISTSTYLE EVEN on both tables and an interleaved sort key on every column.
D. Move the accounts table to Amazon DynamoDB.
Answer: B. Same distribution key collocates the join; the date sort key lets zone maps skip old blocks. A copies two large tables to every node (storage and load cost explode). C guarantees redistribution and interleaved keys on many columns are costly. D breaks the join entirely.
A team must load hourly Parquet files from an encrypted S3 bucket into Redshift Serverless with the least operational overhead and no long-lived credentials. Which approach is best?
A. A Lambda function that inserts rows one by one through a JDBC connection using a database password in environment variables.
B. COPY ... FORMAT AS PARQUET using an IAM role associated with the namespace that can read the bucket and decrypt with the KMS key.
C. Download the files to an EC2 instance and use psql with \copy.
D. Convert the files to CSV, then use COPY with access keys in the command.
Answer: B. Parallel load, IAM role, no secrets. A is slow and stores a password. C adds a server to manage. D adds a conversion step and embeds keys.
A small risk team runs a few complex SQL queries a week over 300 GB of Parquet data in S3. They want the lowest cost and no infrastructure to manage. What should a solutions architect recommend?
A. A provisioned Redshift cluster with reserved nodes. B. Amazon Athena over the Glue Data Catalog. C. Amazon RDS for SQL Server with the data loaded nightly. D. Amazon EMR running 24×7.
Answer: B. Pay per query scanned, nothing to run. A and D bill continuously; C is an OLTP engine and requires loading.
Completion checklist
- I can explain MPP, slices, distribution and sort keys to a colleague in two minutes
- I wrote DDL with deliberate
DISTKEY/SORTKEYchoices for transactions and a dimension - I can estimate a Serverless bill from RPU, hours and storage
- I know how to load with
COPYand an IAM role, then reconcile - I deleted any workgroup, namespace and snapshots I created
Key takeaways
- Redshift is a columnar MPP warehouse for heavy, repeated, concurrent analytics; Athena stays the default for ad hoc lake queries.
- Distribution keys collocate joins; sort keys let zone maps skip blocks. Measure skew and read
EXPLAIN. - Load with
COPYfrom curated Parquet using an IAM role, then reconcile before publishing. - Serverless bills RPU-seconds while busy (from 4 RPUs) plus storage; set usage limits.
- Keep raw PII out: the warehouse holds curated, tokenised data only.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Amazon Athena: partition pruning, CTAS, workgroups with cost limits and encrypted results
Query Harbourline's lake with Amazon Athena: prune partitions, compare bytes scanned on CSV and Parquet, build gold tables with CTAS, INSERT INTO and UNLOAD, cap spend with workgroup data-usage controls, encrypt query results, and run parameterised queries from C#.
Streaming ingestion: Kinesis Data Streams, Data Firehose and MSK
Move Harbourline transactions from batch files to a near-real-time stream: produce to Kinesis from C#, consume with a .NET Lambda that reports partial batch failures, deliver to S3 with Data Firehose, and know when Amazon MSK is the better fit.