DataLake Vs Datawarehouse

DataLakes and DataWarehouses are used to store data but they are not interchangeable. In simple words , datalakes store raw data  whereas the data warehouses store processed data (Cleansed, Filtered, Aggregated etc..). 

Key Differences 

Data Lake                    Vs       Datawarehouse
Raw Data                                 Processed Data
More Storage                            Storage is less comparable to Data Lake
Usage not yet determined          In Use
Date Scientist                           Business Professionals
Data Prep/ Additional Tools         Reports & Dashboards
needed
Not structured                         Structured and schema well defined

will be continued with example soon..

Continue Reading

Cumulative Addition in SQL

Cumulative Addition in SQL 

create table product(id int, value int);

insert into product values(1,500);
insert into product values(2,200);
insert into product values(3,400);


Method 1 : Self Join
Select a.id,a.value,sum(b.value) cvalue from product a ,product b
where
a.id>=b.id
group by a.id,a.value order by 1 ;


Method 2: Analytic Function

select id, value,sum(value) over (order by id) from product order by 1 ;

Method 3: Correlated Column Query

Select id, value , (select sum(value) from product where id<=p.id) cvalue
from product p ;



create table product1(id int, value int);

insert into product1 values(1,500);
insert into product1 values(2,200);
insert into product1 values(3,400);


Select * from product1;

with cte as
(select * from product order by id)
select a.id,a.value , sum(b.value) cvalue from cte a ,cte b where a.id>=b.id
group by a.id,a.value order by 1 ; 

Continue Reading