Staging Dataflows -- Linked tables from dataflow still causing gateway server to run query

While you are working, the dataflow is folding queries back to the server, and it does this every time you make a change.

It depends on how large the data is, but you can block that folding during development by adding Table.Buffer() around one of the statements. So if you have something like a Table.AddColumns(#"Some Step Name", "New Field", each [Value] + 1), change it to Table.Buffer(Table.AddColumns(#"Some Step Name", "New Field", each [Value] + 1))

Now it will hold the table in memory and do everything locally in the gateway or service.

When you are done and have everything working, remove Table.Buffer. The dataflow will send one query to the server to get the data for as much of your transformations as it can (what will fold) and will do the rest locally.

Note: If your table has millions of records, this won't work. It will just cause your memory to fill up. If that is the case, for the purposes of work, add a step early on to select the top 10,000 records or something. Enough to work with, but without holding a few GB of data in memory. Then do the Table.Buffer trick.

Then remove Table.Buffer and the top 10,000 records limiter step before the final save.

I think that is correct. Your thinking here is 100% on point, but during the development of the dataflow it is a load on the server. 

Another alternative - do you have a test or dev environment? You could develop the dataflow on that, then simply change the database/server names when done.

But even if you don't, once the dev work is finished, the dataflow will be the only impact on your SQL server, refreshing however many times a day it needs to. Everyone else will write reports from the dataflows.

One other thought. Not totally best practice, but will really reduce load on the servers. Just copy the tables straight to the dataflow, unaltered except maybe date filters so you don't pull in 20 years of data. Then write your transformation dataflows off of those. You'll import more data but that is less of a load on the servers vs continually writing transformations.

If you were good with M code, you could then take those transformations and apply to the actual SQL tables. Essentially you are creating your own DEV environment. But you have to know how to work in the advanced editor to move the M code around. On a scale of 1-10, I'd say you need to be a 5-7 in M code to do that effectively.

You are copying (importing) data, but Power Query creates SQL statements, called Query Folding, to the server. So it offloads the transformations to the server, then the server sends the results back. that is what you are importing. But during the development process, it is effectively DirectQuery. Query Folding is actually a byproduct of the DirectQuery process. 

Huge benefits. Say you do a big transformation to:

1. connect to a table or view
2. Filter for the last 3 years of data
3. remove all but 10 columns
4. group by 3 columns and summarize 7 of them.
5. add 2 more computed columns
6. Rename the columns
7. Change the data types.

Power Query will analyze all of those, and will figure out that steps 1-6 can be sent to the server. So it sends a single query to the server and the server returns a single table representing the result of those 6 transformations. Then PQ applies the data types (the server cannot do that) and step 7 breaks folding. Then it loads. If you do other steps, 8-15 for example, chance are most of those will be happening locally in the gateway. Not always. PQ might look at steps 8-10 and see those can be moved to after 6 in the query optimization plan, and send those to the server, then do the folding breaking step.

But you are developing, so when you do steps 1-6 above, those go to the server every single time. In fact, when you step 3, it sends 1-3 as one query. When you step 4, it redoes the query and sends 1-4. Then 1-5, etc. That is why the development process can be murder on an overworked server.