ADLS structure approach

Thank you @Marcin Policht I appreciate your help alot. Since in bronze and silver layer we are creating the hierarchical structure through databricks, I would follow this approach:

1. Change the databricks code , and incoporate your suggestions and implement key-value based heirarchy in bronze and silver layer.
2. Should I go for full blown hierarchical structure starting with

     **1.**  datasouce=marketing
   
               year=2022/
   
                   month= 01/
   
                         day=12/
   
                                  file1.parquet 
   
                         or
   
                  **2.**               year=2022/
   
                                         month= 01/
   
                                              day=12/
   
                                                     file1.parquet
   

or option 2 will suffice.

Also,since we already have data in these layers, would simply changing hierarchal place holders at the databricks code level , would it impact underlying data, and are there any other consideratons that I should take care of.

Please help and suggest. Thank you very much again.

I'd agree that moving bronze/silver to a key-value (Hive-style) partition layout is the right call. As far as deciding between the two variants is concerned, you mentioned:

1. datasource=marketing/year=2022/month=01/day=12/file1.parquet
2. year=2022/month=01/day=12/file1.parquet

• If you already separate domains in the metastore (catalog/schema/table) or by top-level folders like /bronze/marketing/... then Option 2 is sufficient for the partition portion.
• If you store multiple domains in the same table root/path and need the path itself to distinguish them, then keep a domain key as a non-partition folder above the table root or make it a partition column if you'll filter on it.

I'd suggest a table-centric layout, with domain separation at catalog/schema (Unity Catalog) or top-level directory, and then Hive-style key=value partitions for pruning:

/Volumes/bronze/marketing/affiliate/        <-- table root (Delta recommended)
    entity=affiliate/                        <-- only if you truly need entity as a filter/partition
    year=2022/
      month=01/
        day=12/
          part-0000-....snappy.parquet|delta

or, without entity= if the table is already the entity:

/Volumes/bronze/marketing/affiliate/
    year=2022/
      month=01/
        day=12/
          ...

In general, don't over-partition. Common sweet spot is (year,month,day) (by event date or ingestion date—see below). In addition, reserve extra partition keys (like entity) only if they're selective and commonly filtered.

Spark/Databricks/Synapse auto partition discovery expects key=value/… folder names. Partition pruning (and file-skipping) works only when the filterable dimension is a partition column (and encoded in the path). Delta Lake (recommended over naked Parquet) further gives OPTIMIZE/VACUUM/time travel and removes the need for manual “repair partitions”.

Regarding the migration plan, you asked if “simply changing placeholders in Databricks code” impacts data. Changing the write path does not move existing files; it just starts writing to a new location. So plan the cutover:

1. Pick partition columns
   • Prefer event_date (the business date) → year, month, day.
   • If you have late arriving data and want “what arrived when”, also keep an ingestion_date column (not necessarily a partition).
2. Create new table paths (Delta recommended)
   • Start writing new data to the new table root with partitionBy("year","month","day").
   • Backfill later (see step 4) or union old+new via a view during transition.
3. Keep old readers working
   • Create a view that unions the old layout and the new layout so downstream jobs don't break:

          CREATE OR REPLACE VIEW silver.marketing_affiliate AS
          SELECT * FROM silver_old.marketing_affiliate
          UNION ALL
          SELECT * FROM silver_new.marketing_affiliate;
     

   • Swap dependencies gradually, then retire the old.
4. Backfill historical data (optional but recommended)
   • A one-time job that reads old layout, derives year/month/day, and rewrites to the new root.
   • For Delta targets: after write, run OPTIMIZE (and later VACUUM).
5. Tombstone/redirect
   • When confident, update consumers to the new table only, and archive or delete the old path.

Regarding code patterns (Databricks / PySpark), derive partitions and write Delta:

from pyspark.sql import functions as F

df = source_df.withColumn("event_date", F.to_date("event_ts")) \
              .withColumn("year",  F.year("event_date")) \
              .withColumn("month", F.date_format("event_date", "MM")) \
              .withColumn("day",   F.date_format("event_date", "dd"))

# Bronze example (external location or UC Volume):
target_path = "/Volumes/bronze/marketing/affiliate"

(df.write
   .format("delta")
   .mode("append")
   .partitionBy("year","month","day")
   .save(target_path))

# Register as a table (preferred for governance)
spark.sql(f"""
  CREATE TABLE IF NOT EXISTS bronze.marketing_affiliate
  USING DELTA
  LOCATION '{target_path}'
""")

With Auto Loader (if you ingest files continuously):

from pyspark.sql.functions import *

raw_path = "/Volumes/raw/marketing/affiliate"
bronze_path = "/Volumes/bronze/marketing/affiliate"

(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")       # or csv/parquet
  .load(raw_path)
  .withColumn("event_date", to_date("event_ts"))
  .withColumn("year",  year("event_date"))
  .withColumn("month", date_format("event_date","MM"))
  .withColumn("day",   date_format("event_date","dd"))
  .writeStream
  .option("checkpointLocation", bronze_path + "/_checkpoint")
  .format("delta")
  .partitionBy("year","month","day")
  .outputMode("append")
  .start(bronze_path))

Query pruning works naturally:

SELECT count(*) 
FROM bronze.marketing_affiliate
WHERE year = 2024 AND month = '07';

Backfill old layout → new layout:

old = spark.read.format("parquet").load("/old/bronze/Current marketing/affiliate/*/*/*/*")
old2 = (old
  .withColumn("event_date", F.to_date("event_ts"))
  .withColumn("year",  F.year("event_date"))
  .withColumn("month", F.date_format("event_date","MM"))
  .withColumn("day",   F.date_format("event_date","dd"))
)

(old2.write
   .format("delta")
   .mode("append")
   .partitionBy("year","month","day")
   .save("/Volumes/bronze/marketing/affiliate"))

Delta maintenance:

OPTIMIZE bronze.marketing_affiliate;     -- compaction
VACUUM bronze.marketing_affiliate RETAIN 168 HOURS;  -- after you're sure no readers need old files

Synapse & Spark “discover” and prune partitions when folders are col=value. So your colleague is right: use key=value names (e.g., year=2022/month=01/day=12). If you stick with plain 2022/01/12, the engines treat them as directories, not partition columns, and you lose pruning unless you manually parse paths.

Other considerations to keep in mind:

• Delta vs Parquet: Prefer Delta for bronze/silver. You keep Parquet inside Delta anyway, but gain transaction logs, schema evolution, OPTIMIZE/VACUUM, and simpler partition management.
• Schema evolution: If your API evolves, enable controlled evolution (mergeSchema on write or DLT expectations) and track with table constraints.
• Partition size: Aim for 128–512 MB file sizes post-OPTIMIZE. Too many tiny files kill performance.
• Late data: If you partition by event date, writes for old days will land in older partitions—fine in Delta. If your SLA is ingestion-based, partition by ingestion date instead, and keep event_date as a query column.
• Metastore vs path readers: If any downstreams read paths directly, path changes will break them. Prefer reading tables (catalog.schema.table). Provide a compatibility view during migration.
• Repair/discover:
  • Delta tables: ALTER TABLE … SET LOCATION if you move the root; no MSCK needed.
  • Non-Delta Hive tables: MSCK REPAIR TABLE to load partitions.
• Naming: Use consistent lower_snake_case table and column names; partition columns typed as INT (year), STRING (‘MM', ‘dd') or INT—just be consistent with filters.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

nice bro