What is the most efficient approach for data transformation when designing data models for scalability?

• A. Data transformation should be done as close to the source as possible, before reaching Power BI.
• B. Data transformation should always be done using Power Query in Power BI.
• C. Data transformation should be done using DAX, after data is ingested and loaded to the model.
• D. Data transformation should be done in a separate ETL tool after loading to the model.

Answer: (A)

Transforming data as close to the source as possible is the most scalable approach because it minimizes the volume of data that needs to be loaded and processed in the BI layer, ensures consistent, clean data for all consumers, and keeps refresh and model performance predictable as data grows. When you perform heavy cleansing, joining, and normalization upstream, the data model in the analytics tool remains lean and faster to query, which scales better with increasing data volume and more reports.

If you push transformations into the BI layer, whether in a query editor or with calculations after load, the workload shifts into the model itself. This can inflate the data footprint, slow down refreshes, and complicate maintenance since the same rules may need to be replicated across multiple reports or models. While light transformations and dynamic calculations have their place in the BI tool, relying on upstream transformations for the bulk of data shaping keeps the system more performant and scalable as needs grow. Using an external ETL step after loading to the model adds complexity and latency, further hindering scalability.