In our previous post, we discussed why we recommend that you use PowerShell for data integration. If you’re a data professional, you may have some objections. (Indeed, if you’re not a technical person, you should probably skip this post).
First, PowerShell isn’t great for data transformation.
For example, if you’ve used SSIS or a similar tool, you may be used to mapping data in your integration tool. That, indeed, is how the ETL – Extract/Transform/Load integration model works. You take data from source 1 (extract), you transform it in your tool, and you push it into source 2 (load).
And indeed, we don’t recommend this paradigm. Instead, we prefer ELT– Extract/Load/Transform.
Which means all PowerShell is doing is pulling the data and pushing into another database. Once the data is loaded, we then use SQL to transform it for its final use.
The point – we don’t do transformations in intermediate tools. We do it in the database source or in the database target.
Speed may be your next objection.
Purpose built tools can be faster than PowerShell.
But as our customer, you’re a solid mid-market company our customer with revenue anywhere from 50 to 500 million dollars. And customers like you don’t have “big data”. It’s rare that any of your integrations process more than 100,000 records at a time.
A decent server can process this quantity of data within minutes. And indeed, in the world of Rest APIs, the speed constraint is most often the API, not PowerShell. And again, as a mid-market company, you’re not looking for instantaneous, 7×24 performance. Ease of support and training are much more important.
if you do have tens of millions of records to load at a time or you need instantaneous updates, we’ll agree that PowerShell is not the answer. And we’ll admit that we’re not the right partner for you.