UNO Data Lake

Este repositório contém os modelos de dados utilizados no Uno Data Lake, um sistema para gestão e armazenamento de dados de franquias e seus negócios relacionados. Esta documentação explica a estrutura de cada modelo e seus campos.

Bem-vindo(a) à documentação do UNO Data Lake. Este ambiente concentra os dados operacionais das franqueadoras e suas franquias/empresas em um cluster Amazon Redshift, permitindo a construção de dashboards, relatórios e análises de BI sobre uma base unificada.

Esta documentação descreve o que cada tabela representa, quais colunas existem, como elas se relacionam e como consultá-las corretamente. Foi escrita para um público misto: analistas de BI (com SQL básico) e engenheiros de dados.

Índice

• Visão geral
• Convenções importantes
  • Multi-tenant: a coluna schema e o franchisor_id
  • Regra de ouro dos JOINs
  • Chaves técnicas vs. chaves de negócio
  • Distribuição e ordenação no Redshift (DISTKEY / SORTKEY)
  • Datas, fuso horário e tipos
  • Atualização incremental (metadata)
• Mapa de relacionamentos
• Catálogo de tabelas
• Tabelas — Estrutura organizacional
  • franchises
  • companies
  • employees
  • customers
• Tabelas — CRM e funil comercial
  • deals
  • budgets
• Tabelas — Vendas (orders e itens)
  • orders
  • order_services
  • order_products
  • order_plans
  • order_courses
  • order_lessons
• Tabelas — Financeiro
  • payments
  • accounts_payable
• Tabelas — Catálogo (produtos, serviços, planos, cursos)
  • services
  • products
  • plans
  • courses
  • classes
• Tabelas — Operação clínica/atendimento
  • appointments
  • rooms
  • room_time_slots
  • measures
  • slimming_goals
• Tabelas — Metas e desempenho
  • goals
  • employee_goals
• Tabelas — Royalties e taxas da franqueadora
  • franchise_royalties
  • franchise_royalty_periods
  • franchise_advertising_fees
  • franchise_advertising_fee_periods
  • franchise_categories
  • franchise_category_types
• Tabelas — Marketing (Facebook Ads)
  • facebook_accounts
  • facebook_campaign_data
• Tabelas — Sistema
  • metadata
• Enums e dicionários de valores
• Exemplos de queries para BI
• Boas práticas de performance no Redshift

Visão geral

O Data Lake recebe réplicas dos dados transacionais das aplicações operacionais (Vendas, CRM, Atendimento, Financeiro, Marketing) e os organiza em tabelas analíticas amplas, denormalizadas (com nomes já materializados em colunas *_name) para facilitar a leitura por ferramentas de BI.

Características principais:

• Banco: Amazon Redshift.
• Modelo: estrela "achatada" — cada tabela já contém o nome (denormalizado) das principais entidades relacionadas, evitando a maior parte dos JOINs em consultas típicas.
• Multi-tenant: todas as tabelas operacionais possuem a coluna schema que isola os dados de cada franquia/empresa. Toda consulta deve filtrar por schema.
• Histórico: as tabelas refletem o estado atual da operação. Para análises temporais, use as colunas de data (created_at, updated_at, due_date, liquidation_date, etc.).

Convenções importantes

Multi-tenant: a coluna schema e o franchisor_id

Quase todas as tabelas possuem duas colunas críticas:

Por que isso importa? Os IDs de negócio (como order_id, customer_id, appointment_id) são gerados por franquia — ou seja, não são globalmente únicos. Duas franquias diferentes podem ter um order_id = 1234 apontando para vendas completamente distintas.

Regra de ouro dos JOINs

Toda relação entre tabelas deve ser feita pela combinação schema + <id_de_negócio> — nunca apenas pelo ID.

Exemplos corretos:

-- ✅ CORRETO: JOIN por schema + order_id
SELECT *
FROM orders o
JOIN payments p
  ON p.schema   = o.schema
 AND p.order_id = o.order_id
