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

Comments

Popular posts from this blog

mongoDB error : aborting after fassert() failure

What to do when facing errors on mongoDB “aborting after fassert() failure”

I like errors, in mongoDB this is the first error I faced and luckily many times. This error i faced during restoring name-space on local and restarting db system. I am still searching the exact root cause of this issue but i am able to resolve the current problem through below steps.

Remove all relevant namespace files from data-file route path..Now repair mongo instance using mongod process.mongod --repair ////////// execute command from bin folder path Then start server using mongd process, if started server successfully then ..mongod  ////////// execute command from bin folder path Restore last backups as normal process.Now check database by connecting mongo shell. Thanks for reading, 
Please comment your experience if you faced and also share knowledge if you have better steps to resolve...


https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

SQL71562: external references are not supported when creating a package from this platform

Last week I got this error from one of developer who was trying to deploy his project from Testing server to SQL Azure QA server. He was using “Deploy Database to SQL Azure” option from SSMS Tool-Task option.

After connecting to SQL Azure portal when operation started to deployment below errors occurs.

Validation of the schema model for data package failed. Error SQL71562: Error validating element xx.xxx.xx:function .dbo.xxx has an unresolved refrence to object xx.dbo.xxxx external refrences are not supported when creating a package from this platform.



Reason: The reason of the this error was; some functions of project was dependent on master database and only single database was being deploy to SQL Azure. DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references So, this error was coming.

Solution : I suggested him to create those function to locally on local database what…

How to recover msdb database from suspect mode

It was Monday 9th 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..