CODEWITHSUNDEEP
snowflake-cloud-data-platform
Dgbow
Dgbow

Reputation: 85

Input format in snowflake

I am inputting data into my holding database "Premium_DB" in snowflake. But I noticed an omission in the numeric columns. For some reason, snowflake is removing the decimals in the holding database. For example: This is my current result

(Selected column)       (Input Column)
"GrossPremium"        "Gross_Premium"
    30.52                   30
     4.1                     4

Expected Result:

(Selected column)       (Input Column)
"GrossPremium"         "Gross_Premium"
    30.52                    30.52  
     4.1                     4.1

Is there any way to archive this? Thank you so much for your help.

set Reference_Org_Var = 'EXCHANGE_BEN';
set Load_Date_Var = current_date();


insert into PREMIUM_DB(Issue_State, Gross_Premium, Commissions, Premium_Tax, Carrier_Fee, Admin_Fee, 
Net_Premium, REPORT_DATE, FileRecdDate, Reference_Org, LoadDt
)

    select STATE, GROSSPREMIUM, COMMISSION, PREMIUMTAX, CARRIERFEE, ADMINISTRATOR, 
NETPREMIUM, REPORT_DATE, FILERECDDATE, $Reference_Org_Var, 
    
$Load_Date_Var

from EXCHANGE_BEN_PREM;

CREATE OR REPLACE TABLE PREMIUM_DB

(Reference_Org varchar,
Gross_Premium number,
Commissions number,
TPA_Fee number,
Premium_Tax number,
Carrier_Fee number,
Admin_Fee number,
Net_Premium number,
Report_Date date,
LoadDt date,
FileRecdDate date,
);

Upvotes: 1

Views: 84

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

The key is the data type:

NUMBER

Precision - Total number of digits allowed.

Scale - Number of digits allowed to the right of the decimal point.

By default, precision is 38 and scale is 0 (i.e. NUMBER(38, 0))

I suggest changing: Gross_Premium number to Gross_Premium number(38, 2).

Details: https://docs.snowflake.com/en/sql-reference/data-types-numeric.html#data-types-for-fixed-point-numbers

Upvotes: 2

Related Questions