The Source has too many files

Hi SamyAbdul​,

I would avoid both extremes in bronze: 150 tables per source is hard to operate, and one giant table with the union of all columns becomes mostly nulls and breaks every time a file changes. A pattern that scales better is a generic landing table in bronze, and real tables only from silver onwards:

Files/raw/<source>/<entity>/<yyyy-mm-dd>/...json   (keep originals, cheap)
          |
bronze.raw_records  (one table per source, or even one for all sources)
   source_name | entity_name | file_name | ingested_at | schema_hash | payload (JSON string)
          |
silver.<entity>     (typed columns, only for entities the business actually uses)

Loading it is a small generic notebook, no per-file code:

from pyspark.sql import functions as F

df = (spark.read.option("multiLine", True).text("Files/raw/sourceA/*/2026-10-23/*.json", wholetext=True)
        .withColumn("file_name", F.input_file_name())
        .withColumn("entity_name", F.regexp_extract("file_name", r"sourceA/([^/]+)/", 1))
        .withColumn("source_name", F.lit("sourceA"))
        .withColumn("ingested_at", F.current_timestamp())
        .withColumnRenamed("value", "payload"))

df.write.mode("append").saveAsTable("bronze_raw_records")

Silver then parses only what is needed, per entity, with from_json and a schema that is versioned in a metadata table. New or changed files never break bronze; they just show up as a new schema_hash that you can review before promoting them to a silver table.

So my overall approach would be:

Keep raw files as-is in Files/
-> Bronze: generic table with metadata + JSON payload
-> Silver: typed table per entity that is actually used
-> Metadata table drives parsing; schema changes become a review step

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.