Skip to main content

Step 5: Standardize Schema

Convert DB2's UPPERCASE column names to BigQuery's lowercase snake_case convention and add partition fields. This replaces DataStage Transformer field mapping.

The Goal​

  • Convert TRANSACTION_ID → transaction_id
  • Remove original uppercase fields
  • Add BigQuery partition field _partition_date
  • Carry forward all transformed fields from previous steps

Why This Matters​

Example convention: Lowercase, snake_case column names Query Simplicity: SELECT transaction_id vs SELECT "TRANSACTION_ID" Partitioning: Date partitions are an authored layout choice; measure query and billing behavior against the target workload

DataStage Equivalent​

In DataStage, schema mapping requires:

  1. Transformer Stage with manual field-by-field assignment
  2. Metadata propagation to ensure downstream stages see new names
  3. Separate logic for partition field generation

Expanso simplifies this with explicit field mapping in a single processor.

Implementation​

Add the schema standardization processor after categorization:

Partial snippet
step-5-schema.yaml
pipeline:
  processors:
    # Steps 1-4 from previous...

    # Step 5: Standardize field names for BigQuery
    - mapping: |
        # Map DB2 columns to BigQuery format
        root.transaction_id = this.TRANSACTION_ID
        root.customer_id = this.CUSTOMER_ID
        root.transaction_date = this.TRANSACTION_DATE
        root.transaction_type = this.TRANSACTION_TYPE
        root.merchant_name = this.MERCHANT_NAME
        root.merchant_category_code = this.MERCHANT_CATEGORY_CODE
        root.source_system = this.SOURCE_SYSTEM
        root.created_at = this.CREATED_AT

        # Carry forward transformed fields from previous steps
        root.amount_usd = this.amount_usd
        root.original_amount = this.original_amount
        root.original_currency = this.original_currency
        root.account_number_masked = this.account_number_masked
        root.account_number_hash = this.account_number_hash
        root.transaction_category = this.transaction_category
        root._lineage = this._lineage

        # Add BigQuery partition field
        root._partition_date = this.TRANSACTION_DATE.ts_parse("2006-01-02").ts_format("2006-01-02")

Understanding the Code​

ExpressionWhat It Does
root.transaction_id = this.TRANSACTION_IDRename field to lowercase
root = ... (starting fresh)Include explicitly mapped fields
.ts_parse("2006-01-02")Parse date string to timestamp
.ts_format("2006-01-02")Format timestamp back to date string
Go Time Format

Expanso uses Go's time format convention where 2006-01-02 is the reference date. This is different from YYYY-MM-DD in other languages.

Expected Output​

Use the Explorer checkpoints for the schema-stage input and output, including the currency and account fields preserved from earlier stages.

Production Considerations​

Automatic Case Conversion​

For tables with many columns, automate the conversion:

Partial snippet
# Convert all top-level fields to lowercase
root = this.map_each_key(k -> k.lowercase())

Preserving Unknown Fields​

If DB2 schema might change:

Partial snippet
Partial snippet: preserve unknown fields
# Start with all fields lowercased
root = this.map_each_key(k -> k.lowercase())

# Then override specific fields
root._partition_date = this.transaction_date
# Preserve as-is
root._lineage = this._lineage

Multiple Partition Fields​

For time and region partitioning:

Partial snippet
root._partition_date = this.TRANSACTION_DATE
root._partition_region = match this.SOURCE_SYSTEM {
this.contains("EU") => "eu",
this.contains("US") => "us",
this.contains("APAC") => "apac",
_ => "global"
}

BigQuery Clustering​

Add clustering keys for query performance:

Partial snippet
# These fields should be declared as clustering keys in BigQuery
# Order matters: most filtered first
root._cluster_1 = this.transaction_category
root._cluster_2 = this.customer_id

Next Step​

📖 Step 6: Validate Required Fields →