If you want to load a target table in an order, either ascending or descending, you can achieve this functionality by following the steps outlined below. 
This guide uses an example where a CSV file is the source and a PostgreSQL table is the target.

Need

The primary need is to ensure that data loaded into a PostgreSQL target table maintains a specific order (e.g., ascending or descending based on a particular column), especially when the source data itself is unordered.  For instance, your source CSV file might have data like this:

The goal is to have this data appear in the target table sorted by CUS_ID.

Summarized Solution


To achieve ordered data loading in a PostgreSQL target within a data integration process, you can use the "After Query Expression" option. This option is found in the properties of the target table's integration template.  Here, you can add an SQL ORDER BY clause to define the sorting logic for a specific field.

Detailed Solution


a. Understand the Mapping and Source Data
  1. Consider the example mapping where data flows from a source linge_order_test (representing a CSV file) to a target dim_customer(a PostgreSQL table).
  2. As noted, the source CSV file contains data that is not pre-sorted by the CUS_IDfield.

 b. Configure the Target for Ordered Loading

  1. To implement the ordering, click on the integration template (INT template) of the Target table for which you want to enforce a specific line order.
  2. In the properties menu for this template, locate the "After Query Expression" option.
  3. In the "After Query Expression" field, enter an SQL expression that defines the ordering. For this example, to sort the data in descending order based on the CUS_ID column, the expression is: order by CUS_ID desc.
Note: 
You can modify the expression (e.g., order by CUS_ID ascfor ascending order, or use multiple columns) as per your specific requirements.

c. Verify the Generated Query

After setting the expression, the generated insert query for the target table will include this ORDER BY clause.



Always verify the generated SQL query after making changes to expressions to ensure it behaves as expected


Ensure the column used in the ORDER BY clause is correctly named and exists in the source data being selected


d. Check the result

After the successful execution of the mapping, the data in the PostgreSQL target table will be ordered according to the specified expression.  In this case, the CUS_ID field will be in descending order.

You can verify this by querying the target table:


select * from postgres.hotel_datamart.dim_customer;

The resulting data in the target table will appear as follows: