Unload data from redshift with CSV file type to S3

To ensure that your unloaded files have a .csv extension, you can modify your UNLOAD command by adding the EXTENSION option. Here's how you can adjust your query:

UNLOAD ('SELECT * FROM {table_name} where {where_clause}') TO 's3://{s3_bucket}/api_testing/{table_name}_{filter_filename}' IAM_ROLE 'arn:aws:iam::839052322807:role/RedshiftAccessRole' CSV DELIMITER AS ',' ALLOWOVERWRITE PARALLEL OFF EXTENSION 'csv'

By adding the EXTENSION 'csv' option at the end of your UNLOAD command, Amazon Redshift will append the .csv extension to your unloaded files. This will result in files with names like staging.customer_ecs_stg_last_pay_date_20240314_site_0080000.csv in your S3 bucket.

The EXTENSION option allows you to specify the file extension for the unloaded files, making it easier to identify them as CSV files and work with them in other applications that expect .csv file extensions.

Remember that the EXTENSION option is used to add the specified extension to the file names, not to change the file format itself. Your files are already in CSV format due to the CSV option in your UNLOAD command; this change just ensures the file names reflect that format with the appropriate extension.
Sources
UNLOAD - Amazon Redshift
Unloading data to Amazon S3 - Amazon Redshift