Job Details

As part of HR DQ project, we have developed the below Talend jobs which gets the data from SuccessFactor tables and ingest data into the respective STG/ODS tables. We are following the DataOcean standard jobs. 

As part of Procurement DQ project, we have developed the below Talend jobs which gets the data from SAP (WP1 and PF1) tables and ingest data into the respective STG/ODS tables. We are following the DataOcean standard jobs. 

Talend flow explanation

Main Flow

• • Setup meta_run_id and filename of the output file

• • Call the Rest API Job (J001*) which will invoke the SuccessFactor Rest API to extract the data (full) and load and save the file to GCS
  • Call the standard job to upload the files from GCS to ODS
  • If everything is OK, update the log. 

We are following the DataOcean standard Jobs except the J001* (SuccessFactor Rest API) job which is explained below

The above job invokes the SuccessFactor Rest API (by passing the username / password) to extract the data. Below are the main SuccessFactor parameters which are getting used while extraction, these parameters are common to all the SuccessFactor jobs.

l_VAR_SAP_SF_ENV : This parameter will contain DEV / PROD as the value, this is to mask the confidential information. If its DEV then the column will get populated with "" in the BQ tables, if PROD then it will have the source value in the target BQ. 
l_VAR_SAP_SF_LIMIT : This parameter is used to manage the number of records per iteration

l_VAR_SAP_SF_URL : This parameter is used to hold the SuccessFactore REST API URL

l_VAR_SAP_SF_PASSWORD : This parameter is used to store the password required to invoke the API
l_VAR_SAP_SF_USERNAME : This parameter is used to store the username required to invoke the API

Possibilities for the Talend job failure

As this is a full extract job and we are not doing any transformation (its 1:1 mapping), so the possibilities of failure could be one of the below 

• STG / ODS table not present
• Credentials issue (i.e. incorrect Username / Password to access the SuccessFactor RestAPI, in that case you can reach out to tania.pereira@solvay.com, she can help with the credentials)
• RestAPI error : This could be if the API is not accessible
• Talend RemoteServer Issue

Once the issue is fixed, we can rerun the job from start and it will create the file again and load the data and there will be no loss of data. 

******************************************************************************************************************************************************************************************

Data Validation/Troubleshoot queries

Data Validation:

<Provide the SQL queries to validate the data or the record count in the BQ target tables>

Logging:

<Details about the log tables in Big query>

Troubleshoot steps:

<Provide the steps to debug the ETL flow in case of failure. For example, if an ETL job fails, how should we trigger it? Can we directly rerun the job, or are there steps to delete the data from the previous execution before rerunning the job? or any change should be made in the context table for date executions>

Failed Records

Description

As part of HR DQ project, we have developed the below Talend jobs about running some data quality validation queries and writing the results in Google Drive files.

Source

This job uses data quality queries stored in prj-data-dq-selfservice-dev.DM.Dataplex_quality table and 

`prj-data-dq-selfservice-dev.DataOcean_dataquality_kpi` schema.

Destination

The data quality quality executed queries are materialized in Google Drive Failed Records folder.

Drive Id:

Main Flow

F001_BQ_to_Gsheet_Drive_Selfservice

This job points to J001_BQ_to_Gsheet_Drive_SelfService job, which has all the detailed business rules.

J001_BQ_to_Gsheet_Drive_Selfservice

P1

P2

1. Set meta_run_id
2. Get Data Quality SQL queries to run / Run query and store results in local file
3. Set internal variables
4. Get the Google Drive domain code folder Id
5. For every local file generated in step 2, the process uploads the file to Google Drive Rule Name folder. This flow has a sub-process to create new rule name folders
6. Write Talend Job Log

Plan to Schedule

From Monday to Friday at 08am

Logs

select *
from `STG.run_jobs`
where job_name = 'J001_BQ_to_Gsheet_Drive_Selfservice'
order by meta_end_date desc