← Home

SQL Databricks Data Warehouse

Table of Contents

About

Github

GitHub - maithtruong/databricks-data-warehouse: Building a Data Warehouse for a sportswear company.

Building a Data Warehouse for a sportswear company. - maithtruong/databricks-data-warehouse

https://github.com/maithtruong/databricks-data-warehouse

Tech Stack

• Databricks

• dbt

• SQL

Introduction

Requirement Analysis

Context

• ERP - SAP: for internal operations management

• CRM - Salesforce: for customer interactions management

Project Requirements

Data Architecture Design

• Data Warehouse
• Suitable for structured, relational data and analytical workloads.

• Medallion Architecture
• Provides clear separation of concerns and progressive data refinement across layers.

Choose Architecture Type

Choose Data Warehouse Approach

• Inmon Approach
• Sources → Staging → Enterprise Data Warehouse (3NF) → Data Marts → BI Layers
• EDW:
• Data Marts: Small subsets of the EDW, ready to be consumed for reporting. Ex: Data marts for customers, sales, products, etc.
• Slower, more complex to model

• Kimball Approach
• Sources → Staging →→→→→→ → → → → → → → → → Data Marts → BI Layers
• Faster shipping speed
• Tradeoff in data consistency

• Data Vault
• Sources → Staging →→ Raw Vault →→Business Vault →→ Data Marts → BI Layers
• Splits the central layer into more layers
• Raw Vault: Original data
• Business Vault: All business rules and transformations to prepare for the data marts.

• Medallion Architecture
• Sources → Bronze →→ Silver →→Gold → BI Layers
• Bronze: As-is data, similar to staging layer.
• Silver: Clean and transformed data.
• Gold: Business-level data models (similar to data marts), can build different business-ready objects. Used for reporting, ML, AI, etc.

Design Data Layers

Separations of Concerns

• Break down complex systems into smaller parts

• Each part responsible for specific tasks

• Each component must not be duplicated

Draw the Data Architecture

Project Start

General Principles

• Name Convention: snake_case, use lowercase letters and underscores to separate words.

• Language: English

• Avoid reserved words

Naming Conventions

Set of rules or guidelines for naming anything in the project.

• Database

• Schema

• Tables

• Store procedures

Catalog Naming Conventions

• All catalog names must start with its primary function and suffixed by its environment type.

• <function>_<environment>
• Ex: core_dev
• The data warehouse for core operations, used in development.

Table Naming Conventions

Bronze Rules

• All names must start with the source system name.

• Table names must correspond to their original name.

• Entity names are not abbreviated, are nouns, and are in singular forms.

• Ex:
• crm_cust_info

Silver Rules

• All names must start with the source system name.

• Table names must correspond to their original name.

• Entity names are not abbreviated, are nouns, and are in singular forms.

• <source_system>_<entity>
• Ex: crm_cust_info

Gold Rules

• All names must use meaningful, business-aligned names for tables, starting with the category prefix.

• Entity names are not abbreviated, are nouns, and are in singular forms.

• <category>_<entity>
• Ex: dim_customer

Glossary of Category Patterns

Column Naming Conventions

Surrogate Keys

• All primary keys in dimension tables must use the suffix _key

• <entity_name>_key
• Ex: customer_key → surrogate key in the dim_customer table

Technical Columns

• All technical columns must start with the prefix dwh_, followed by a descriptive name indicating the column’s purpose.

• dwh_<column_name>
• Ex: dwh_load_date → Column used to store the date when the record was loaded, generated by the system.

Stored Procedure

• load_<layer>
• Ex: load_bronze → stored procedure for loading data into the bronze layer.

Build the Data Warehouse

Create the Volumes, Catalog and Schemas

Build Bronze Layer

Workflow

• Analyze: Interview source system experts

• Code: Ingest data
• Load source files
• Create data schema
• Insert records (full load) from source files

• Code: Data completeness & schema checks

• Docs: Document & do versioning in git

Analyze: Source Systems

• Business Context & Ownership
• Who owns the data?
• Understand the source of the data
• What Business Process it supports?
• Understand the importance of the data
• System & Data documentation
• Save time when building data models
• Data Model & Data Catalog
• Save time when building data models by reading existing table and column descriptions.

• Architecture & Technology Stack
• How is data stored? (SQL Server, Oracle, AWS, Azure, etc.)
• What are the integration capabilities? (API, Kafka, File Extract, Direct DB, etc.)

