Azure Synapse and Teradata Table Differences
I have great news if you are migrating between Teradata and Azure Synapse. Tera-Tom Coffing has written 85 books on all database platforms, and he is about to show you the key differences and similarities between creating tables on Teradata and Azure Synapse.
How much experience does Tera-Tom have with converting table structures? Tom’s Nexus software instantly converts tables between all database platforms and migrates data between systems, so he is the foremost expert in the world.
Here is what you need to know when converting between Teradata and Azure Synapse. In the picture below, Teradata has a database or a user (same thing) and tables, but Synapse has a database, schema, and tables. Azure adds another level of security by including a schema. The brackets you see in Synapse are optional.
Teradata and Azure Synapse have tables that can use a hash formula to distribute the data across the AMPs/Distributions. Teradata creates its distribution key as a Primary Index, but Synapse creates a distribution key. Below we list the differences, but the examples are basically equivalent.
Teradata and Azure Synapse have tables that distribute the rows of a table randomly but evenly. These tables are excellent for staging tables because they load quickly. However, they are not good for joining or fast subsecond queries.
Teradata and Azure Synapse have tables that copy a table in its entirety across all AMPs/Distributions. The purpose is so smaller tables can join locally to large tables without any data movement across nodes.
Teradata and Azure Synapse have tables that each AMP/Distribution can store in a columnar format. Each AMP/Distribution owns the entire row, but they store each column in separate blocks on their disks.
Above are two examples of partitioned tables from Azure Synapse and Teradata.
If your team needs an Architecture and SQL class on Teradata or Azure Synapse, please contact Tom Coffing at Tom.Coffing@CoffingDW.com or call Tom at 513 300-0341. Check out the course outline for Tom's Big Data courses here. https://www.nexusdataserver.com/services-9.
If you want the best query tool for Teradata and Azure Synapse, the Nexus is the best query tool on the market. And the best part is that Nexus can query all database platforms in your enterprise. Did I mention that the Nexus also migrates, integrates, pivots answer sets, and produces Analytics from answer sets? Nexus also writes the SQL for users automatically if they desire.
Here is a YouTube video of the Nexus Query tool in action. https://www.youtube.com/watch?v=o7YGXfxrJOM&t=24s.
Here is a YouTube video of the Nexus Server migrating data at high speeds. Nexus migrates between any two systems with the click of the mouse. https://www.youtube.com/watch?v=_9ABivdwJPk.