Skip to main content

Creating expressions

Compose for Data Warehouses allows you to create data transformations in several different places according to your needs. A transformation can either be a filter (i.e. excluding certain data) or an expression (i.e. manipulating a single record). The table below lists the places where transformations can be created and provides reasons for creating the transformation in each of the specified places.

Note:

Changes in a dimension expression or lookup of a column in a dimension are not updated retroactively. In order to update historical data, you would need to reload the data which could take a long time depending on the number of records and their history.

Data transformation location comparison
Where the Transformation is Created Reasons to Create a Transformation There When the Transformation is Applied

Replicate

  • Filtering large amounts of data that is not needed for the data warehouse (in the present or the future)
  • Obfuscation due to regulatory reasons or internal policies
  • Data type conversion (e.g. converting a source data type that is not supported on the data warehouse platform)

Before the data reaches the landing area.

Model

  • The default location if you are not sure where to put it
  • General business logic
  • Needed for several sources or several data marts

Between the staging area and the data warehouse.

Data Warehouse

  • Specific source preparation
  • Needed for merging several sources

Between the landing area and the staging area.

Data Mart

  • Specific to a data mart
  • Managed by a data mart data specialist

Between the data warehouse and the data mart.

See also Defining reusable transformations.

The following topics describe the Expression Builder: