Please enable JavaScript.

Coggle requires JavaScript to display documents.

⚔️ Data 🐉 Adventure - Coggle Diagram

- - - - Batch
        
        By intervals
      - Stream
        
        By conditions
  - - - Extract
        
        Transform
        
        Load
      - less storage
      - :check: :shield: PII
    - - Extract
        
        Load
        
        Transform
      - MORE STORAGE
      - :warning: :shield: PII
  - - - Split tasks
        (4 x x_time)
        
        :arrow_left:
        
        Process :desktop_computer:
        (1/4 of time)
        (1/4 of memory)
        
        Process :desktop_computer:
        (1/4 of time)
        (1/4 of memory)
        
        Process :desktop_computer:
        (1/4 of time)
        (1/4 of memory)
        
        Process :desktop_computer:
        (1/4 of time)
        (1/4 of memory)
        
        :arrow_left:
        
        Join tasks
        (4 x y_time)
  - - - Analysis Optimized
      - Data Cube
        
        Hypercube
      - smaller volume
      - hundreds of users :silhouette:
    - - Transactions Optimized
      - LARGE VOLUME
      - thousands of users :silhouette:
- - - - 1NF
        
        2NF
        
        3NF
        
        BCNF
        
        4NF
        
        5NF
        
        2 more items...
        
        :arrow_right: Normalization :arrow_right:
        
        Solves :check:
        
        Data anomalies
        
        Insertion anomaly
        
        Deletion anomaly
        
        Update anomaly
      - ACID
        
        Transaction
        
        Atomic
        
        Consistent
        
        Isolated
        
        Durable
    - - Table Types
        
        Dimension
        
        :heavy_plus_sign:
        
        Deep questioning
        :!?:
        
        Kimball Slowly Changing Dimensions (SCD)
        
        Type 1
        
        Original
        
        Zoom In
        
        New
        
        Zoom In
        
        Type 2
        
        Original
        Zoom In
        
        New
        
        Zoom In
        
        Type 3
        
        Original
        
        Zoom In
        
        New
        
        Zoom In
        
        Modern Approach
        
        Snapshots :camera_with_flash:
        
        Fact
      - Schema
        
        Schema Types
        
        Snowflake
        
        Example
        
        Star
        
        Example
        
        Galaxy
        
        Schema elements
        
        Zoom In
        
        Columns
        
        Types
        
        Relations
        
        Tables
        
        Database modeling
        
        Zoom In
        
        Conceptual
        
        Zoom In
        
        :arrow_right:
        
        Logical
        
        Zoom In
        
        :arrow_right:
        
        1 more item...
      - Superkey :old_key:
        
        Key 🔑
        
        Candidate Keys
        
        Surrogate Key
      - Views :frame_with_picture:
        
        👺 Hide sensitive :shield:
        columns
        
        Good for Multiple JOIN queries
        
        Types of View
        
        Materialized
        
        Stores data
        
        :turtle: Good for slow queries
        
        Refreshed with pipeline schedulers
        
        Non-materialized
        
        :robot_face: Stores queries
        
        :pencil2: Good for write-intensive DBs
      - Roles :check:
        
        :silhouettes: Group Role
        
        Permissions on DB
        
        :silhouette: User Role
      - :hocho: Partitioning
        
        Vertical
        
        Zoom In
        
        Horizontal
        
        Zoom In
        
        Sharding :heavy_division_sign: :desktop_computer:
    - - Unique
      - Primary Key
      - Foreign Key
      - Default
      - Check
      - Not Null
      - Types
      - Referential Integrity
    - - MySQL
      - PostgreSQL
      - SQL Server
      - SQLite
    - - UNION ALL :crossed_swords: UNION
      - CTE to filter before JOIN
      - Avoid SELECT *
      - Use LIMIT or TOP
      - EXISTS :crossed_swords: IN
      - Use appropriate Datatype
      - Don's use functions in WHERE clauses
      - Use wildcard ONLY at the end of a phrase 'A%'
      - Use INDEXes
        (for OLTP only)
  - - - YAML
      - XML
      - JSON
    - - Key-value store
        
        Zoom In
      - Document store
        
        Zoom In
      - Columnar database
        
        Zoom In
      - Graph database
        
        Zoom In