• Extract & Load
• Incremental vs. Full Loads?
• Data Scope & Historical Needs
• Whether we need all data or just a subset of the entire data history.
• Whether we need to build data history.
• What is the expected size of the extracts? (MB, GB, TB)
• Understand whether we have the right tools
• Are there any data volume limitations?
• Conform to limitations and capability of the source system.
• How to avoid impacting the source system’s performance?
• Authentication and Authorization (tokens, SSH keys, VPN, IP whitelisting, …)

Understanding the data structure is done by 2 primary methods:

• Doing interviews with source system owners (developers)

• Doing data profiling on your own

Code: Ingest data

• Create the core_dev catalog

• Create a new volume called source

• Create bronze, silver and gold schemas

• Create 6 tables based on the original source file names
• crm_cust_info
• crm_prd_info
• crm_sales_details
• erp_cust_az12
• erp_loc_a101
• erp_px_cat_g1v2

Methods to ingest

• Bulk Insert
• Massively load raw files (txt, csv) directly into the database.

• Insert
• Load data row-by-row into the database.

• Full Load
• Rewrite all old data with the newest version of the data.

• Incremental Load
• Incremental batch load
• Inserts data into the database at regular intervals.
• Incremental stream load
• Inserts data into the database when new data emerges or old data is updated.

• New versions of the data (in .csv format) is pulled into the source/ volume in Databricks.

• Current tables are dropped and the newest version of the data is rewritten onto the tables in the bronze schema.

Stored Procedure

A precompiled SQL code that can be saved and reused.

• Unlike a saved query: Stored procedures
• can be reused with new parameter inputs.

• Unlike a UDF (user-defined function): Stored procedures
• can return 0, 1 or many values
• accept DML operations
• can manage transactions
• can call other stored procedures

COPY INTO in Databricks

This is a statement that does bulk insert (similar to INSERT INTO in MySQL). However, this operation is:

• retryable

• idempotent (the property of certain operations in mathematics and computer science whereby they can be applied multiple times without changing the result beyond the initial application, by Wikipedia)

Which means if files in the source location have been loaded, then are skipped (even if the files have been modified).

Alternative ingestion methods

This project adopts a simple ingestion method. I think there are more efficient ways to approach this, but I will adopt them in a different project. Some changes I can think of as of now:

• Skip the source volume and pull data directly to bronze. This reduces half of the storage needed for raw data.

• Use incremental load instead of full load. It is much faster and more storage efficient (it comes with some downsides, though).

Docs: Data Flow

Data Flow
Describes the data lineage (data dependency through each layer).

Build Silver Layer

Workflow

• Analyze: Explore & understand data
• Find issues in bronze data tables and log in bronze/profile_bronze script.
• Find table relationships and document in an integration diagram.

• Code: Ingest data
• Define silver tables’ schema.
• Standardize silver data and insert into tables based on findings from bronze/profile_bronze.

• Code: Data quality checks
• Perform manual data quality checks on the newly created silver tables, store scripts in scripts/silver/test_silver.

• Code: Automate data quality checks
• Create automated tests on silver data in dbt.

• Docs: Document & do versioning in git

Analyze: Explore & Understand Data

• Data Uniqueness Constraint

• Data Validity

• Table Relationships

• …

• About: Customer demographic data from the CRM system.

• Structure: Non-standard, append-only structure (pseudo SCD Type 2 behavior).
• No exact duplicate rows.
• cst_id and cst_key are not unique due to multiple records over time.
• Multiple records exist for the same (cst_id, cst_key) combination, differentiated by cst_create_date.
• Changes across versions are incremental: null values are progressively filled with valid values (no overwrites).
• No explicit versioning columns (e.g., start/end dates).

• Issues:
• Rows with cst_id IS NULL contain invalid and inconsistent data.
• Redundant historical records where earlier versions contain mostly null values.

• Conclusion:
• Remove rows with null cst_id (data errors).
• Deduplicate by keeping only the latest record per (cst_id, cst_key) based on cst_create_date.
• Treat the table as SCD Type 0 in the Silver layer (no history retained).

• About: Product master data with historical tracking.

• Structure: Proper SCD Type 2 implementation.
• prd_key represents the business entity (product).
• prd_id represents a specific version of the product over time.
• prd_start_dt and prd_end_dt define the validity period.
• Active records have prd_end_dt IS NULL.
• One prd_key can have multiple prd_id values (multiple versions).

