Suggestions for data pipelines between domains

Cathryn Crane

Suggestions for data pipelines between domains

I would love some suggestions of tools to assist with the ETL or ELT process.  I have a Dev environment in a domain that I pull new records into which is an SQL VM in Azure that I need to massage and push to our Prod SQL Server in a different domain.  Traditionally I have done SSIS but I know there are much better solutions now.  I am playing with data pipelines but struggling with messaging when the pipeline errors out.  I'm thinking about a NoSQL solution to stage all the data and massage before pushing to the Prod SQL Server but not sure if that's really the right way to go either.  Normally I would move Dev to Stage to Prod all in SQL Server.

 

Things need to go in a particular order and most of it is in Stored procs right now.  The amount of data I need to pull and push is in the thousands, not the millions so it's not huge.

Does anyone have any suggestions on the strategy of doing this with better technology?

Michelle Knight

RE: Suggestions for data pipelines between domains
(in response to Cathryn Crane)

I would caution using a NoSQL solution to stage all the data and massage it because of difficulties as this DZone article mentions. Have you looked at other data integration software tools (See Solutions Review?) or considered a  Data Virtualization related solution? My experience physically using such tools has been limited though.

Ravindra Punuru

RE: Suggestions for data pipelines between domains
(in response to Cathryn Crane)

Diyotta would be perfect fit for your use case.

Diyotta is a data integration technology built on an ELT architecture. Using Diyotta you can perform ELT workloads on cloud data warehouses, Hadoop or any MPP style data architectures. Visit https://www.diyotta.com for more info.