Upsolver Case Sensitivity Approach

Understanding how Upsolver handles case sensitivity in column names

Upsolver offers a consistent methodology for managing case sensitivity in column names during SQL query execution. Understanding its approach is crucial for precise and efficient data operations. This guide details Upsolver's handling of case sensitivity and how column names are mapped across various target systems in SELECT queries.

Column Case Insensitivity in SQLake

Upsolver views column names without considering their case. In other words, it doesn't matter if column names are in upper, lower, or mixed case; Upsolver sees them all as the same when executing queries.

Retaining Original Column Case in SELECT Queries

While Upsolver strives to maintain the original case of column names, its behavior might differ based on the target system in question. Below is a breakdown of how Upsolver retains the original case in a SELECT query across different systems:

Mapping of Column Names by Target System

Target System	Top-Level Columns	Variant / Struct fields
Snowflake	Uppercase	Original Case
Athena	Lowercase	Lowercase
Other	Original Case	Original Case

Examples of Mapping Column Names for SELECT *

Let's illustrate how Upsolver maps column names when performing a SELECT * query for various target systems:

Suppose we have the following table structure with original case column names: CustomerID, OrderDate, BillingAmount, and a nested column named CustomerAddress with two sub-fields.

| CustomerID | OrderDate   | BillingAmount | CustomerAddress             |
|------------|-------------|---------------|-----------------------------|
| 101        | 2023-01-15  | 150.00        | { "Line1": "123 Main St",   |
|            |             |               |   "Line2": "Apt 4B" }       |
| 102        | 2023-02-10  | 200.00        | { "Line1": "456 Elm Rd",    |
|            |             |               |   "Line2": "Suite 200" }    |

Here's how the column names map for different source fields and target systems:

Source Field	Snowflake	Athena	Other
CustomerID	CUSTOMERID	customerid	CustomerID
OrderDate	ORDERDATE	orderdate	OrderDate
BillingAmount	BILLINGAMOUNT	billingamount	BillingAmount
CustomerAddress	CUSTOMERADDRESS	customeraddress	CustomerAddress
CustomerAddress.Line1	CUSTOMERADDRESS.Line1	customeraddress.line1	CustomerAddress.Line1
CustomerAddress.Line2	CUSTOMERADDRESS.Line2	customeraddress.line2	CustomerAddress.Line2

Known Limitations

Jobs created prior to Upsolver version 2023.08.27-14.13 will not adhere to the rules outlined above. In these cases, all columns will be converted to lowercase for all target systems except Snowflake, where the column names will be converted to uppercase.