Skip to main content

ETL Features of SQL Data Platform

Either Datawarehouse or BigData, ETL or ELT is the major part, or you can say it plays a vital role in Data Transformation Life cycle. ELT-ETL used in any kind of data related operations like, Data
migration, Data transformation , Business intelligence, Datawarehouse, Big data and Analytics.

 ETL is Extract transform and Load whereas ELT is extract load then transform. Both have their own pros and cons but now a days ELT is more popular than ETL. This is why because size of data is growing & growing for transformation. ELT also supporting Data virtualization concept where actual original data will reside on system without any modification and transformation will less.

 SQL server has reach sets of tools for ETL & ELT .  Here I am sharing some important Differences between ETL and ELT which we should know before jumping on features.
SrlNo
Dimension
ETL
ELT
1Technology AdoptionETL is a well-developed process used for over 20 yearsELT is a new technology
2ComplianceThis makes it easier to satisfy GDPR, HIPAA, and CCPA compliance standards. This could violate GDPR, HIPAA, and CCPA standards.
3Data size vs. complexity of transformationsETL is best suited for dealing with smaller data sets that require complex transformations.ELT is best when dealing with massive amounts of structured and unstructured data.
4Implementation ComplexityETL experts are easy to procure when building an ETL pipeline. As a new technology, the tools to implement an ELT solution are still evolving. Moreover, experts with the requisite ELT knowledge and skills can be difficult to find.
5Order of the extract, transform, load processData transformations happen immediately after extraction within a staging area. After transformation, the data is loaded into the data warehouse.Data is extracted, then loaded into the target data system first. Only later is some of the data transformed on an “as-needed” basis for analytical purposes.
6Transformation processTransformations happen within a staging area outside the data warehouse.Transformations happen inside the data system itself, and no staging area is required.
7Unstructured data supportETL can be used to structure unstructured data, but it can’t be used to pass unstructured data into the target system.ELT is a solution for uploading unstructured data into a data lake and make unstructured data available to business intelligence systems.
SQL Server have reach sets of data transformation features available since 2000 to now.
Functionality
SSIS
BULK INSERT
BCP
INSERT … SELECT
Azure Data
Factory
SQL Dest.
OLE DB Dest
SQL Server Version
2005 - Now
2005 – Now
7.0, 2000 to Now
6.0, 7.0, 2000 to Now
2008 to Now
2016
Protocol
Shared Memory
TCP/IP
Named Pipes
In Memory
TCP/IP
Shared Memory
Named Pipes
In Memory
Cloud Pipeline & Link Service
Speed
Faster / Fastest
Fast / Fastest
Fastest
Fast
Slow / Fastest
Fastest
Data Source
Heterogeneous
Heterogeneous
Data File Only
Data File Only
Any OLE DB
Heterogeneous / Hybrid
Transformation
Yes
Yes
No
No
Yes
Yes
ETL & ELT
ETL
ETL
E & L
E & L
ETL
ETL & ELT
Invoked from
DTEXEC / BIDS
DTEXEC / BIDS
Transact-SQL
Command Line
Transact-SQL
SaaS/API/ Web Service
Use Case
DW
DW
Small Scale DW
VLDB
VLDB
Big Data

Thanks for reading
Plz dont forget to like Facebook Page..https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

Labels:

Comments

Post a Comment

Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

Popular posts from this blog

How to encrypt and decrypt Table data in postgres

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default. However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS. Example CREATE EXTENSION pgcrypto; CREATE TABLE userinfo (username varchar(20), password bytea); >>    Inserting the data in an encrypted format INSERT INTO userinfo VALUES(' suman ',encrypt('111222','password','aes')); select * from userinfo ; >>    Retrieving the data as decrypted format SELECT decrypt(password,decode('password','escape'::text),'aes'::text) FROM userinfo; Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757
1 comment
Read more

How to recover msdb database from suspect mode

 It was Monday 9 th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server. “MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..
5 comments
Read more

History of MySQL from AB Corp to Cloud Database

MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius. Original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL appeared on 23 May 1995. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter,and "SQL", the abbreviation for Structured Query Language. ·          23 May 1995 - First internal release ·          Year 1996 - Version 3 o     Simple CRUD operations o     January 1997 Windows version was released on 8 January 1998 for Windows 95 and NT o     production release 1998, from www.mysql.com ·          Year 2002 - Version 4 o     MyISAM o     unions o     Tracking o     B-trees o     s...
34 comments
Read more