Forum Discussion

JonBFabric's avatar
JonBFabric
Icon for Helper I rankHelper I
9 months ago
Solved

Lakehouse Table Generate Create Table

Hi,

 

I maintain server lakehouses, and due to issues with the deployment pipelines tend to apply schema changes through script, and to retain the data in the table use the following process:

 

  1. Create a new table with the desired structure
  2. Insert all records from oringinal table in to new table
  3. After checking rounts drop the original table and rename the new table.

For Step 1, to be able base the new table on the existing table I need to be able to identify existing data types for all existing fields, and it appears there is no reliable way of doing this. The main issue relates to Char and Varchar fields, as there appears to be no current mothod for determining the appropriate character length.

 

I have tried various methods on the lakehouse, but those always show the fields to be String, with no maximum size.

 

I have also tried querying INFORMATION_SCHEMA COLUMNS through the sql endpoint, and the problem here is that value for CHARACTER_MAXIMUM_LENGTH appears to be 4 times the actual defined maximum number of characters, up to a maximum of 8000. I.e. A character length of 100 is shown as 400, 1000 is shown as 4000, 2000 and higher are always shown as 8000.

 

Does anyone know of a reliable way of generating a create statement for an existing lakehouse table?

 

data engineering
lakehouse
  • JonBFabric's avatar
    JonBFabric
    9 months ago

    One final update on this.

     

    The logfile containing the schema is not necessarily the most recent, and there is not necessarily only one version of the schema. There is a schema associated with every modification made to the table structure, be that the original creation or subsequent alterations. Consequently, the logfile we need is the most recent with a schema.

     

    from delta.tables import DeltaTable
    import json
     
    # Note that the table name must be lowercase
    table_path = '<Path_To_Table>'
     
    # Identify log files
    log_dir = f"{table_path}/_delta_log"
    files = [f.path for f in notebookutils.fs.ls(log_dir) if f.name.endswith(".json")]
     
    # Identify log files with a schema
    filesWithSchema = []

    for file in sorted(files, reverse=True) :
        content = notebookutils.fs.head(file, 5000000)
        JSONdocs = content.split('\n')

        for doc in JSONdocs:
            if 'schemaString' in doc:
                filesWithSchema.append(file)
     
     
    # Load the header for the latest log file containing a schema
    latest = sorted(filesWithSchema, reverse=True)[0]
    content = notebookutils.fs.head(latest, 5000000)
     
    # Extract the schema
    JSONdocs = content.split('\n')

    for doc in JSONdocs:
        if 'schemaString' in doc:
            schemaString = json.loads(doc).get("metaData", {}).get("schemaString")

    # Extract Field Metadata
    FieldList = []
    OrdinalPosition = 0

    for field in json.loads(schemaString).get("fields") :
        OrdinalPosition += 1
        FieldDetails = {}
        FieldDetails['FieldName'] = field.get("name")
        FieldDetails['Nullable'] = field.get("nullable")
        FieldDetails['OrdinalPosition'] = OrdinalPosition

        match field.get("type").split('(')[0]:
            case 'string':
                FieldDetails['SQLType'] = field.get("metadata").get("__CHAR_VARCHAR_TYPE_STRING")
            case 'timestamp':
                FieldDetails['SQLType'] = 'timestamp'
            case 'date':
                FieldDetails['SQLType'] = 'date'
            case 'integer':
                FieldDetails['SQLType'] = 'int'
            case 'short':
                FieldDetails['SQLType'] = 'smallint'
            case 'long':
                FieldDetails['SQLType'] = 'bigint'
            case 'decimal':
                FieldDetails['SQLType'] = field.get("type")
            case 'boolean':
                FieldDetails['SQLType'] = 'boolean'

        FieldList.append(FieldDetails)


    display(FieldList)

10 Replies

  • Zanqueta's avatar
    Zanqueta
    Icon for Super User rankSuper User
    9 months ago

    Hi JonBFabric,

     

    ✅ Why this happens

    • Delta tables in Fabric do not enforce fixed-length character types like CHAR(n) or VARCHAR(n); they store text as variable-length strings.
    • The SQL endpoint maps these to STRING for compatibility, so the original length constraint is not preserved.

    ✅ Why CHARACTER_MAXIMUM_LENGTH shows 4x

    • The SQL endpoint assumes UTF-16 encoding internally, so the reported length is multiplied by 4.
    • This is a known limitation and does not affect actual storage or query behaviour.

     

    Official References:

    What is a lakehouse? - Microsoft Fabric | Microsoft Learn

    Table utility commands | Delta Lake

     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

    • JonBFabric's avatar
      JonBFabric
      Icon for Helper I rankHelper I
      9 months ago

      Thanks. Great to get the explanation as to what is happening and why. But going back to the original question...

       

      Is it possible to identify the SQL statement used to originally create a table? I'm getting the impression that the answer is no. And given that the maximum record length that can be handled by the SQL endpoint is 8060 bytes, those character limits are crucial and need to be tightly controlled.

      • v-hashadapu's avatar
        v-hashadapu
        Icon for Community Support rankCommunity Support
        9 months ago

        Hi JonBFabric , Thank you for reaching out to the Microsoft Community Forum.

         

        Fabric lakehouse cannot return the original CREATE TABLE statement because that information is never stored. Delta Lake only keeps a structural schema in its transaction log and all string columns are recorded simply as string without any notion of the original CHAR(n) or VARCHAR(n) definitions. Because the lakehouse storage layer does not preserve fixed length constraints, there is no system level metadata you can query later to recover them.

         

        The SQL analytics endpoint also cannot help because it exposes a compatibility projection rather than the real underlying schema. Its inflated character lengths, including the 4× multiplier and the cap at 8000 are generated by the endpoint itself and do not represent the actual table definition or any original DDL. That surface is designed for querying, not schema reconstruction and therefore does not retain the information you are looking for.

         

        Given these constraints, there is no reliable way to extract the exact SQL used to create a lakehouse table after the fact. If those character limits matter for downstream SQL workloads, the only viable approach is to rebuild the DDL by measuring actual data lengths in the table and defining controlled column sizes going forward. For future tables, the only dependable method is to version control the DDL at creation time or store it explicitly as metadata, because the platform does not preserve it automatically.

         

        What Is Data Warehousing in Microsoft Fabric? - Microsoft Fabric | Microsoft Learn

        What is a lakehouse? - Microsoft Fabric | Microsoft Learn

        Data Types in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

        What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn

        Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn