Tabelas de Catálogo no Redshift: Aprimorando a Governança do seu Data Warehouse.

Estratégias práticas para melhorar a eficiência e controle de dados.

Com a evolução do nosso projeto DBT em 2023, reconhecemos a necessidade essencial de aprimorar a governança do nosso Data Warehouse. Neste artigo, pretendo explorar o papel das tabelas de catálogo de sistema no Redshift. Essas tabelas são fundamentais para o controle efetivo do ambiente, desempenhando um papel vital na manutenção e governança dos dados que alimentam nosso sistema.

Ao dividir nossa abordagem em dois tópicos, observamos que algumas tabelas de sistema são totalmente acessíveis, sem apresentar tipos ou funções incompatíveis. Contudo, para aquelas incompatíveis, nossa equipe desenvolveu algumas procedures específicas. Essas procedures possibilitam o congelamento diário dos dados fonte, estabelecendo uma base sólida para a geração de acompanhamentos e proporcionando um controle mais eficaz sobre as informações críticas para o nosso projeto.

Ao considerar a importância dessas tabelas de catálogo no contexto do Redshift, fica claro como sua implementação não apenas facilita a manutenção do ambiente, mas também fortalece a governança de dados, garantindo a consistência e confiabilidade essenciais para o sucesso contínuo do nosso Data Warehouse. Ao oferecerem uma visão abrangente e estruturada dos metadados e objetos no banco de dados, essas tabelas funcionam como um diretório central, proporcionando informações essenciais sobre schemas, tabelas, views e funções.

>> Tabelas Compatíveis

As tabelas destacadas a seguir operam perfeitamente com funções SQL convencionais, como CREATE TABLE e INSERT INTO, permitindo sua utilização convencional no DBT. Assim, incorporamos essas tabelas de maneira convencional no DBT, utilizando-as como fonte de dados source e materializando-as como table, refletindo assim a situação atual do ambiente de forma consistente.

Os exemplos a seguir serão apresentados no formato padrão, incluindo o nome da tabela, uma breve descrição, as colunas utilizadas pelo time, link Github com o código do source DBT e o link para a documentação oficial da AWS.

pg_class_info

Uma tabela de sistema do Redshift que oferece a data de criação das tabelas no cluster. Essencial para monitorar tabelas antigas no ambiente de homologação que poderiam ser descartadas.

• reloid: ID da tabela.
• relname: Nome da tabela.
• relnamespace: ID do schema.
• relcreationtime: Data e hora da criação da tabela.

Link Github — DBT: pg_catalog_pg_class_info_source

Link AWS: PG_CLASS_INFO

pg_namespace

Uma tabela de sistema do Redshift que disponibiliza o ID e o nome dos schemas presentes no cluster. Essencial para a correlação de dados com a pg_class_info.

• oid: ID do schema.
• nspname: Nome do schema.

Link Github — DBT: pg_catalog_pg_namespace_source

Link AWS: PG_NAMESPACE

svv_datashare_objects

Uma tabela de sistema do Redshift que fornece informações sobre datashares, incluindo seus respectivos schemas e objetos. No ecossistema Afya, mantemos uma conta AWS por unidade de negócio, tornando a gestão do datashare fundamental para os processos rotineiros da empresa.

• share_type: Tipo do datashare, se é OUTBOUND ou INBOUND.
• share_name: Nome do datashare.
• object_type: O tipo do objeto especificado como schemas, tabelas e views.
• object_name: Nome do objeto com o schema, por exemplo — schema.view, schema.table.
• producer_account: O ID da conta do produtor que compartilhou os dados.
• producer_namespace: O identificador exclusivo do cluster do produtor de compartilhamento de dados.
• include_new: A propriedade que especifica se qualquer tabela criada futuramente, view ou UDFs no schema especificado devem ser adicionadas ao datashare. Este parâmetro só é relevante para datashares OUTBOUND e apenas para tipos de schema no datashare.

Link Github — DBT: pg_catalog_svv_datashare_objects_source

Link AWS: SVV_DATASHARE_OBJECTS

svv_table_info