WHERE o.schema = 'minha_franquia';

-- ❌ INCORRETO: JOIN apenas por order_id
SELECT *
FROM orders o
JOIN payments p ON p.order_id = o.order_id;  -- vai cruzar dados de franquias diferentes!

Para análises a nível de franqueadora (várias franquias da mesma rede), filtre por franchisor_id e mantenha o schema em todos os JOINs:

SELECT *
FROM orders o
JOIN payments p
  ON p.schema   = o.schema
 AND p.order_id = o.order_id
WHERE o.franchisor_id = '42';

Chaves técnicas vs. chaves de negócio

Cada tabela tem uma coluna id (auto-incremento) usada apenas como chave primária técnica do Redshift. Ela não tem significado de negócio e pode ser ignorada em consultas analíticas.

Use sempre as colunas com sufixo _id (order_id, customer_id, appointment_id, …) — são essas que vêm dos sistemas operacionais e que devem ser usadas em filtros e JOINs (sempre combinadas com schema).

Distribuição e ordenação no Redshift (DISTKEY / SORTKEY)

Para entender por que filtrar por schema é tão importante, vale conhecer como o Redshift organiza as tabelas:

Implicação prática: consultas que não filtram por schema forçam o cluster a varrer dados em todos os nós, ficando muito mais lentas e caras.

Datas, fuso horário e tipos

• Todas as colunas de data/hora usam TIMESTAMP WITH TIME ZONE (UTC). Converta para o fuso local da empresa (a coluna companies.timezone indica o fuso, ex.: America/Sao_Paulo) quando for exibir para o usuário final.
• Valores monetários estão em DOUBLE PRECISION (mapeados como FLOAT no ORM). Sempre use ROUND(...) ao exibir.
• A moeda padrão de cada empresa está em companies.currency.

Atualização incremental (metadata)

A tabela metadata registra o último updated_at processado por tabela e por schema. Use-a se precisar saber até quando o Data Lake está atualizado para uma franquia específica.

Mapa de relacionamentos

                    franchises (1) ──< companies (N)
                         │
                         └──< franchise_royalties ──< franchise_royalty_periods
                         └──< franchise_advertising_fees ──< franchise_advertising_fee_periods
                         └──< franchise_category_types >── franchise_categories

  customers ──┐
              ├──< deals
              ├──< budgets ──> orders
              ├──< orders ──< order_services ──> services
              │                ──< order_products ──> products
              │                ──< order_plans    ──> plans
              │                ──< order_courses  ──> courses ──< classes
              │                ──< order_lessons
              │                ──< payments
              ├──< appointments ──> rooms ──< room_time_slots
              ├──< measures
              └──< slimming_goals

  employees ──< employee_goals >── goals
  accounts_payable (financeiro – contas a pagar)
  facebook_accounts ──< facebook_campaign_data

Todos os JOINs implícitos acima usam schema + <id>.

Catálogo de tabelas

Tabelas — Estrutura organizacional

franchises

Franquias (unidades operacionais) cadastradas.

companies

Empresas (CNPJs) operadas pelas franquias. Em geral existe uma company por schema.

employees

Funcionários das empresas (vendedores, atendentes, profissionais, etc.).

customers

Clientes finais (PF e PJ).

Tabelas — CRM e funil comercial

deals

Negociações/oportunidades do funil comercial. Um deal pode (ou não) ser convertido em uma order.

Para taxa de conversão: deals com converted_at IS NOT NULL ou com order_id preenchido.

budgets

Orçamentos elaborados para clientes.

Tabelas — Vendas (orders e itens)

orders

Cabeçalho da venda. Os itens estão nas tabelas order_services, order_products, order_plans, order_courses.

order_services

Itens de serviço de uma venda.

order_products

Itens de produto de uma venda.

order_plans

Planos contratados em uma venda.

order_courses

Cursos contratados em uma venda.

order_lessons

