Redshift - Why does Node of redshift cluster in private subnet has public IP.

First, when a Redshift cluster is made private, it is not accessible via internet but instead is accessible from any resources within the VPC via private IP addresses (provided the the resourced connection is whitelisted in the Security Group).

The public IP address you see in the Redshift console is different from the Elastic IP assigned to the cluster when the cluster is made public. This public IP address is present regardless of the cluster being public or private and is used in certain scenarios such as ingesting the data into the cluster from an EC2 instance or EMR cluster via SSH. For more information, please see https://docs.aws.amazon.com/redshift/latest/dg/loading-data-from-remote-hosts.html.

You can check it by performing a dig or nslookup on your Redshift cluster endpoint. You will receive the private IP if the cluster is private or an Elastic IP in case of public cluster.

dig +short [endpoint of cluster]

or

nslookup [endpoint of cluster]