Uma tabela de sistema do Redshift que fornece informações detalhadas sobre as tabelas presentes no cluster. Utilizamos principalmente para monitorar o armazenamento do ambiente, controlar o tamanho das tabelas e sua distribuição.

• database: Nome do banco de dados.
• schema: Nome do schema.
• table_id: ID da tabela.
• table: Nome da tabela.
• encoded: Valor que indica se alguma coluna possui compressão de codificação definida.
• diststyle: Estilo de distribuição ou coluna de chave de distribuição, se a distribuição chave estiver definida.
• sortkey1: Primeira coluna na chave de classificação, se a chave de classificação estiver definida.
• max_varchar: Tamanho da maior coluna que usa o tipo de dados VARCHAR.
• sortkey1_enc: Codificação de compressão da primeira coluna na chave de classificação, se a chave de classificação estiver definida.
• sortkey_num: Número de colunas definidas como chaves de classificação.
• size: Tamanho da tabela, em blocos de dados de 1 MB.
• pct_used: Porcentagem do espaço disponível que é usada pela tabela.
• unsorted: Porcentagem de linhas não classificadas na tabela.
• stats_off: Número que indica o quão desatualizadas estão as estatísticas da tabela — 0 é atual, 100 está desatualizado.
• tbl_rows: Número total de linhas na tabela. Este valor inclui linhas marcadas para exclusão, mas ainda não limpas.
• skew_sortkey1: Razão entre o tamanho da maior coluna não chave de classificação e o tamanho da primeira coluna da chave de classificação, se a chave de classificação estiver definida. Use este valor para avaliar a eficácia da chave de classificação.
• skew_rows: Razão entre o número de linhas na fatia com mais linhas e o número de linhas na fatia com menos linhas.
• estimated_visible_rows: As linhas estimadas na tabela. Este valor não inclui linhas marcadas para exclusão.
• vacuum_sort_benefit: O máximo estimado de melhoria percentual de desempenho de consulta de varredura quando você executa vacuum sort.

Link Github — DBT: pg_catalog_svv_table_info_source

Link AWS: SVV_TABLE_INFO

>> Tabelas Incompatíveis

As tabelas destacadas a seguir não operam com funções SQL convencionais, como CREATE TABLE e INSERT INTO, por apresentarem tipos ou funções incompatíveis. Assim, criamos procedures que possibilitam o congelamento diário dos dados fonte e rodamos elas via pre_hook de um modelo source convencional no DBT.

Os exemplos a seguir serão apresentados no formato padrão, incluindo o nome da tabela, uma breve descrição, as colunas utilizadas pelo time, link Github com o DDL da tabela, link Github com o código da procedure e do source DBT e o link para a documentação oficial da AWS.

OBS: É importante usar o DDL para criar a tabela no Redshift antes de executar a procedure pela primeira vez.

pg_tables

Uma tabela de sistema que armazena informações sobre as tabelas no banco de dados. Utilizamos para rastrear o usuário que atua como proprietário da tabela, permitindo o controle de que os dados de produção estejam associados a um usuário padrão, em vez de um membro específico da equipe.

• schemaname: Nome do schema contendo a tabela.
• tablename: Nome da tabela.
• tableowner: Nome do proprietário da tabela.

Link Github — DDL: tb_pg_tables

Link Github — Procedure: update_tb_pg_tables

Link Github — DBT: admin_tb_pg_tables_source

Link AWS: PG_TABLES

pg_views

Uma tabela do sistema que armazena informações sobre as views no banco de dados. Utilizamos para rastrear o usuário que atua como proprietário da view, permitindo o controle de que os dados de produção estejam associados a um usuário padrão, em vez de um membro específico da equipe.

• schemaname: Nome do schema contendo a view.
• viewname: Nome da view.
• viewowner: Nome do proprietário da view.

Link Github — DDL: tb_pg_views

Link Github — Procedure: update_tb_pg_views

Link Github — DBT: admin_tb_pg_views_source

Link AWS: PG_VIEWS

svv_redshift_columns

