Uma breve história com melhores práticas do Power Query e por que Dataflow Gen 2 (Fluxo de dados Geração 2) é uma grande novidade ao separar o E, T e L para aumentar o poder de processamento e flexibilidade no seu processo de preparação de dados complexo.

Introdução

Power Query, que estreou como um complemento do Excel em 2013 sob o nome de “Data Explorer”, é uma ferramenta amigável para usuários de negócios realizarem tarefas relacionadas a dados no âmbito ETL (extrair, transformar, carregar). Tarefas que antes eram executadas manualmente ou com a ajuda de alguma macro VBA passaram a estar disponíveis com apenas alguns cliques do mouse de uma forma consistente e reprodutível como nunca antes. Quer dizer, se você esqueceu algo em um passo anterior, basta corrigi-lo e clicar em atualizar. Ou se quiser atualizar para os dados mais recentes, basta clicar em atualizar! Com isso, nunca foi tão fácil para o usuário comum de negócios que não é da área de TI obter dados limpos e atualizados em uma planilha.

Desde a incorporação do Power Query nos Analysis Services e no Power BI, novas fontes de dados foram adicionadas e as funcionalidades de transformação ficaram ainda mais completas. Tudo isso com uma interface ao usuário fácil e intuitiva (que agora está servindo de inspiração para o Data Wrangler para escrever código em Python) que escreve uma linguagem única e não tão fácil de dominar, M. Destaco a série de postagens do Ben Gribaudo, que se aprofunda mais do que qualquer um que conheço neste domínio.

Nem tudo são flores

Mas há um problema. Ele é bem adequado para fontes de dados dobráveis (query folding) e pequenas fontes de dados não dobráveis, porque utiliza conceitos como streaming, que funciona muito bem com fontes compatíveis com o query folding. Embora o streaming possa ser uma otimização, se sua fonte for grande, lenta, não dobrável ou sua transformação for complexa, você pode começar a ter problemas de desempenho.

Isso ocorre porque seu motor que processa o Power Query (mashup engine) processa dados em contêineres que são limitados tanto em quantidade quanto em tamanho, tornando-o bem adequado para pequenos dados ou transformações que podem ser enviadas para a fonte (também conhecido como query folding). Ele não possui uma área de preparação de alta velocidade integrada que permite isolamento da fonte de dados, o que significa que a mesma operação pode ser chamada mais de uma vez, dependendo da complexidade da sua transformação. Isso acontece porque, devido à limitação de disponibilidade de recursos do motor, as consultas carregadas são avaliadas independentemente umas das outras de uma maneira não muito bem orquestrada ou otimizada, da saída para a entrada, levando a que uma fonte/consulta intermediária comum seja provavelmente avaliada mais de uma vez.

Dataflow Gen 1 como um upgrade

É quando o Power Query Online entra em cena. Existente na Power Platform e no Power BI Service (agora Microsoft Fabric) sob o nome de Dataflow Gen1, ele tem basicamente o mesmo motor de avaliação do Power BI Desktop durante a edição, mas se você estiver usando uma capacidade Premium, o motor agora terá seu próprio local de armazenamento quando você habilitar o carregamento dessas tabelas intermediárias com a finalidade melhorar o desempenho (é quando aparece aquele ícone de relâmpago). Isso significa que sua fonte de dados lenta será acessada apenas o suficiente para materializar essa tabela intermediária, salvando-a em uma área de pouso/estágio persistente. Portanto, caso tenha uma transformação que precise mesclar fontes de dados e/ou possua uma lógica complexa, essas  tabelas intermediárias serão avaliadas apenas uma vez, permitindo que seu fluxo de dados (dataflow) seja atualizado mais rapidamente.

Ao usar o Dataflow Gen1, você agora está vinculado a um artefato item que possui armazenamento e processamento integrados. A parte ruim é que a implementação do Gen1 compartilha os dados de saída apenas por meio da permissão no nível de workspace, o que leva a problemas, como já falei anteriormente aqui. Ele também tem outras melhorias internas pelo motor de computação aprimorado, que é pouco documentado, e que oferece uma grande melhoria de desempenho na maioria dos casos, se você aproveitar bem a materialização de tebelas intermediárias na área de preparação.

Observe que a experiência de fazer alguma manutenção em um fluxo de dados complexo atualmente não se beneficia das melhorias de desempenho da materialização na área de preparo, o que significa que a atualização pode ser rápida, mas a edição do Power Query Online levará mais tempo do que o esperado para solucionar problemas ou alterar seu código.

Conheça o Dataflow Gen2

Como parte do Microsoft Fabric e suas diretrizes de separação de armazenamento e processamento, como falamos anteriormente aqui, o Dataflow Gen2 utiliza internamente outros itens do Fabric, como Lakehouse e Warehouse, para preparar e processar consultas intermediárias. Desta forma, você pode, implicitamente, aproveitar um motor realmente poderoso capaz de lidar adequadamente com uma grande quantidade de dados. Leia mais sobre a implementação interna aqui. Observe que atualmente esses itens são visíveis, mas em breve serão ocultados para o usuário, pois se destinam apenas ao uso interno da implementação do Dataflow Gen 2.

Gen2 permite configurar o destino final para cada tabela separadamente

Ele também vem com o poderoso recurso de selecionar o local de destino para outros itens de armazenamento, como LakeHouse ou Warehouse. Isso é essencial para tratar o Dataflow Gen2 como um motor capaz apenas de extrair E ou de apenas transformar T de maneira separada ao carrgar os dados para outros itens específicos de armazenamento do Fabric. Como discutimos anteriormente aqui, você pode aproveitar as habilidades multidisciplinares de uma equipe e preferências usando soluções de Data Pipelines que orquestram um trabalho que extrai dados usando notebooks e transforma usando DF-gen2 ou vice-versa. Essa flexibilidade certamente reduz o tempo até que o time consiga entregar valor ao negócio, que em minha perspectiva é o principal recurso que torna o Fabric irresistível.

Mesmo se você planeja fazer seu ETL usando apenas Dataflows, pode aproveitar a orquestração de Data Pipelines ao selecionar como destino itens de armazenamento como Lakehouse para cada consulta, desta forma é possível construir dataflows menores menores e mais especializados, superando alguns dos problemas de tortura  manutenção que discutimos anteriormente. Então, você pode pensar que isso levará a ter ainda mais itens em workspace: a resposta é SIM. Mas já temos uma experiência aprimorada de filtro de itens no workspace e esperamos que entreguem as pastas no espaço de trabalho em um futuro próximo!

Novas opções para atualização de dados oferecem flexibilidade

Junto com o recurso de seleção de um destino personalizado, você também tem a opção de acrescentar (append) dados a ele. Isso significa que você pode ler os dados em seu destino atual, usá-los para consultar sua fonte externa em busca de dados adicionais e, em seguida, decidir quais dados você deve (ou não) acrescentar ao seu destino. De certa forma, você pode ver um padrão de Atualização Incremental aqui.

Ok, mas quais são as outras diferenças entre Dataflow Gen1 e Gen2?

Você pode ver uma comparação entre Dataflow Gen1 e Gen2 aqui. Tenha em mente que o Dataflow Gen 2 está atualmente em pré-visualização pública e as coisas estão mudando (para melhor) em um ritmo realmente acelerado. O link também cita outros novos recursos, como salvar um rascunho, histórico de atualização aprimorado e recursos de monitoramento que resolvem algumas limitações do Gen1.

Gen2 é atualmente o único lugar onde você pode importar arquivos PQT

Sim, você pode simplesmente ctrl+c ctrl+v dados de/para qualquer versão do Power Query que você tenha, mas se você tiver um conjunto muito grande e complexo de consultas, isso não funcionará. Sua melhor alternativa é extrair um arquivo PQT, com a vantagem de que você pode arquivá-lo caso precise dele no futuro.

