📊 Data Models Documentation¶

-- Materialized as incremental table
-- Partitioned by: on_ground
-- Unique key: ['id', 'time']
-- Location: s3a://datalake/intermediate/flight_radar/explode_complete_flight

SELECT
    ID,
    ALTITUDE,
    LATITUDE,
    LONGITUDE,
    HEADING,
    ICAO_24BIT,
    VERTICAL_SPEED,
    GROUND_SPEED,
    NUMBER,
    SQUAWK,
    CALLSIGN,
    REGISTRATION,
    ON_GROUND,
    TIME,
    -- Departure airport details
    DEPARTURE.IATA AS DEPARTURE_IATA,
    DEPARTURE.LATITUDE AS DEPARTURE_LATITUDE,
    DEPARTURE.LONGITUDE AS DEPARTURE_LONGITUDE,
    DEPARTURE.NAME AS DEPARTURE_NAME,
    DEPARTURE.COUNTRY_NAME AS DEPARTURE_COUNTRY_NAME,
    -- Arrival airport details
    ARRIVAL.IATA AS ARRIVAL_IATA,
    ARRIVAL.LATITUDE AS ARRIVAL_LATITUDE,
    ARRIVAL.LONGITUDE AS ARRIVAL_LONGITUDE,
    ARRIVAL.NAME AS ARRIVAL_NAME,
    ARRIVAL.COUNTRY_NAME AS ARRIVAL_COUNTRY_NAME,
    -- Airline details
    AIRLINE.IATA AS AIRLINE_IATA,
    AIRLINE.NAME AS AIRLINE_NAME,
    AIRLINE.ICAO AS AIRLINE_ICAO,
    -- Aircraft details
    AIRCRAFT.CODE AS AIRCRAFT_CODE,
    MODEL_MISSING_DATA,
    __TIMESTAMP,
    -- Derived fields
    CASE
        WHEN AIRCRAFT.MODEL IS NOT NULL THEN split_part(AIRCRAFT.MODEL, ' ', 1)
        ELSE NULL
    END AS AIRCRAFT_MANUFACTURER
FROM {{ source('flight_radar_prepared', 'complete_flight') }}

-- Handles flight records with missing data
-- Enriches missing flight information where possible
-- Materialized as incremental table

-- One Big Table (OBT) combining complete and missing flights
-- Provides unified view of all flight data
-- Materialized as table

-- Top 10 airlines by flight count
-- Materialized as Iceberg table
-- Location: warehouse/flight_radar/marts/company_with_the_most_active_flight
-- Catalog: flight_radar
-- Schema: marts

{{ config(
    materialized='table',
    alias='company_with_the_most_active_flight',
    tags=['company_with_the_most_active_flight'],
    database='flight_radar',
    schema='marts',
    location_root='s3a://warehouse/flight_radar/marts/company_with_the_most_active_flight'
) }}

SELECT
    AIRLINE_NAME,
    AIRLINE_IATA,
    COUNT(*) AS TOTAL_FLIGHTS
FROM {{ ref('full_flight_obt') }}
WHERE AIRLINE_NAME IS NOT NULL
GROUP BY AIRLINE_NAME, AIRLINE_IATA
ORDER BY TOTAL_FLIGHTS DESC
LIMIT 10

-- Identifies airports with significant departure/arrival imbalances
-- Useful for capacity planning and route optimization

-- Airlines operating the most regional flights
-- Focuses on short-haul domestic operations

-- Aircraft manufacturers with most active flights
-- Provides insights into fleet composition

-- Identifies the longest currently active flights
-- Real-time flight tracking insights

-- Geographic analysis of longest flights by continent
-- Route planning and market analysis

-- Combines customer and order data
-- Provides unified customer transaction view
-- Materialized as table

-- Customer behavior and purchasing patterns
-- Segmentation and lifetime value analysis
-- Materialized as Iceberg table
-- Location: warehouse/ecommerce/marts/customer_analytics
-- Catalog: ecommerce
-- Schema: marts

{{ config(
    materialized='table',
    alias='customer_analytics',
    tags=['customer_analytics'],
    database='ecommerce',
    schema='marts',
    location_root='s3a://warehouse/ecommerce/marts/customer_analytics'
) }}

SELECT
    customer_id,
    customer_name,
    COUNT(*) as total_orders,
    SUM(order_amount) as total_spent,
    AVG(order_amount) as avg_order_value,
    MAX(order_date) as last_order_date
FROM {{ ref('customer_order') }}
GROUP BY customer_id, customer_name
ORDER BY total_spent DESC

-- Standardized real estate sales data
-- Cleansed and validated property information
-- Materialized as table

-- Market analysis and trend identification
-- Price analysis and forecasting
-- Materialized as Iceberg table
-- Location: warehouse/asset_property/marts/property_market_trends
-- Catalog: asset_property
-- Schema: marts

