@Gharhub Good questions!!!!
A few patterns that tend to work well for this kind of catalog:
Schema for heterogeneous raw materials: rather than one wide table per material category (which breaks every time a new attribute shows up), model a core materials table (id, category, supplier, base UoM) plus a category-specific attributes column stored as VARIANT (or MAP<STRING,STRING> if you're on an older Delta version) instead of literal EAV rows. That gets you schema flexibility without a proliferation of sparse columns, while keeping core dimensions (price, supplier, category) as real typed columns for fast filtering/joins.
Real-time-ish pricing: "real-time" freight/bulk pricing is rarely true streaming end-to-end -- usually it's: ingest price/freight-rate change events via Auto Loader into a Structured Streaming pipeline, apply pricing rules as a streaming MERGE into a current_prices Delta table (keyed by SKU+region+tier), and serve reads from that table directly if consumers tolerate seconds-old data, or via an online table/Lakebase if you need sub-100ms lookups from a checkout path. Keep the pricing rules as data in a rules table joined in, not hardcoded logic, since freight/bulk tiers change often.
DLT pipeline shape: standard medallion -- Bronze via Auto Loader off raw ERP/EDI drops, Silver applying the schema above plus dedup/CDC handling (APPLY CHANGES INTO if your source gives you CDC), Gold as the pricing-and-inventory marts your BI/pricing engine reads from. For inventory specifically, use APPLY CHANGES INTO with sequencing on your source's change timestamp so out-of-order updates from multiple warehouses resolve correctly instead of last-write-wins on arrival order.
