Redshift Autocopy Monitoring

To monitor failures in Redshift Autocopy jobs, you're on the right track by using SYS_COPY_JOB_DETAIL to check the status and SYS_COPY_JOB_INFO for error messages.

While the documentation explicitly states that system tables with the STL prefix (like STL_LOAD_ERRORS) retain data for approximately 7 days, there isn't specific documentation that confirms the exact retention period for the SYS_COPY_JOB_INFO view.

However, since SYS_COPY_JOB_INFO is part of the system views used to monitor COPY JOB status and progress (along with SYS_COPY_JOB and SYS_COPY_JOB_DETAIL), it's reasonable to assume it follows similar retention policies as other system views and tables in Redshift.

For a comprehensive monitoring solution, I would recommend querying multiple system views to get complete information about your auto-copy jobs:

1. SYS_COPY_JOB_DETAIL - For status of files (pending, ingested, error)
2. SYS_COPY_JOB_INFO - For logged messages about COPY JOBs
3. SYS_COPY_JOB - For information about defined COPY JOBs
4. STL_LOAD_ERRORS - For specific errors from COPY commands
5. SYS_LOAD_ERROR_DETAIL - For details of COPY command errors

If you need to retain this information beyond the system's retention period, consider implementing a process to regularly extract and store this data in a separate table for long-term monitoring and analysis.
Sources
Troubleshooting S3 event integration and COPY JOB errors - Amazon Redshift
Create an S3 event integration to automatically copy files from Amazon S3 buckets - Amazon Redshift