Cloud / Amazon Redshift Interview questions

Amazon Redshift is AWS's fully managed, petabyte-scale cloud data warehouse. It is built for analytical (OLAP) queries - large scans, joins and aggregations over billions of rows - not for transaction processing.

Data is stored in a columnar format and queries run across many nodes using massively parallel processing (MPP). You talk to it with PostgreSQL-compatible SQL over JDBC, ODBC or the Data API, so BI tools such as Tableau, Power BI and QuickSight connect without much effort.

You can run it as a provisioned cluster or as Redshift Serverless, and it can also query files sitting in an S3 data lake through Redshift Spectrum.

A Redshift cluster has three building blocks: one leader node, one or more compute nodes, and node slices inside each compute node.

Clients connect only to the leader node. It plans the query and hands pieces of work to the compute nodes. Each compute node is divided into slices, and every slice scans and processes its own share of the data in parallel. Data lives either on local SSD (DC2) or in Redshift managed storage (RA3).

flowchart TD
  Client["SQL client (JDBC / ODBC)"] --> Leader["Leader node"]
  Leader --> CN1["Compute node 1: slice 0, slice 1"]
  Leader --> CN2["Compute node 2: slice 2, slice 3"]
  CN1 --> Store["Storage: local SSD or RA3 managed storage"]
  CN2 --> Store

A single-node cluster is the exception: the leader and compute functions share one node.

The leader node is the coordinator of the cluster. It accepts client connections, parses the SQL, builds and optimizes the execution plan, compiles it into code, and sends that code to the compute nodes.

When the slices finish, the leader merges the partial results (final aggregation, sorting, LIMIT) and returns them to the client. It also holds the system catalog metadata. It does not store user table data, and in a multi-node cluster you are not billed separately for it.

A few functions, such as GENERATE_SERIES(), run only on the leader node, so combining them with user tables raises an error.

A node slice is a partition of a compute node. Each slice gets its own share of the node's memory and disk and works on its own portion of the data, so slices run in parallel and independently.

The number of slices depends on the node type: ra3.xlplus and dc2.large have 2 per node, ra3.4xlarge has 4, and ra3.16xlarge and dc2.8xlarge have 16. The distribution style decides which slice stores which row.

Slice count matters during loading. A COPY works best when the number of input files is a multiple of the slice count, so every slice has something to read. You can list slices with the STV_SLICES system table.

Columnar storage means Redshift keeps the values of each column together in disk blocks, instead of storing complete rows one after another. Each block is 1 MB.

Analytical queries usually touch a few columns out of many. With columnar storage, SELECT SUM(amount) FROM sales reads only the blocks of the amount column, even if the table has 50 columns. That cuts disk I/O sharply.

It also compresses better, because a column holds one data type and often repeats values. Redshift keeps min/max metadata per block (zone maps) to skip blocks that cannot match a filter.

MPP means a query is split into pieces that run at the same time on every slice, each working only on its own share of the data. It is a shared-nothing design: slices do not compete over a single disk or CPU.

The leader node creates the plan, the slices execute it in parallel, and the leader merges the partial results. A scan of 10 billion rows becomes many small scans running side by side.

Adding nodes adds more slices and so more parallelism. The catch is data movement: when a join needs rows that sit on a different slice, Redshift must redistribute or broadcast them, and that is where MPP queries usually slow down.

Provisioned Redshift offers two current families: RA3 (ra3.xlplus, ra3.4xlarge, ra3.16xlarge) and the older DC2 (dc2.large, dc2.8xlarge). Older DS2 nodes are legacy.

RA3 nodes use Redshift managed storage: data lives in S3-backed storage and hot blocks are cached on local SSD, so compute and storage scale separately. DC2 nodes keep everything on local SSD, so storage grows only by adding nodes.

AWS generally recommends RA3 for new clusters. If you do not want to pick nodes at all, Redshift Serverless is the alternative.

Redshift has four distribution styles that control how table rows are spread across slices: AUTO, EVEN, KEY and ALL.

The goal is to avoid moving data at join time and to keep slices evenly loaded.

