212 What is Medallion Architecture?

 212 What is Medallion Architecture?

Medallion Architecture is a data design pattern used to logically organize data in a data lake or lakehouse (such as Databricks or Snowflake). It structures data storage in progressive layers—traditionally called Bronze, Silver, and Gold—to steadily improve the quality, structure, and usability of data as it flows through the pipeline.

• Bronze Layer (Raw): Ingests raw data directly from source systems in its original format (JSON, CSV, CDC streams). Data is append-only and acts as a historical audit trail.

• Silver Layer (Cleaned & Conformed): Cleanses, normalizes, deduplicates, and structures the data (often into relational tables or dimensional models). It provides an enterprise "single source of truth."

• Gold Layer (Curated & Aggregated): Highly refined, business-level data optimized for consumption by BI tools, dashboards, data marts, and machine learning models (e.g., star schemas, fact/dimension tables, or aggregated rollups).

How to Do ETL and Data Modeling on Medallion Architecture

Implementing ETL and data modeling across the medallion layers follows a sequential transformation path:

Step 1: Extract and Load into the Bronze Layer (Raw Ingestion)

• ETL Action: Pull raw data from operational databases, APIs, or event streams and land it into the Bronze layer storage (e.g., cloud object storage or raw lakehouse tables) with minimal transformation.

• Data Modeling: None required. Maintain schema-on-read or raw append-only structures with metadata columns (e.g., ingestion_timestamp, source_file_name).

Step 2: Transform Bronze to Silver (Cleansing & Normalization)

• ETL Action: Read from Bronze, parse payloads, handle missing values, filter out corrupt records, deduplicate rows, and apply enterprise data types.

• Data Modeling:

  • Apply relational database principles (3NF or normalized structures) or stage data for dimensional modeling.

  • Implement Change Data Capture (CDC) tracking and handle Slowly Changing Dimensions (SCD Types 1 and 2).

Step 3: Transform Silver to Gold (Dimensional Modeling & Aggregation)

• ETL Action: Read clean data from the Silver layer, perform enterprise joins, compute business metrics, and load into consumption-ready structures.

• Data Modeling:

  • Build dimensional models (Star Schema or Snowflake Schema).

  • Design Fact Tables (at atomic or transaction-level grain, e.g., Policy_ID, Sale_Date, Customer_ID) and Dimension Tables (Customer, Agent, Branch).

  • Create pre-aggregated summary tables or data marts for high-performance reporting.

211 ) How do you calculate the size of each table

 211  ) How do you calculate the size of each table 

What are the datatypes and their Memory size calculation 

https://data-modeling-made-easy.blogspot.com/2024/07/sql-data-types-and-sizes.html

210) Did you work on NOSQL database

 210  Did you work on NOSQL database

 )

--------------------------------------------------------------------------------- Working with NO SQL Databases

 ---------------------------------------------------------------------------------

Wide-Column Stores: Organizes data into column families for massive horizontal scaling and time-series logging (e.g., Apache Cassandra, Google Cloud Bigtable).

Working with NoSQL Databases," provides a comprehensive guide to NoSQL storage models, querying techniques, database migration, and a deep dive into Apache Cassandra.

------------------------------------------------------------------------------------

The key sections are summarized below:

1. Types of NoSQL Databases

• Document Databases (e.g., MongoDB, Couchbase): Stores data as semi-structured JSON/BSON documents instead of rigid rows and columns. Ideal for content management systems and user profiles.

• Key-Value Stores (e.g., Redis, DynamoDB): The simplest model, pairing unique keys with values. Ideal for caching layers, sessions, and real-time leaderboards.

• Wide-Column Stores (e.g., Cassandra, Bigtable): Groups data into column families rather than rows, allowing massive horizontal scaling. Ideal for IoT logging and time-series data.

• Graph Databases (e.g., Neo4j, Neptune): Uses nodes, edges, and properties to represent interconnected data. Ideal for fraud detection, recommendation engines, and social networks.

