Thanks for the detailed background on the problem. As the X-SMALL warehouse has limited computing capacity, a limited number of files are ingested concurrently. You have multiple thousand files with 10-50 MB. Using a larger warehouse (large, x-large) should parallelize the bulk loading operation. Refer to this doc on improving bulk loading.

Regarding why you cannot load the historical files using SnowPipe, Snowflake doesn't have information on files created before SQS notifications are made. In Snowflake docs, there is a step to load history using bulk load. Please refer to this docs https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3#step-5-load-historical-files