A sort key defines the physical order in which rows are stored on disk. Redshift records the min and max value of every block (zone maps), so a filter on the sort key column lets it skip every block outside the requested range.

Choose columns that are filtered or range-scanned most often, usually a timestamp or date. Redshift supports compound (default), interleaved and AUTO sort keys.

CREATE TABLE sales (
  sale_id   BIGINT,
  sale_date DATE,
  amount    DECIMAL(12,2)
)
SORTKEY (sale_date);

Sort order also helps merge joins and ORDER BY, because less sorting is needed at run time.

Compression encodings are per-column methods that shrink data on disk, which also reduces I/O and speeds up scans. Common ones are AZ64 (numbers, dates, timestamps), ZSTD (general purpose), LZO, BYTEDICT, RUNLENGTH and RAW (none).

If you do not specify encodings, Redshift uses ENCODE AUTO and picks and adjusts them for you. A COPY into an empty table can also analyze a sample and apply encodings automatically.

CREATE TABLE events (
  event_id   BIGINT       ENCODE AZ64,
  event_type VARCHAR(30)  ENCODE BYTEDICT,
  payload    VARCHAR(500) ENCODE ZSTD
);

ANALYZE COMPRESSION events;

ANALYZE COMPRESSION reports the encoding it would recommend for each existing column.

COPY is the standard way to bulk load data into a Redshift table. It reads files in parallel across all slices from Amazon S3, DynamoDB, EMR, or remote hosts over SSH, and supports CSV, JSON, Avro, Parquet and ORC.

COPY sales
FROM 's3://my-bucket/sales/2026/10/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS CSV
GZIP
IGNOREHEADER 1
REGION 'us-east-1';

The cluster needs an IAM role with read access to the bucket, so no access keys have to appear in the SQL. COPY is far faster than row-by-row INSERTs because every slice loads its own files. You can also create a COPY job so new S3 files load automatically.

UNLOAD exports the result of a SELECT query to files in Amazon S3. It is the reverse of COPY and also runs in parallel, writing at least one file per slice by default.

UNLOAD ('SELECT * FROM sales WHERE sale_date >= ''2026-01-01''')
TO 's3://my-bucket/exports/sales_'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftUnloadRole'
FORMAT AS PARQUET
PARTITION BY (sale_date);

Typical uses are archiving cold data, feeding a data lake, and handing data to other tools. Useful options include FORMAT AS PARQUET, PARTITION BY, MANIFEST and PARALLEL OFF (a single file stream, slower). Files can be encrypted with KMS.

Redshift Spectrum lets you run SQL on data stored in Amazon S3 without loading it into Redshift tables. You define an external schema and external tables, then query them like any other table and even join them with local tables.

CREATE EXTERNAL SCHEMA lake
FROM DATA CATALOG DATABASE 'clickstream'
IAM_ROLE 'arn:aws:iam::123456789012:role/SpectrumRole';

SELECT page, COUNT(*) FROM lake.page_views GROUP BY page;

Table definitions usually live in the AWS Glue Data Catalog (or Athena, or a Hive metastore). A separate Spectrum layer scans and filters the S3 files and sends the reduced result to your cluster. It reads Parquet, ORC, CSV, JSON and Avro, and is billed by the data scanned.

Redshift Serverless is a mode where you run a data warehouse without provisioning or managing nodes. It scales compute up and down automatically and bills only for the time your queries use compute.

It has two main objects: a namespace (databases, users, encryption settings, snapshots) and a workgroup (the compute, networking and security settings). Capacity is measured in RPUs (Redshift Processing Units); the default base capacity is 128 RPUs and you can change it, and set maximum limits to control spend.

Compute is billed per RPU-hour, metered per second, and storage is billed separately. It suits spiky or unpredictable workloads.

VACUUM reclaims disk space left by deleted or updated rows and re-sorts rows into sort key order. Redshift does not remove deleted rows immediately; it only marks them, so space comes back after a vacuum.

VACUUM FULL sales TO 95 PERCENT;

Redshift also runs an automatic vacuum in the background when load is low, but large deletes or loads often still justify a manual run.