2. Working with NoSQL Databases (Queries)

Unlike relational databases that rely heavily on standard SQL, NoSQL systems use APIs, domain-specific drivers, or proprietary query languages matching their storage model:

• Document (MongoDB): Uses native drivers or shells (e.g., db.users.find({ username: "john_doe" })).

• Key-Value (Redis): Uses simple CLI commands like SET and GET.

• Wide-Column (Cassandra): Uses Cassandra Query Language (CQL), which resembles SQL but requires strict partitioning rules.

3. Migrating NoSQL to a Relational Database

Moving flexible or nested NoSQL structures into rigid relational tables requires flattening the data through a multi-step process:

1. Schema Mapping & Normalization: Break down nested arrays/JSON objects into parent and child tables linked by foreign keys.

2. Extraction: Export data into flat files (e.g., CSV, JSON) using native tools like mongoexport.

3. Transformation & Flattening: Parse attributes, handle missing fields, cast data types, and map primary/foreign keys using Python/Pandas or staging scripts.

4. Loading: Bulk insert the cleaned tabular data into the target RDBMS (e.g., PostgreSQL, MySQL).

5. Validation: Run row-count reconciliation and integrity checks to ensure zero data loss.

4. Deep Dive: Apache Cassandra

• Storage Architecture: Uses a distributed, masterless structure consisting of Keyspaces, Tables, Partitions (determined by hashing a partition key), and Clustering Columns (for sorting rows within a partition). Physically writes data sequentially to a CommitLog and Memtable, flushing them into immutable SSTables.

• Project Example: Frequently used in high-velocity banking/fraud-detection environments where relational databases fail to scale horizontally for real-time write streams.

• Migrating Cassandra to Snowflake: Involves redesigning wide Cassandra structures into star-schema dimension and fact tables, exporting data into flat files, staging them in cloud storage (AWS S3/Azure Blob), bulk-loading them into Snowflake using COPY INTO, and performing data reconciliation.

------------------------------------------------------------------------------------

1. Types of NoSQL Databases Available in the Market

NoSQL databases are broadly categorized into four primary types based on their data storage model:

• Document Databases
• How they store data: Stores data as semi-structured documents (JSON, BSON, XML) rather than rigid rows and columns.
• Use Cases: Content management systems, catalogs, and user profiles.
• Market Examples: MongoDB, Couchbase, Amazon DocumentDB.
• Key-Value Stores
• How they store data: The simplest NoSQL structure, storing data as a collection of unique keys paired with values (strings, blobs, or JSON).
• Use Cases: Caching layers, session management, and real-time leaderboards.
• Market Examples: Redis, Amazon DynamoDB, Memcached.
• Wide-Column Stores (Column-Family)
• How they store data: Stores data in column families grouped together rather than traditional rows, allowing massive horizontal scaling across distributed nodes.
• Use Cases: Time-series data, Internet of Things (IoT) logging, and high-velocity transaction tracking.
• Market Examples: Apache Cassandra, ScyllaDB, Google Cloud Bigtable.
• Graph Databases
• How they store data: Uses nodes, edges, and properties to represent and store interconnected data relationships directly.
• Use Cases: Fraud detection, social networks, recommendation engines, and network topology mapping.
• Market Examples: Neo4j, Amazon Neptune, ArangoDB.

2. How to Work with NoSQL Databases (Running Queries)

Unlike relational databases that rely heavily on ANSI SQL, NoSQL systems use APIs, proprietary query languages, or domain-specific query drivers matching their storage model.

Example A: Working with a Document Database (MongoDB)

• Query Concept: Fetching a user profile where the username matches a specific value using the native MongoDB driver or shell.
• Query Command:

JavaScript

// MongoDB shell query

db.users.find({ username: "john_doe" });

Example B: Working with a Key-Value Store (Redis)

• Query Concept: Setting a cache value and retrieving it instantly via its unique key.
• Query Command:

Bash

# Redis CLI commands

SET session:1001 "active_user_token"

