De-duplicate rows in place vs de-duplicate on transfer

Question

Doing what: Loading data from many flat table CSV files into their final destination, a "Target" table.

Using: TSQL / SQL Server 2008

Goals: Don't include "duplicate" rows What are duplicates in this context? Two scenarios:

1. For some tables, if two rows have the same value in all columns. ex "*"
2. For other tables, if two rows have the same value in some columns ex. "COL_A, COL_B, COL_C"

I know which tables fall into which scenario, and in scenario 2, which columns are the some.

Current Strategy:

• Load "raw" data in batches (i.e. 1-5.csv, 5-10.csv, etc) into separate "Staging" database
• Dynamically create a TSQL script that transfers from staging to target removing duplicates both...
  • Within the staged data itself
  • And between the staged data, and the target database where it's being transferred
• Execute the transfer TSQL script (in batches corresponding to each Staging Database) to move to a single Target database

For each of the two scenarios (i.e. whether a Table's duplicates is based on all columns (1) or some columns (2)), I have a different transfer query. Here is a pattern of each, respectively:

1. INSERT INTO TARGET.TABLE1 SELECT * FROM STAGING.TABLE1 EXCEPT SELECT * FROM TARGET.TABLE1

2. INSERT INTO TARGET.TABLE2 SELECT * FROM ( SELECT STAGING.*, ROW_NUMBER() OVER (PARTITION BY STAGING.COL_A, STAGING.COL_B, STAGING.COL_C) AS ROW_NUMBER FROM STAGING.TABLE2 as STAGING LEFT OUTER JOIN TARGET.TABLE2 TARGET ON ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL OR STAGING.COL_A = TARGET.COL_A {... repeat for COL_B and COL_C } WHERE TARGET.COL_A IS NULL) as T WHERE ROW_NUMBER = 1

I can add unique constraints if that would help.

My questions:

1. Is there anything wrong with overall approach of using staging databases to store "raw data", and transferring batches of that stage into a target, eliminating duplicates within stage and between stage+target during the transfer.

I was thinking it might be more efficient to just load all raw data into its final destination, and "de-duplicate" "in-place" (though I don't know what this looks like)

2. If the general approach is sound, are the above queries OK? Is "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY and JOIN BY the some columns a fast way to avoid transferring duplicates in scenario 2?

Answer 1

Is there anything wrong with overall approach of using staging databases to store "raw data", and transferring batches of that stage into a target, eliminating duplicates within stage and between stage+target during the transfer.

This is a standard and typically the best approach. Staging should be a fast, logicless "ETL" step. Get into your OLTP source systems, get your data, and get out! The only reason to spend time doing logic on the source query is if it will result in a total reduced load on the OLTP. e.g. Incremental logic on a 10million row table may result in a faster query and shorter locks than if you try and do a dumb load of 10million rows every execution.

If you don't have resource contention issues (like a flat file) you can still maintain this approach, such that all of your Transform operations occur in a DB and ETL tool for efficiency, maintenance, and consolidation of ETL logic. Nothing like tricking another dev by leaving a little key piece of logic in the staging steps while the majority of data transformation resides elsewhere.

If the general approach is sound, are the above queries OK? Is "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY and JOIN BY the some columns a fast way to avoid transferring duplicates in scenario 2?

I personally love Window Functions as they are performant with clean, concise code. EXCEPT is also, typically, a very efficient operator and you are likely safe using it. There's nothing immediately wrong with your approach, so I'd save optimization until it actually becomes an issue. Since you used the word "safe" just double check the rules and how they apply to your data - you may get unexpected results if you don't account for how EXCEPT operates.

Answer 2

I asked this question because the transfer query was getting unacceptably slow in scenario 2.

After trial and error I learned which part of the scenario 2 query caused the problem. It is the use of ISNULL() in this part of the clause:

ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL OR STAGING.COL_A = TARGET.COL_A

Specifically

ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL...

Which is logical equivalent of...

STAGING.COL_A IS NULL AND TARGET.COL_A IS NULL

Except the latter performs much more quickly.

I'd love if anyone can explain the technical / (execution plan?) reason that the ISNULL() use is so slow (I'm already willing to admit ISNULL() is more obtuse/ hard to understand without even being much shorter)

Other improvements to Scenario 2 query is: Instead of doing, in this order (i.e. from inner SELECT statements to outer SELECT statements )

1. JOIN all of staging and all of target WHERE target is NULL
2. THEN FILTER only staging rows WHERE ROWNUM = 1

I do the opposite order. FILTER staging (first), JOIN filtered-staging and target (second). This way, the JOIN may produce fewer results and may be a bit faster.

Secondly, instead of this order in the clause ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL OR STAGING.COL_A = TARGET.COL_A

1. ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL
2. OR STAGING.COL_A = TARGET.COL_A

I do the opposite order. Check for column match first, and check for STAGING.COL_A IS NULL AND TARGET.COL_A IS NULL second. This way, if SQL Server uses Short-Circuit logic (and if the NULL checks are unlikely, as they are in my cases), it may be faster.

In summary, three changes:

1. Worked {COL_A} IS NULL AND {COL_B} IS NULL instead of ISNULL({COL_A}, {COL_B}) IS NULL
2. May work for you If you'd have an anonymous table anyway; filter before Joining, so Join results are smaller
   • Nope, in order to do the filtering first, I required an additional inner anonymous query, which made execution plans different, and made execution times consistently and significantly longer, even though the join results took up a bigger percentage of time
3. May work for you Use more-likely clause before less-likely clause for WHERE in case of short-circuiting
   • Indeed, execution plans were identical, but execution time was consistently shorter , presumably because of the nature of my data, that in the case of a join, value is more likely to be non null.

I know this question is convoluted (I did not even state my real problem, how my scenario 2 query is so slow.) I would not mind restating it or closing it.

Thanks,