ANALYZE updates table statistics (row counts, distinct values, value distribution) that the query planner uses to choose join order, join type and memory allocation. Stale statistics lead to poor plans.

ANALYZE sales;
ANALYZE sales PREDICATE COLUMNS;

Redshift runs automatic analyze in the background, but after a big load you can run it yourself. PREDICATE COLUMNS analyzes only columns used in filters, joins and sorts, which is cheaper on wide tables.

The stats_off column in SVV_TABLE_INFO shows how stale a table's statistics are; 0 is current and higher means more drift.

In Redshift, primary key, unique and foreign key constraints are informational only. They are not enforced, so you can insert duplicate key values without an error. Only NOT NULL is actually enforced.

They are still worth declaring because the query planner uses them as hints. Knowing a column is unique can remove unnecessary work, for example in some joins and aggregations.

Because nothing is enforced, the pipeline must guarantee uniqueness itself, usually by deduplicating in a staging table before the final load. Wrong constraints can produce wrong query results, so declare only what is true.

A materialized view stores the precomputed result of a query as a physical table-like object. Queries read the stored result instead of re-running the joins and aggregations each time.

CREATE MATERIALIZED VIEW daily_sales
AUTO REFRESH YES
AS SELECT sale_date, SUM(amount) AS total
   FROM sales
   GROUP BY sale_date;

Refresh it with REFRESH MATERIALIZED VIEW daily_sales; or let AUTO REFRESH YES do it. Redshift applies an incremental refresh where it can, instead of recomputing everything. Materialized views can also sit on external tables and on streaming sources.

Redshift has two kinds of snapshots: automated and manual. Both are incremental backups stored in S3, and restoring one always creates a new cluster.

Snapshots can be copied to another AWS Region for disaster recovery, and shared with other accounts.

Workload management controls how queries are queued and how much concurrency and memory they get, so a few heavy queries do not starve short, interactive ones.

By default Redshift uses automatic WLM, which decides concurrency and memory per query. With manual WLM you define up to 8 queues and assign queries by user group or query group. Both support query monitoring rules that log, hop or abort queries that break a limit, for example a runtime above 60 seconds.

Short query acceleration (SQA) additionally sends short queries to a fast lane ahead of long ones.

Concurrency Scaling adds temporary extra clusters when many users run queries at once, so queries do not wait in the queue. Eligible queries are sent to these transient clusters automatically and results come back through the same endpoint.

You enable it per WLM queue and cap it with max_concurrency_scaling_clusters. Redshift accrues about 1 hour of free scaling credit for every 24 hours your main cluster runs; usage beyond the free credits is billed per second.

It handles read workloads and many write operations such as COPY and INSERT, and the scaling clusters see the same up-to-date data as the main cluster.

SUPER stores semi-structured data such as JSON, arrays and nested objects in a single column without defining a fixed schema up front. You query it with PartiQL-style dot and bracket navigation.

INSERT INTO events
VALUES (JSON_PARSE('{"type":"order","customer":{"name":"Asha"}}'));

SELECT payload.customer.name
FROM events
WHERE payload.type = 'order';

It is schema-on-read, so new JSON attributes do not need ALTER TABLE. Use JSON_PARSE to turn text into SUPER, and unnest arrays by joining the column in the FROM clause. For hot attributes, move them into ordinary typed columns for better compression and speed.

There are three common paths: JDBC/ODBC drivers, language connectors such as redshift_connector for Python, and the Redshift Data API. The console also provides Query Editor v2 for browser-based SQL.

Drivers open a persistent connection to the cluster endpoint on port 5439, so the security group must allow your client. Authentication can use database users or IAM, including federated SSO.

The Data API is a plain HTTPS call: you submit SQL, get a statement ID, and fetch results later. There is no connection to manage, which is ideal for Lambda functions and other short-lived callers.

Pick the column that your largest and most frequent joins use, so matching rows from both tables land on the same slice and no data has to move at query time.

A good distribution key also has high cardinality and an even spread of values. Avoid columns with many NULLs or a few hot values, and avoid columns used for narrow filters: if you distribute on event_date, one day's rows all land on a single slice and that slice does all the work.