Uma tabela de sistema que lista todas as colunas disponíveis no banco de dados. Utilizamos essa fonte para controlar o encoding das colunas, otimizando a compressão de dados por meio do Vacuum, e para monitorar a configuração de distkey e sortkey para garantir uma distribuição eficiente. Para uma compreensão mais aprofundada, recomendo a leitura do artigo “Melhores práticas com Amazon Redshift: Arquitetura, organização e otimização de performance”.

Além disso, é crucial que todas as tabelas alimentadas pelo DBT estejam documentadas, e esse acompanhamento é facilitado pela coluna “remarks”. Ao preencher o schema.yml do projeto, as tabelas do Redshift são automaticamente comentadas, sendo necessário habilitar o campo persist_docs no dbt_project.yml do seu projeto para isso. Mais detalhes sobre esse campo podem ser encontrados no artigo “Guia prático do DBT: Organizando as pastas do projeto”.

• database_name: O nome do banco de dados onde a tabela contendo as colunas existe.
• schema_name: O nome do schema da tabela.
• table_name: O nome da tabela.
• column_name: O nome de uma coluna.
• ordinal_position: A posição da coluna na tabela.
• data_type: O tipo de dados da coluna.
• is_nullable: Um valor que define se uma coluna é anulável.
• encoding: O tipo de codificação da coluna.
• distkey: Um valor que é verdadeiro se esta coluna é a chave de distribuição para a tabela e falso caso contrário.
• sortkey: Um valor que especifica a ordem da coluna na chave de classificação.
• remarks: Documentação da coluna.

Link Github — DDL: tb_svv_redshift_columns

Link Github — Procedure: update_tb_svv_redshift_columns

Link Github — DBT: admin_tb_svv_redshift_columns_source

Link AWS: SVV_REDSHIFT_COLUMNS

svv_redshift_tables

Uma tabela de sistema que lista todas as tabelas disponíveis no banco de dados. Uma tabela de sistema que lista todas as tabelas disponíveis no banco de dados. Utilizamos ela tanto para catalogar o tipo, se é view ou tabela, quanto para verificar se está documentada no campo “remarks”, conforme mencionado no tópico anterior.

• database_name: O nome do banco de dados onde a tabela especificada existe.
• schema_name: O nome do schema para a tabela.
• table_name: O nome da tabela.
• table_type: O tipo de tabela. Os valores possíveis são views e tabelas.
• remarks: Documentação da tabela.

Link Github — DDL: tb_svv_redshift_tables

Link Github — Procedure: update_tb_svv_redshift_tables

Link Github — DBT: admin_tb_svv_redshift_tables_source

Link AWS: SVV_REDSHIFT_TABLES

>> Modelo catálogo

Vou adicionar ao repositório GitHub, na pasta deste projeto, um exemplo de modelagem DBT que integra os dados de todas as tabelas mencionadas acima, visando desenvolver uma catalogação e controle efetivos do ambiente Redshift.

Link Github — DBT: dc_data_catalog

Desenvolver um processo de monitoramento para o seu ambiente é crucial para garantir a efetiva governança dos dados armazenados. À medida que o ambiente se expande sem um controle adequado, torna-se mais oneroso para a equipe lidar com a falta de governança em dados legados. Portanto, é vital manter um controle ativo para lidar com as divergências ao longo do percurso.

Ao iniciar o trabalho com as tabelas catálogo padrões do Redshift, enfrentamos um desafio significativo com as tabelas incompatíveis com funções SQL convencionais. Não encontramos documentação ou suporte que nos orientassem nessa tarefa.

Embora tenhamos recebido a recomendação de migrar para tabelas compatíveis, algumas informações específicas exigiam sua utilização. Com esforços conjuntos da minha equipe, desenvolvemos a lógica por meio de uma procedure que atendeu às nossas necessidades. Assim, espero que essa solução possa ser útil para outras equipes, evitando que enfrentem o mesmo problema.

Caso tenha sugestões ou comentários sobre o conteúdo, por favor, não hesite em entrar em contato comigo pelo Linkedin.

English version: https://medium.com/@alice_thomaz/ee03daf5bcad

Projeto Github: Project 1: Redshift Catalog