GET session:1001

Example C: Working with a Wide-Column Store (Cassandra)

• Query Concept: Using Cassandra Query Language (CQL), which resembles SQL but requires strict partitioning rules.
• Query Command:

SQL

-- Cassandra CQL query

SELECT * FROM sensor_logs

WHERE sensor_id = 'A-402' AND date = '2026-08-01';

3. How to Migrate NoSQL Data to a Relational Database

Migrating semi-structured or unstructured NoSQL data into a rigid relational database (RDBMS) requires transforming nested documents or flexible attributes into a normalized or flattened tabular structure.

Step-by-Step Migration Process:

1. Schema Mapping & Normalization Design:
• Break down nested JSON arrays or embedded objects from the NoSQL store into separate relational child tables linked by foreign keys and surrogate keys.
• Example: A MongoDB document containing an array of multiple addresses must be split into a parent users table and a child user_addresses table.
2. Extraction (Exporting from NoSQL):
• Export data from the NoSQL source into an intermediate structured format (such as CSV, newline-delimited JSON, or flat files) using native export utilities.
• Example (MongoDB):

Bash

mongoexport --collection=users --out=users_export.json

3. Transformation & Flattening:
• Write data transformation scripts (using Python with Pandas, or SQL-based staging tables) to parse JSON attributes, handle missing fields, cast data types, and generate proper primary/foreign key relationships.
4. Loading into the Relational Database:
• Load the cleaned and flattened tabular data into the target RDBMS (e.g., PostgreSQL, SQL Server, or MySQL) using bulk insert tools, ETL pipelines, or staging schemas.
• Example (PostgreSQL Bulk Copy):

SQL

COPY users(user_id, username, email)

FROM '/path/to/users_export.csv'

WITH FORMAT csv, HEADER true;

5. Validation and Integrity Checks:
• Run row-count reconciliation, data type validation, and relational integrity queries (checking foreign key matches and completeness) to ensure no data loss occurred during the transition from the flexible NoSQL store to the rigid relational structure.

  ----------------------

How to work with CASSANDRA ?

1. How Data is Stored in Cassandra (Format)

Apache Cassandra is a distributed wide-column NoSQL database. Unlike traditional relational databases that store data in rows on pages, Cassandra organizes data physically and logically using a decentralized, highly optimized structure:

• Logical Format (Keyspace -> Table -> Partition -> Rows -> Columns):

  • Keyspace: The highest-level container (similar to a database in RDBMS) that defines replication settings and strategies.

  • Table: A structured collection of rows and columns within a keyspace.

  • Partition: The fundamental unit of data storage. Every table has a Partition Key. Cassandra uses a hashing algorithm (Murmur3) on the partition key to determine which node in the cluster holds that specific partition. All rows with the same partition key are stored physically together on disk.

  • Clustering Columns: Within a partition, rows are sorted and stored in disk order based on defined clustering columns, allowing extremely fast range queries.

• Physical Format on Disk:

  • CommitLog: When a write arrives, it is first appended sequentially to an on-disk commit log for durability.

  • Memtable: Simultaneously, the write is written to an in-memory structure called a memtable.

  • SSTables (Sorted String Tables): When the memtable fills up, it is flushed to disk as an immutable SSTable. Because SSTables are immutable, Cassandra never overwrites existing data in place; updates and deletes write new timestamps, and background processes (Compaction) periodically merge and clean up old SSTables.

2. Cassandra Commands: Database Creation, Tables, Inserts, and Queries

Cassandra uses CQL (Cassandra Query Language), which syntactically resembles SQL but operates under strict NoSQL distributed storage rules.

Step A: Create a Keyspace (Database)

SQL

CREATE KEYSPACE banking_keyspace 
WITH replication = {
    'class': 'SimpleStrategy', 
    'replication_factor': 1
};

Step B: Use the Keyspace & Create a Table

Note: In Cassandra, the primary key consists of a Partition Key (determines the node/cluster distribution) and optional Clustering Columns (determines internal sort order).

