Thanks @Sergio_Ferragut .
Please find below my response to your questions:
- Can you share the ingestion spec?
We are using Multi Stage Ingestion using SQL. Please find the query below. Also note that the number of tasks used is 7 while running the query.
REPLACE INTO UNSPC_TAXONOMY_fact_extrapo_zip OVERWRITE ALL
WITH ext AS (SELECT *
FROM TABLE(
EXTERN(
'{"type":"s3","prefixes":["s3://data-dev/druid_poc_1/denormalised_druid_data/UNSPC_TAXONOMY_fact_extrapo_zip/"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"product_id","type":"long"},{"name":"unspsc_1","type":"string"},{"name":"unspsc_2","type":"string"},{"name":"unspsc_3","type":"string"},{"name":"unspsc_4","type":"string"},{"name":"taxonomy_id","type":"long"},{"name":"manf_desc","type":"string"},{"name":"prod_desc","type":"string"},{"name":"sku","type":"string"},{"name":"state_id","type":"long"},{"name":"state_name","type":"string"},{"name":"state_abbr","type":"string"},{"name":"location_id","type":"long"},{"name":"zip_code","type":"long"},{"name":"monthenddatekey","type":"string"},{"name":"sales_year","type":"long"},{"name":"sales_month","type":"long"},{"name":"sales_quarter","type":"long"},{"name":"dist_total_revenue","type":"long"},{"name":"dist_total_units","type":"long"},{"name":"modified_date","type":"string"},{"name":"facility_type","type":"string"}]'
)
))
SELECT
TIME_PARSE(monthenddatekey) AS __time,
product_id,
unspsc_1,
unspsc_2,
unspsc_3,
unspsc_4,
taxonomy_id,
manf_desc,
prod_desc,
sku,
state_id,
state_name,
state_abbr,
location_id,
zip_code,
sales_year,
sales_month,
sales_quarter,
dist_total_revenue,
dist_total_units,
modified_date,
facility_type
FROM ext
PARTITIONED BY DAY
- Which log shows that error?
Middle Manager
- What does your cluster look like for each process type?

Also EBS volume mount on each of data node is 2TB
Additional Info on JVM config:
Historicals:
-server
-Xms4g
-Xmx4g
-XX:MaxDirectMemorySize=6g
-XX:+ExitOnOutOfMemoryError
-Duser.timezone=UTC
-Dfile.encoding=UTF-8
-Djava.io.tmpdir=var/tmp
-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager
Indexer
-server
-Xms4g
-Xmx4g
-XX:MaxDirectMemorySize=4g
-XX:+ExitOnOutOfMemoryError
-Duser.timezone=UTC
-Dfile.encoding=UTF-8
-Djava.io.tmpdir=var/tmp
-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager