Journal Adjustment

The journal adjustment transfer is a more advanced example of uploading an excel workbook and grabbing the data from a sheet by name from the workbook and then loading that data into the journal adjustment fact table.

Use Case: When accountants or auditors come in with their magic wands (spreadsheets), they might decree, "This sale was actually a return," or "That discount wasn't supposed to be that generous." Time for some manual adjustments!

Navigate to the Admin -> Transfers page, and select "example - Journal Adjustment" from the drop down menu to view the entire configuration for the upload.

The following features are configured for this transfer:

• Excel file for source

  • Grab the sheet that matches the name "journal_adjustments" from the workbook

  • Limit the file selection to .xlsx files

• Allow downloads of the uploaded files

• Upload history shows files uploaded from all users

Where the features are more advanced:

• Use a custom sql insert statement to load the table

• Use Snowflake functions to populate target fields

• Use environment variables to add the username and upload_log_id to the target table

• Change the order of the columns from the source to match the layout of the table

• Skip the first line of the staged file with sql window function

• Add the current time to the target table lastupdated field

source data layout

OriginalTransactionID,AdjustmentType,AdjustmentAmount,AdjustmentDate,Comments
1001,Return,-50.00,2022-01-15,"Corrected sale to return"
1002,Discount,-10.00,2022-02-20,"Adjusted discount amount"
1003,Correction,25.00,2022-03-05,"Corrected transaction amount"
1004,Return,-30.00,2022-04-10,"Reversed incorrect sale"

table columns:

AdjustmentGUID VARCHAR(60) PRIMARY KEY,
OriginalTransactionID INT,
AdjustmentDate DATE,
AdjustmentAmount DECIMAL(10, 2),
AdjustmentType VARCHAR(50),
Comments VARCHAR(255),
upload_log_id VARCHAR(255),
username VARCHAR(255),
LastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP

The following custom sql is run and makes use of the replacement variables available in the transfer app.

insert into {tableName} 
     (AdjustmentGUID,OriginalTransactionID,AdjustmentDate,AdjustmentAmount, AdjustmentType,
        Comments,upload_log_id,username,LastUpdated)

select uuid_string() as AdjustmentGUID
     , $1 as OriginalTransactionID
     , $4 as AdjustmentDate
     , $3 as AdjustmentAmount
     , $2 as AdjustmentType
     , $5 as Comments
     , '{logId}' as upload_log_id
     , '{username}' as username
     , current_timestamp() as LastUpdated
  FROM (
               SELECT $1,$2,$3,$4,$5, ROW_NUMBER() OVER (ORDER BY NULL) AS row_num
                 FROM @{stageFilePath}
        )
 QUALIFY row_num > 1
 ;

Replacement Variables for Custom SQL:

• ${stageFilePath} - the location of the file uploaded

• ${tableName} - db.schema.table defined for the transfer

• ${logId} - the log id for the upload

• ${username} - the snowflake username

The attached file can be used to test the upload.

15KB

leads_and_journals.xlsx

Open