Data flow sink supports user db schema for staging in Azure Synapse and PostgreSQL connectors

Published Apr 29 2022 02:46 PM 1,102 Views
Microsoft

To achieve the fastest loading speed for moving data into a data warehouse table, load data into a staging table. 

Consider that loading is usually a two-step process in which you first load to a staging table and then insert the data into a production data warehouse table.  Loading to the staging table takes longer, but the second step of inserting the rows to the production table does not incur data movement across the distributions. 

 

Data flow sink transformation supports staging. By default, a temporary table will be created under the sink schema as staging. For Azure Synapse Analytics and Azure PostgreSQL, you can alternatively uncheck the Use sink schema option and instead, specify a schema name under which Data Factory will create a staging table to load upstream data and automatically clean them up upon completion. Make sure you have create table permission in the database and alter table permissions on the schema.

 

Please follow links below for more details.

User db schema for staging in Azure Synapse Analytics 

User db schema for staging in Azure PostgreSQL 

%3CLINGO-SUB%20id%3D%22lingo-sub-3299210%22%20slang%3D%22en-US%22%3EData%20flow%20sink%20supports%20user%20db%20schema%20for%20staging%20in%20Azure%20Synapse%20and%20PostgreSQL%20connectors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299210%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20achieve%20the%20fastest%20loading%20speed%20for%20moving%20data%20into%20a%20data%20warehouse%20table%2C%20load%20data%20into%20a%20staging%20table.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConsider%20that%20loading%20is%20usually%20a%20two-step%20process%20in%20which%20you%20first%20load%20to%20a%20staging%20table%20and%20then%20insert%20the%20data%20into%20a%20production%20data%20warehouse%20table.%26nbsp%3B%20Loading%20to%20the%20staging%20table%20takes%20longer%2C%20but%20the%20second%20step%20of%20inserting%20the%20rows%20to%20the%20production%20table%20does%20not%20incur%20data%20movement%20across%20the%20distributions.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20flow%20sink%20transformation%20supports%20staging.%26nbsp%3BBy%20default%2C%20a%20temporary%20table%20will%20be%20created%20under%20the%20sink%20schema%20as%20staging.%20For%20Azure%20Synapse%20Analytics%20and%20Azure%20PostgreSQL%2C%20you%20can%20alternatively%20uncheck%20the%20%3CSTRONG%3EUse%20sink%20schema%3C%2FSTRONG%3E%20option%20and%20instead%2C%20specify%20a%20schema%20name%20under%20which%20Data%20Factory%20will%20create%20a%20staging%20table%20to%20load%20upstream%20data%20and%20automatically%20clean%20them%20up%20upon%20completion.%20Make%20sure%20you%20have%20%3CSTRONG%3Ecreate%20table%3C%2FSTRONG%3E%20permission%20in%20the%20database%20and%20%3CSTRONG%3Ealter%20table%3C%2FSTRONG%3E%20permissions%20on%20the%20schema.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20follow%20links%20below%20for%20more%20details.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconnector-azure-sql-data-warehouse%3Ftabs%3Ddata-factory%23sink-transformation%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EUser%20db%20schema%20for%20staging%20in%20Azure%20Synapse%20Analytics%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconnector-azure-database-for-postgresql%3Ftabs%3Ddata-factory%23sink-transformation%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EUser%20db%20schema%20for%20staging%20in%20Azure%20PostgreSQL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3299210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22use-sink-schema.png%22%20style%3D%22width%3A%20584px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F368139i3AE4267290996044%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22use-sink-schema.png%22%20alt%3D%22use-sink-schema.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3299210%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Integration%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Synapse%20Analtyics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Flows%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPostgreSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Apr 29 2022 02:45 PM
Updated by: