0

I have an issue with SQL Query activity / Query Studio where I am getting an incorrect number of records when I am trying to find the difference of records between 2 Data Extensions. I need to find the difference to align both DEs to have the same number of records and it would be easier to find a small number and update the second DE with it. This is what I have:

  • Data_Extension_1 - contains "33 412 235" records
  • Data_Extension_2 - contains "33 411 588" records
  • Both DEs have the same structure and one Primary Key - "Id"

I have tried these approaches:

SELECT
a.Id
FROM Data_Extension_1 a
WHERE a.Id NOT IN (SELECT b.Id FROM Data_Extension_2 b)

and

SELECT
a.Id
FROM Data_Extension_1 a
LEFT JOIN Data_Extension_2 b ON a.Id = b.Id
WHERE b.Id IS NULL

So, since the structure is the same and we are definitely not generating any duplicates, we should have ended up with "648" records difference but in reality, those SQLs are giving "5373" records.

Kind of stuck and not sure what to check at this point. Any advice?

4
  • You tried both queries in Automation Studio? Or are you suggesting the difference is Query Activity vs Query Studio? Aug 3, 2021 at 17:13
  • 1
    Have you considered that although the records are close in numbers that there may be other records, outside the 648, that are in DE 1 but not in DE 2. Meaning there are also records in DE 2 that are not in DE 1? That sounds like what the issue is, and not that the Query is returning incorrect info. Aug 3, 2021 at 17:22
  • @AdamSpriggs, I meant that I have tried both queries in the Automation Studio and in the Query Studio and started to question everything already, even maybe some hidden simple mistake that I am missing or some incorrect DE configuration.
    – Niko
    Aug 3, 2021 at 19:27
  • @Gortonington, aaand that is a correct guess, was not even considering this since this is a part of old automation and both DEs should have been updated together and always aligned besides those 648 missing records... that was a lame mistake. Now got another problem to solve... Was looking for such an uncontextualized suggestion. I can accept your comment as an answer though.
    – Niko
    Aug 3, 2021 at 19:30

1 Answer 1

1

Moved to Answer from Comment for Easier Reference


Have you considered that although the records are close in numbers that there may be other records, outside the 648, that are in DE 1 but not in DE 2?

For instance that the 5373 records are the correct response and that the difference is not that there are only 648 records that are different, just that the record count is off. This means there are also a bunch of records in DE 2 that are not in DE 1.

Judging from your information in your question, that sounds like what the issue is and not that the Query is returning incorrect info.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .