Skip to content
BranchesTags

Latest commit

 

History

16 Commits

Folders and files

NameName
Last commit message
Last commit date
docs
docs
 
 
sql
sql
 
 
.gitignore
.gitignore
 
 
LICENSE
LICENSE
 
 
README.md
README.md
 
 

Repository files navigation

🗄️ Olist Sales Analytics — Modelagem Dimensional PostgreSQL

PostgreSQL Star Schema SQL License: MIT Dataset

Banco de dados analítico aplicando Star Schema sobre dataset real de e-commerce brasileiro (100k pedidos), com SQL avançado, integridade referencial e queries otimizadas para insights de negócio.

Tecnologias: PostgreSQL 15 | Star Schema | Window Functions | CTEs | Índices Funcionais

📊 Visão Geral

Objetivo: Criar estrutura analítica para responder perguntas de negócio sobre vendas, produtos, clientes e vendedores em um marketplace brasileiro.

Dataset: Brazilian E-Commerce Public Dataset by Olist
Período: 2016-2018 | Volume: ~100.000 pedidos | Linhas carregadas: 98.778 em fato_itens
Tecnologia: PostgreSQL 15 | Modelagem: Star Schema

🛠️ Tech Stack

Database

  • PostgreSQL 15 — Banco relacional com suporte avançado a índices funcionais

Modelagem

  • Star Schema — Arquitetura dimensional para OLAP
  • 3FN — Normalização nas tabelas dimensão

SQL Features

  • Window Functions — LAG(), RANK() OVER PARTITION BY
  • CTEs — Common Table Expressions para queries complexas
  • Functional Indexes — DATE_TRUNC() para agregações temporais
  • Constraints — PKs compostas, FKs com CASCADE/RESTRICT

Tools

  • DBeaver — IDE SQL para desenvolvimento e testes
  • dbdiagram.io — Modelagem visual do DER
  • Git/GitHub — Versionamento de código

🗄️ Modelo de Dados

Diagrama Entidade-Relacionamento

Arquitetura: Star Schema

📂 Dimensões (3FN)

  • dim_clientes — Dados cadastrais de clientes (customer_id, cidade, estado)
  • dim_produtos — Catálogo de produtos por categoria
  • dim_vendedores — Informações de sellers (seller_id, localização)

📊 Fatos (Desnormalizadas para OLAP)

  • fato_pedidos — Transações de pedidos (granularidade: 1 pedido)
  • fato_itens — Itens vendidos (granularidade: 1 produto × 1 pedido × 1 vendedor)

🔑 Decisões Técnicas

✅ Integridade referencial: 4 Foreign Keys com políticas CASCADE/RESTRICT
✅ Performance: 15 índices estratégicos (FKs, campos temporais, geográficos)
✅ Qualidade de dados: 240 duplicatas removidas antes de criar PK composta em fato_itens
✅ Otimização temporal: Índice funcional em DATE_TRUNC('month', order_purchase_timestamp)

Documentação completa:

✨ Features

📊 Modelagem de Dados

  • ✅ Star Schema com 3 dimensões + 2 fatos
  • ✅ Integridade referencial via 4 Foreign Keys
  • ✅ Normalização 3FN nas dimensões
  • ✅ Limpeza de 240 duplicatas pré-constraints

⚡ Otimização

  • ✅ 15 índices estratégicos (FKs, temporal, geográfico)
  • ✅ Índice funcional para agregações mensais
  • ✅ Queries otimizadas validadas via EXPLAIN ANALYZE

📈 Análises Implementadas

  • ✅ KPIs executivos (receita mensal, crescimento MoM)
  • ✅ Ranking de produtos por categoria (RANK OVER PARTITION BY)
  • ✅ Análise geográfica com % do total nacional
  • ✅ Performance de vendedores (top sellers)
  • ✅ Análise de custos logísticos (frete/receita)

📚 Documentação

  • ✅ Dicionário de dados completo (Markdown)
  • ✅ Análise de normalização e trade-offs
  • ✅ Prints de queries executadas com dados reais
  • ✅ Scripts DDL padronizados e comentados

🚀 Como Reproduzir

Pré-requisitos

  • PostgreSQL 15 ou superior
  • DBeaver, pgAdmin ou psql
  • Dataset Olist (download aqui)

1. Clonar repositório

git clone https://github.com/rodrigodesouza7/ecommerce-olist-analytics.git
cd ecommerce-olist-analytics

2. Baixar dataset

3. Criar banco de dados

CREATE DATABASE olist_analytics;

4. Executar scripts DDL (ordem importa)

psql -d olist_analytics -f sql/ddl/01_create_dim_clientes.sql
psql -d olist_analytics -f sql/ddl/02_create_dim_produtos.sql
psql -d olist_analytics -f sql/ddl/03_create_dim_vendedores.sql
psql -d olist_analytics -f sql/ddl/04_create_fato_pedidos.sql
psql -d olist_analytics -f sql/ddl/05_create_fato_itens.sql
psql -d olist_analytics -f sql/ddl/06_create_constraints.sql  # Crítico: FKs + limpeza de duplicatas
psql -d olist_analytics -f sql/ddl/07_create_indexes.sql

5. Carregar dados

Use DBeaver (Import Data) ou COPY via psql:

-- Ajustar paths conforme localização dos CSVs
\COPY dim_clientes FROM 'dados/olist_customers_dataset.csv' CSV HEADER;
\COPY dim_produtos FROM 'dados/olist_products_dataset.csv' CSV HEADER;
\COPY dim_vendedores FROM 'dados/olist_sellers_dataset.csv' CSV HEADER;
\COPY fato_pedidos FROM 'dados/olist_orders_dataset.csv' CSV HEADER;
\COPY fato_itens FROM 'dados/olist_order_items_dataset.csv' CSV HEADER;

