I received a request from Shoaib Ali to have Bill To data fields populated in Snowflake. Bill To fields are not currently in scope of what we feed into Snowflake.
Solution would be to add fields "BillTo Code”, “BillTo Name”, “BillTo Address1”, “BillTo Address2”, “BillTo City”, “BillTo State”, “BillTo Zip" to the TMS_PLANNED_COST table and related views in Snowflake.
As far as I’ve checked in SelectStar, despite several report and dashboard dependencies to this table/view, I don’t believe adding more fields would be a problem. Please assess and advise feasibility.
Meanwhile I advised Shoaib that even if we add these fields, we would not be backfilling data on these fields for historical loads. But, since currently we are only publishing GEC data to Snowflake, the data on these fields would be pretty much the same for all historical records, as below screenshot:
Kayvan Shadpour
From: Ishan Trikha <Ishan.Trikha@pb.com>
Sent: Wednesday, November 16, 2022 12:24 PM
To: Kayvan Shadpour <Kayvan.Shadpour@pb.com>; David Mejia <david.mejia@pb.com>; Irina Ashurova <irina.ashurova@pb.com>
Cc: Amit Gupta <AMIT.GUPTA@pb.com>; Seth Knight <seth.knight@pb.com>; TMSCore <TMSCore@pb.com>
Subject: RE: Snowflake TMS Table Expansion Request
Hi Kayvan,
I have modified the pipeline to include the below new columns in the Planned Cost table.
BILLTO_CODE, BILLTO_NAME, BILLTO_ADDRESS1, BILLTO_ADDRESS2, BILLTO_CITY, BILLTO_STATE, BILLTO_ZIP
The updated files with the new columns have started pushing data in the table and older records have been backfilled with the values you mentioned in the previous email. I also have modified the view CROSS_BU_DD_DB_PROD.TRANSPORTATION.TMS_PLANNED_COST_VW to include the new columns. Please let me know if you have any questions.
Thanks,
Ishan
Attachments Open full size
From: Kayvan Shadpour <Kayvan.Shadpour@pb.com>
Sent: Monday, November 14, 2022 1:20 PM
To: Ishan Trikha <Ishan.Trikha@pb.com>; David Mejia <david.mejia@pb.com>; Irina Ashurova <irina.ashurova@pb.com>
Cc: Amit Gupta <AMIT.GUPTA@pb.com>; Seth Knight <seth.knight@pb.com>; TMSCore <TMSCore@pb.com>
Subject: Re: Snowflake TMS Table Expansion Request
Thanks Ishan,
Attached is a sample run of the Planned cost file with the additional fields.
Please let me know when you are ready for me to start feeding this new layout.
Regards,
Kayvan
Attachments Open full size
From: Ishan Trikha <Ishan.Trikha@pb.com>
Sent: Wednesday, November 9, 2022 2:59 PM
To: Kayvan Shadpour <Kayvan.Shadpour@pb.com>; David Mejia <david.mejia@pb.com>; Irina Ashurova <irina.ashurova@pb.com>
Cc: Amit Gupta <AMIT.GUPTA@pb.com>; Seth Knight <seth.knight@pb.com>; TMSCore <TMSCore@pb.com>
Subject: RE: Snowflake TMS Table Expansion Request
Hi Kayvan,
We can add those fields in the TMS_PLANNED_COST table. Please send us the sample file through email with the new fields and we can start updating our data pipeline. For historical data if data is going to be same like the sample you have provided, we can run an onetime update query in snowflake which can backfill the older records.
Thanks,
Ishan
Attachments Open full size
In Select Star
Attachments Open full size
For GEC loads:
Attachments Open full size