How long does it take your data engineers to move a record from your CRM to your HRIS system?
The answer is probably too long. It might involve using a variety of servers, coordinating between several internal teams, and a really complicated development process.
A director of software development at a private investment firm outlined an easier and more cost-effective way of moving data during a meetup with the Business Systems Community.
He explained that you can streamline the process of moving data from your source systems into a data warehouse with the help of an iPaaS. Once there, the data can move across your target systems and fuel your analysis and reporting capabilities in apps like Tableau.
His step-by-step process gives your business analysts a bigger role and it saves your team a significant amount of time—particularly engineers, which allows them to allocate more of their attention towards high-priority tasks.
Here’s a recap on his process of moving data through the data lake and into the data warehouse.
Want to attend future meetups with your peers? Then join our Business Systems Community.
Build Your Data Lake
In order to have a functional data warehouse, you’ll have to pull your data from your source systems, clean it, and determine permissions and governance. The speaker laid out the 3 phases his company uses to build this type of warehouse: Bronze, Silver, and Gold.
Here’s a breakdown on each layer:
Layer 1: Bronze
This level will just copy the raw data from your source systems to help you reference the data lineage.
Layer 2: Silver
You’ll do basic validation, check for empty files, verify date columns, normalize the data and check that they’re in UTC. You’ll also convert files into parquet format so that it’s easier to read and run queries on them.
Layer 3: Gold
You’ll now conform dimensions between systems and secure your data. In the case of the latter, you’ll make sure that only people with the right permissions have access to specific data. This allows data analysts and business power users to look at the data that’s safe for them to see and begin to derive value out of it.
Finally, the warehouse
This is where the data is turned into facts and dimensions, and is designed to be queried quickly. This is also where you can start to put real reporting and BI tools on top of your data.
It’s worth noting that a data engineer needs to be involved at every stage of this process. For example, you’ll need a data engineer to transform a raw file to parquet format. And they’ll help with lineage as the data moves along the data lake.
How a Modern iPaaS Can Help
A modern iPaaS can automate large parts of the process and put business analysts in control.
For starters, business analysts can use a set of recipes (steps a modern iPaaS follows to get work done between applications) to move data from the source to the data warehouse.
Since business analysts can look at a recipe’s job history to track data lineage, you can eliminate the bronze layer.
Business analysts can then build a warehouse into the iPaaS. This allows them to automatically put all of the data into a common repository, like S3.
They can then use the iPaaS to read the data in the repository and transform it automatically—effectively eliminating the silver layer.
What are we left with? A process where the business analyst can do nearly all the work. Our meetup speaker said that they can end up doing all of it once it’s possible to bring some of the ETL functionalities into the iPaaS.
Now that business analysts can use a modern iPaaS to move data from a source system to the data warehouse nearly all on their own, your engineers, database administrators, information security specialists, etc. can save a significant amount of time—and dedicate it instead to more business-critical initiatives. All the while, your business analysts can access the data faster, which allows them to use it more effectively in driving meaningful change at your organization.
Get more forward-looking insights by being part of our Business Systems Community.
