Pular para o conteúdo principal

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 TypeSnowflake Type
stringVARCHAR
integerINTEGER
realFLOAT
dateDATE
datetimeTIMESTAMP
booleanBOOLEAN

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:

  1. Replicate source tables to Snowflake (using Snowflake replication or ETL)
  2. Update table references in relation.table if needed
  3. Verify schema compatibility between source and Snowflake
  • LoadExcelTransformer - For Excel file inputs (not database tables)
  • ContainerTransformer - For post-load transformations
  • UnionTransformer - For combining multiple database sources