alesco-auto-database

Databázové Technológie

O Projekte

Tento repozitár predstavuje úkážkovú implementáciu ELT procesu v Snowflake pre Star schému. Projekt pracuje s datasetom Alesco Auto Database, najväčšou a najpresnejšou automobilovou databázov s viac ako 238 miliónmi záznamov o vozidlách (vrátane vlastníctva, pravidelne aktualizovaných z proprietárnych transakcií). Projekt sa zameriava na analýzu vlastníctva vozidiel, preferencií spotrebiteľov a demografických údajov súvisiacich s automobilmi.

Obsah

1. Úvod a popis zdrojových dát
2. Návrh dimenzionálneho modelu
3. ELT proces v Snowflake
4. Vizualizácia dát

1. Úvod a popis zdrojových dát

Projekt analyzuje dáta o vozidlách, spotrebiteľov a geografických údajov:

• vlastníctva vozidiel
• preferencií spotrebiteľov
• demografických údajov súvisiacich s automobilmi

Dáta pochádzajú z Snowflake Marketplace datasetu Alesco Auto Database.

1.1 Dátová Architektúra

ERD Model

Dáta z datasetu sú usporiadané v relačnom modeli, ktorého štruktúra je znázornená na entitno-relačnom-diagrame.

Model obsahuje jednu primárnu tabuľku:

• AUTO_DATA_SAMPLE_VIEW

Obrázok 1: Entitno-relačná schéma

2. Návrh dimenzionálneho modelu

Na základe Kimballovej metodológie modelovania bola pre tento projekt navrhnutá schéma typu Star.

Štruktúra navrhnutého hviezdicového modelu je znázornená na nasledujúcom obrázku:

Obrázok 2: Star schéma

Centrálna faktová tabuľka fact_vehicle_ownership obsahuje merateľné fakty o vlastníctve vozidiel (napr. dátum registrácie, počet vozidiel, ...) a je prepojená s viacerými denormalizovanými dimenziami prostredníctvom kľúčov. Tento prístup zabezpečuje vysokú výkonnosť dotazov a jednoduchú rozšíriteľnosť modelu.

Tabuľka faktov obsahuje nasledujúcich 7 dimenzií schémy:

• dim_vehicle: Informácie o vozidle (VIN, rok výroby, výrobca, značka, model, trieda, typ paliva a odhadovaný nájazd).
• dim_contact: Kontaktné údaje (persistent ID, e-mail, telefón, typy e-mailov a DNC status).
• dim_geography: Geografický kontext (FIPS kódy, CBSA/MSA, census tract/block a časové pásmo).
• dim_date: Hierarchia dátumu (rok, štvrťrok, mesiac, deň). Použitá pre dátum prvej a poslednej registrácie.
• dim_address: Detailná adresa (ulica, mesto, štát, PSČ, súradnice, dĺžka bývania a doručovacie kódy).
• dim_person: Osobné údaje vlastníka (meno, priezvisko, titul, pohlavie a persistent ID).
• dim_household: Informácie o domácnosti (odhadovaný príjem, hodnota domu, vlastníctvo/podnájom, počet vozidiel a prítomnosť detí).

Prehľad dimenzií a SCD typov:

Dimenzia	Vzťah k tabuľke faktov	Typ SCD
dim_vehicle	1:N	Typ 0
dim_contact	1:N	Typ 1
dim_geography	1:N	Typ 0
dim_date	1:N	Typ 0
dim_address	1:N	Typ 1
dim_person	1:N	Typ 1
dim_household	1:N	Typ 1

3. ELT proces v Snowflake

Spracovanie dát v tomto projekte sleduje modernú schému ELT (Extract, Load, Transform). Umožňuje vykonávať komplexné transformácie priamo v databáze až po ich úspešnom načítaní. Celý proces je spravený tak, aby premenil surové dáta na hviezdicovú schému.

Extract

Zdrojové údaje boli získané zo Snowflake Marketplace od poskytovateľa Alesco Data. Prvým krokom bolo vytvoriť tzv. staging vrstvy (v našom prípade iba jednu - alesco_auto_staging). Tento proces nám zabezpečuje stabilitu pre transformáciu dát.
Zdrojová databáza: Alesco Auto Database
Zdrojová schéma: PUBLIC
Zdrojový pohľad: AUTO_DATA_SAMPLE_VIEW
SQL príkaz pre vytvorenie staging tabuľky:

-- STAGING - import surových dát 
CREATE OR REPLACE TABLE alesco_auto_staging AS 
SELECT * FROM ALESCO_AUTO_DATABASE_SAMPLE.PUBLIC.AUTO_DATA_SAMPLE_VIEW;

