how to get actual specific column data types in snowflake using sql?

If the COLUMNS-view (https://docs.snowflake.com/en/sql-reference/info-schema/columns.html) doesn’t provide you enough information, you can try to use SHOW COLUMNS to list all columns from all tables for which you have access priviliges: https://docs.snowflake.com/en/sql-reference/sql/show-columns.html

According to the docs you can see at least presicion and scale of the data type there:

{"type":"FIXED","precision":38,"scale":0,"nullable":true}