Aulas individuais associadas a order_courses (controle de presença).

Tabelas — Financeiro

payments

Recebíveis (parcelas a receber) gerados pelas vendas.

accounts_payable

Contas a pagar da empresa (despesas, fornecedores, etc.).

Tabelas — Catálogo (produtos, serviços, planos, cursos)

Todas as tabelas de catálogo seguem o mesmo padrão: *_id, schema, franchisor_id, franchise_identifier (id no escopo da franquia), name, price, active, created_at, updated_at.

services

Serviços oferecidos pela franquia.

products

Produtos comercializados. Sem colunas adicionais além do padrão.

plans

Planos comercializados. Sem colunas adicionais além do padrão.

courses

Cursos oferecidos. Sem colunas adicionais além do padrão.

classes

Turmas de cursos.

Tabelas — Operação clínica/atendimento

appointments

Agendamentos de serviços para clientes.

rooms

Salas de atendimento.

room_time_slots

Janelas de funcionamento das salas (por dia da semana).

measures

Medidas corporais coletadas (típico de redes de estética/saúde).

slimming_goals

Metas de emagrecimento por cliente.

Tabelas — Metas e desempenho

goals

Metas globais da empresa por período.

employee_goals

Metas individuais por funcionário.

Tabelas — Royalties e taxas da franqueadora

franchise_royalties

Regras de cobrança de royalties por franquia.

franchise_royalty_periods

Valores apurados de royalties por período.

franchise_advertising_fees

Regras de taxa de publicidade. Mesma estrutura de franchise_royalties, trocando royalty por advertising_fee.

franchise_advertising_fee_periods

Valores apurados de taxa de publicidade. Mesma estrutura de franchise_royalty_periods, trocando royalty por advertising_fee.

franchise_categories

Categorias para classificar franquias.

franchise_category_types

Tabela de junção entre franquia e categoria.

Tabelas — Marketing (Facebook Ads)

facebook_accounts

Contas de anúncio do Facebook integradas.

facebook_campaign_data

Métricas diárias por anúncio.

Para atribuir leads/vendas a campanhas, cruze com deals.facebook_source_id.

Tabelas — Sistema

metadata

Registra a última atualização processada para cada par (schema, table).

Use para verificar a "freshness" do Data Lake:

SELECT "table", last_updated
FROM metadata
WHERE schema = 'minha_franquia'
ORDER BY "table";

Enums e dicionários de valores

franchises.status

companies.status

orders.status

budgets.status

payments.payment_status_id

appointments.status_id

Exemplos de queries para BI

Em todos os exemplos, troque 'minha_franquia' pelo schema desejado.

1. Faturamento mensal (vendas faturadas)

SELECT date_trunc('month', created_at) AS mes,
       SUM(total_price)               AS faturamento,
       COUNT(*)                       AS qtd_vendas
FROM orders
WHERE schema = 'minha_franquia'
  AND status = 'BILLED'
  AND created_at >= '2025-01-01'
GROUP BY 1
ORDER BY 1;

2. Recebíveis pendentes (a vencer e vencidos)

SELECT payment_status_name,
       COUNT(*)        AS qtd,
       SUM(value)      AS total
FROM payments
WHERE schema = 'minha_franquia'
  AND payment_status_id IN (1, 3) -- Pendente, Vencido
GROUP BY 1;

3. Ranking de vendedores no mês

SELECT seller_id,
       seller_name,
       COUNT(*)            AS qtd_vendas,
       SUM(total_price)    AS receita,
       SUM(total_paid)     AS recebido
FROM orders
WHERE schema = 'minha_franquia'
  AND status = 'BILLED'
  AND created_at >= date_trunc('month', current_date)
GROUP BY 1, 2
ORDER BY receita DESC;

4. Conversão do funil de CRM

