Enable Data Type Casting in Lakehouse Analytical Endpoints for Shortcuts

[DürsinKurt]

Description:
Currently, when using Shortcuts in a Lakehouse, the Analytical (SQL) Endpoint automatically infers schema from Delta/Parquet and exposes all string columns as VARCHAR(8000). This creates friction in real-world scenarios where data engineers need consistent, strongly typed schemas for downstream processes (e.g., Warehouse loading, Power BI models).

Problem:

• In multi-layer architectures (Bronze → Silver → Gold), the same Bronze object often appears in multiple ETL pipelines.
• Without a way to define or persist casts at the Lakehouse level, type enforcement must be repeated in every ETL.
• This increases maintenance overhead and risk of schema drift.
• Current workaround: materialize new Delta tables in Silver with Spark, but this adds unnecessary duplication and complexity.

Proposed Solution:

Introduce a Type Conversion Layer in the Lakehouse Analytical Endpoint for Shortcuts:

• Allow users to configure column-level type overrides (e.g., VARCHAR(200), DECIMAL(18,2)) when exposing data via the SQL Endpoint.
• If schema drift occurs (source type changes), the system should:
  • Raise an error during refresh, OR
  • Create a secondary object (e.g., a managed view) to preserve the original schema.
  • The original, uncasted object should always remain available for full flexibility.

Benefits:

• Reduces repetitive casting logic across ETL pipelines.
• Improves governance and consistency in Silver/Gold layers.
• Aligns Lakehouse with Warehouse capabilities (e.g., VARCHAR(MAX) support).

 Considerations:

• Acknowledge that this introduces complexity for schema evolution.
• Provide clear UI/UX for managing type overrides and handling drift.