Significados diferentes para habilitar carga (ou habilitar o preparo) e definir um novo destino

Em uma instância local do Power Query ou Dataflow Gen1 não premium, é aconselhável carregar apenas consultas que você precisará mais tarde. Seja para não carregar tabelas temporárias desnecessárias em seu modelo de dados ou para compartilhar via Dataflow como uma fonte de dados com apenas as tabelas de saída finais.

Para melhorar o desempenho, você pode realmente colocar algumas funções Binary.Buffer, Table.Buffer ou List.Buffer ou a mais moderna Table.StopFolding naquele ponto específico. Eu ainda não encontrei uma diretriz universal para buffering, pois é desafiador criar um padrão que sempre funcione. É mais uma questão de tentativa e erro, e se você colocar o Buffer no lugar errado, pode até piorar seu desempenho.

Algumas regras não verificadas que tenho em mente são:
• Nunca force salvar em memória (buffer) tabelas como último passo de uma consulta
• Salve em memória (buffer) ambas as tabelas que você inclui em uma mesclagem (de preferência reduzindo as linhas e colunas para apenas aquelas que são estritamente necessárias antes da mesclagem)
• Salve em memória (buffer) quando você precisa de alguma lógica de loop complexa usando List.Generate ou List.Accumulate.

Cuidado com a ordenação de suas tabelas salvas na área de preparo

Por padrão, os motores de cálculo do Fabric implementam uma otimização de ordem de classificação chamada VORDER (também conhecida como VertiPaq Order) sempre que você grava dados, seja na área de preparo ou no destino final. Esse pode ser um tópico para outro post do blog, mas esteja ciente de que a Experiência Online do Power Query existente no Dataflow Gen2 usa praticamente o mesmo motor de mashup das outras instâncias e não aproveita os dados salvos na área de preparo. Quando a atualização real acontece, ela usa os dados salvos na área de preparo que podem ter uma ordem de classificação diferente.

Se suas transformações dependem de referenciar alguma tabela salva na área de preparo anteriormente e adicionar alguma coluna de índice para uso posterior como âncora, você pode obter alguns resultados inesperados. Quero dizer, o que você vê no modo de edição do Power Query Online pode não ser o que é realmente armazenado após a atualização.

As melhores práticas ao trabalhar com Dataflow mudaram desde o Gen1 premium

Quando você está editando um Dataflow Gen1 com recursos premium, a boa prática é habilitar a carga em praticamente todas as consultas internas. Seja porque sua fonte é lenta/não compatível com a Dobra de Consulta (ou seja, o motor de origem fará todo o trabalho de transformação) e você não quer que o streaming acerte várias vezes a fonte, ou pelo fato de necesitar mesclar dados oriundos de duas consultas com diferentes níveis de privacidade, que ativa a proteção do Formula.Firewall que te previne ter dados vazados não intencionalmente, porém piora bastante a performance. Pode ver mais detalhes aqui.

Dataflow Gen2 “habilitar o preparo” muda essa mentalidade

Agora, com o Dataflow Gen2 e a capacidade de definir um destino para cada tabela separadamente, o significado de habilitar carga mudou. Na verdade, foi renomeado de “habilitar carga” para “habilitar o preparo”. Por quê? Porque com o Gen2, temos a nova opção de destino de saída que muda como as coisas são armazenadas e processadas.

Quando você habilita o preparo, está permitindo que o motor escreva a saída diretamente no Lakehouse ou Warehouse de preparo (staging da figura abaixo) antes que esses dados sejam copiados para seu destino de saída (se configurado). Esses itens de preparo (staging) são compartilhados entre todos os Dataflow Gen2 no mesmo workspace e eventualmente serão ocultados, pois são parte da implementação interna do Dataflow Gen2.

 

