147 ) Working with NO SQL Databases

 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.