Azure Data Factory Copy Geometry data from Azure SQL to Azure Cosmos DB

Hi @Vijay Rudrasamudram

Thanks for the question and using MS Q&A platform.

When considering the optimal method for transferring 40 million records with geometry data from Azure SQL to Cosmos DB, it is essential to weigh the specific requirements and priorities of the task at hand. The following outlines two principal methodologies, taking into account the substantial volume of data:

1. Text Storage with Client-Side Conversion (Bulk Copy):

Advantages:

• The implementation process is relatively straightforward, utilizing Azure Data Factory’s (ADF) Data Flow activity coupled with derived column transformation for .STAsText() conversion.
• ADF’s inherent capabilities render it efficient for the bulk transfer of data.

Disadvantages:

• This method results in the loss of native spatial data within Cosmos DB.
• It necessitates the addition of client-side conversion for geospatial queries, thereby introducing added complexity to the application’s logic.

2. Alternative Data Format with Native Support (Bulk Import):

Advantages:

• It facilitates comprehensive geospatial querying capabilities within Cosmos DB.
• Over the long term, this approach may prove to be more effective for geospatial workloads.

Disadvantages:

• It requires a conversion process from the current data format (presumably WKT or Well-Known Text) to a format that Cosmos DB natively supports (such as GeoJSON). Although achievable in C#, this conversion may need optimization for handling large datasets.
• The process might necessitate the exploration of alternative data migration tools or the development of bespoke code for the bulk import into Cosmos DB.

Given the magnitude of 40 million records, consider the following suggestions:

• Bulk Transfer with Text Storage: Leverage Azure Data Factory's (ADF) strength in bulk data movement for a smooth migration. This involves converting geometry data to text format (.STAsText()) using ADF's Data Flow activity.
• Optimize Client-Side Conversion: While it adds complexity, libraries like PostGIS.NET can streamline the conversion process within your application.
• Alternative Data Format: Consider using a format natively supported by Cosmos DB (e.g., GeoJSON). This requires data conversion efforts, potentially using the Azure Cosmos DB Bulk Import tool or custom C# code for efficiency.

In conclusion, the most suitable method will be contingent upon the unique requirements, balancing factors such as the ease of migration, performance considerations, and the necessity for future geospatial querying capabilities.

For additional information please refer the following documents:

https://video2.skills-academy.com/en-us/sql/t-sql/spatial-geometry/spatial-types-geometry-transact-sql?view=sql-server-ver16
https://video2.skills-academy.com/en-us/azure/cosmos-db/nosql/how-to-geospatial-index-query

Hope this helps! Let me know if you have any further questions.