Let's say I've got this hypothetical schema:

Source (OLTP) DB:

Table Orders
OrderID int IDENTITY (PK),
CustomerID int NOT NULL,
OrderAmount decimal NOT NULL

Destination (DSS) DB:

Table Activity
ActivityID int IDENTITY (PK),
PersonID int NOT NULL,
Amount decimal NOT NULL

Table ActivityOrderImport
ActivityID int NOT NULL,
SourceOrderID int NOT NULL

Table CustomerMapping
CustomerID int NOT NULL,
PersonID int NOT NULL

Obviously the real deal is considerably more complicated with more transformations. But assume for the moment that all this ETL does is merge specific transactions ("orders") from an external entity into a DSS that tracks generic "activities". The links between the external customer and the DSS person are in the CustomerMapping table.

The idea of the "Import" table is to provide some sort of audit trail in case something goes wrong. We don't have a lot of control over the source system and know it to be a bit on the flaky side. So it's really important for us to be able to understand the origin of any given activity.

Right now, there's a script that does this with DDL, which looks something like this:

ADD OrderID int NULL

MERGE Activity
USING #StagingOrders

INSERT ActivityOrderImport (ActivityID, SourceOrderID)
SELECT a.ActivityID, s.OrderID
FROM #StagingOrders s
INNER JOIN Activity a
    ON a.OrderID = s.OrderID


This works fine, in the sense of not crashing and burning, it just makes me nauseous every time I look at the DDL.

  • Permanently adding an OrderID column to the Activity table really isn't an option because the data may come from multiple sources, each currently needing their own log table. Adding a separate column to the main table for all of these would break normalization and quickly turn the production database into a dog's breakfast.

  • Removing the IDENTITY constraint from the Activity table and using some natural derived key is a better option, but still impractical for the same reason - because every system has a different way of defining it, we'd end up having to use an nvarchar column for the primary key (ick). Not to mention we'd lose the sequential-ness which is important for a lot of things.

So I've been wondering, is there a better way to do this, one that doesn't involve DDL but still takes into account the heterogeneous nature of the external keys (and thus the need for different tracking tables for each source)?

I'm not necessarily asking for just a script that'll work under the current design. I realize that the design might have to be modified and I'm open to creating more tables or even separate staging databases; the only things I'm not willing to do at this point are (a) add those tracking columns to the base table and/or (b) remove the IDENTITY field. It's important to maintain the separation of concerns here and keep the actual activity data separate from the import "log" data.

Any thoughts as to what I could do with this design to achieve all of the objectives here, or have I painted myself into a corner and made that impossible with the current restrictions?

  • Can you explain what you're doing to create #StagingOrders and what you do to put non-NULL values into OrderID on the Activity table? – AndrewSQL Feb 23 '11 at 18:19
  • @Andrew: #StagingOrders would be, in the context of this question, essentially just a copy of the Orders source table with the association column (PersonID) matched. Therefore OrderID is never going to be NULL for any inserts on the Activity table because it is just being taken directly from the source. – Aaronaught Feb 23 '11 at 19:07

I would suggest adding a (set of) staging tables to the destination or an intermediary that can be better controlled and more stable. Place the tracking info there. Then do all the transformation from the staging to the final destination, either carrying the tracking info with it or discarding it.

There are several ways you can generate a tracking key from multiple systems, as long as they all follow the same algorithm, it doesn't have to be an INT. It could be a 2 char prefix with a sequential number. For that matter it doesn't have to be just one column.

| improve this answer | |
  • Glad to know that we're not the only ones doing that (altho it does seem like the most sensible and easiest to formulate solution doesn't it?) – jcolebrand Feb 23 '11 at 14:57
  • I really don't understand what the first paragraph here is saying. I'm already using staging tables; the staged data still needs to eventually map to a primary key in the production table in the destination. The second paragraph seems to be suggesting I add permanent tracking columns to the production table, which is exactly what I don't want to do; can you explain how this recommendation differs from what I've already described? – Aaronaught Feb 23 '11 at 15:09
  • @Aaronaught you should be using the staging tables in such a way that you can rebuild the production tables from the data that's still in the staging tables, where possible. Obviously if you're processing terrabytes of data everyday that's not an option, but if you're only processing gigabytes of data per day, that's only a mild duplication of space, and at the end of a year, you can migrate the data from the staging tables out to standby disks if needed to reduce space? So the traceability is in the recreatability. – jcolebrand Feb 24 '11 at 2:32
  • @jcole: All of that is accomplished by the current design. Still doesn't get me any closer to a solution without DDL or permanent tracking columns in the prod table. Edit - just re-read your last sentence: The traceability is not in the ability to recreate for us; for example we might be talking about financial data and if we need to reconcile a botched transaction, we are not going to do that by rebuilding the entire database. It's also perhaps worth noting that these records may have a lot of relationships, which can only be staged with a link between the source and production keys. – Aaronaught Feb 24 '11 at 2:39
  • Then in that case a column or two on the arse-end of the record would work best. – jcolebrand Feb 24 '11 at 2:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.