For example, if orders and customers join on customer_id, declare DISTKEY(customer_id) on both. Small dimension tables go to DISTSTYLE ALL. If no column is a clear winner, use EVEN or AUTO.

A compound sort key sorts by the first column, then the second, and so on, like a phone book. An interleaved sort key gives each listed column (up to 8) equal weight.

If a query filters only on the second column of a compound key, zone maps barely help. That is the case interleaved keys target, but the maintenance cost is why compound or AUTO is usually the better choice.

The key difference is where data lives. RA3 nodes use Redshift managed storage (backed by S3) with a local SSD cache, so you scale compute and storage independently. DC2 nodes store all data on local SSD, so more storage means more nodes.

For a 200 TB warehouse with modest compute needs, RA3 avoids paying for nodes you only need for disk space.

Elastic resize changes the node count (and in some cases the node type) of the existing cluster by redistributing slices across nodes. It usually completes in minutes, with a short interruption while connections are dropped.

Classic resize provisions a new target cluster and copies all data across. The source stays read-only while that happens, which can take hours or days for large datasets.

Use elastic resize for routine scaling and classic resize when elastic cannot reach the configuration you want.

Redshift is a data warehouse with its own managed storage and compute, tuned through distribution keys, sort keys and workload management. Athena is a serverless query service that reads files directly from S3 and charges per TB scanned.

Many teams use both: Athena to explore raw data and Redshift for curated, frequently queried datasets.

Redshift is optimized for scanning large volumes of data, not for fast single-row reads and writes. Storage is columnar in 1 MB blocks, so changing one row touches several column blocks.

Updates and deletes are delete-plus-insert operations that leave ghost rows until a vacuum, and each commit has a noticeable cost, so thousands of small transactions per second queue up. There are also no enforced unique constraints or b-tree indexes to speed point lookups.

Use Amazon Aurora, RDS or DynamoDB for the transactional side, then move data into Redshift with batch loads or zero-ETL integrations for analytics.

With a provisioned cluster you choose node type and count and pay for them while the cluster is running. With Serverless you choose a base RPU capacity and Redshift scales compute automatically, billing per second only when queries run.

Spiky or unpredictable workloads usually fit Serverless. Steady, high-utilization workloads running around the clock often cost less on provisioned clusters with reserved nodes.

With automatic WLM Redshift decides how many queries run concurrently and how much memory each gets, using the query's needs and current load. You influence it with query priorities (lowest to highest).

With manual WLM you define up to 8 queues yourself, each with a fixed concurrency level and memory share, and route queries by user group or query group. Total concurrency across user queues is capped at 50.

Automatic WLM is the default and works well for mixed workloads. Choose manual only when you need hard isolation, such as guaranteeing capacity for a specific team or job.

VACUUM FULL (the default) reclaims space from deleted rows and re-sorts the table. VACUUM DELETE ONLY only reclaims space; it does not restore sort order, so it is faster.

Use DELETE ONLY when rows were loaded in sort key order and you mostly delete old data, for example dropping last year's partitions of a time-series table. The remaining rows are still sorted, so there is nothing to re-sort.

Use FULL when SVV_TABLE_INFO shows a high unsorted percentage after many updates or out-of-order loads. By default VACUUM skips the sort step if the table is already more than 95% sorted.

Redshift uses multiversion concurrency control. A DELETE does not erase the row; it marks it as deleted, leaving a ghost row on disk. An UPDATE is a delete of the old version plus an insert of a new version.

New row versions are written to the unsorted region at the end of the table. Scans still read the ghost rows, and zone maps get less effective as the unsorted region grows, so queries slowly degrade.

A VACUUM removes ghost rows and merges the unsorted region back into sort order, and ANALYZE refreshes statistics. Redshift runs both automatically in the background when it is quiet, but heavy churn tables need watching.

COPY speed depends on how well the load is spread across slices. These are the settings that matter most:

