Showing posts with label SQL. Show all posts

Snowflake Database — A Beginner-Friendly Guide

1. What is Snowflake?

Snowflake is a cloud-native data warehouse designed for storing, processing, and analyzing large-scale data.
Unlike traditional databases, Snowflake is:

Fully managed
Extremely fast and scalable
Designed only for cloud
Supports structured, semi-structured, and unstructured data

It runs on AWS, Azure, and GCP without you worrying about servers.

2. Why Snowflake Is Different

Feature	Traditional DB (MySQL/Postgres)	Snowflake
Compute + Storage	Tied together	Fully separate
Scaling	Hard, slow	Instant, elastic
Performance	Slows with big data	Optimized for huge datasets
Maintenance	Manual tuning required	Zero management
Concurrency	Limited	Nearly unlimited
Semi-Structured	Basic support	Native JSON, Parquet, Avro

3. How Snowflake Stores Data Internally

Snowflake stores all data in compressed, columnar micro-partitions (50–500 MB each).
Each partition includes metadata such as:

Min/max values
Statistics
Row count

This lets Snowflake skip entire chunks of data during queries → much faster.

Internally:


+-----------------------------------------------+
|                Micro-Partition 1              |
|  - Columnar data                              |
|  - Compressed                                 |
|  - Stats (min/max/values)                     |
+-----------------------------------------------+
+-----------------------------------------------+
|                Micro-Partition 2              |
|  ...                                          |
+-----------------------------------------------+
              ⬇ Stored in cloud
 AWS S3 / Azure Blob / Google Cloud Storage

4. Snowflake Architecture (Text-Based Diagram)

The core of Snowflake is a 3-layer architecture.
Here is a clean text-based diagram:


                     ┌────────────────────────────┐
                     │      Cloud Services Layer   │
                     │-----------------------------│
                     │ Authentication & Security   │
                     │ Metadata & Catalog          │
                     │ Query Optimization          │
                     │ Transaction Management      │
                     └───────────▲────────────────┘
                                 │
                                 │
                   ┌─────────────┴───────────────┐
                   │     Compute Layer (VMs)      │
                   │------------------------------│
                   │   Virtual Warehouses         │
                   │   - Independent compute      │
                   │   - Auto-scale / auto-pause  │
                   │   - No contention            │
                   └────────────▲─────────────────┘
                                 │
                                 │
                  ┌──────────────┴────────────────┐
                  │        Storage Layer           │
                  │-------------------------------│
                  │   Micro-partitions (Columnar) │
                  │   Compression & Encryption     │
                  │   Stored in Cloud Storage      │
                  │   (S3 / Blob / GCS)            │
                  └────────────────────────────────┘

Explanation of the layers:

1️⃣ Storage Layer

Data is stored in micro-partitions
Automatically compressed and optimized
Very cheap because it uses cloud object storage

2️⃣ Compute Layer

Queries run on Virtual Warehouses
You can create multiple warehouses for different workloads
Compute never affects other teams (no blocking)

3️⃣ Cloud Services Layer

Manages metadata
Handles SQL compilation
Provides security & governance
Controls access, optimization, caching

5. Why Snowflake is Special

✔ Separation of Storage and Compute

Scale storage and compute independently.

✔ Zero Maintenance

No indexing, partitioning, tuning, vacuuming.

✔ Time Travel

Look at data from 1–90 days in the past.

✔ Native Support for JSON/Parquet

Query JSON directly using SQL.

✔ Instant Elasticity

Warehouse can auto-scale within seconds.

✔ Global Data Sharing

Share live data without copying.

6. Advantages (Pros)

👍 1. Extremely Fast

Columnar storage + micro partitions = lightning performance.

👍 2. Highly Scalable

Run XS, S, M, L, XL warehouses depending on load.

👍 3. Pay Only for What You Use

Pause compute → no extra charges.

👍 4. Multi-Cloud Support

Move across AWS, Azure, GCP without rewrite.

👍 5. Easy Integration

Kafka, Spark, Airflow, dbt, BI tools (Tableau/Looker).

👍 6. Strong Security

Auto-encryption, IAM, SSO, MFA, roles.

7. Limitations (Cons)

👎 Not for OLTP

Snowflake is not good for small frequent transactions.

👎 Compute Cost Can Grow

If warehouses run 24/7 accidentally.

👎 Vendor Lock-In

Difficult to migrate out due to architecture.

👎 Not Very Good for Real-Time

Sub-second latency workloads need other systems.

8. Snowflake vs Other Databases

▶ Snowflake vs PostgreSQL

Postgres is for OLTP
Snowflake is for analytics

▶ Snowflake vs Redshift

Snowflake separates compute & storage
Redshift requires cluster resizing

▶ Snowflake vs BigQuery

Snowflake uses warehouse model
BigQuery is serverless “pay per query”

9. When to Use Snowflake

Use it when you need:
✔ Analytics
✔ BI dashboards
✔ ETL/ELT
✔ Data lakes
✔ Huge datasets (TB–PB)
✔ Multi-team workloads

Don’t use it for:
✘ Banking transactions
✘ Real-time transactional apps
✘ High-frequency small writes

10. Summary

Snowflake is a modern, cloud-native data warehouse that offers:

Micro-partition storage
Separation of compute & storage
Blazing-fast analytics
Zero management
Time-travel
Multi-cloud support
Massive parallelism

It is ideal for any company that needs fast analytics on large data without managing servers.

How to create Procedure in MySql?

1.Create a database and procedure


CREATE DATABASE `pretech`

DELIMITER $$
DROP PROCEDURE IF EXISTS `pretech`.`Helloworld` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Helloworld`()
BEGIN
 Select 'HELLO WORLD';
END $$
DELIMITER ;

2. Run it


CALL Helloworld()

3.Call procedure

Comparison of SQL Delete, Truncate and Drop

Delete

It is a DML statement
Rollback is possible after execution
Triggers can fire out
Where clause can use for condition
Table structure will exists

Truncate

It is a DDL statement
Rollback not possible
Trigger fire out not possible
Cant apply where clause
It is very fast compare to delete
Table structure will exists

Drop

Table structure will not exists
Roll back not possible

SQL Joins Simple examples

Here are some simple examples for SQL joins.(Inner,Outer,Full). I hope this will help to understand the basics of SQL joins.

Tables Used

Below two tables used to describe the queries (pretechEmployee and pretechDepartment)

INNER JOIN

Inner join is using the fetch the common records from both the tables, here i want to fetch the records which are there in the both employee and department table.

SELECT * FROM pretechEmployee e1 INNER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

LEFT OUTER JOIN

As its names says it is using to fetch all records of employee table(left) and fetch the department details of employees

SELECT * FROM pretechEmployee e1 LEFT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

See one scenario, I want the employee details who is not under any department see below query.

SELECT * FROM pretechEmployee e1 LEFT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id WHERE d1.emp_id is null

RIGHT OUTER JOIN

Right outer join help us to fetch all records of department (Right) and the employee details who all are having the department.

SELECT * FROM pretechEmployee e1 RIGHT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

SELECT * FROM pretechEmployee e1 RIGHT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id WHERE e1.emp_id is null

FULL OUTER JOIN

Full outer join will fetch all the records from employee and Departments.

SELECT * FROM pretechEmployee e1 FULL OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

Below full outer join query will fetch all the records from employee and department except employees are in department

How to create Triggers in Oracle

List all triggers

SELECT * FROM USER_TRIGGERS

Create Trigger

CREATE TRIGGER TRI_PRETECHEMPLOYEE
BEFORE INSERT OR UPDATE ON PRETECHEMPLOYEE
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Trigger started');
END;