Data Quality Rules- Checking that value is a number

[JohnB]

Hi all,

I have an ID field in my source database where the data type is varchar despite only containing numeric values, so I'd like to convert it to an integer so users get the best query performance when using my data mart.

Rather than create a query source and use SQL functions to make the conversion I thought I'd try Data Quality Rules in Compose, the idea being that I could first check the value was indeed numeric (just in case!), reject and report if not, otherwise perform the conversion. However I'm stuck at the first hurdle as 'ISNUMERIC(${ID})' doesn't seem to work in my condition expression.

Now there are only 5 in-built string functions listed under the Functions tab in the expressions editor, so does that mean we are limited to those 5 (I'm using Nov 2020 release)?

I'll resort to performing the conversion in SQL for now, but does anyone have any insight into whether the above is possible in Compose?

Thanks,

John

[TimGarrod]

Hi John - 

Since Compose is an ELT solution - that is generating and executing code against your datawarehouse platform, you can use ANY sql functions / capabilities within that platform.   Compose provides some common functions for each platform within the expression builder, but not the exhaustive list of functions that are available to you. 

These functions can be built in (like ISNUMERIC() on SQL Server) or they can be scalar user-defined functions that you have built within the platform yourself eg. dbo.my_udf($(ID)) .

So any of the "casting" you are doing in "SQL" are what you would place into the data validation expression editor.   Please note that the expression should be for GOOD data and the net result should be a TRUE or FALSE.

eg. For your condition - I'd put ISNUMERIC(${ID}) =1  - the =1  provides the "test condition" and says that numeric values are what I'm after.  

Hope this helps!  

If you have further questions - please reply here and include your DW platform (SQL Server / Snowflake etc.)