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 Gsheet and save the output file to bucket cs-ew1-prj-data-industrial-dash-dev-staging/fc/
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-test 
2. prj-data-industrial-dash-[env]

Source

BigQuery

Project = prj-data-dq-selfservice-test

BQ Dataset = DataOcean_industrialreporting_workspace

BQ Table = FixedCosts

Destination

DataOcean

• Bucket = cs-ew1-prj-data-industrial-dash-[env]-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

• DM Table names =
  • FACT_fixedCosts
• DPL View name = 
  • V_FACT_fixedCosts

Product

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

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_WeeklyExecution_FixedCosts

1.2. Reloading data

FixedCosts

• Just run the TASK_WeeklyExecution_FixedCosts.

1.3 Plan to schedule

It is scheduled by plans below on WS_IND_DASHBOARD

• TASK_DailyExecution_FixedCosts weekly every 30 min starting at 07am CET ~ 6pm 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