Data Wareshouse design in Hive

@APentyala Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our CDW experts @smruti @asish  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

@APentyala  Please find the answers below:

1. Which data modeling approach is recommended for this domain?

Ans: If you have large data, we would recommend to go with Partitioning or multi-level partitioning. You could implement Bucketing if the data inside partition is large.

2. Are there any sample models available for reference?

Ans: You could take a refrence for partitioning and bucketing in https://www.linkedin.com/pulse/what-partitioning-vs-bucketing-apache-hive-shrivastava/
You could create a new table perfroom CTAS with Dynamic Partitiining from the existing table
Refrence: https://www.geeksforgeeks.org/overview-of-dynamic-partition-in-hive/

3. What best practices should we follow to ensure data integrity and performance?

Ans: Please follow below best parctices:

a. Paartion and bucket it
b. You could use Iceberg table which would reduce the significant load on Metastore, if you are using CDP Public CLoud or CDP private CLoud(ECS/Opesnshit)
c. Use ORC/parquet
d. Use EXTERNAL tables,if you dont perfrom Update/Delete as reading External table is faster.

4. How can we efficiently manage large-scale data ingestion and processing?

Ans: The model follows as:
Kafka/Spark Streaming: Ingestion
Spark: Data Modelling
Hive: Warehosuing where you extract the data

Please. be specific on the use case.

5. Are there any specific challenges or pitfalls we should be aware of when implementing a lakehouse in this sector?

Ans: There should be no challenges, we would request to provide more briefing on this.

@APentyala Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.  Thanks.