1. Split the input into many files of similar size, ideally a multiple of the slice count (roughly 1 MB to 1 GB each after compression).
2. Compress files with gzip, bzip2 or zstd, or use columnar Parquet/ORC.
3. Use one COPY command for all the files in a prefix or manifest, not many parallel COPYs into the same table.
4. Load in sort key order to avoid a large unsorted region.
5. On repeated loads into populated tables, turn off per-load compression and stats work (COMPUPDATE OFF STATUPDATE OFF) and run ANALYZE once at the end.
6. Keep the bucket in the same Region as the cluster.

A single huge file is read by one slice, so the other slices sit idle.

Security is layered. Each layer covers a different risk:

1. Network - run the cluster in a VPC, use private subnets and security groups, and turn on enhanced VPC routing so COPY/UNLOAD traffic stays inside your VPC.
2. Authentication - IAM, federated SSO, or Secrets Manager rather than shared passwords.
3. Authorization - GRANT on schemas and tables, roles, column-level access, row-level security and dynamic data masking.
4. Encryption - at rest with AWS KMS, and in transit with SSL/TLS (set require_ssl to reject plain connections).
5. Auditing - audit logging to S3 or CloudWatch Logs, plus CloudTrail for API calls.

Use a materialized view when the same expensive query runs repeatedly and slightly stale data is acceptable, such as a dashboard that aggregates a billion rows every few minutes.

Stay with a regular view when data must be real-time, the base tables are small, or the query is cheap. Redshift can also rewrite eligible queries to use a materialized view automatically.

A late-binding view is created with WITH NO SCHEMA BINDING, so it is not tied to the underlying objects when created. You can drop and recreate the base tables without having to drop the view first.

CREATE VIEW reporting.orders_v AS
SELECT order_id, amount
FROM public.orders
WITH NO SCHEMA BINDING;

They are required for views over external (Spectrum) tables and useful when ETL regularly rebuilds tables. Two caveats: you must schema-qualify every object name, and Redshift does not check the underlying objects until the view is queried, so a typo only shows up at query time.

Redshift supports the MERGE statement, which updates matching rows and inserts new ones in one command.

MERGE INTO sales
USING sales_staging s
ON sales.sale_id = s.sale_id
WHEN MATCHED THEN UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN INSERT (sale_id, amount) VALUES (s.sale_id, s.amount);

The older, still common pattern uses a staging table inside one transaction: load staging with COPY, DELETE FROM sales USING sales_staging s WHERE sales.sale_id = s.sale_id, then INSERT INTO sales SELECT * FROM sales_staging, then commit.

Remember that primary keys are not enforced. Deduplicate the staging data first, because duplicate source keys can insert duplicates or make a MERGE fail. PostgreSQL's ON CONFLICT syntax is not available.

A query moves from the client to the leader node, is planned and compiled, runs in parallel on the slices, and the leader merges the partial results back into one answer.

flowchart TD
  A["Client sends SQL"] --> B["Leader parses and checks result cache"]
  B --> C["Planner builds plan using statistics"]
  C --> D["Code is generated and compiled, or reused from cache"]
  D --> E["Segments sent to compute node slices"]
  E --> F["Slices scan, filter, join, aggregate"]
  F --> G["Partial results returned to leader"]
  G --> H["Leader merges and returns result"]

1. Parse and cache check - the leader validates the SQL and returns a cached result if one is valid.
2. Plan - the optimizer uses table statistics to pick join order and data movement.
3. Compile - the plan is split into streams, segments and steps and turned into compiled code; previously compiled segments are reused.
4. Execute - each slice scans its blocks (skipping some via zone maps), filters, joins and aggregates; rows move between slices only if a join requires it.
5. Merge - the leader performs the final aggregation, sort and LIMIT, then returns rows to the client.

The first run of a new query shape pays a compile cost, which is why a query can be slower the first time than on repeat runs.

Redshift must move rows across the network at query time so that matching join keys end up on the same slice. This shows up in EXPLAIN as a DS_* label, and heavy movement is a common cause of slow joins.

Fixes: give both tables the same DISTKEY on the join column, use DISTSTYLE ALL for small dimensions, or reduce the rows involved with filters before the join.