SQL

USE banking_keyspace;

CREATE TABLE account_transactions (
    branch_id text,
    account_id uuid,
    transaction_date date,
    transaction_id timeuuid,
    amount decimal,
    transaction_type text,
    PRIMARY KEY ((branch_id, transaction_date), account_id, transaction_id)
);

(Explanation: branch_id and transaction_date form the composite partition key, while account_id and transaction_id act as clustering columns).

Step C: Store Data (Inserts)

SQL

INSERT INTO account_transactions (branch_id, account_id, transaction_date, transaction_id, amount, transaction_type) 
VALUES ('HYD-01', 550e8400-e29b-41d4-a716-446655440000, '2026-08-01', now(), 1500.00, 'DEPOSIT');

INSERT INTO account_transactions (branch_id, account_id, transaction_date, transaction_id, amount, transaction_type) 
VALUES ('HYD-01', 550e8400-e29b-41d4-a716-446655440001, '2026-08-01', now(), 250.50, 'WITHDRAWAL');

Step D: Run Queries

Rule: Cassandra requires queries to include the partition key in the WHERE clause to avoid unindexed, expensive cluster-wide scans.

SQL

-- Query transactions for a specific branch on a specific date
SELECT * FROM account_transactions 
WHERE branch_id = 'HYD-01' AND transaction_date = '2026-08-01';

3. Project Example: Real-Time Fraud & Audit Logging System

• Project Scenario: A major banking platform logs millions of high-velocity customer transactions per second.

• Why Cassandra was chosen: Relational databases fail to handle horizontal write scaling for millions of fast-incoming transaction streams. Cassandra provides masterless, high-availability multi-node scaling, ensuring zero downtime during peak banking hours.

• Architecture Flow:

  1. Customers execute card swipes or mobile app transfers.

  2. Ingestion streams push raw payloads into Cassandra (account_transactions table partitioned by branch and date).

  3. Fraud analytics engines read live partitions to detect abnormal withdrawal spikes.

4. Steps to Migrate Cassandra to Snowflake

Migrating from a wide-column NoSQL store (Cassandra) to a cloud data warehouse (Snowflake) requires flattening wide or nested structures into tabular format for analytical querying.

Step 1: Schema Mapping and Redesigned Modeling

• Analyze Cassandra tables and flatten composite primary keys, collections, or wide columns into normalized or dimensional star-schema models (dim_ and fact_ tables) optimized for Snowflake's columnar storage.

Step 2: Extract Data from Cassandra

• Export data from Cassandra into intermediate flat files (CSV or newline-delimited JSON) using native export tools like Cassandra Copy or custom Spark extraction jobs.

  Bash

  # Example using cqlsh COPY command to export to CSV
  cqlsh -e "COPY banking_keyspace.account_transactions TO 'transactions_export.csv' WITH HEADER=TRUE;"
  

Step 3: Stage Files in Cloud Storage (AWS S3 / Azure Blob)

• Upload the exported flat files from step 2 into an intermediate cloud storage staging bucket (e.g., Amazon S3 or Azure Blob Storage) matching your Snowflake environment deployment.

Step 4: Load Data into Snowflake via Staging

• Create target relational tables in Snowflake and ingest the staged files using Snowflake's bulk loading command (COPY INTO).

  SQL

  -- Create target table in Snowflake
  CREATE TABLE snowflake_transactions (
      branch_id VARCHAR(50),
      account_id VARCHAR(50),
      transaction_date DATE,
      transaction_id VARCHAR(50),
      amount DECIMAL(18,2),
      transaction_type VARCHAR(20)
  );
  
  -- Copy from cloud storage staging into Snowflake
  COPY INTO snowflake_transactions
  FROM @my_s3_stage/transactions_export.csv
  FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
  

Step 5: Validation and Reconciliation

• Execute row-count reconciliation, financial total aggregations, and data type validation checks between Cassandra source counts and Snowflake target records to confirm a zero-loss migration.