--> Kontrola
SELECT * FROM alesco_auto_staging LIMIT 10;
DESCRIBE TABLE alesco_auto_staging;

Vysvetlenie: Príkaz Vytvoré fyzickú kópiu dát v našom úložisku, čím zabezpečíme, že naše transformácie nebudú ovplyvnené zmenami v marketplace zdroji a budeme mať rýchly prístup k surovým dátam.

Load

Fáza načítania spočívala vo vytváraný viacdimenzionálnej architektúry(Star Schema). Dáta sú rozdelené do dimenzií a faktovej tabuľky. Toto zahrňalo dva hlavné kroky:

1. Tvorba dimenzií: Z pôvodnej tabuľky boli extrahované unikátne entity (domácnosti, osoby, vozidlá, adresy, kontakty, geografia a dátumy).Použili sme techniku SCD Typ 0 (Static), kde sú dáta historicky fixné, čo pre tento typ datasetu je vhodné. Každému záznamu sme pridelili technický kľúč (Surrogate Key) pomocou funkcie ROW_NUMBER().

--> Tabuľka dimenzie adries
CREATE OR REPLACE TABLE dim_address AS (
    SELECT DISTINCT
        ROW_NUMBER() OVER (ORDER BY ADDRESS_ID) AS ADDRESS_KEY,
        ADDRESS_ID,
        FULL_ADDRESS,
        
        LEFT(FULL_ADDRESS, CHARINDEX(' ', FULL_ADDRESS) - 1) AS STREET_NUMBER,
        SUBSTR(FULL_ADDRESS, CHARINDEX(' ', FULL_ADDRESS) + 1) AS STREET_NAME,
        
        ADDRESS_LINE,
        CITY,
        STATE,
        ZIP5,
        ZIP4,
        COUNTY_NAME,
        LATITUDE,
        LONGITUDE,
        ADDRESS_TYPE_INDICATOR,
        CARRIER_ROUTE,
        SCF_CODE,
        DELIVERY_POINT_BAR_CODE,
        DPV_INDICATOR,
        LENGTH_OF_RESIDENCE
        
    FROM alesco_auto_staging
    WHERE ADDRESS_ID IS NOT NULL AND CHARINDEX(' ', FULL_ADDRESS) > 0
);

--> Kontrola
SELECT * FROM dim_address LIMIT 10;
DESCRIBE TABLE dim_address;

2. Načítanie faktovej tabuľky a validácia: Po naplnení dimenzií prebehlo prepojenie faktovej tabuľky prestredníctvom JOIN operácií, čím sme zabezpečíli referenčnú integritu modelu.

Transform

V tejto fáze sme využívali: SQL funkcie na čistenie dát (napr. SUBSTR pre extrakciu ulice) a window funkcie na obohatenie faktov.

1. Čistenie, deduplikácia a casting:

• Deduplikácia: Pomocou SELECT DISTINCT sme odstránili duplicitné záznamy osôb a vozidiel.
• Casting (Pretypovanie): Napr. AUTO_YEAR::INT zabezpečuje, že s rokom výroby môžeme vykonávať matematické operácie.
• Ošetrenie chýb: Použitie WHERE ... IS NOT NULL a logických podmienok pre validáciu formátu adries.

2. Window Functions(Analytické funkcie): Tieto funkcie boli povinne implementované vo faktovej tabuľke na generovamie pokročilých metrík bez potreby zložitého agregovania pri dopytoch.

--> Window funkcie na obohatenie faktov:

    --> rank áut v domácnosti:
        RANK() OVER ( 
            PARTITION BY s.HOUSEHOLD_ID
            ORDER BY v.AUTO_YEAR DESC
        ) AS VEHICLE_RANK_IN_HOUSEHOLD,

    --> celkový počet vozidiel:
        COUNT(*) OVER () AS TOTAL_VEHICLES_GLOBAL,

    --> priemerny vek výroby vozidiel v danom zip5 (PSČ):
        AVG(v.AUTO_YEAR::INT) OVER (
            PARTITION BY a.ZIP5
        ) AS REGIONAL_AVG_YEAR

4. Vizualizácia dát

V tejto časti prezentujeme 6 vizualizácií, ktoré využívajú výpočtové metriky z faktovej tabuľky fact_vehicle_ownership na poskytnutie doležitých informácií.

Vizualizácia 1: TOP 10 štátov podľa počtu vozidiel

SELECT 
    a.STATE,
    COUNT(*) AS VEHICLE_COUNT,
    ROUND((COUNT(*) / MAX(f.TOTAL_VEHICLES_GLOBAL)) * 100, 2) AS MARKET_SHARE_PCT
