LoadSqlProxyTransformer

Overview

Transforms Tableau LoadSqlProxy nodes to SQL SELECT statements. Maps database table sources to Snowflake tables with optional field transformations.

Node Type: .v1.LoadSqlProxy Output: SQL SELECT with field transformations

Capabilities

Reads from database tables (Oracle, SQL Server, PostgreSQL, etc.)
Preserves column schema from table definition
Supports column renames (RenameColumn action)
Supports column removal (RemoveColumns action)
Supports type casting (ChangeColumnType action)
Field name sanitization (spaces to underscores, lowercase)
Handles large field lists (100+ columns)

Tableau Node Example

Input (Tableau format)

{
  "id": "a7b3c9d2-ef45-4b89-9a12-c3d4e5f6a7b8",
  "name": "Sales Database",
  "node_type": ".v1.LoadSqlProxy",
  "base_type": "input",
  "relation": {
    "type": "table",
    "table": "[ANALYTICS].[DBO].[SALES_DATA_TABLE]"
  },
  "fields": [
    {
      "name": "Region Code",
      "type": "string",
      "ordinal": 0
    },
    {
      "name": "Sales Rep ID",
      "type": "integer",
      "ordinal": 1
    },
    {
      "name": "Customer Number",
      "type": "integer",
      "ordinal": 2
    },
    {
      "name": "Product Code",
      "type": "string",
      "ordinal": 3
    },
    {
      "name": "Order Date",
      "type": "date",
      "ordinal": 4
    },
    {
      "name": "Invoice Amount",
      "type": "real",
      "ordinal": 5
    },
    {
      "name": "Units Sold",
      "type": "integer",
      "ordinal": 6
    },
    {
      "name": "Division Name",
      "type": "string",
      "ordinal": 7
    },
    {
      "name": "Sales Amount Local",
      "type": "real",
      "ordinal": 8
    },
    {
      "name": "Sales Amount USD",
      "type": "real",
      "ordinal": 9
    },
    {
      "name": "Material Number",
      "type": "string",
      "ordinal": 10
    },
    {
      "name": "Order Type Code",
      "type": "string",
      "ordinal": 11
    }
  ],
  "actions": []
}

Generated SQL Output

SELECT region_code,
       sales_rep_id,
       customer_number,
       product_code,
       order_date,
       invoice_amount,
       units_sold,
       division_name,
       sales_amount_local,
       sales_amount_usd,
       material_number,
       order_type_code
FROM ANALYTICS.DBO.SALES_DATA_TABLE

Output Schema

{
  "region_code": "string",
  "sales_rep_id": "integer",
  "customer_number": "integer",
  "product_code": "string",
  "order_date": "date",
  "invoice_amount": "real",
  "units_sold": "integer",
  "division_name": "string",
  "sales_amount_local": "real",
  "sales_amount_usd": "real",
  "material_number": "string",
  "order_type_code": "string"
}

How It Works

1. Resolve Table Name

Extracts table name from relation object:

{
  "relation": {
    "type": "table",
    "table": "[ANALYTICS].[DBO].[SALES_DATA_TABLE]"
  }
}

Converts to: ANALYTICS.DBO.SALES_DATA_TABLE

2. Extract Field Schema

Reads column definitions from node.fields:

{
  "name": "Region Code",
  "type": "string",
  "ordinal": 0
}

3. Apply Actions (if any)

Processes actions in order:

RenameColumn: Renames columns in SELECT
RemoveColumns: Excludes columns from SELECT
ChangeColumnType: Adds CAST expressions

4. Sanitize Field Names

Converts Tableau field names to SQL identifiers:

Spaces → underscores
Lowercase
Remove special characters

"Sales Amount USD" → sales_amount_usd
"Region Code" → region_code

5. Generate SELECT Statement

Creates SQL selecting columns from source table:

SELECT <sanitized_columns>
FROM <source_table>

Actions Support

RenameColumn Action

Input

{
  "actions": [
    {
      "actionType": ".v1.RenameColumn",
      "columnName": "Sales Amount USD",
      "newColumnName": "Total Sales USD"
    }
  ]
}

Generated SQL

SELECT region_code,
       sales_amount_usd AS total_sales_usd,
       order_date
FROM ANALYTICS.DBO.SALES_DATA_TABLE

RemoveColumns Action

Input

{
  "actions": [
    {
      "actionType": ".v1.RemoveColumns",
      "columnNames": ["Material Number", "Order Type Code"]
    }
  ]
}

Generated SQL

SELECT region_code,
       sales_rep_id,
       customer_number,
       product_code,
       order_date,
       invoice_amount,
       units_sold,
       division_name,
       sales_amount_local,
       sales_amount_usd
FROM ANALYTICS.DBO.SALES_DATA_TABLE

ChangeColumnType Action

Input

{
  "actions": [
    {
      "actionType": ".v1.ChangeColumnType",
      "fields": {
        "Invoice Amount": {
          "type": "integer"
        }
      }
    }
  ]
}

Generated SQL

SELECT region_code,
       sales_rep_id,
       CAST(invoice_amount AS INTEGER) AS invoice_amount,
       order_date
FROM ANALYTICS.DBO.SALES_DATA_TABLE

Field Type Mapping

Tableau Type	Snowflake Type
`string`	`VARCHAR`
`integer`	`INTEGER`
`real`	`FLOAT`
`date`	`DATE`
`datetime`	`TIMESTAMP`
`boolean`	`BOOLEAN`

Table Name Resolution

Simple Table Name

{
  "relation": {
    "type": "table",
    "table": "SALES_DATA"
  }
}

Converts to: SALES_DATA

Schema.Table Format

{
  "relation": {
    "type": "table",
    "table": "[ANALYTICS].[SALES_DATA]"
  }
}

Converts to: ANALYTICS.SALES_DATA

Database.Schema.Table Format

{
  "relation": {
    "type": "table",
    "table": "[ANALYTICS].[DBO].[SALES_DATA_TABLE]"
  }
}

Converts to: ANALYTICS.DBO.SALES_DATA_TABLE

Handling Large Field Lists

LoadSqlProxy can handle hundreds of fields efficiently:

574 fields example: The transformer processes extensive field lists from legacy systems
Selective column removal: Use RemoveColumns action to filter down to needed fields
Batch renames: Apply multiple RenameColumn actions for standardization

Limitations

⚠️ Database-specific SQL not converted - Advanced database features (stored procs, functions) must be manually rewritten for Snowflake
⚠️ Custom SQL not supported - Only table-based sources; custom SQL queries in relation are not fully converted
⚠️ Supported actions only - Only RenameColumn, RemoveColumns, and ChangeColumnType actions are supported

Best Practices

When to Use LoadSqlProxy Transformer

✅ Database table reads with pre-defined schema
✅ Simple SELECT statements from single tables
✅ Field filtering and renaming from large source tables

Source Database Migration

When migrating from source databases to Snowflake:

Replicate source tables to Snowflake (using Snowflake replication or ETL)
Update table references in relation.table if needed
Verify schema compatibility between source and Snowflake

Related Transformers

LoadExcelTransformer - For Excel file inputs (not database tables)
ContainerTransformer - For post-load transformations
UnionTransformer - For combining multiple database sources