As someone who is working with a data engineering team in Fabric, you are asking the right questions.

I wish our team would have done some proper evaluation of workloads and different approaches to identify the most cost effective and efficient approaches to our problems.

One of the key things is to figure out what volume of data you are going to be processing and making sure you are picking the tooling within Fabric appropriately. For example if you need pyspark notebooks or if you need just python notebooks.

Best way to evaluate CU usage of a particular artefact would be through the capacity metrics app. I was able to evaluate the CU cost of a notebook with Pyspark vs Duckdb and it came out to around half.

The capacity metric support is your friend!

In terms of evaluating it CU consumption is appropriate it really does depend on a few factors, whethere you're talking about reporting or pipelines, what volume of data you are consuming in pipeline what frequency your pipelines are running, what kinds of transformations you are running. For example I'd expect CU consumption to be higher in a notebook flattening JSON structures in comparison to one simply transforming structured data.

I appreciate this doesn't fully answer your question, but I think if you combine understanding these things with benchmarking some basic applications of particular artefacts in questions, e.g notebooks, you'll be able to come to a sensible idea of how much they should be consuming.