3

I get a database every month with the prior month's data in it. There are three types of data: flag settings, demographic and transactional. I need to import the data into my standing database.

My original plan was to just add a date field to every table and populate my standing database with the new data. As I started to look at the more closely, the flag data just needed to import new flag values and the demographic data just needs to import demographic data when there has been a change.

Sample demographic data:

Account Number, Name, Phone Number

If the phone number changes, I need the new record (but I need the old record too because we need to track changes). If nothing changes, I don't need to keep the record.

The problem comes into being when I start importing the data. I'm not sure how to deal with the scenario of taking the 10 demographic tables in my import database and comparing them to my standing data. I thought I would use an Except query and add the Except records and then put the current data on them.

Another thing I'm worried about is having to make queries that I worry will be expensive:

Select * from TransactionsForAllTime a join DemograpicsChangesOnly b on 
a.AccountNo = b.AccountNo 
join   (select AccountNo, max(ImportDate) from DemograpicsChangesOnly group by AccountNo) c on a.AccountNo = c.AccountNo

How do I design this? Thoughts, ideas, advice welcome.

6
  • 1
    So do I understand correctly that (a) the new transactional data simply needs to be appended wholesale, and (b) the flag and demographic data only need to be appended where they have occurred since the last month's import? Obviously (a) requires no filtering, and it seems to me that the filtering on (b) can be performed purely on the imported data itself, by excluding any rows that predate the last month's import (i.e. no join will be required).
    – Steve
    Aug 29 '19 at 17:19
  • 1
    Can you request modifications / extra columns for the input data? Like a timestamp for each record when the last change occured? That would give you an opportunity to separate changed records from unchanged ones.
    – Doc Brown
    Aug 29 '19 at 17:28
  • 1
    It sounds like you are close to needing a data warehouse - which are systems designed precisely to take recurring archival/analysis data from a transactional database but remain query and storage efficient. They are difficult/expensive for lots of good reasons, as you are seeing, unfortunately. A more piecemeal strategy as you are considering with timestamps might be a more reasonable strategy, as it is relatively simple.
    – BrianH
    Aug 29 '19 at 17:53
  • @Steve - Yes - that is correct. I'm concerned that the filtering might be expensive and error prone. It would be more bulletproof to bring in all the data and put a date on everything but that would create performance issues.
    – Missy
    Aug 29 '19 at 19:18
  • @DocBrown I've already looked into that. Major data providers are incredibly inflexible.
    – Missy
    Aug 29 '19 at 19:19
2

I've written something very similar. Each month we received a file. I loaded it into a staging database then used that to add/ change/ remove rows in the main database.

First thing is to ensure there are robust keys defined for each table. This is how you define what is new/ updated/ redundant - by matching the key from the new DB to the key in the existing one. Here I mean the "natural" key made from values familiar to the business users. Things like "part number", "company name" and "book title". Even if your tables have surrogate keys (usually integers defined as IDENTITY) this should not be used for matching. Ideally they are application-internal optimisations and not intended for external consumption. Only if you have control over the new source and existing master data could internal surrogate keys be appropriate.

If the source and destination tables have compatible schemas use a tool. SQL Server Data Tools (SSDT) allow such comparisons easily. It can produce a script to show exactly what will happen. This can be edited before execution or put in source control for audit. Redgate have something similar.

You have three classes of data each with different requirements. It would be appropriate to use different approaches for each.

Transactional data seems to need no discussion. It seems that flags are append-only without updates or deletion. For this the EXCEPT construct would work. Likely there will be a unique constraint (and thus index) on the natural key for data quality purposes. Given this the query plan will likely be an efficient key-order scan of each table. An alternative syntax is EXISTS. For complex queries they can produce separate plans even though the semantics are the same. For this case I doubt it will make much difference. Test and see.

For demographic data there are two considerations - detecting differences and storing history. For these I used three separate queries. New and removed rows can be detected by comparing key columns and joining to the base tables. In pseudo-code

-- To insert
with NewRows as
(
  select <natural key> from source
  except
  select <natural key> from destination
)
insert destination
select <columns> from source
inner join NewRows on <natural key>

-- To delete
delete d
from destination as d
where not exists
( select 1 from source as s
  where s.<natural key> = d.<natural key>
)

Of course the EXISTS syntax works for inserts just as well.

To track changes I'd suggest temporal tables. They take care of all the administration and boiler-plate code. The restrictions when changing schema are slight and I found easy to deal with. It also accounts for DELETE properly.

Detecting changes will necessitate a column-by column WHERE clause or calculating a hash for each row in the source and destination:

update s
  set ...
from source as s
inner join destination as d
  on d.<key> = s.<key>
where s.a <> d.a
or s.b <> d.b
..
or s.z <> d.z

-- OR --

update s
  set ...
from source as s
inner join destination as d
  on d.<key> = s.<key>
where s.<hash> <> d.<hash>

The hash can be calculated in a view or by adding computed columns to the tables. Ensure the hash function is robust to the changes expected and at low risk of hash collisions. I got bitten by BINARY_CHECKSUM not noticing that a decimal point had moved. Both approaches need to properly account for NULLs.

I was working on about five to ten thousand source records per file. My hardware could handle this in a single database transaction. You may need to batch-process with provision for checkpoint & restart logic. Unless you're considering huge volumes I wouldn't get stressed about performance. This will be run for a short time once each month. Optimise for maintainability and correctness. It will co-habit well with other usage as long as you keep database transactions short.

1
  • Thanks for the detailed response! Very helpful and I really appreciate it :)
    – Missy
    Sep 16 '19 at 23:52

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.