Below are the list of Talend jobs which were developed as part of Global Supply Chain CO2 Emission Shipment costs detailed analysis TIERS (Core Query).
Talend Project : DATA_OCEAN_DOMAIN_SUSTAINABILITY
GCP Project : prj-data-dm-sust-prod
Schedule : jobs are scheduled to run on 3rd of every Month.
Talend Plan in TMC: PL_GSC_CO2Emission_Shipment
| BQ_Dataset | BQ_Table | CloudStorage Bucket |
| STG | STG_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | cs-ew1-prj-data-dm-sust-ppd-staging/CO2_Emission/Processed |
| ODS | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | cs-ew1-prj-data-dm-sust-ppd-staging/CO2_Emission/Processed |
Final excel file is loaded in the below bucket folder for the User's to access and download it.
Bucket: cs-ew1-prj-data-dm-sust-ppd-staging/CO2_Emission/Final.
Filename: YYYYMM_TALEND_PRE_PROD_CO2_BW_QRY_MVSDTR01_0010.xlsx. For example for the month of November, Data is extracted for the previous month and loaded as in below:
| FlowName | Tasks | Description | Source_table/query | Target_Table | Complex transformation, if Yes,then logic |
| F1000_F_W_CO_MVSDTR01_0010 | <this is the Main orchestration flow which includes Data Ocean ref jobs and the new jobs> | Automated extraction of shipment details in order to calculate their CO2 emissions
Step 1: Compute the Year & Previous Month and store in a variable (in the format YYYYMM). Step 2: Compute meta run id using DataOcean framework job and store in a global variable Step3: Build the BW query and pass the query to the Step 4. Step4: Extract the data from xtract and load the csv to the cloud storage bucket. Job: J1000_BW_Query_csv_to_GCS Step 5: Using the Data Ocean reference jobs, process the above file in step 4 into STG & ODS tables. ref to data ocean bucket to STG jobs: 2 - Move data from bucket to staging STG to ODS: 3 - Move data from staging to ods Step6: Extract the data from the ODS table for that batch execution Job: J1100_BW_Query_ods_to_Bucket Step 7: Using the Data Ocean reference jobs, Write the success logs into the log table. | TALEND_<ENV>_DI_BW_QRY_MVSDTR01_0010 | cs-ew1-prj-data-dm-sust-<env>-staging/CO2_Emission/Final/<YYYYMM>_TALEND_PRE_PROD_CO2_BW_QRY_MVSDTR01_0010.xlsx | |
| F1000_F_W_CO_MVSDTR01_0010 | J1000_BW_Query_csv_to_GCS | Use XtractUniversal tool to retrieve BW queries and upload the result to GCS 1.Extract the data from xtract and load the csv to the cloud storage bucket. Once the csv file bucket load is completed then delete the file in local Talend server. | BW: DI_BW_QRY_MVSDTR01_0010 Talend: TALEND_<env>_DI_BW_QRY_MVSDTR01_0010 | cs-ew1-prj-data-dm-sust-<env>-staging/CO2_Emission | |
| F1000_F_W_CO_MVSDTR01_0010 | J1100_BW_Query_ods_to_Bucket |
Extract the data from the ODS table for that batch execution based on Meta_Execution_ID. Query: "SELECT * FROM "+ context.l_VAR_GCP_PROJECT_ID+"."+ context.l_LOCAL_VAR_STAGING_TO_ODS_DATASET_ODS + "."+context.l_LOCAL_VAR_STAGING_TO_ODS_Target_TABLE + " where meta_execution_id='" + context.Meta_Execution_ID + "'". The values for above context's can be found in the RDS database. | prj-data-dm-sust-<env>.ODS.ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | cs-ew1-prj-data-dm-sust-test-staging/CO2_Emission/Final/YYYYMM_TALEND_DEV_CO2_BW_QRY_MVSDTR01_0010.xlsx |
| ProjectID | Source Table | ODS Target Table Name | Source Column | Rule's | Final Target Attribute Name |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | BFC_GBU | BFC_GBU | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Source_System | Source_System | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Actual_Shipment_Completion_Y_M | Ship_Actual_Shipment_Completion_Y_M | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Actual_shipment_Completion | Ship_Actual_Shipment_Completion | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Order_number | Order_number | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number | if Shipment_Number is "PF1_020/4111623978" then expected is "4111623978" for example if Shipment_number contains"SF1_020/" then replace it with empty. | Shipment_Number |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Shipping_condit_Key | if contains "PF1_020/" then replace it with empty | Shipping_conditions_core_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Shipping_condit_Name | Shipping_conditions_core_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Shipping_Line_Key | Ship_Shipping_Line_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Shipping_Line | Ship_Shipping_Line_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Forward_Agent | if contains "PF1_020/" then replace it with empty | Forward_Agent_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Forward_Agent_Key | Forward_Agent_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Shipment_Type_Core_Key | if contains "PF1_020/" then replace it with empty | Shipment_Type_Core_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Shipment_Type_Core_Name | Shipment_Type_Core_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Shipping_Type_Key | if contains "PF1_020/" then replace it with empty | Main_Shipping_Type_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Shipping_Type_Name | Main_Shipping_Type_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Shipment_Vessel_name_Key | Shipment_Vessel_name_MoT_arrival_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Shipment_Vessel_name_Name | Shipment_Vessel_name_MoT_arrival_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Packaging_materials_Key | if contains "PF1_020/" then replace it with empty | Packaging_materials_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Shipment_Number_Packaging_materials_Medium_Name | Packaging_materials_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Plant_Key | if contains "PF1_020/" then replace it with empty | Plant_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Plant | Plant_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Key | if contains "PF1_020/" then replace it with empty | Ship_Departure_Point_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point | Ship_Departure_Point_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Postal_Code_Key | Ship_Departure_Point_Postal_Code_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Location_Key | Ship_Departure_Point_Location | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Country_Key | Ship_Departure_Point_Country_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Country_Name | Ship_Departure_Point_Country_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Transportation_Zone_Key | Ship_Departure_Point_Transportation_Zone_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Transportation_Zone_Name | Ship_Departure_Point_Transportation_Zone_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Region_Key | Ship_Departure_Point_Region_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Departure_Point_Region_Name | Ship_Departure_Point_Region_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Key | if contains "PF1_020/" then replace it with empty | Ship_Destination_Point_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi | Ship_Destination_Point_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Postal_Code_Key | Ship_Destination_Point_Postal_Code | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Location_Key | Ship_Destination_Point_Location | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Country_Key | Ship_Destination_Point_Country_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Country_Name | Ship_Destination_Point_Country_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Transportation_Zone_Key | Ship_Destination_Point_Transportation_Zone_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Transportation_Zone_Name | Ship_Destination_Point_Transportation_Zone_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Region_Key | Ship_Destination_Point_Region_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Destination_Poi_Region_Name | Ship_Destination_Point_Region_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Key | if contains "PF1_020/" then replace it with empty | Ship_Route_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Medium_Name | Ship_Route_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Transportation_Key | Ship_Transportation_Mode_Key | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Transportation | Ship_Transportation_Mode_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Pre_Shipping_Type_Key | if contains "PF1_020/" then replace it with empty | Pre_Shipping_Type_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Pre_Shipping_Type_Name | Pre_Shipping_Type_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Shipping_Type_Key | if contains "PF1_020/" then replace it with empty | Ship_Route_Main_Shipping_Type_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Shipping_Type_Name | Ship_Route_Main_Shipping_Type_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Sub_Shipping_Type_Key | if contains "PF1_020/" then replace it with empty | Sub_Shipping_Type_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Sub_Shipping_Type_Name | Sub_Shipping_Type_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Dep_Node_Point_Key | if contains "PF1_020/" then replace it with empty | Dep_Node_Point_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Dep_Node_Point_Medium_Name | Dep_Node_Point_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Dest_Node_Point_Key | if contains "PF1_020/" then replace it with empty | Dest_Node_Point_Key |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Ship_Route_Dest_Node_Point_Medium_Name | Dest_Node_Point_Name | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | Gross_W_dels | Gross_W_dels | |
| prj-data-dm-sust-<env> | TALEND_PROD_DI_BW_QRY_MVSDTR01_0010 | ODS_BWH_0000_0000_F1000_F_W_CO_MVSDTR01_0010 | No_of_Deliveries | No_of_Deliveries |
Link: CO2 Emission_Talend_DA&AI TSD_WO0000000761141 - Google Sheets
Link: Scope 3 - CO2 emissions - List of dimensions
Requirements Link:
Data Validation:
<Provide the SQL queries to validate the data or the record count in the BQ target tables>
Logging:
Run the below query to check the meta_status in run_jobs table as below:
SELECT * FROM `prj-data-dm-sust-ppd.STG.run_jobs` WHERE job_name='F1000_F_W_CO_MVSDTR01_0010';
or use below query:
select L.*,S.job_name,S.meta_status from prj-data-dm-sust-ppd.STG.log_tables L Inner Join `prj-data-dm-sust-ppd.STG.run_jobs` S on L.meta_run_id=S.meta_run_id where S.job_name='F1000_F_W_CO_MVSDTR01_0010'
Troubleshoot steps:
In case of any failure, the plan can be triggered multiple times and each time, the final excel will be replaced in the Final bucket folder.