📈 Queries Analíticas Implementadas

1️⃣ Evolução da Receita Mensal com Crescimento (KPI Executivo)

Receita Mensal

Problema de negócio: CEO precisa monitorar crescimento mensal para identificar tendências e sazonalidades.

Técnicas SQL: DATE_TRUNC, LAG() (window function), cálculo percentual de crescimento

-- Ver query completa em: sql/queries/q01_receita_mensal_crescimento.sql

Insight: Crescimento de 23% entre nov/2017 e dez/2017 (Black Friday + Natal)

2️⃣ Top Produtos por Categoria (Ranking)

Top Produtos

Problema de negócio: Gerente de produto quer saber quais itens investir em estoque prioritário.

Técnicas SQL: RANK() OVER PARTITION BY, agregações por categoria

-- Ver query completa em: sql/queries/q02_top_produtos_por_categoria.sql

Insight: Categoria "eletrônicos" concentra 3 dos 5 produtos mais lucrativos (alto ticket, alto giro)

3️⃣ Análise Geográfica de Vendas

Análise Geográfica

Problema de negócio: Diretor comercial quer identificar regiões com maior potencial para abertura de centros de distribuição.

Técnicas SQL: Agregação geográfica, percentual do total via window function

-- Ver query completa em: sql/queries/q03_analise_geografica_vendas.sql

Insight: SP concentra 41.8% da receita nacional, mas ticket médio no DF é 15% superior

4️⃣ Performance de Vendedores (Top 20 Sellers)

Problema de negócio: Marketplace precisa identificar top sellers para programas de incentivo.

-- Ver query completa em: sql/queries/q04_performance_vendedores.sql

5️⃣ Análise de Custo de Frete por Categoria

Problema de negócio: CFO quer reduzir custos operacionais identificando categorias com frete desproporcional.

-- Ver query completa em: sql/queries/q05_analise_custo_frete.sql

Insight: Categoria "móveis" tem frete médio 38% do valor do produto (oportunidade de renegociação logística)

🛠️ Stack Técnica

  • Banco de dados: PostgreSQL 15
  • Modelagem: Star Schema (Kimball)
  • SQL: DDL, Constraints (PKs/FKs), Índices, Window Functions, CTEs, Agregações
  • Ferramentas: DBeaver, VS Code, dbdiagram.io
  • Versionamento: Git/GitHub

📂 Estrutura do Projeto

ecommerce-olist-analytics/ ├── dados/ │ └── *.csv (gitignored) ├── docs/ │ ├── modelo-conceitual.png │ ├── dicionario-dados.md │ ├── normalizacao.md │ ├── print1_receita_mensal_crescimento.png │ ├── print2_top_produtos_ranking.png │ └── print3_analise_geografica.png ├── sql/ │ ├── ddl/ │ │ ├── 01_create_dim_clientes.sql │ │ ├── 02_create_dim_produtos.sql │ │ ├── 03_create_dim_vendedores.sql │ │ ├── 04_create_fato_pedidos.sql │ │ ├── 05_create_fato_itens.sql │ │ ├── 06_create_constraints.sql │ │ └── 07_create_indexes.sql │ ├── queries/ │ │ ├── q01_receita_mensal_crescimento.sql │ │ ├── q02_top_produtos_por_categoria.sql │ │ ├── q03_analise_geografica_vendas.sql │ │ ├── q04_performance_vendedores.sql │ │ └── q05_analise_custo_frete.sql │ └── views/ │ └── vw_analise_final.sql ├── .gitignore └── README.md

🎓 Aprendizados Técnicos

  • Modelagem dimensional aplicada a cenário real de marketplace
  • Normalização (3FN) vs. Desnormalização estratégica para OLAP
  • Integridade referencial com políticas ON DELETE CASCADE/RESTRICT
  • Otimização de queries via índices em FKs e campos de filtro frequente
  • Qualidade de dados: limpeza de duplicatas antes de aplicar constraints
  • SQL avançado: Window Functions (LAG, RANK OVER PARTITION BY), CTEs, índices funcionais

📝 Status do Projeto

  • Modelagem conceitual (DER validado)
  • Implementação DDL (7 scripts padronizados)
  • Constraints e índices (4 FKs + 15 índices)
  • Carga de dados (98.778 linhas em fato_itens)
  • Queries analíticas (5 queries documentadas)
  • View de negócio (vw_analise_final)
  • Documentação técnica completa

👤 Sobre o Autor

Rodrigo de Souza Silva Profissional de Tecnologia da Informação com formação em Sistemas de Informação e pós-graduação em Data Science, Machine Learning e IA.

🔗 LinkedIn: https://www.linkedin.com/in/rodrigodesouzasilva
💻 GitHub: https://github.com/rodrigodesouza7

📄 Licença

MIT License — Projeto de portfólio profissional

About

Modelagem dimensional Star Schema em PostgreSQL com dataset real de e-commerce brasileiro (Olist), aplicando SQL avançado, integridade referencial e queries analíticas para insights de negócio

Topics

analyticsbrazilian-datasetbusiness-intelligencedata-analysisdata-engineeringdata-modelingdata-warehousedimensional-modelingecommercekaggle-datasetolappostgresqlsqlstar-schemawindow-functions

Resources

Readme
MIT license
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors