Dicionário de Dados — Data Lake (BI)

Referência técnica das tabelas disponibilizadas no BigQuery para clientes com licença de BI.

1. Visão Geral da Arquitetura

O Data Lake segue uma estrutura de 3 camadas:

2. Tabela: events_{company}

Tabela principal de fatos. Contém todos os eventos brutos capturados pelo agente desktop nas máquinas dos colaboradores. Cada registro = uma janela (aplicativo) em foco por um período.

Particionamento: por coluna, em p2o_datetime_in (granularidade diária). Como o particionamento é por coluna, não existe a pseudo-coluna _PARTITIONDATE nesta tabela — filtre pela própria p2o_datetime_in.

Volume típico: milhares a milhões de registros/dia dependendo do número de colaboradores

Atualização: conforme licença contratada (diária ou intra-dia)

3. Tabela: rel_department_hierarchy_{company}

Hierarquia de departamentos. Define a estrutura organizacional em níveis (1 = topo, N = mais específico).

4. Tabela: rel_role_attribute_{company}

Cadastro de cargos/funções. Tabela dimensional de referência para cargos dos colaboradores.

5. Tabela: rel_region_attribute_{company}

Cadastro de regiões/localidades. Dimensão geográfica dos colaboradores.

6. Tabela: rel_hierarchy_employee_associative_{company}

Vínculo colaborador × hierarquia organizacional. Tabela associativa que conecta cada colaborador ao seu departamento, cargo, região e processo. Contém também dados de jornada e férias.

7. Tabela: rel_hierarchy_domain_associative_{company}

Classificação de domínios/aplicativos por contexto organizacional. Define se um domínio/app é produtivo, neutro ou improdutivo para cada combinação de departamento/cargo/região.

8. Tabela: time_extension_request_{company}

Solicitações de extensão de jornada. Registra pedidos de hora extra feitos pelos colaboradores e sua aprovação/rejeição por gestores.

9. Tabela: time_tracking_{company}

Registros de ponto/jornada. Marcações de início e fim de jornada, incluindo intervalos de almoço.

10. Tabela: dim_employee_daily_{company}

Dimensão de colaborador por dia. Tabela desnormalizada com a "foto" organizacional de cada colaborador em cada data. Ideal para análises históricas — mostra em qual departamento/cargo/região o colaborador estava em cada dia.

11. Tabelas de Suporte

11.1 network_raw_latest

SSIDs de redes corporativas cadastradas. Usada para determinar se o colaborador está no escritório ou remoto.

11.2 text_clearing_rules

Regras de limpeza e anonimização aplicadas aos títulos de janela antes da análise.

11.3 app_classificationV2

Regras de classificação de aplicativos e sites. Define nome, classe e prioridade para matching via regex.

12. UDFs (User Defined Functions)

Funções JavaScript disponíveis no BigQuery para processamento de dados:

13. Diagrama de Relacionamentos (ER)

erDiagram
          events ||--o{ dim_employee_daily : "p2o_username = employee AND DATE(p2o_datetime_in) = p2o_date"
          events ||--o{ rel_hierarchy_employee_associative : "p2o_username = employee"
          rel_hierarchy_employee_associative }o--|| rel_department_hierarchy : "fk_department_code = department_code"
          rel_hierarchy_employee_associative }o--|| rel_role_attribute : "fk_role_code = role_code"
          rel_hierarchy_employee_associative }o--|| rel_region_attribute : "fk_region_code = region_code"
          rel_hierarchy_domain_associative }o--|| rel_department_hierarchy : "fk_department_code = department_code"
          time_tracking ||--o{ events : "username = p2o_username AND action_date = DATE(p2o_datetime_in)"
          time_extension_request ||--o{ events : "username = p2o_username"
      
          events {
              STRING bigquery_id PK
              STRING p2o_username
              TIMESTAMP p2o_datetime_in
              FLOAT64 p2o_total_seconds
              STRING p2o_app_exe
              STRING p2o_url_domain
          }
      
          dim_employee_daily {
              STRING employee PK
              DATE p2o_date PK
              STRING department_name
              STRING role_name
              STRING Region
          }
      
          rel_department_hierarchy {
              STRING department_code PK
              STRING department_name
              INT64 department_level
          }
      
          rel_role_attribute {
              STRING role_code PK
              STRING role_name
          }
      
          rel_region_attribute {
              STRING region_code PK
              STRING region_name
          }
      
          rel_hierarchy_employee_associative {
              STRING employee PK
              STRING fk_department_code FK
              STRING fk_role_code FK
              STRING fk_region_code FK
          }
      
          rel_hierarchy_domain_associative {
              STRING domain PK
              STRING fk_department_code FK
              STRING waterfall_classification
          }
      
          time_tracking {
              STRING action_id PK
              STRING username
              TIMESTAMP beginDay
              TIMESTAMP endDay
          }
      
          time_extension_request {
              STRING username
              DATE request_date
              BOOLEAN accepted
          }

14. Padrões de Consulta Recomendados

JOIN eventos + dimensão colaborador (histórico correto)

SELECT
        e.p2o_username,
        d.employee_alias,
        d.department_name,
        d.role_name,
        d.Region,
        SUM(e.p2o_total_seconds) AS total_seconds
      FROM `fnk-bi-empresa.empresa.events_empresa` e
      JOIN `fnk-bi-empresa.empresa.dim_employee_daily_empresa` d
        ON LOWER(e.p2o_username) = d.employee
        AND DATE(e.p2o_datetime_in) = d.p2o_date
      WHERE DATE(e.p2o_datetime_in) BETWEEN '2026-01-01' AND '2026-01-31'
      GROUP BY 1, 2, 3, 4, 5

Filtro de qualidade recomendado para events

WHERE
        LOWER(p2o_username) != 'fhinck'
        AND p2o_total_seconds >= p2o_total_idle
        AND p2o_total_seconds > 0
        AND (p2o_source IS NULL OR UPPER(p2o_source) != 'CORRUPTED')
        AND (odd_behavior IS NULL
          OR (odd_behavior NOT LIKE '%Invalid total_seconds%'
              AND odd_behavior NOT LIKE '%Unexpected Time Change%'
              AND odd_behavior NOT LIKE '%Corrupted File%'))

15. Detalhes Técnicos do Pipeline

Informações extraídas do pipeline de atualizacao diaria da Fhinck que popula as tabelas diariamente às 3h. Clustering verificado em produção (fnk-bi-{cliente}).

15.1 Particionamento e Clustering

15.2 Estratégia de Deduplicação

Todas as tabelas dimensionais (rel_*, dim_*) usam ROW_NUMBER() para manter apenas o registro mais recente por chave primária:

-- Padrão aplicado em TODAS as tabelas rel_*
      SELECT *
      FROM (
        SELECT *,
          ROW_NUMBER() OVER (
            PARTITION BY {primary_key}
            ORDER BY creation_date DESC
          ) AS rn
        FROM source_table
      )
      WHERE rn = 1

15.4 Frequência e Lógica de Atualização

• CRON diário às 3h (horário de Brasília, UTC-3)
• Eventos: carga incremental — apenas registros com p2o_datetime_last_insert >= último timestamp processado
• Tabelas dimensionais: recriadas por completo (CREATE OR REPLACE TABLE) a cada execução
• Tabelas de suporte: sincronizadas por completo do dataset DATA_SUPPORT
• Janela padrão de eventos: últimos 90 dias (configurável por cliente)