To Explicitly convert or not to Explicitly convert, that is the question.
More often then not while moving data around you will be forced to make some data type conversion decisions, either due to different data types from system to system or to satisfy certain business requirements. If you rely on implicit conversions then you leave yourself wide open for potential performance and data quality type problems in both the short and long term.
Consider an implicit conversion from varchar to numeric. At design you would simply get a data type conversion warning. At runtime however if the varchar cannot be converted to the expected data type you will get an error and the job will fail or worse the job may continue with unexpected data. For example if the converted data type is shorter then the source data type truncation would happen. If the target data type is INT and the source data type contains a larger number then INT can support you will get unexpected data, the job will continue ahead with potential data quality problems.
Beware of Harmless looking conversion warnings like this that can cause havoc in the future.
Data Services supports a wide variety of data types. When a non-supported data type or when data mapping is done between different data types Data Services must do an implicit conversion from the unsupported data type to the supported data type. A complete listing of supported data types and implicit conversion rules can be found in the Data Services documentation.
As a best practice when validating your code in Data Services you should see no Data type conversion warnings like the one shown above. Data type conversion warnings should be addressed before releasing your job by applying Explicit conversions where required.
Understanding your data and making a conscious decision on the explicit conversion VS relying implicit conversions helps to ensure data quality, performance and overall good coding standards. There is nothing worse then opening a cohort’s job only to find endless amounts of needless conversion warnings.
So to answer the question, Explicitly convert, for the following main points.
1 – Data Quality
2 – Performance
3 – Clean warning free code
For more information on data type conversions please review the Data Type Processing section of the Data Services reference guide.