{{ config(
    materialized='table',
    alias='property_market_trends',
    tags=['property_market_trends'],
    database='asset_property',
    schema='marts',
    location_root='s3a://warehouse/asset_property/marts/property_market_trends'
) }}

SELECT
    property_type,
    location,
    AVG(price) as avg_price,
    COUNT(*) as property_count,
    MIN(price) as min_price,
    MAX(price) as max_price,
    STDDEV(price) as price_volatility
FROM {{ ref('stg_real_estate_sales') }}
GROUP BY property_type, location
ORDER BY avg_price DESC

-- Incremental model configuration
{{
  config(
    materialized='incremental',
    unique_key=['id', 'time'],
    partition_by=['on_ground']
  )
}}

-- Incremental logic
{% if is_incremental() %}
WHERE __TIMESTAMP > (SELECT MAX(__TIMESTAMP) FROM {{ this }})
{% endif %}

-- Partition by business-relevant columns
partition_by=['on_ground']           -- Flight status
partition_by=['sale_date']           -- Time-based partitioning
partition_by=['customer_segment']    -- Business logic partitioning

-- Built-in dbt tests
-- models/schema.yml
models:
  - name: explode_complete_flight
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: latitude
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: -90
              max_value: 90

-- Add new column
ALTER TABLE flight_radar.intermediate.explode_complete_flight 
ADD COLUMN new_field STRING;

-- Rename column
ALTER TABLE flight_radar.intermediate.explode_complete_flight 
RENAME COLUMN old_field TO new_field;

-- Drop column
ALTER TABLE flight_radar.intermediate.explode_complete_flight 
DROP COLUMN unused_field;

-- dbt tests
-- tests/assert_flight_data_quality.sql
SELECT *
FROM {{ ref('explode_complete_flight') }}
WHERE latitude < -90 OR latitude > 90
   OR longitude < -180 OR longitude > 180
   OR altitude < 0 OR altitude > 50000

-- Statistical anomaly detection
WITH flight_stats AS (
    SELECT 
        AVG(ground_speed) as avg_speed,
        STDDEV(ground_speed) as speed_stddev
    FROM {{ ref('explode_complete_flight') }}
)
SELECT *
FROM {{ ref('explode_complete_flight') }}
WHERE ground_speed > (
    SELECT avg_speed + 3 * speed_stddev 
    FROM flight_stats
)

-- Time-based partitioning for temporal data
partition_by=['time']

-- Business logic partitioning
partition_by=['on_ground', 'airline_iata']

-- Geographic partitioning
partition_by=['departure_country_name']

-- Cluster by frequently queried columns
CLUSTER BY airline_iata, departure_iata, arrival_iata

-- Create indexes for common query patterns
CREATE INDEX idx_flight_time ON explode_complete_flight (time);
CREATE INDEX idx_flight_airline ON explode_complete_flight (airline_iata);

-- Ranger policies for data access
-- Grant access to specific users/roles
GRANT SELECT ON flight_radar.marts.company_with_the_most_active_flight 
TO ROLE analytics_users;

-- Restrict access to sensitive data
REVOKE SELECT ON ecommerce.customer 
FROM ROLE public_users;

-- Automatic data retention policies
-- Delete data older than 2 years
DELETE FROM flight_radar.intermediate.explode_complete_flight 
WHERE time < CURRENT_DATE - INTERVAL '2 years';

-- Monitor data quality metrics
SELECT 
    COUNT(*) as total_records,
    COUNT(DISTINCT id) as unique_flights,
    COUNT(CASE WHEN latitude IS NULL THEN 1 END) as missing_latitude,
    COUNT(CASE WHEN longitude IS NULL THEN 1 END) as missing_longitude
FROM {{ ref('explode_complete_flight') }}
WHERE __TIMESTAMP >= CURRENT_DATE

-- Monitor query performance
SELECT 
    query_id,
    query_text,
    execution_time,
    rows_returned,
    bytes_scanned
FROM system.runtime.queries
WHERE query_text LIKE '%explode_complete_flight%'
ORDER BY execution_time DESC
LIMIT 10

-- Table naming
-- Raw: {domain}_prepared.{table_name}
-- Staging: {domain}.intermediate.stg_{table_name}
-- Marts: {domain}.marts.{business_concept}

-- Column naming
-- Use snake_case
-- Be descriptive and consistent
-- Include units where applicable

-- Unit tests for transformations
-- Integration tests for data pipelines
-- Performance tests for query optimization
-- Data quality tests for validation

-- Kafka integration for real-time data
-- Stream processing with Apache Flink
-- Real-time analytics and alerting

-- Feature engineering for ML models
-- Model training and inference
-- Automated anomaly detection

-- Graph analytics for flight networks
-- Time series analysis for trends
-- Spatial analytics for geographic insights