Advanced table mapping

Prequel Import anticipates the case where Source Dataset schemas do not perfectly match target Destination schemas without some mapping and transformation. To support bridging between the two, Prequel supports a number of mapping and transformation options.

Who’s responsible for performing the mapping?

Prequel Import provides tooling to support both the Destination owner (you) to perform the mapping, as well as to expose this experience to your users. If you plan to embed this experience for your customers, please see the Customer Experience guide.

Custom queries

At a full table level, custom SQL queries can be used to transform the Source data. This is a good solution for users that are comfortable with SQL, or who need aggregations or logic that spans multiple rows.

Mapping categories

Each Stream mapping has a field_type that determines how the target Destination field is populated. The two categories are summarized below.

Column mapping

A field mapping copies a Source column value directly into a target Destination field.

Column transformation

An expression mapping computes the target value from one or more Source columns using a formula (expression).

See the example below for a Stream that combines column and transformation mapping.

POST /streams

curl -X POST https://api.prequel.co/import/streams \
  -H "X-API-Key: $PREQUEL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "stream": {
      "name": "users-stream",
      "dataset_id": "<DATASET_ID>",
      "destination_id": "<DESTINATION_ID>",
      "mappings": [
        {
          "target_field": "id",
          "field_type": "field",
          "column": { "name": "id" }
        },
        {
          "target_field": "email",
          "field_type": "field",
          "column": { "name": "email_address" }
        },
        {
          "target_field": "customer_segment",
          "field_type": "expression",
          "expression": { "expr": "IF(annual_revenue >= 100000, \"enterprise\", IF(annual_revenue >= 10000, \"mid_market\", \"smb\"))" }
        },
        {
          "target_field": "full_name",
          "field_type": "expression",
          "expression": { "expr": "CONCATENATE(first_name, \" \", last_name)" }
        },
        {
          "target_field": "signup_date",
          "field_type": "expression",
          "expression": { "expr": "TIMESTAMPVALUE(created_at)" }
        },
        {
          "target_field": "source_system",
          "field_type": "expression",
          "expression": { "expr": "\"crm\"" }
        }
      ]
    }
  }'

Expression syntax

Expressions use the expr-lang language. Any operator or built-in function from the expr-lang language definition is supported.

Additional supported functions

Prequel Import also provides the following additional helper functions for common transformations:

Function	Signature	Description
`TIMESTAMPVALUE`	`TIMESTAMPVALUE(text)`	Parses a date or timestamp string.
`IF`	`IF(condition, true_val, false_val) -> any`	Returns one of two values based on a condition.
`TODAY`	`TODAY() -> string`	Returns today’s date.
`TEXTSPLIT`	`TEXTSPLIT(text, delimiter) -> []string`	Splits text on a delimiter.
`PROPER`	`PROPER(text) -> string`	Capitalizes the first letter of each word.
`SUBSTITUTE`	`SUBSTITUTE(text, old_text, new_text) -> string`	Replaces occurrences of `old_text` with `new_text`.
`LEFT`	`LEFT(text, num_chars) -> string`	Returns the leftmost characters of a string.
`RIGHT`	`RIGHT(text, num_chars) -> string`	Returns the rightmost characters of a string.
`MID`	`MID(text, start_num, num_chars) -> string`	Returns a substring starting at a given position.
`LEN`	`LEN(text) -> int`	Returns the length of a string.
`FIND`	`FIND(find_text, within_text) -> int`	Returns the position of `find_text` in `within_text`. Case-sensitive.
`SEARCH`	`SEARCH(find_text, within_text) -> int`	Returns the position of `find_text` in `within_text`. Case-insensitive.
`TEXT`	`TEXT(value, format_text) -> string`	Formats a value as a string using the given format.
`VALUE`	`VALUE(text) -> float64`	Parses a numeric string into a float.
`CONCAT`	`CONCAT([str]...) -> string`	Concatenates the given strings.
`CONCATENATE`	`CONCATENATE([str]...) -> string`	Concatenates the given strings.
`SHA256`	`SHA256(text) -> string`	Returns the SHA-256 hash of the input string.
`AND`	`AND([expr]...) -> bool`	Returns `true` if all arguments evaluate to `true`.
`OR`	`OR([expr]...) -> bool`	Returns `true` if any argument evaluates to `true`.
`RECORD_SCHEMA`	`RECORD_SCHEMA() -> map`, `RECORD_SCHEMA(col) -> map`	Returns the source record schema map or column metadata.

Object storage virtual columns

For object storage and SFTP sources, every extracted record includes two virtual columns derived from the object’s storage metadata, such as the object key and its last modified time. Reference them in mappings and expressions by name, like any other Source column. For example, an expression can extract a report date from the object key when the rows themselves do not contain one. See Datasets for how these columns are populated and their role in change detection.

Column	Prequel Type	Description	Example
`meta__filename`	`string`	Key or path of the source object	`"exports/2023/users.parquet"`
`meta__file_last_modified`	`timestamp`	Last modified time of the source object	`"2023-01-01T14:30:00Z"`

Source record schema

In addition to referencing Source columns directly by name, expressions can inspect the Source record schema via the RECORD_SCHEMA() function. It can be called with no arguments to return the full schema map, or with a column name to inspect that column’s metadata:

RECORD_SCHEMA("<column_name>").column_name (string): The name of the source column.
RECORD_SCHEMA("<column_name>").type or .prequel_type (string): The Prequel data type (e.g. "string", "integer", "timestamp").
RECORD_SCHEMA("<column_name>").raw_type (string): The native data type from the source system (e.g. "VARCHAR(255)", "NUMERIC(10,2)").

Alternatively, access fields via map indexing: RECORD_SCHEMA()["<column_name>"].type. This allows expressions to dynamically inspect types and conditionally adapt transformations to schema variations across sources without colliding with source columns:

{
  "target_field": "amount",
  "field_type": "expression",
  "expression": {
    "expr": "IF(RECORD_SCHEMA(\"amount\").type == \"integer\", amount, VALUE(amount))"
  }
}

Getting started

Core concepts

Features

Logging, Monitoring, and Observability

Sources

Security & compliance

Developer SDKs

Advanced table mapping

Who’s responsible for performing the mapping?

Custom queries

Mapping categories

Column mapping

Column transformation

Expression syntax

Additional supported functions

Object storage virtual columns

Source record schema

​Who’s responsible for performing the mapping?

​Custom queries

​Mapping categories

Column mapping

Column transformation

​Expression syntax

​Additional supported functions

​Object storage virtual columns

​Source record schema

Who’s responsible for performing the mapping?

Custom queries

Mapping categories

Expression syntax

Additional supported functions

Object storage virtual columns

Source record schema