O destino da saída pode ser o próprio Dataflow Gen2 (para isso, basta habilitar o preparo), mas agora você provavelmente vai querer definir um destino separado. Isso ocorre porque o compartilhamento de Dataflow ainda está no nível do workspace e, ao gravá-lo diretamente em um LakeHouse ou Warehouse, você tem um controle de permissão mais granular como falei anteriormente aqui.

Se você habilitar o preparo e tiver um destino de saída, o tempo de atualização será maior porque será escrito em dois lugares em vez de um. Nos itens com preparo habilitado e no destino de saída. Portanto, por favor, não habilite o preparo e defina o destino de saída ao mesmo tempo, pois você está apenas fazendo o trabalho duas vezes desnecessariamente.

Nunca habilite o preparo no Dataflow Gen2 a menos que tenha um motivo para isso

Então, como regra geral, ao lidar com um Dataflow Gen2, você não deve habilitar o preparo a menos que precise se beneficiar dela. Você pode ter um dataflow sem que nenhuma consulta tenha o preparo habilitado desde que defina o destino de saída para pelo menos uma consulta.

Razões válidas para habilitar o preparo em suas consultas de extração ou intermediárias em vez de carregar dados diretamente para um destino são:
• Quando sua fonte de dados tem um grande volume de dados, não compatível com o Query Folding (ou seja, o motor de origem fará todo o trabalho de transformação) e você precisa fazer algumas transformações sobre ele
• Se você está realizando transformações intensivas de recursos computacionais/memória, como mesclar ou agrupar por que envolvam tabelas de grande volume de dados
• Se você precisa juntar dados de diferentes fontes de dados, especialmente se elas têm diferentes níveis de privacidade

Cuidado se sua fonte de dados requer o uso de um Gateway

O uso de um gateway de dados atua como uma ponte entre suas fontes de dados locais e o serviço Power BI (Fabric). As transformações e processamentos de dados ocorrem no nível do gateway na máquina em que está instalado.

Sempre que você usa um Gateway para acessar uma fonte de dados, está dizendo ao motor:

“Use este gateway para executar todo o dataflow para acessar, transportar e transformar os dados desta fonte”

Isso significa que, se você habilitar a carga e tiver um destino de saída definido, o motor realmente usa o gateway para escrever nos itens de preparo (staging), depois os dados dos itens salvos no ambiente de preparo são enviados de volta ao gateway e, mais tarde, o gateway escreve no destino de saída.

Portanto, especialmente se sua fonte de dados requer um gateway, evite habilitar o preparo a todo custo. Se você realmente precisa que esses dados sejam mesclados com outras fontes, recomendo que tenha dataflows separados. Um para extrair dados via gateway para um LakeHouse ou Warehouse específico e, em seguida, ter outro Dataflow para mesclar esses dados e fazer a lógica complexa que você precisa. Essa orquestração pode ser gerenciada usando Data Pipelines.

 

Resumindo

Em conclusão, o Dataflow Gen2 oferece um sistema intrincado e poderoso que permite a separação de armazenamento e motor de cálculo, opções aprimoradas de preparo, configurações aprimoradas de destino de saída que permitem um controle de permissão mais granular. No entanto, a transição para o Dataflow Gen2 também introduz um novo conjunto de melhores práticas e considerações, incluindo a gestão das tabelas carregadas na área de preparo e de como lidar com conexões que necessitem de gateway. Ambos controlam o uso correto das configurações de carregamento no preparo e/ou destino final. Os usuários devem reconhecer essas nuances para realmente aproveitar o poder desta ferramenta. No geral, a jornada do Power Query para o Dataflow Gen2 enfatiza o compromisso da Microsoft com a inovação contínua e oferece aos usuários opções mais robustas para gerenciar processos complexos de preparação e transformação de dados.

Gostaria de agradecer ao Jeroen Luitwieler por revisar este post!