The Source has too many files

SamyAbdul​ 

I would recommend avoiding the creation of 150 separate Lakehouse tables without first checking whether the files represent different business entities or variations of the same entity.

Recommended approach:

• Bronze layer: Store the raw API responses or files with source metadata, such as source name, file name, ingestion timestamp, and schema version.
• Schema standardization: If the files represent the same business entity with different columns, use a superset schema with nullable columns for fields that are missing in some files.
• Separate tables when necessary: If the files represent different business entities, keep them separate rather than forcing them into one wide table.
• Silver layer: Standardize column names, data types, and structures, and consolidate compatible datasets into curated tables.

My recommendation: Keep Bronze flexible for raw ingestion and perform schema harmonization in Silver. Avoid creating one massive table containing unrelated fields just to reduce the table count.

AI assistance was used to format this response.

Thank you Olufemi7​  jubinsoni​ v-aatheeque​  , what I can see is suggestion along the lines to refrain from 150 tables , and that makes sense. But having them all in one table also a not a productive and efficient solution either. The schema varies, and that is the real issue here. What to transverse forward from this tricky situation. Thank again for your help.

@SamyAbdul

Since you have many files, I suggest below approach:

• Bronze Layer: Store API responses in their original format, organized by source.
• Schema handling: Group files with similar schemas into the same table. Keep different entities separate.
• Silver Layer: Standardize schemas, map common fields, and apply data quality rules.
• Scalability: Use metadata-driven ingestion to manage multiple sources efficiently.
  
  
• Gold Layer: Clean curated, business friendly tables for analytics and AI ready data.

Please avoid creating a table for every file or combining all possible columns into one wide table.

If this helps, ✓ Mark as Kudos | Help Other
AI Assisted Draft

Hello SamyAbdul​,
I will steer clear of creating a separate Lakehouse table for every file, but I wouldn’t cram everything into one giant wide table just to keep the table count down either. Microsoft’s medallion architecture guidance points the same way: keep the source data in Bronze, then refine it into structured, validated tables in Silver.

In Silver, I will group files that belong to the same business entity and have compatible schemas.

For schema changes, keep track of new columns and changes to existing ones so you can handle them before they affect downstream tables.

A single Bronze table with a JSON column is another option if you need to query the raw data centrally, but it isn’t a Fabric requirement.

One thing I will clarify first: are the 150 files different datasets, or are they different extracts of the same datasets? That makes a big difference to how I will structure the Lakehouse.

Docs
Schema evolution for Delta tables

Understand medallion architecture for Fabric with OneLake

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.

Hi SamyAbdul​ 

Thanks for sharing the issue.

The error “The source has too many files” may be related to the number of files being processed by the source operation. To narrow this down, could you please confirm which Fabric item or activity is failing (for example, a Data Pipeline Copy activity, Dataflow Gen2, or a notebook) and share the complete error message?

If you are using a Data Pipeline Copy activity to load many small files into a Fabric Warehouse, one approach worth testing is to merge the small files into larger files before loading them. Fabric Community discussions have recommended the Copy activity's Merge files option for scenarios where numerous small files affect Warehouse loading performance.

<Link url="https://community.fabric.microsoft.com/discussions/df_pipelines/speed-of-writing-to-fabric-data-warehouse-from-fabric-pipeline-vs-adf-pipeline/3871823/replies/3899516

The appropriate solution depends on the failing activity and source type. Please share the activity name, source type, approximate number of files, and complete error details so we can identify the most suitable next step.

Thanks!