Splitting tables between directquery and import mode

[Hussein_charif]

hello,

i've been seeing recently many articles and videos about composite/hybrid models, where the developer splits the fact table between directquery and import mode, having directquery for older historical data and import for new, up to date data, and so when the user wants to see newer data, the import mode queries the new data, and vice versa.

can anyone guide me / explain how this works exactly, and how we can do it in the most efficient way? 

[HarishKM]

@Hussein_charif Hey,
I will try explain as simple possible manner in belows steps

Composite/Hybrid models allow combining DirectQuery and Import modes in a single Power BI model to optimize performance:

1. Split Fact Table: Divide the fact table into two segments:

- DirectQuery: Use for older, historical data accessed directly from the source to save import time and space.
- Import Mode: Use for recent data to enhance query performance as data is readily available in-memory.

1. Setup:

- Start by setting up DirectQuery connections for historical data.
- Import recent data into the Power BI model.

1. Data Model Design: Ensure relationships are correctly defined so users can seamlessly query both older and newer data.

1. Efficiency: Balance the data split considering frequency and size of newer data updates to ensure efficient query performance without overloading the system.

This method enables a dynamic and efficient system, leveraging DirectQuery's real-time capabilities for often less accessed historical data, while optimizing performance for frequently queried recent data via Import mode.

Thanks

Harish M
Kindly give Kudos and accept it as solution if its solves your problem

[v-kpoloju-msft]

Hi @Hussein_charif,

Thank you for reaching out to the Microsoft fabric community forum and sharing the details. Also, thanks to @Shahid12523, @Gabry, @Nabha-Ahmed, @amitchandak, for those inputs on this thread.

You are right. what you are describing is called a hybrid/composite model in Power BI, where part of the fact table is cached in Import mode for speed, and the rest is kept in Direct Query, so you don’t have to load all the historical data.

The easiest and most efficient way to achieve this is by using Incremental Refresh with Hybrid Mode. You can tell Power BI to keep (for example) the last 6 months of data in Import mode, and anything older will stay in Direct Query. That way, when users look at recent data, it loads instantly from memory, but if they need history, Power BI queries the source on demand.

Here is how you can set it up: In Desktop, define an incremental refresh policy (Modelling → Incremental Refresh). Choose how much data to keep in Import and how far back you want to enable Direct Query. Enable Hybrid Mode. Publish the dataset, Power BI automatically manages the split partitions for you. This setup is much cleaner than manually splitting tables, and it scales well.

Refer these links:
1. https://learn.microsoft.com/en-in/power-bi/connect-data/incremental-refresh-overview 
2. https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @Hussein_charif,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[Shahid12523]

Idea: Keep recent data in Import (fast, in-memory) and old data in DirectQuery (lighter, on-demand).

How:

If you have Premium/Fabric → use Incremental Refresh with Hybrid mode (Power BI automatically partitions the table).

Without Premium → manually split into two tables (Import for recent, DirectQuery for historical) and UNION them.

Result: Users see one seamless fact table. Queries on recent data are fast; old data is only queried when needed.

[Hussein_charif]

so i split the fact table to 2 seperate tables, one that is direct query and getting only old data (example <=2023) and the other is import and getting new data (>2023), and then i append both tables in power query?

[Gabry]

Hey,

I think what you are looking for are hybrid tables. Take a look on this great article from datamozart https://data-mozart.com/hybrid-tables-in-power-bi-the-ultimate-guide/ 

[Nabha-Ahmed]

Hi @Hussein_charif , great question!

What you’re describing is called a **composite model** (or hybrid model). The idea is:

* **Recent / frequently used data** → loaded in **Import mode** for fast performance.
Older / historical data → kept in DirectQuery so you don’t overload memory with millions of rows that are rarely accessed.

How it works:

1. In Power BI Desktop, connect to your source twice (e.g., SQL Server).

* One connection as *Import** (limit to recent data, e.g., last 2 years).
* Another connection as **DirectQuery** (for all data, or older partitions).
2. Combine them into a **single fact table** using **table relationships** or **union logic** (sometimes via a Date table).
3. When users query the report:

* If they slice/filter on recent dates, the Import part is used (fast).
* If they go further back, Power BI uses DirectQuery to fetch only the historical data.

For efficiency:

* Keep the Import dataset as small as possible (only what needs fast queries).
* Use **aggregations** on the DirectQuery table to speed up queries.
* Make sure your Date table is consistent and connected to both parts.

📖 Official docs: [Composite models in Power BI](https://learn.microsoft.com/power-bi/transform-model/desktop-composite-models)

🎥 Good video: [Hybrid tables in Power BI (SQLBI)](https://www.youtube.com/watch?v=ZaWt0uKWrC8)

Hope this helps clarify! If it answers your question, please mark it as a solution ✅.

---

[amitchandak]

@Hussein_charif , Hybrid table is one option, refer from Guyinacube 
https://www.youtube.com/watch?v=1GvVCHeqJz4

https://www.youtube.com/watch?v=Hpx52kOodPc