TMS Issue Tracking - QA and Production

Add Bill To fields to Snowflake feed

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
  • Nov 9 2022
  • Resolved
  • Attach files
  • Kayvan Shadpour commented
    November 16, 2022 21:04

    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

  • Kayvan Shadpour commented
    November 14, 2022 22:46

    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

  • Kayvan Shadpour commented
    November 14, 2022 22:45

    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

  • Kayvan Shadpour commented
    November 09, 2022 19:44

    In Select Star

  • Kayvan Shadpour commented
    November 09, 2022 18:45

    For GEC loads: