Fetch data in staging database through Talend if any of the dependent table data modified in source system
Hi,
After Data warehouse completed successfully, I want to delete data from the staging table.
Below is my scenario,
I have two dependent table Employee and Department with 'Created Date' and 'Modified date' columns. The first time we fetched all data from source to staging database through ETL initial load.
When all Staging data dumped in Datawarehouse successfully for a particular date through job then after we truncate the staging tables (based on max date).So it will delete all records from staging database.
Now in my scenario, after 20 days the employee's name 'xxx' department changed to 'yyy'.Now when I run the staging job(to delete old records) it deletes data from Employee and Department. After that incremental load for staging is run, It fetches data from Department table as data is modified (based on the 'Modified Date' column) but for Employee table we didn't get any data as there is no modification( i.e; Employee table Modified date = NULL) because data was already deleted.
My view query is something like this :
Select E.Empid, E.EmpName, D.DepartmentName
From tbl_Emp E
Left Join tbl_Dept D on E.Empid= D.Empid
So for Dimension table, while fetching data from the staging view, I didn't get proper data as an employee whose department was changed that information is available in the Department table but Employee table has no data. When I apply join in view using EmployeeId to fetch data it gives NULL as Department has data but Employee has no data for that EmployeeId.
I want to fetch all data from Employee table whose department is updated in the Department table.
Hello, The CDC feature is available in talend subscription solution not open source. With talend open studio, If you want to capture the changed data and only load these changed data into target table to achieve table sync, you can compare tables by using tMap. The work flow should be: Target DB-->tMap(make inner join on your input and set the "Catch lookup inner join reject" as true)-->output Source DB--> The output will be the changed data.
