Missing Data from Source Table to DW table.

Question

Hello @Amira Bedhiafi , This time, I noticed there 300 row of data missing from my datawarehouse data when comparing with the source table. the pipeline of this store procedure has been running successfully everyday but don't know why the data are still missing ? I don't think there are any restriction under the data warehouse at all since the previous Engineers did not handover the work properly. I have no ideas why, however, I have the main part of store procedure attached if you don't mind to have a look into this for me please?

with distinct_recs as
	 (
		SELECT [SITUATION_ID]
		,[PERSON_ID]
		,[AGE]
		,[SITUATION_DATE]
		,[ACTIVITY]
		,[ACCOMMODATION_TYPE]
		,[ACCOMMODATION_SUITABLE]
		,[IN_TOUCH_ON_BIRTHDAY]
		FROM [stg].[MO_CARE_LEAVER_SITUATIONS]
	 )
	MERGE [prs].[dimCareLeaverSituations] AS t		--	
	USING (SELECT row_number() OVER (ORDER BY [SITUATION_ID] DESC) + @CareLeaverSituationsPK AS [SituationPK]			  
				,[SITUATION_ID]				AS [SituationCodeBK]	
				,[PERSON_ID]				AS [PersonCodeBK]
				,[AGE]						AS [Age]
				,[SITUATION_DATE]			AS [SituationDate]
				,[ACTIVITY]					AS [Activity]
				,[ACCOMMODATION_TYPE]		AS [AccommodationType]
				,[ACCOMMODATION_SUITABLE]	AS [AccommodationSuitable]
				,[IN_TOUCH_ON_BIRTHDAY]		AS [InTouchOnBirthday]
		   FROM distinct_recs) AS s
	ON t.[SituationCodeBK] = s.[SituationCodeBK]
	WHEN MATCHED
		AND
					ISNULL(t.[PersonCodeBK]	,'')				<>  ISNULL(s.[PersonCodeBK]		,'') OR				
					ISNULL(t.[Age]	,0)							<>  ISNULL(s.[Age]		,0) OR
					ISNULL(t.[SituationDate]	,'1900-01-01')	<>  ISNULL(s.[SituationDate]		,'1900-01-01') OR
					ISNULL(t.[Activity]	,'')				<>  ISNULL(s.[Activity]		,'') OR
					ISNULL(t.[AccommodationType],'')		<>  ISNULL(s.[AccommodationType]		,'') OR
					ISNULL(t.[AccommodationSuitable],'')	<>  ISNULL(s.[AccommodationSuitable]		,'') OR
					ISNULL(t.[InTouchOnBirthday]	,'')	<>  ISNULL(s.[InTouchOnBirthday]		,'') 					
 
	THEN UPDATE SET  t.[PersonCodeBK]=s.[PersonCodeBK]
					,t.[Age]=s.[Age]
					,t.[SituationDate]=s.[SituationDate]
					,t.[Activity]=s.[Activity]
					,t.[AccommodationType]=s.[AccommodationType]
					,t.[AccommodationSuitable]=s.[AccommodationSuitable]
					,t.[InTouchOnBirthday]=s.[InTouchOnBirthday]
					,t.UpdatedAt					= sysutcdatetime()
					,t.BatchID						= 'DimCareLeaverSituations-' + CONVERT(NVARCHAR (128),  sysutcdatetime(), 120)
 
	WHEN NOT MATCHED BY TARGET 
		   THEN INSERT ([SituationPK]
			  ,[SituationCodeBK]	
			  ,[PersonCodeBK]
			  ,[Age]
			  ,[SituationDate]
			  ,[Activity]
			  ,[AccommodationType]
			  ,[AccommodationSuitable]
			  ,[InTouchOnBirthday]
			  ,[CreatedAt]
			  ,[UpdatedAt]
			  ,[BatchID])
 
                VALUES (s.[SituationPK]
			           ,s.[SituationCodeBK]	
			           ,s.[PersonCodeBK]
					   ,s.[Age]
					   ,s.[SituationDate]
			           ,s.[Activity]
			           ,s.[AccommodationType]
					   ,s.[AccommodationSuitable]
					   ,s.[InTouchOnBirthday]			   
			           ,sysutcdatetime()
					   ,NULL
				  	   ,'DimCareLeaverSituations-' + CONVERT(NVARCHAR (128),  sysutcdatetime(), 120)
			           );

Many thanks Nicky

Answer 1

Hello Nick Wong,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

Missing Data from Source Table to DW table.

There are 300 rows of data missing from datawarehouse data when comparing with the source table. the pipeline of this store procedure has been running successfully. Are there are any restriction under the data warehouse causing this issue?

Solution:

The issue was due to a configuration issue. The ETL procedure that populates the table(table name here-DimLeaverSituations) was disabled. So due to this reason the missing data was not loaded into the table.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.