In synapse delta lake table how to give decimal type size(p,s) same as numeric, money data of sql server and write data from SQL server Money, numeric columns

heta desai 247 Reputation points
2022-08-17T10:16:19.887+00:00

I am pulling data from SQL server where numeric, money data type columns are there. I want to write data into delta lake using synapse pipeline. In delta lake table I have taken DECIMAL datatype for numeric and money datatype columns of source with respective size same as source. When I executed dataflow from pipeline It do not write data into delta table and also do not throws any error.

Please suggest me If I am using wrong datatype in delta lake table for money and numeric columns of SQL server.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,422 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,644 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 31,151 Reputation points Microsoft Employee
    2022-08-19T10:21:26.71+00:00

    Hi @Anonymous ,

    Thankyou for using Microsoft Q&A platform and asking your query here.

    As I understand your ask , you want to load data of a table which has columns with datatype as 'money' and 'numeric' from SQL server to Delta lake and you want to know how to preserve the datatype till the target database. Please let me know if my understanding is incorrect.

    I tried to repro your case , for that I performed following steps:

    1. I created a table in my Azure SQL database (Note: Dataflow doesnt support self hosted IR, so you cant use on premise SQL server) with columns having datatype as money and numeric(8,2)

    232806-image.png

    2. Inside the dataflow , I created a dataset pointing to the above SQL db and selected the same in the source transformation.

    232881-image.png

    3. In the sink transformation, use inline dataset and select Delta format , select the linked service which would eventually store the data in the ADLS in .parquet format.

    232845-image.png

    In the sink settings, provide the folder path for creation of the file.

    232816-image.png

    4. After running the pipeline calling the dataflow, it created a .parquet file inside the folder. Right click on the same and select 'Create external table' using sql script.

    232823-image.png

    The generated sql script preserves the numeric datatype but 'money' is replaced by numeric , kindly change it back to money explicitly

    232846-image.png

    5. In the external table , check the table schema, it should be reflecting same datatype as expected

    232884-image.png

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators