The Data Integration Developer’s Dilemma – Use Hand-coded SQL or Not?

dilemma
The Data Integration Developer’s Dilemma – Use Hand-coded SQL or Not?

In the age of digital revolution, this topic may sound trivial. However the reality is many organizations still use hand-coding SQL in their data integration environment.  Some organization have their entire data integration environment built on hand-coding and many others have taken a mix and match approach of using a combination of Data Integration tool and hand-coding – wherever makes sense.  Having said that, can you share what percentage of your data integration environment is hand-coded SQL?

There is always a temptation to hand-code, when you see unique situations or scenarios. Is that the right approach? Hand-coded SQL may provide unlimited flexibility initially, but can become difficult to maintain and scale as the scope of your project broadens. They can pose many problems. Firstly they are not transparent – the business or transformation logic is buried in the code and they lack documentation. Secondly they are not easy to test, maintain and may not follow development best practices. As your business grows, your data integration environment needs to scale and perform to meet new business and data demands.

 

 

Informatica PowerCenter is the defacto Data Integration tool in many organizations. PowerCenter offers an end-to-end data integration platform, with a highly visual environment lets you connect and integrate data from any data source in any data format and automatically generate data integration mappings and workflows, without any hand-coding. Whether you’re planning a data warehousing project, integrating or migrating applications, or evolving to a hybrid architecture (on-premises and cloud),  PowerCenter will address your current and future needs. Most importantly it delivers results at speeds up to five times faster than hand-coding and traditional graphical tools.

What if you already have PowerCenter and have hand-coded SQL in your data integration environment? Throw away the SQL code or hide them? Informatica has built this cool capability for our customers to convert their existing hand-coded SQL to Informatica Mappings. This is available in PowerCenter V10. This capability brings several advantages for our customers –

  • Make opaque hand-coded SQL transparent to metadata Manager
  • Data lineage and impact analysis
  • Make it more reusable across projects
  • Minimize the time and cost of maintaining the code

Please take a look at the technical demo on SQL to Mapping converter. And here is a recording of the webinar we did recently on SQL to Mapping and Dynamic Mapping.