Best practice for deciding between Eventstream transformations and Eventhouse update policies

Hello ShivekMaharaj 

welcome to this Microsoft Fabric community portal.

In short, your current thinking is correct.

Use the Eventstream solely for ingesting the data and do the heavy lifting in Eventhouse.

You want to have access to the original message; then a proper medallion architecture is the way to go.

You ingest all data in bronze layer Eventhouse tables using Eventstream transformation only for technical purposes like rejecting corrupted messages or adding fields (like ingestion timestamp).

From there, use table update policies to transform the data to silver tables where needed. If deduplication is needed, add silver materialized views. Why the silver tables? Because some data-consuming Fabric Items work only with tables at the moment, not materialized views. This way, you can serve all items.

The update policies can even enrich the message (in a limited way) if needed.

Because you have the same data in both the bronze and silver tables, at some point in time you need to think about the retention time of the bronze data (you probably want to keep the silver quality data). 

Because of the excellent compression of Eventhouse, you can postpone this decision so you have plenty of time to check and double-check if the policies do their job correctly.

I have a sweet spot for Direct Ingest because I have full control over the original table mapping towards the Bronze table. I love a combination of a message identifier of the sender (line sensorID) taken from the original message, the timestamp taken from the original message, and a dynamic with the full original message.

This way, if the message format changes, I'm still flexible because at least the message arrived in the Bronze table. From there, I can fix things.

This is because I'm probably not in control of the original message format. It's coming from the outside world so if the message is rejected due to some failing transformation, it's lost. I hope the identifier and timestamp do not move within the message...

Historical data can be moved around from table to table in Eventhouse. This takes time (ingestion is fast, but billions of rows can still take time). Do it in batches, and track any issues.

Aggregations- that looks like a job for materialized views as a gold layer. You want them for a certain purpose, so keep them separated. Materialized views can be backfilled if needed.

Keep in mind there is a learning curve for many of the concepts seen here.

Take some time to explore how the Eventhouse works and how to use the different 'tools' in there... 

You will end up with a versatile, cost-effective, and blazingly fast timeseries database.

These are my 50 cents 😉

---

If this answer helps you, marking it as the accepted answer is appreciated. All community members with similar questions will benefit by doing so. Your contribution is highly appreciated.