Kicking off the New Year Testing New Starfish ETL Functionality

This new Starfish ETL functionality builds on top of the SQLite Xref and SQLite Connector functionality already in Starfish. The SQLite Xref functionality greatly increased the speed and reliability of the Xref system and allowed us to update Xref lists as opposed to just inserting new, possibly duplicate, records. That's great, but it was still kind of limiting. We wanted a way for users to be able to fully interact with the local Starfish SQLite database. Thus, we've introduced 3 new functions:

Insert
var = SQLiteInsert("test_table", "field1,field2", "'@@ORG:DirectoryRelative@@','@@ORG:Name@@'")
This returns a boolean, true if successful.

Update
var = SQLiteUpdate(“test_table”,” textfield1='hey', numfield3=45", "name=@@ORG:Name@@")
This returns a boolean, true if successful.

Delete
var = SQLiteDelete("test_table", "field2='@@ORG:Name@@'")

Select
var = SQLiteSelect("test_table", "field1,field2", "field2='@@ORG:Name@@'")
This returns a multi dimensional array which you can loop through:

if UBound(var)>-1 then    'there is data
  For i = 0 to uBound(var)
    logmessage var(i,0)
    logmessage var(i,1)
  Next
else
  logmessage "no data"
end if

Now, you have the Origin you're reading from and the Destination your writing to AND you also have your local SQLite database you can fully interact with. What does this do for you? Well, you could pull down some values from the Destination to your local SQLite database and use those local values to compare against as opposed to trying to do possibly slow lookups into the Destination. The possibilities are endless.

New Starfish ETL Functionality

The second new piece of functionality is adding the SQLite Levenshtein distance function to the SQLite connector AND the new functions mentioned above. Levenshtein distance is a great way to do "fuzzy" comparisons to check for duplicates. This part gets fairly complicated fairly quickly, but you can essentially populate your local SQLite database with Account names, for example, and as your going, look for possible duplicates. If you find a possible duplicate, mark it as a possible duplicate!

Stay tuned to the Starfish Wiki and Starfish News for more information.

Posted in:

StarfishETL