A zone map is metadata that records the minimum and maximum value of each column in every 1 MB block. When a query filters on a column, Redshift compares the predicate to those ranges and reads only blocks that could contain matches.

Example: if sale_date is the sort key and each block holds about one day of data, WHERE sale_date = '2026-09-15' touches only a handful of blocks. If the same column is in random order, almost every block's range spans the whole year and nothing can be skipped.

That is why a sort key on the most commonly filtered column matters, and why a large unsorted region hurts: new rows there have wide, overlapping ranges. Regular vacuuming restores tight ranges.

Skew means some slices hold or process far more data than others, so the whole query waits for the busiest slice. Start by measuring it:

SELECT "table", diststyle, skew_rows, size
FROM svv_table_info
ORDER BY skew_rows DESC;

skew_rows is the ratio of rows on the fullest slice to the emptiest. Values far above 1 are a warning. For a running query, SVL_QUERY_REPORT shows rows and time per slice, and one slow slice with the most rows confirms the problem.

Typical causes and fixes:

• Low-cardinality or hot distribution key - choose a more even column, or use EVEN/AUTO.
• NULLs in the key - all NULLs land on one slice, so filter, replace or choose another key.
• Filtering on the distribution key - concentrates work on few slices.

After changing the key, rebuild the table with CREATE TABLE AS or ALTER TABLE ... ALTER DISTKEY and re-check the numbers.

First decide whether the query is slow because it waited or because it ran slowly. Then work down the list:

1. Find the query in SYS_QUERY_HISTORY and compare queue_time against execution_time. High queue time points to WLM, so consider concurrency scaling, short query acceleration, or priorities.
2. Run EXPLAIN and look for large DS_DIST_BOTH / DS_BCAST_INNER steps and nested loop joins.
3. Check per-step timing and disk spill in SVL_QUERY_SUMMARY (is_diskbased = t means it ran out of memory).
4. Read STL_ALERT_EVENT_LOG, which flags missing statistics, nested loops and very selective filters.
5. Check SVV_TABLE_INFO for high unsorted or stats_off, then VACUUM and ANALYZE.
6. If it is still slow, revisit distribution and sort keys or select fewer columns.

A disk-full error usually comes from table data, intermediate query results, or both. Check which one first.

SELECT owner, host, diskno, used, capacity,
       used * 100 / capacity AS pct_used
FROM stv_partitions
ORDER BY pct_used DESC;

If usage is high even when idle, look at the biggest tables in SVV_TABLE_INFO, run VACUUM to clear deleted rows, and check skew. If it spikes only during a query, a join with a missing or weak predicate (a near cross join) is probably creating a huge intermediate result; SVL_QUERY_SUMMARY with is_diskbased = t confirms spilling.

Fixes: add proper join conditions, select fewer columns, fix skew, drop unused tables, give the queue more memory through fewer slots, or move to RA3 or Serverless so table storage is no longer tied to node disk.

Spectrum cost and speed come down to how much S3 data it has to read. The levers are:

1. Columnar formats - store data as Parquet or ORC, compressed, so only the needed columns are read.
2. Partitioning - partition S3 by columns you filter on, such as dt, and include that column in the WHERE clause so partitions are pruned.
3. File sizing - aim for files of roughly 64 MB to 1 GB; thousands of tiny files hurt.
4. Table statistics - set row counts so the planner can order joins well.
5. Keep hot, small tables local - join large external tables with local dimensions.

ALTER TABLE lake.sales SET TABLE PROPERTIES ('numRows'='170000');

Check results in SVL_S3QUERY_SUMMARY (files and bytes scanned) and SVL_S3PARTITION (partitions pruned).

When an enabled WLM queue fills up, eligible queries are redirected to transient Concurrency Scaling clusters that read the same data, instead of waiting.

flowchart TD
  Q["Queries arrive at main cluster endpoint"] --> W{"Queue full and query eligible?"}
  W -- No --> M["Run on main cluster"]
  W -- Yes --> S["Route to scaling cluster"]
  S --> D["Scaling cluster reads current data from managed storage"]
  D --> R["Results returned via same endpoint"]
  S --> X["Cluster released when demand drops"]

