How to test ETL from one DB to another

[Rom Cruz]

Hi,

 

I'm trying to test an ETL package that gets an input from Operational Data Store (ODS) DB and stores the transformed result into a Datamart DB. My test goes like this:

 

!3 Connect to ODS DB

!|ConnectUsingFile|.\ODS.connection.properties|

!3 Insert data into the ODS

| Insert | ods.SignageRuleGroup|

| SignageRuleGroupCode| SignageRuleGroupName| SignageRuleText| SignageRuleNotes | SignageRuleGroupID? |

| ROM TEST| ROM TEST SIGNAGE RULE GROUP| THIS IS JUST A TEST RULE GROUP| I SAID THIS IS JUST A TEST RULE GROUP | >>srgid |

!3 Execute ETL 

!3 Connect to ODMart DB

!|ConnectUsingFile|.\ODMart.connection.properties|

!3 Check that the inserted value reflects in Datamart

| Query | select * from odmart.d1_SignageRuleGroup |

| SignageRuleGroupID | SignageRuleGroupCode?| SignageRuleGroupName?|

| >>srgid | ROM TEST| ROM TEST SIGNAGE RULE GROUP|

 

However, upon connecting to the Datamart DB, the variable X is evaluated by DBFit as missing. The error is logged as

java.lang.NumberFormatException: For input string: ">>srgid"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Long.parseLong(Unknown Source)
	at java.lang.Long.parseLong(Unknown Source)
	at fit.TypeAdapter$ClassLongAdapter.parse(TypeAdapter.java:215)

 

My questions are:

1) Is there a DBFit best practice on testing ETL that transforms from one DB to another?

2) How do I preserve the value of the variable even when I have changed DB connections?

 

 

Thanks in advance to your help!

Rom

DBFit newbie

 

 

 

[Yavor Nikolov]

Hi Rom,

1) It's context specific. In general I prefer using database links and synonyms on top of them to access remote databases: so I see local and remote objects from single connection.

* In your example: Consider executing |Close| before connecting to other database.

2) Seems in your Query your intention is to read the variable: in such case you should prefix it with << instead of >>.

Yavor

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at http://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/groups/opt_out.

[Rom Cruz]

Thanks Yavor! That's very helpful. I used the db alias via fully qualified table names. Thus no need to do another connect and did not need to Close the connection as well. I also fixed the >> prefix.

 

Thanks again,

-Rom