Description

The data source getting from prj-data-dq-selfservice, which get from DataOcean_industrialreporting_workspace.FixedCosts tables in order to get the information of FixedCosts.

Tools: Talend

Talend project IND_DASHBOARD

• F001_GSheet_site_to_BQ_FixedCosts

From source to ODS 

Job: F001_GSheet_site_to_BQ_FixedCosts (FixedCosts)

1. Define variable and Generate meta_run_id
2. Call reference job to load from BW and save the output file to bucket cs-ew1-prj-data-industrial-dash-dev-staging/fc/z
3. Load from Bucket to STG and ODS
4. Move data from ODS to DM
5. Update log

From DM to Operational Dashboard

Dataflow

prj-data-dq-selfservice-test.DataOcean_industrialreporting_workspace.FixedCosts → prj-data-industrial-dash-dev.STG.STG_FIL_0000_0000_F001_F_M_fc → prj-data-industrial-dash-dev.ODS.ODS_FIL_0000_F001_F_M_fc → prj-data-industrial-dash-dev.DM.FACT_fixedCosts → prj-data-industrial-dash-dev.DPL.V_FACT_fixedCosts 

Access rights

It is required to access

1. prj-data-dq-selfservice-dev 
2. prj-data-industrial-dash-dev

Source

BigQuery

Project = prj-data-dq-selfservice-dev

BQ Dataset = DataOcean_industrialreporting_workspace

BQ Table = FixedCosts

Destination

DataOcean

• Bucket = cs-ew1-prj-data-industrial-dash-dev-staging/
  • fc
  • FIL_IND_0000_0000_F001_20241126224947_0000_F_M_fc.csv
• STG Table names =

  • STG_FIL_0000_0000_F001_F_M_fc

• ODS Table names =

  • ODS_FIL_0000_F001_F_M_fc

Product

• GCP = prj-data-industrial-dash-[env]
• DataOcean
  • V_FACT_fixedCosts 

Format

columnar format

Sizing

• STG_FIL_0000_0000_F001_F_M_fc         around   258,056 records

Loading

1.1 Full load

• FixedCosts Source to ODS : TASK_MonthlyExecution_FixedCosts

1.2. Reloading data

FixedCosts

• Just run the TASK_MonthlyExecution_FixedCosts.

1.3 Plan to schedule

It is scheduled by plans below on WS_DATA_OCEAN_DOMAIN_MARKETING

• TASK_DailyExecution_FixedCosts weekly every 30 min starting at 07:00 CET

1.4 Timing

• Case 5 minutes from source to ODS (full)

Criticality

• Low?

Logging

Check the loading records 

select job.job_name, job.meta_start_date, job.meta_execution_id, logs.meta_run_id, logs.meta_source_system, logs.meta_step, logs.meta_status, logs.meta_num_lines, logs.meta_error_lines from STG.log_tables logs join STG.run_jobs job on logs.meta_run_id = job.meta_run_id
where  logs.meta_run_id in (SELECT meta_run_id FROM STG.run_jobs order by meta_start_date desc limit 1000)
and job_name in ('F001_GSheet_site_to_BQ_FixedCosts')
and meta_start_date >  DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 32 DAY)
order by job.meta_start_date desc