Lake DB and SQL DB

Question

Hello,

In my Synapse Analytics workspace, i have both Lake DB (where i am getting data from Dataverse) and SQL DB (for tables i created within synapse). How can I join tables from both: imagine i have a Table A from Lake DB and Table B from SQl DB and I want to do : select a.column_1 , b_column_2 from table_a a join table_b b on a.id = b.id

knowing that i am just able to read Table A using serverless pool and Table B using dedicated pool.

Thank you

Answer 1

You can create CTAS (CREATE TABLE AS SELECT) and then join it within the dedicated SQL pool. I recommend this approachif you plan on joining these tables frequently, as it avoids the need to move data repeatedly.

If you are familiar with the external tables, you can use them in your dedicated SQL pool that references your Lake DB table. You will be able to directly query your Lake DB data from within your dedicated SQL pool, and make a join with your SQL DB tables.

Take a look on join transformation in mapping data flow.