Etl's side effects


The first role of an Etl is often to perform modifications on objects (database content, create, move or delete files, …). NBi is supporting a way to test these state’s updates after the run of a given etl. The process to achieve this is firstly to run the etl during the setup phase, then define your system-under-test as the impacted object by your etl (by example, through a query to a table) and finally assert the correct state of this object (the content of the result-set returned by your query).

In the sample bellow the etl will read the content of a file and populate a table. To test that the file has been correctly loaded, we’ll execute a select on the corresponding table and compare with the content of this file.

Define the setup

In the xml element named setup you’ll need to define the etl to run. The syntax is exactly the same than for etl defined in a system-under-test.

<test name="Etl in setup" uid="0003">
  <setup>
    <etl-run name="Sample.dtsx" path="Etl\">
      <parameter name="DataToLoadPath">C:\data.csv</parameter>
    </etl-run>
  </setup></test>

Next, you’ll need to define your query on a table as a system-under-test

<system-under-test>
  <execution>
    <query>
      select CurrencyCode, Name from [Sales].[Currency]
    </query>
  </execution>
</system-under-test>

And finally, you need to define your expected result-set:

<assert>
  <equalTo keys="first">
    <column index="1" type="text" role="value"/>
    <resultSet file="C:\result.csv"/>
  </equalTo>
</assert>

The test will execute your etl, then execute the query and compare its result-set to the expected result-set defined in the assertion.

The whole sample would be:

<test name="Etl in setup" uid="0003">
  <setup>
    <etl-run name="Sample.dtsx" path="Etl\">
      <parameter name="DataToLoadPath">C:\data.csv</parameter>
    </etl-run>
  </setup>
  <system-under-test>
    <execution>
      <query>
        select CurrencyCode, Name from [Sales].[Currency]
      </query>
    </execution>
  </system-under-test>
  <assert>
    <equalTo keys="first">
      <column index="1" type="text" role="value"/>
      <resultSet file="C:\data.csv"/>
    </equalTo>
  </assert>
</test>