- - - - Histograms
        
        Modality (Peaks)
        
        Zoom In
        
        Skewness (Symmetry)
        
        Zoom In
        
        Kurtosis
        
        Zoom In
        
        Binwidth
        
        Not informative
        
        Good
        
        Noisy
      - Box Plots
        
        Mid-line
        
        Zoom In
        
        Box
        
        Whiskers
        (horizontal lines)
        
        Zoom In
        
        Inter-Quartile Range (IQR)
      - Scatter Plot
        
        Correlation
        
        Zoom In
        
        Trend Lines
        
        Zoom In
        
        Logarithmic Scale
        
        Concentration of data points
        
        Zoom In
        
        Better vizualization with log scale
        
        Zoom In
        
        Formula
        
        Zoom In
      - Line Plot
        
        Comparing multiple lines
        
        Zoom In
        
        Trend Lines + Log Scale
        
        Zoom In
      - Bar Plots
        
        Vertical
        
        Zoom In
        
        Horizontal (easier to read)
        
        Zoom In
        
        Percentages (stacked bars)
        
        Zoom In
        
        :red_cross: Logarithmic Scale
      - Dot Plots
        
        Order
        
        Zoom In
        
        Bar x Dot plot
        
        Zoom In
        
        :check: Log Scale
        
        Zoom In
        
        Multiple dots
        
        Zoom In
    - - Pair Plots
        
        Zoon In
      - Correlation Heatmaps
        
        Zoon In
      - Parallel Coordinates Plots
        
        All together is a mess
        
        Zoom In
        
        Division into groups
        
        Zoom In
    - - Pie Plots
        
        Beautiful!
        BUT HARD TO READ
        
        Zoom In
        
        ...compared to bar plot
        
        Zoom In
        
        In circular data...
        
        Zoom In
        
        ...maybe they can be useful
        
        Zoom In
  - - - Color
        
        Zoom In
        
        Properties
        
        Hue
        
        Unordered categories
        
        Zoom In
        
        Chroma
        
        Ordered categories
        
        Zoom In
        
        Diverging categories
        
        Zoom In
        
        Luminance
        
        Viridis palettes
        
        Zoom In
        
        🎨 Pre-set color packs
        
        :eye: Color-blind friendly
      - Size
        
        Zoom In
      - Transparency
        
        Zoom In
      - Shapes
        
        Zoom In
      - Several Panels
        
        Zoom In
    - - Color
        
        Zoom In
      - Line Type
        
        Zoom In
- - - - examples
        
        Amazon Redshift
        
        Google BigQuery
        
        Snowflake
      - Structured
      - smaller volume
      - Purpose
        
        Analysis
      - Update frequency
        
        Pipeline schedule
      - Querying
        
        Fast
      - Data from the whole
        organization 🏢
        
        child
    - - All types
      - BIG VOLUME
      - examples
        
        s3 + Redshift
    - - Purpose
        
        Store data
        
        Quick access
      - Update frequency
        
        Real time
      - Querying
        
        Slow
      - examples
        
        Workday
      - :two_hearts:
        
        Data Lakehouses
        
        Examples
        
        Redshift Spectrum
    - - Data from a department 🏠
    - - Users
        
        level of experience
        
        business x scientists
      - Use cases
        
        quick
        insightful
        analytics
        
        complexmachine learning
      - Volume x Organization
      - Costs
        
        Data Lakes are cheaper
        
        Data Warehouses are expensive
  - - - Analytics Optimized
      - Zoom In
      - Compressing data optimized
    - - Transactions Optimized
      - Zoom In
- - - - Data Staging Layer
        
        :arrow_right:
        
        Data Storage Layer
        
        :arrow_right:
        
        Data Presentation Layer
        
        Power BI
        
        Amazon QuickSight
        
        Tableau
        
        Data Warehouse
        
        :arrow_right:
        
        Inmon top-down
        
        Data Marts
        
        :arrow_left:
        
        Kimball bottom-up
        
        1 more item...
        
        ETL
        
        Temporary storage
- - - - real time
      - hourly
      - daily
      - monthly
    - - PostgreSQL
      - MySQL
      - CSV
      - MongoDB
    - - PII anonymization
      - Access control