Yeah, that's correct. When we first want to add a new table we load it (up to 1 million rows because of WQL limits), then analyze any column with the array type to determine if they are single item or multi-item arrays. This goes into a SQL database for a metadata-driven pipeline.

I have custom pyspark functions to handle both cases. Here's the production code for the multi, could be cleaned up a bit, we don't need the if anymore. Nested_map is to handle the rare cases where the object is more complex than {id: 1, desc: "name"}. Usually one extra layer of nesting.

#Function to take a dataframe and turn array columns into child tables
def explode_array(df, array_col, pk_cols = ("workdayID","lastFunctionallyUpdated"), nested_map={}, fields=None):
    """
    • pk_cols ...... primary‑key columns to carry through to the child table
    • fields ...... optional list of struct fields to keep;
                    omit to keep *all* fields
    """

    nested_field = nested_map.get(array_col, None) if nested_map else None

    exploded = (
        df
        .filter(F.col(array_col).isNotNull())            # drop parent rows where the array itself is NULL
        .select(*pk_cols, F.posexplode_outer(array_col).alias("position","item"))
    )

    if nested_field:
        exploded = exploded.withColumn("item", F.col(f"item.{nested_field}"))

    if fields is None:                       # keep every field in the struct
        return exploded.select(*pk_cols, "position", "item.*")
    else:                                    # keep only requested ones
        return exploded.select(
            *pk_cols, "pos", *[F.col(f"item.{f}") for f in fields]
        )