Fast Data Loads in Redshift

Now that you have created your dimension tables and set up your star schema in Amazon Redshift, you are ready for the next step: moving data in and out of your warehouse efficiently. In the previous lesson, you learned how to organize your data for analytics by building tables that are optimized for fast queries and reporting. In this lesson, you will learn how to load large amounts of data from your S3 data lake into Redshift using the COPY command, and how to export curated datasets back to S3 using the UNLOAD command. These are essential skills for any data engineer working with Redshift, as they allow you to quickly populate your warehouse and share results with downstream users or other systems. By the end of this lesson, you will be able to use Python and the boto3 library to move data between S3 and Redshift in a way that is both fast and reliable.

Before you can load or export data, you need to make sure your Redshift environment has the right permissions and configuration. As a reminder, you will need an IAM role that allows Redshift to access your S3 buckets. This role should have both GetObject and PutObject permissions, since you will be reading from and writing to S3. You should also have your environment variables set up for your AWS region, Redshift workgroup name, database name, the ARN of your AWS Secrets Manager secret, your S3 bucket, and your AWS account ID. If you are working in the CodeSignal environment, these libraries and most environment variables are already set up for you, but it is good practice to know how to configure them on your own machine.

To connect to Redshift and run SQL commands from Python, you will use the boto3 library and the Redshift Data API. Here is a quick reminder of how to set up your client and environment variables:

import boto3
import os

REGION = os.environ.get('REGION')
WORKGROUP_NAME = os.environ['WORKGROUP_NAME']
DATABASE = os.environ['DB_NAME']
SECRET_ARN = os.environ['SECRET_ARN']
IAM_ROLE = os.environ['IAM_ROLE_ARN']
S3_BUCKET = os.environ['S3_BUCKET']
ACCOUNT_ID = os.environ['ACCOUNT_ID']

client = boto3.client('redshift-data', region_name=REGION)

With this setup, you are ready to start moving data.

To interact with Redshift from Python, you can use a helper function that executes SQL statements and waits for them to finish. This function will also fetch results for queries that return data (like SELECT), and handle errors gracefully.

import time

def run_statement(sql):
    response = client.execute_statement(
        WorkgroupName=WORKGROUP_NAME,
        Database=DATABASE,
        SecretArn=SECRET_ARN,
        Sql=sql
    )
    statement_id = response['Id']
    # Wait for the statement to finish
    while True:
        desc = client.describe_statement(Id=statement_id)
        if desc['Status'] in ['FINISHED', 'FAILED', 'ABORTED']:
            break
        time.sleep(1)
    if desc['Status'] == 'FINISHED':
        # Only try to get results if there are rows (i.e., SELECT)
        if desc.get('HasResultSet', False):
            result = client.get_statement_result(Id=statement_id)
            return result['Records']
        else:
            return None
    else:
        print(f"Statement failed: {desc.get('Error', 'Unknown error')}")
        return None

def run_command_with_retry(sql, label, retries=3, base_delay=1):
    for attempt in range(1, retries + 1):
        response = client.execute_statement(
            WorkgroupName=WORKGROUP_NAME,
            Database=DATABASE,
            SecretArn=SECRET_ARN,
            Sql=sql
        )
        statement_id = response['Id']
        while True:
            desc = client.describe_statement(Id=statement_id)
            if desc['Status'] in ['FINISHED', 'FAILED', 'ABORTED']:
                break
            time.sleep(1)
        if desc['Status'] == 'FINISHED':
            return True
        print(f"{label} failed on attempt {attempt}: {desc.get('Error', 'Unknown error')}")
        if attempt < retries:
            time.sleep(base_delay * attempt)
    return False

Let’s walk through how to load data from S3 into your Redshift fact table using the COPY command. The COPY command is designed for high-speed, parallel data loading. It can read data in various formats, but Parquet is especially efficient because it is columnar and schema-aware.

Suppose your fact table is named with your account ID for isolation, like warehouse_{ACCOUNT_ID}.fact_borrow_{ACCOUNT_ID}. Here’s how you can load Parquet data from your S3 bucket into this table:

FACT_BORROW_TABLE = f"warehouse_{ACCOUNT_ID}.fact_borrow_{ACCOUNT_ID}"

copy_sql = f"""
COPY {FACT_BORROW_TABLE}
FROM 's3://{S3_BUCKET}/processed/library/'
IAM_ROLE '{IAM_ROLE}'
FORMAT AS PARQUET;
"""

print("Running COPY command...")
run_command_with_retry(copy_sql, "COPY command")
print("COPY completed.")

This command tells Redshift to load all Parquet files from the specified S3 path into your fact table. The IAM_ROLE parameter provides the necessary permissions, and FORMAT AS PARQUET tells Redshift to expect Parquet files. Before running COPY, make sure the incoming Parquet schema is compatible with your Redshift target table: column names, order expectations, and data types should line up closely enough to avoid runtime mapping or coercion errors. In practice, it is worth validating the upstream schema before large loads so you catch mismatches early.

After loading data, it is important to verify that the expected number of rows has been loaded into your table. You can do this by running a simple SELECT COUNT(*) query:

count_sql = f"SELECT COUNT(*) FROM {FACT_BORROW_TABLE};"
print(f"Counting rows in {FACT_BORROW_TABLE}...")
result = run_statement(count_sql)
if result:
    row_count = result[0][0]['longValue']
    print(f"Successfully loaded {row_count} rows")
else:
    print("No results or table does not exist.")

This code will print the number of rows currently in your fact table, confirming that your data load was successful. A typical run might look like this:

Counting rows in warehouse_{ACCOUNT_ID}.fact_borrow_{ACCOUNT_ID}...
Successfully loaded 10 rows

Once your data is loaded and verified, you may want to share a subset of it with other teams or systems. The UNLOAD command allows you to export the results of a query from Redshift back to S3, often in Parquet format for efficiency and compatibility. You can also partition the output files by a column, which makes it easier for downstream users to work with the data.

Here is an example that exports a summary of total fines by book title to S3, partitioned by book_title. The output files are named with your account ID for uniqueness:

unload_sql = f"""
UNLOAD ($$
  SELECT b.book_title, SUM(fine_amount) AS total_fines
  FROM {FACT_BORROW_TABLE} f
  JOIN warehouse_{ACCOUNT_ID}.dim_book_{ACCOUNT_ID} b USING (book_id)
  GROUP BY 1
$$)
TO 's3://{S3_BUCKET}/curated/library/mart_book_fines_{ACCOUNT_ID}_'
IAM_ROLE '{IAM_ROLE}'
FORMAT AS PARQUET PARTITION BY (book_title);
"""

print("Running UNLOAD command...")
run_command_with_retry(unload_sql, "UNLOAD command")
print("UNLOAD completed.")

This script runs a query that joins the fact and dimension tables, groups by book title, and sums the fines. The results are exported to S3 in Parquet format, with one partition per book title. In real pipelines, you should think carefully about export layout: partition only on columns that improve downstream filtering, prefer compressed columnar output such as Parquet, and avoid producing too many tiny files because they increase storage overhead and slow later reads. Good partitioning and file sizing usually improve both query performance and cost.

In this lesson, you learned how to move data efficiently between S3 and Redshift using the COPY and UNLOAD commands. You saw how to load Parquet data into your fact table, verify the load with a row count, and export a curated data mart back to S3 for sharing or further analysis. These skills are essential for building a fast, reliable analytics warehouse on AWS. In the next set of practice exercises, you will get hands-on experience running these commands yourself, so you can confidently move data in and out of Redshift in your own projects.