• Data Quality Notes:
• No duplicate rows or duplicate (prd_id, prd_key) combinations.
• Some invalid date ranges (prd_start_dt > prd_end_dt).
• Some null or missing values in attributes (e.g., prd_cost).

• Conclusion:
• Retain full history (valid SCD Type 2 design).
• Handle invalid date ranges and null values during transformation.

• About: Sales transaction data.

• Structure: Flattened fact table.
• No duplicate rows.
• No single-column primary key.
• Grain: one row per product per order per customer.
• Composite uniqueness: (sls_ord_num, sls_cust_id, sls_prd_key).

• Relationships:
• One customer → many orders (1:M).
• One order → many products (1:M).
• Products ↔ orders form an M:N relationship (resolved in this table).

• Conclusion:
• Acts as a fact table with defined grain.
• Suitable for direct transformation into a star schema fact table.

• About: Customer attributes (e.g., gender, birthdate) from ERP.

• Structure: Normalized table with unique cid.

• Issues:
• cid format is inconsistent:
• Contains varying prefixes.
• Does not directly match cst_id or cst_key in CRM.
• Matching requires transformation (e.g., substring extraction).
• Partial overlaps and mismatches exist between ERP and CRM identifiers.
• Duplicate cid values exist under different formats.

• Conclusion:
• Normalize cid by removing prefixes to align with CRM identifiers.
• Expect partial mismatches during integration.
• Use transformation logic to standardize joins with CRM data.

• About: Customer location data (country).

• Structure: Normalized table with unique cid.

• Issues:
• cid contains formatting inconsistencies (e.g., dashes ).
• Requires cleaning (e.g., removing ) before joining.
• Matches with CRM data are mostly consistent after normalization, with minor exceptions due to upstream data issues.

• Conclusion:
• Clean cid (remove special characters) before integration.
• Join primarily with CRM (cst_key) after normalization.
• Treat unmatched records as data quality issues.

• About: Product category and subcategory reference data.

• Structure: Normalized lookup table.
• id represents a subcategory (derived from category + subcategory codes).
• No duplicate rows or IDs.

• Relationships:
• id maps to prefixes of prd_key in crm_prd_info.

• Issues:
• Some products (e.g., category CO-PE) do not have matching category records.
• Indicates that this table may be incomplete or outdated.

• Conclusion:
• Use as a category lookup where matches exist.
• Handle unmatched products separately (data gap).
• Consider this table as potentially stale reference data.

Code: Ingest data

Metadata columns

Extra columns added by data engineers that do not originate from the source data.

Common types:

• create_date: The record’s load timestamp.

• update_date: The records’s last update timestamp.

• source_system: The origin system of the record.

• file_location: The file source of the record.

Data Cleansing

Types of data transformations

This diagram summarizes the key operations in data transformation.

• Remove invalid data
• Remove null cst_id
• Remove duplicates (history customer info)
• Handle missing data (using a default value)

• Standardize data
• Map coded values to user-friendly descriptions
• Remove unwanted spaces in string values

• Standardize data
• Map coded values to user-friendly descriptions
• Cast to a suitable type

• Remove invalid data
• Handling missing data (using a default value)

• Data enrichment (add new values to the dataset)

• Standardize data
• Convert to standard convention
• Replace invalid values with placeholder
• Cast to a suitable type
• Derive valid values from upstream columns

• Remove invalid data
• Handle missing data (using a default value)

• Standardize data
• Convert to standard convention
• Map coded values to user-friendly descriptions

• Remove invalid data

• Standardize data
• Map coded values to user-friendly descriptions
• Remove unwanted spaces

Business Rules

• Total Sales = Sum of (Quantity * Price)

• Negatives, zeros and nulls are not allowed.

Code: Automate Data Quality Checks

dbt
An open-source tool for data engineering following software engineering best practices such as testing, versioning and documentation for structured data.

Benefits of using dbt

• Data platform agnostic: dbt uses its own version of SQL and can connect to different data platforms, so any script (such as tests) can be executed regardless of the platform.

• Automated testing: avoid faults and save time creating models. The table will not be created if it does not pass mandatory tests. This ensure our business do not make decisions based on erroneous data.

Build Gold Layer

Workflow

• Analyze: Explore & Understand the Business Object

• Code: Data Integration
• Build the Business Object
• Choose type: Dimension vs. Fact
• Rename to friendly names