FROM fact_vehicle_ownership f
JOIN dim_address a ON f.DIM_ADDRESS_KEY = a.ADDRESS_KEY
GROUP BY a.STATE 
ORDER BY VEHICLE_COUNT DESC LIMIT 10;

Ktoré štáty maju najväčŚie zastúpenie v databáze a aký je ich percentuálny podiel?

Graf ukazuje dominanciu štátu Texas (TX) s trhovým podielom cez 16%, nasledne je Flodida (FL) s 12%. Tieto informácie umožňujú efektívne rozdelenie regionálneho marketingu do lokalít s najvyššou hustotou vozidiel.

Obrázok 3: Vizualizácia 1

Vizualizácia 2: TOP 5 výrobcov

SELECT 
    v.AUTO_MANUFACTURER_CODE,
    COUNT(*) AS VEHICLE_COUNT
FROM fact_vehicle_ownership f
JOIN dim_vehicle v ON f.DIM_VEHICLE_KEY = v.VEHICLE_KEY
GROUP BY v.AUTO_MANUFACTURER_CODE LIMIT 5;

Ktoré značky výrobcov sú najrozšírenejšie?

Najúspešnejším výrobcom je kód "L" s takmer 12 000 vozidlami. Tento prehľad pomáha pri hladaní značky, ktorá je najrozšírenejšia.

Obrázok 4: Vizualizácia 2

Vizualizácia 3: Distribúcia typu paliva

SELECT 
    v.AUTO_MANUFACTURER_CODE,
    COUNT(*) AS VEHICLE_COUNT
FROM fact_vehicle_ownership f
JOIN dim_vehicle v ON f.DIM_VEHICLE_KEY = v.VEHICLE_KEY
GROUP BY v.AUTO_MANUFACTURER_CODE LIMIT 5;

Ktorá pohonná hmota je najrozšírenejšia?

Drvivá väčšina využíva benzín (kód "G"), čo predstavuje takmer celú plochu koláčového grafu. Naznačuje nízku adopciu elektromobility.

Obrázok 5: Vizualizácia 3

Vizualizácia 4: vlastníctvo vozidiel podľa pohlavia

SELECT 
    p.GENDER,
    COUNT(*) AS VEHICLE_COUNT
FROM fact_vehicle_ownership f 
JOIN dim_person p ON f.DIM_PERSON_KEY = p.PERSON_KEY
GROUP BY p.GENDER;

Existujú rozdiely vo vlastníctve vozidiel medzi mužmi a ženami?

Muži (M) vlastnia približne o 50% viac vozidiel ako ženy (F). Výraznú časť tvoria aj neznáme hodnoty (null).

Obrázok 6: Vizualizácia 4

Vizualizácia 5: distribúcia vozidiel podľa roku výroby

SELECT
    v.AUTO_YEAR,
    COUNT(*) AS VEHICLE_COUNT
FROM fact_vehicle_ownership f
JOIN dim_vehicle v ON f.DIM_VEHICLE_KEY = v.VEHICLE_KEY 
WHERE v.AUTO_YEAR IS NOT NULL
GROUP BY v.AUTO_YEAR ORDER BY v.AUTO_YEAR;

Aký je vekový trend aut?

Čiarový graf zobrazuje dva cykly obnovy (okolo rokov 1999 a 2008). Prudký pokles po roku 2018.

Obrázok 7: Vizualizácia 5

Vizualizácia 6: koncentrácia vozidiel podľa hodnoty domáceho trhu

SELECT 
    h.HOME_MARKET_VALUE,
    AVG(f.VEHICLE_RANK_IN_HOUSEHOLD) AS AVG_CARS_PER_HOUSEHOLD
FROM fact_vehicle_ownership f
JOIN dim_household h ON f.DIM_HOUSEHOLD_KEY = h.HOUSEHOLD_KEY 
WHERE h.HOME_MARKET_VALUE IS NOT NULL
GROUP BY h.HOME_MARKET_VALUE
ORDER BY h.HOME_MARKET_VALUE;

Vlastnia majitalia drahších nehnuteľností v priemere viac vozidiel?

Tento graf využíva window funkciu RANK() na určenie poradového čísla auta v domácnosti. Ukazuje to nadprimerný počet vozidiel na jednu rodinu.

Obrázok 8: Vizualizácia 6

Autori

• Emma Kohútová
• Matúš Kovár

Licencia

Tento projekt je licencovaný pod proprietárnou licenciou. Podrobnosti nájdete v súbore LICENSE