Microsoft Power BI is a self-service Business Intelligence tool that lets you connect to multiple different data sources – Power BI offers 92 different data connectors to connect the data sources. With these connectors, you can connect to different data sources. Power BI offers two different Data Connectivity modes to connect the data sources – Import and DirectQuery.
How to choose the right Data Connectivity mode?
Both Data Connectivity modes are having their own unique capabilities and some limitations as well.
Import Connection – Import Data Connectivity mode lets you import data into Power BI cache. Highly recommended to use Import connection when the data size is less than 1 GB and the data is not continually changing. You can import data with schedule refresh to get the latest data. With Import connection, you can take full advantages of the high-performance query engine.
DirectQuery – DirectQuery Connectivity mode lets you connect directly to data. DirectQuery mode used to build Real-time or near real-time BI solutions when the data is changing frequently, and Data volume is very large. No data will be imported into Power BI. Instead Power BI will send queries to the data source upon building visual/interacting with visuals. Each query is restricted to return less than or equal to 1 Million rows.
Here is the comparison between Import and DirectQuery,
| Capability | Import | DirectQuery |
| Size | Up to 1 GB per dataset | No limitation |
| Data Source | Import data from Multiple sources | Data must come from a single Source |
| Performance | High-performance query engine | Depends on the data source response for each query |
| Data Change in the underlying data | Not Reflected. Required to do a Manual refresh in Power BI Desktop and republish the report or Schedule Refresh | Power BI caches the data for better performance. So, it is necessary to Refresh to ensure the latest data |
| Schedule Refresh | Maximum 8 schedules per day | Schedule often as every 15 mins |
| Power BI Gateway | Only required to get latest data from On-premise data sources | Must require to get data from On-premise data sources |
| Data Transformations | Supports all transformations | Supports many data transformations with some limitations |
| Data Modelling | No limitation | Some limitations such as auto-detect relationships between tables and relationships are limited to a single direction. |
| Built-in Date Hierarchy | Available | Not available |
| DAX expressions | Supports all DAX functions | Restricted to use complex DAX functions such as Time Intelligence functions. However, if there is a Date table available in the underlying source then it supports |
| Clustering | Available | Not available |
| Calculated Tables | Available | Not supported |
| Quick Insights | Available | Not available |
| Q&A | Available | Not available |
| Change Data Connectivity mode | Not possible to change Import to DirectQuery | Possible to change DirectQuery to Import |
Ref: https://www.cittabase.com/power-bi-tips-import-vs-direct-query/