SELECT COUNT(*)                                                 AS leads,
       COUNT(converted_at)                                      AS convertidos,
       COUNT(discarded_at)                                      AS descartados,
       ROUND(100.0 * COUNT(converted_at) / NULLIF(COUNT(*),0), 2) AS taxa_conversao_pct
FROM deals
WHERE schema = 'minha_franquia'
  AND created_at >= current_date - INTERVAL '90 days';

5. Vendas com seus pagamentos (uso correto do JOIN)

SELECT o.order_id,
       o.customer_name,
       o.total_price,
       o.total_paid,
       p.payment_id,
       p.value,
       p.due_date,
       p.payment_status_name
FROM orders o
JOIN payments p
  ON p.schema   = o.schema    -- ✅ obrigatório
 AND p.order_id = o.order_id
WHERE o.schema = 'minha_franquia'
  AND o.created_at >= current_date - INTERVAL '30 days';

6. Ocupação de agenda por profissional

SELECT employee_id,
       employee_name,
       COUNT(*) FILTER (WHERE status_id = 5) AS realizados,
       COUNT(*) FILTER (WHERE status_id = 6) AS faltas,
       COUNT(*)                              AS total_agendamentos
FROM appointments
WHERE schema = 'minha_franquia'
  AND start_date >= date_trunc('month', current_date)
GROUP BY 1, 2
ORDER BY total_agendamentos DESC;

7. Custo por lead (Facebook Ads × Deals)

WITH gasto AS (
  SELECT date, SUM(total_spent) AS spent
  FROM facebook_campaign_data
  WHERE schema = 'minha_franquia'
    AND date >= current_date - INTERVAL '30 days'
  GROUP BY 1
),
leads AS (
  SELECT date(created_at) AS date, COUNT(*) AS qtd
  FROM deals
  WHERE schema = 'minha_franquia'
    AND facebook_source_id IS NOT NULL
    AND created_at >= current_date - INTERVAL '30 days'
  GROUP BY 1
)
SELECT g.date,
       g.spent,
       l.qtd                                AS leads,
       ROUND(g.spent / NULLIF(l.qtd, 0), 2) AS cpl
FROM gasto g
LEFT JOIN leads l ON l.date = g.date
ORDER BY g.date;

8. Consolidado por franqueadora (várias franquias da mesma rede)

SELECT schema,
       SUM(total_price) AS faturamento
FROM orders
WHERE franchisor_id = '42'
  AND status = 'BILLED'
  AND created_at >= date_trunc('month', current_date)
GROUP BY schema
ORDER BY faturamento DESC;

Boas práticas de performance no Redshift

1. Sempre filtre por schema. É a DISTKEY e a primeira SORTKEY da maioria das tabelas. Sem isso, o cluster faz varredura completa.
2. Faça JOINs por schema + <id>. Isso evita "data movement" entre nós (porque ambos os lados do JOIN ficam no mesmo nó).
3. Use os campos *_name denormalizados sempre que possível, em vez de fazer JOIN com tabelas-mestras (customers, services, etc.). Eles existem justamente para evitar JOINs em queries comuns de BI.
4. Filtre por colunas da SORTKEY (geralmente schema, <id_de_negócio>, franchisor_id). Filtros nessas colunas são extremamente baratos.
5. Evite SELECT * — Redshift é colunar; selecionar apenas as colunas necessárias reduz drasticamente o I/O.
6. Prefira agregações GROUP BY em colunas de baixa cardinalidade (status, employee_id, schema) e dimensões temporais (date_trunc(...)).
7. Use WITH (CTEs) para deixar a query mais legível — o otimizador do Redshift lida bem com elas.
8. Janelas curtas no created_at/updated_at ajudam muito (ex.: últimos 90 dias) e reduzem o volume escaneado.
9. Para reprocessamentos, consulte metadata para saber até onde o Data Lake está atualizado.

Em caso de dúvidas sobre o significado de um campo ou enum específico, consulte o time de dados — esta documentação é viva e será atualizada conforme novas tabelas e colunas forem adicionadas ao Data Lake.