• Validate: Data Integration Checks

• Docs: Document & do versioning in git
• Data Model
• Data Catalog
• Data Flow

Analyze

Data Modeling

The process of taking raw data, process and structure it in a meaningful way.

Types of Data Models

• Conceptual Model: Represents core entities in the business and how they relate to one another. Info: entities, relationships.

• Logical Model: Represent data constraints, entity names and relationships in a diagram (platform-agnostic). Info: all in previous level + attributes, keys, cardinality.

• Physical Model: Implement the logical model over a specific database technology. Info: all in previous level + data types, SQL tables, indexes, constraints, etc.

Types of Data Schemas

• Star Schema: Has a central fact table surrounded by denormalized dimension tables.
• Pros: Simple and easy to understand and analyze.
• Cons: Dimensions may contain duplicates, data size rise faster → slower queries.

• Snowflake Schema: Has a central fact table surrounded by dimensions that are normalized into multiple related tables.
• Pros: More complex to understand and analyze.
• Cons: Optimize storage by reducing data redundancy.

Table Types

• Dimension tables: Description information that gives context to data.

• Fact tables: Quantitative information that represents events.

Code

Docs: Data Catalog

Data Catalog
A centralized inventory and management system for metadata. Stakeholders can use it for better understanding of an organization’s data.

• purpose and structure of each table

• column names, types and description

Docs: Data Flow Diagram

Data Flow Diagram
Represents the dependency between data collections (tables) in different layers within a system. It shows how data moves within a system.

References

Sources

Overall Review

Highlights

Next Steps

• Loading technique

• SCD handling technique

• Workflow orchestration technique

• Performance optimization technique

• …

Contributions

Tech Stack Changes

• Data Platform
• Migrated from SQL Server to Databricks for scalability and modern data workflows.

• Data Quality & Testing
• Integrated dbt to automate data validation and enforce data quality constraints.

• Version Control
• Combined Git integration in Databricks and VSCode:
• Databricks for data processing and SQL development
• VSCode for dbt workflows and project management

• Environment Management
• Introduced uv for Python dependency management to ensure reproducibility.

Data Engineering Changes

• Added data profiling scripts to systematically analyze source data.

• Introduced automated testing via dbt and environment management via uv.

• Identified additional data quality issues beyond the original pipeline.

Documentation Enhancements

• Expanded documentation across all layers:
• Data architecture
• Data flow
• Data modeling
• …

• Added detailed explanations for:
• Data engineering concepts
• Transformation logic
• Design decisions
• Workflow structure

Other Projects

Gallery view

Automated Spreadsheet Dashboards

Google Sheets

Analytics Dashboard

Data Analyst

Automate Google Sheets to create dashboards that track teams’ sales leads, Marketing campaigns, sales overview and more.

Sequential Recommender System for Video Recommendations

Recommender Systems

Machine Learning

pytorch

API

FastAPI

Data Scientist

AI Engineer

Employee Attrition Dashboard & Analytics

Power BI

Python

People Analytics

Competition

Analytics Dashboard

Data Analyst

Dashboard that analyzes employee attrition root causes by leveraging a detailed analysis process, advanced Power BI features, and Machine Learning for Churn Prediction.

RAG Chatbot for Flower Shop Recommendations

RAG

MongoDB

Vector search

Flask

API

Python

AI Engineer

A Naive RAG chatbot that recommends flower shop products using semantic search on embedded product data. It features a Streamlit interface, a Scrapy-based data scraper; a backend including MongoDB vector search for retrieval, an LLM via OpenRouter; and a Flask API that connects the frontend with the backend.

SQL Databricks Data Warehouse

Databricks

SQL

dbt

Data Engineer

A Data Warehouse in Databricks using a Medallion architecture (bronze-silver-gold), accompanied by data tests in dbt, which performs automated data quality checks for prompt error handling.

Customer Growth using AI (Segmentation, Churn Prediction, CLV Prediction)

Machine Learning

Statistical Modelling

Python

Streamlit

Flask

Data Scientist

Perform RFM customer segmentation and model churn, survival, and customer lifetime value (CLV) using transactional data to identify high-priority customer groups.

TPC-H Data Warehouse Pipeline

Airflow

dbt

Docker

uv

Python

astronomer

Snowflake

Data Engineer

Modern ELT pipeline using Airflow, dbt, and Snowflake built on the TPC-H benchmark dataset (SF1).

Let’s Work Together