The scaling clusters are added within seconds and read the latest committed data, so users see consistent results. The number of clusters is bounded by max_concurrency_scaling_clusters. Some queries stay on the main cluster, for example those using temporary tables or interleaved sort keys.

When demand falls, the extra clusters are removed. Usage is covered by free daily credits first, then billed per second, so it is wise to set a usage limit.

The leader node keeps the result of eligible SELECT queries. If the same query is run again and nothing relevant changed, Redshift returns the stored result instantly without using the slices.

A cached result is used only when all of these hold: the underlying table data has not changed, the query does not use functions that must be re-evaluated each time (such as GETDATE()), the user still has the required permissions, and result caching is enabled, which it is by default.

-- for fair benchmarking
SET enable_result_cache_for_session TO off;

SYS_QUERY_HISTORY shows a result_cache_hit flag, so you can tell whether a fast run was a cache hit. Always switch the cache off when timing query changes.

Data sharing gives other warehouses live read access to your data without copying it. The producer publishes a datashare, and the consumer mounts it as a database and queries it with its own compute.

-- producer
CREATE DATASHARE sales_share;
ALTER DATASHARE sales_share ADD SCHEMA public;
ALTER DATASHARE sales_share ADD TABLE public.sales;
GRANT USAGE ON DATASHARE sales_share TO NAMESPACE 'consumer-namespace-guid';

-- consumer
CREATE DATABASE sales_db FROM DATASHARE sales_share
OF NAMESPACE 'producer-namespace-guid';

Because the data is not copied, consumers always see transactionally consistent, current data, and heavy consumer queries do not slow the producer. It works across clusters, Serverless workgroups, accounts and Regions (cross-Region adds data transfer cost) and needs RA3 or Serverless.

A zero-ETL integration is a managed pipeline that replicates data from a source into Redshift without you building ETL jobs. Supported sources include Aurora MySQL, Aurora PostgreSQL, RDS for MySQL, DynamoDB and some SaaS applications through AWS Glue.

flowchart LR
  Src["Source: Aurora, RDS or DynamoDB"] --> Seed["Initial full copy"]
  Seed --> CDC["Continuous change capture"]
  CDC --> RS["Redshift target database"]
  RS --> BI["Analytics and BI queries"]

1. Create the integration, choosing the source and the target warehouse.
2. Redshift seeds the target with an initial copy, then streams ongoing changes, usually available within seconds.
3. Create a database from the integration, for example CREATE DATABASE orders_db FROM INTEGRATION '<integration-id>';.
4. Optionally filter databases and tables, and monitor with SVV_INTEGRATION and SYS_INTEGRATION_ACTIVITY.

The replicated tables are read-only. Model transformations downstream, for example with materialized views.

Say a fact_sales table has 5 billion rows, dim_customer 50 million, and dim_date and dim_store a few thousand each. Design around the joins and the filters:

CREATE TABLE dim_customer (
  customer_id BIGINT NOT NULL,
  name        VARCHAR(100)
) DISTKEY (customer_id);

CREATE TABLE dim_store (
  store_id INT NOT NULL,
  region   VARCHAR(30)
) DISTSTYLE ALL;

CREATE TABLE fact_sales (
  sale_id     BIGINT,
  sale_date   DATE NOT NULL,
  customer_id BIGINT NOT NULL,
  store_id    INT NOT NULL,
  amount      DECIMAL(12,2)
) DISTKEY (customer_id) COMPOUND SORTKEY (sale_date);

• Fact and large dimension share a DISTKEY on the join column, so that join is co-located.
• Small dimensions use DISTSTYLE ALL, avoiding broadcasts.
• Sort key on the date column used by range filters, and load data in date order.
• Encodings left on AUTO, and VARCHAR sizes kept realistic because oversized columns waste memory during joins and sorts.
• Declare primary and foreign keys as planner hints, and precompute common aggregates with materialized views.

If you are unsure about access patterns, start with DISTSTYLE AUTO and SORTKEY AUTO and let automatic table optimization adjust.