Skip to content

Latest commit

 

History

History
40 lines (29 loc) · 1.95 KB

README.md

File metadata and controls

40 lines (29 loc) · 1.95 KB
Raw

Traders

  • A modern data warehouse derived from a web-platform relational database.
  • This tutorial project implemented locally using WSL ubuntu and postgresql as a dev-layer.
  • The postgresql database "Traders" holds several schemas layers 'dataset' for the source database and for the target data warehouse.
  • Some small tables from the source database have been replaced with seed constant data and others have been denormalized.
  • Pre-commit sqlfluff rules implemented to align sql with postgres standard syntax.

Draft diagram

Traders Data warehouse layers

  1. Source relational database.
  2. Staging layer contains dbt denormalized view models 'stg_'
  3. Vault layer, a data vault modelling is implemented'vlt_'

Draft diagram

  1. Semantic layer, fact and dimension tables representing a star schema models 'sem_'

Draft diagram

Additional notes:

  • When working with big data, it's crucial to utilize distributed computing storage with columnar reading capabilities, such as BigQuery or Synapse, to leverage partitioning for enhanced performance, reduced query latency, and cost optimization.

Advantages of Data Warehouse:

  • Scalability: They can handle massive amounts of data.
  • Performance: Fast BI reports and complex analysis.
  • Consistency: Ensures data consistency across the organization.
  • Flexibility: Data warehouses can be adapted to changing business needs.

Useful commands:

poetry shell 
poetry run pre-commit run sqlfluff-fix --files /RELATIVE_PATH