Speed Up Queries with Dataflow Transformations

To speed up your queries and reduce the number of network round trips, perform data transformations in the ELT process instead of in the query.

Example: GEO Field

Let’s say you have a dataset with the GEO field that contains the value JP, and you want to replace this value with Japan. One solution is to add a case statement to your query.

1q = foreach q1 generate (case when 'GEO' == \"JP\" then \"Japan\" else 'GEO' end) as 'GEO;'

Running this query on each row in a dataset is time-consuming. A faster approach is to add the case statement to the saqlExpression field in the dataflow’s computeExpression transformation. Moving the case statement from the query to the ELT process reduces the query’s network round trips.

1"parameters": {
2    "source": "Opportunity_Data",
3    "mergeWithSource": true,
4    "computedFields": [{
5        "name": "GEO",
6        "type": "Text",
7        "label": "GEO"
8        "saqlExpression": "case when 'GEO' == \"JP" then \"Japan\" else 'GEO' end"}
9]}

You can also improve query performance by shortening decimal values in your dataflow. For example, if the numbers in your dataset have a single decimal digit, such as 9.1 or 924.3, set scale to 1 rather than 4 in the computeExpression transformation. Restricting the decimal value only impacts storage only. SAQL performs query calculations with all decimal values intact.

Tip

Example: Date Format

To change the date format, you can add an intermediate query to filter the stream based on the list selector values. Adding an extra filter creates another network trip. Instead, transform the values in the computeExpression transformation, which you can use with SAQL date functions.

1"parameters":{
2    "source":"Opportunity_Data",
3    "mergeWithSource":true,
4    "computedFields":[ {
5        "name":"UIFormattedDate",
6        "type": "Text",
7        "saqlExpression":"date_to_string(toDate(Date_sec_epoch), "yyyy-MM-dd")" } ] }}

See Also