Redshift CTAS disk full errors

Introduction

Redshift CTAS disk full errors happen when a CREATE TABLE AS SELECT operation generates more intermediate data than the cluster can safely process. In Kleene, this usually points to a query design issue rather than a storage issue with the final table. A common cause is an unintended Cartesian product, where a query joins or cross joins far more rows than expected.

Issue description

A transform failed in Redshift with the following error:

ERROR: Insufficient system resources to support data size (Disk Full)

The failure occurred during query execution because Redshift had to process an unexpectedly large intermediate result set. The final output was not necessarily the problem; the issue was the amount of temporary data created while preparing the result.

Signs

You may be dealing with this issue if a Redshift transform fails with a disk full or insufficient resources message, especially when the query includes joins, cross joins, scaffolding logic, or date expansion. The query may appear logically correct, but Redshift may still create a very large intermediate dataset before filtering or aggregation is applied.

In this case, stv_exec_state showed more than 1 billion intermediate rows and around 300GB of processing, which indicated that the query was creating far more rows than expected.

Basic troubleshooting steps

Start with the following checks to narrow down the cause of the error:

• Review the full Redshift error message and confirm whether the failure refers to disk space, insufficient resources, or intermediate data size.
• Check the transform SQL for joins or cross joins that could multiply row counts unexpectedly.
• Inspect the row counts of each source table used in the final query.
• Use Redshift diagnostic views such as stv_exec_state to understand how many rows are being processed.
• Compare the expected output grain with the grain of each table used in the query.
• Look for places where a full table is being used when only a distinct scaffold is required.

Common causes and how to fix them

Unintended Cartesian joins

A Cartesian join happens when rows from one dataset are matched with many rows from another dataset without a restrictive join condition. This can cause Redshift to generate a very large intermediate result set, even if the final result appears much smaller.

How to fix it: review the join logic and confirm that every join has the correct keys. If the query needs a scaffold, make sure the scaffold contains only the distinct combinations required for the output.

Using a full table as a scaffold

In one case, the final query was cross joining the full employee table. That meant Redshift was multiplying the result by every employee row, rather than working from the smaller set of distinct month, site, and region combinations needed by the transform.

How to fix it: replace the full table with a distinct scaffold. For this case, the employee table was replaced with:

SELECT DISTINCT month_date, site, current_region

This reduced the number of rows Redshift needed to process and removed the unnecessary Cartesian explosion.

Intermediate data larger than the final output

A query can fail even if the final table would be a reasonable size. Redshift still needs to materialise and process intermediate stages, and those stages can become much larger than the final output when joins, filters, or aggregations are applied in the wrong order.

How to fix it: reduce the dataset as early as possible. Apply filters, deduplicate scaffolds, and aggregate before large joins where appropriate.

Query grain mismatch

A grain mismatch occurs when one part of the query operates at a different level of detail than another. For example, joining employee-level data to month/site/region-level data can multiply rows if the query does not explicitly reduce the employee table first.

How to fix it: define the expected grain of the final result, then make sure each joined dataset is reduced to that grain before it is joined.

Practical troubleshooting workflow

1. Read the Redshift error message and confirm that the failure is related to disk space or insufficient system resources.
2. Check Redshift execution diagnostics, such as stv_exec_state, to identify whether the query is producing an unexpectedly large intermediate row count.
3. Review the SQL for cross joins or joins that may not be restricted enough.
4. Identify the expected output grain of the transform.
5. Compare each source table against that grain and look for tables that are more detailed than needed.
6. Replace full-detail tables with distinct scaffolds where only unique combinations are required.
7. Re-run the transform and confirm that the intermediate row count has reduced.
8. If the query still fails, continue simplifying the SQL and test each stage separately.

Best practices to avoid Redshift CTAS disk full errors

• Avoid cross joining full tables unless every combination is genuinely required.
• Build scaffolds using SELECT DISTINCT at the exact grain needed by the output.
• Filter and deduplicate data before joining large tables.
• Check row counts before and after each major join during development.
• Use Redshift diagnostic views to validate how much data the query is processing.
• Keep the final transform grain clear and consistent throughout the SQL.
• Test complex CTAS logic in smaller stages before materialising the final table.

Additional information

For this issue, the resolution was to replace the full employee table in the final query with a distinct month_date, site, and current_region scaffold. This removed the unnecessary Cartesian explosion and allowed the transform to run without generating excessive intermediate data.

If the same error appears again, focus first on joins and scaffolding logic. Disk full errors in Redshift are often caused by the amount of data created during execution, not just the size of the final table.