Forum Discussion
Post ProdigyModelling a large customer table in Fabric
Hi pmscorca
Thanks for using Fabric Community.
Here's how you can approach the customer registry data model in Microsoft Fabric, considering both approaches and best practices:
Understanding the Needs:
- Star Schema: This is a good fit for analytical workloads where you prioritize fast querying and aggregation. It's familiar and works well with Power BI for visualizations.
Star Schema
- Fast querying and aggregation.
- Familiar and easy to understand for analysts.
- Works well with Power BI.
Hybrid Approach: This is a common and effective strategy.
- Create a dimensional model (star schema) for the core customer data and frequently queried attributes (e.g., name, contact details).
- Store detailed information like addresses and notes in separate tables with foreign keys linking them to the customer dimension.
- Consider materialized views for frequently accessed denormalized data within the star schema (e.g., latest address).
Best Practices for Big Data Modeling in Microsoft Fabric:
- Leverage Data Lake: Use Lakehouse to store your raw and semi-structured data.
- Data Transformation: Use Data Factory to ingest, cleanse, and transform data before loading it into the star schema or big table.
- Partitioning: Partition your tables based on access patterns for faster queries.
- Monitoring: Monitor data quality and performance of your data pipelines.
You can refer to these links for more information:
https://community.fabric.microsoft.com/t5/Desktop/Best-data-modeling-approach/td-p/2927540
https://community.fabric.microsoft.com/t5/Desktop/data-modelling/td-p/1797121
https://learn.microsoft.com/en-us/fabric/data-warehouse/data-modeling-defining-relationships
Best practices for lifecycle management in Fabric - Microsoft Fabric | Microsoft Learn
Hope this helps. Please let me know if you have any further questions.
Hi pmscorca
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks
pmscorcaHi, I think that is enough to reply by leveraging on some key concepts.
Having a customer registry with core/header data and some other data with a 1-to-m relationship should represent a pattern scenario, manageable thinking to a dwh by a dimensional model based on a star schema, but thinking to a big data modeling?
Fabric allows to create lakehouses and warehouses, based on Spark delta tables: this format is more performant and has a columnar data organitazion, perfectly suitable to big data.
Moreover, Fabric allows to partition table f.e. respect to a date and so a dimension could be handle in a different manner than a SCD one.
Does the columnar organization of the delta tables for a big data scenario suffer to have more null values?
Fabric allows to implement a big data solution, by his features, but to handle big data occurs to go beyond the known data modeling approaches (E/R model, dimensional model and so on).
In these terms, is it possible to have an initial reply for my issue? Thanks