PetCare Analytics

🇵🇱 Polska wersja dokumentacji

Synthetic dataset generator and analytics-engineering playground for the pet-care SaaS domain. Data is generated with Python + Faker, loaded into DuckDB, and modeled with dbt (raw → staging → marts).

What this project is

PetCare is a two-purpose project:

1. Producer side — generates a realistic, intentionally-flawed synthetic dataset for a pet-care SaaS platform (clients, services, orders, payments, appointments, staff). The dataset is designed for data analysts to practice SQL, joins, merges, deduplication, and data-quality checks on multiple related CSV files.

2. Consumer side — uses that same dataset as a sandbox for practicing analytics engineering with DuckDB and dbt: dimensional modeling, tests, snapshots, documentation.

The "intentional flaws" are the heart of the project. The generator deliberately introduces:

• ~2% duplicate transactions in the payments table (for testing uniqueness constraints)
• ~5% pricing discrepancies between order totals and the sum of order items (for testing data integrity)
• Cancelled orders, refunded payments, unavailable appointments — the messy realities of running a business

Stack

Layer	Tool	Why
Data generation	Python + Faker	Realistic synthetic data, full control over anomalies
Storage	DuckDB	Fast OLAP, single-file, zero infrastructure
Transformation	dbt-core + dbt-duckdb	Industry-standard analytics engineering framework
Documentation	dbt docs (generated)	Auto-generated lineage and column descriptions

Repository layout

PetCareAnalitycs/
├── PetCareSaas.py           # Data generator (Faker → CSV)
├── init_db.py               # CSV → DuckDB loader
├── petcare.db               # Generated DuckDB file (not in git)
├── *.csv                    # Generated raw data (not in git)
├── requirements.txt         # Python dependencies
├── profiles.yml.example     # dbt profile template
├── data_quality/            # (planned) custom validators
├── utils/                   # (planned) shared helpers
└── petcare_transform/       # dbt project
    ├── dbt_project.yml
    ├── models/
    │   ├── staging/         # 1 view per source table — typing, renaming, normalization
    │   └── marts/           # Star-schema dimensions and facts
    ├── tests/               # Custom singular tests (e.g., total_price integrity)
    ├── snapshots/           # (planned) SCD Type 2 history
    └── macros/              # (planned) reusable Jinja macros

Quick start

Prerequisites: Python 3.11+, Git.

# 1. Clone and enter the repo
git clone <repo-url> petcare-analytics
cd petcare-analytics

# 2. Set up a virtual environment
python -m venv venv
source venv/bin/activate           # Linux/Mac
# venv\Scripts\activate            # Windows

# 3. Install dependencies
pip install -r requirements.txt

# 4. Generate the dataset (creates 7 CSV files)
python PetCareSaas.py

# 5. Load CSVs into DuckDB (creates petcare.db)
python init_db.py

# 6. Configure dbt profile
# Copy profiles.yml.example to ~/.dbt/profiles.yml
# Update the path to point to your local petcare.db

# 7. Run the dbt pipeline
cd petcare_transform
dbt debug                          # verify connection
dbt run                            # build all models
dbt test                           # run all tests
dbt docs generate && dbt docs serve  # explore lineage in browser

What you get after dbt run

Three layers of data, all in petcare.db:

• Raw (7 tables): users, staff, services, orders, order_items, payments, appointments — direct from CSV, untyped, with Polish labels.
• Staging (7 views, prefix stg_): cleaned and typed, columns renamed, statuses normalized to English. Materialized as views (always fresh, zero storage cost).
• Marts (3 tables): dimensional model — dim_users, dim_services, fct_orders. Materialized as tables (fast queries).

Test suite

dbt test

38 tests total:

• 36 pass — not_null, unique, accepted_values across all models
• 2 intentionally fail — custom singular tests that detect the seeded anomalies:
  • assert_order_total_matches_items — finds ~73 orders with price discrepancies (~6%)
  • payments — finds ~21 duplicate transaction IDs (~2%)

Where to start reading the code

If you're new to the project (or returning to it after a break), read in this order:

1. This README — what and why
2. docs/ERD.md — entity relationship diagram of the raw layer
3. PetCareSaas.py — how the data is generated (note the anomaly logic)
4. petcare_transform/models/staging/sources.yml — what raw tables exist
5. petcare_transform/models/staging/ — start with stg_orders.sql, then stg_payments.sql
6. petcare_transform/models/marts/ — fct_orders.sql first (deduplication logic), then dimensions

License & data

All data is synthetic. No real personal information. The generator uses Faker with Polish locale to produce realistic-looking but fictional users, addresses, and PESEL-like identifiers.