I am designing a data warehouse using Azure Databases (not Azure Data Warehouse though),

I have 2 databases: the main data warehouse and a staging database. As in a traditional ETL process, the raw data is stored in the staging, then transformed and finally loaded to the data warehouse/data marts.

Since it is Azure SQL, cross database queries don't work and the only workaround I know is using external data sources and external tables, which is pretty problematic.

Can you recommend the best solution to access both databases at the same time? My ideal solution would be cross database queries like in SQL Server on premise.

  • 1
    Azure Data Factory can move data between the two. For cross db queries, research Elastic Database Queries. Out of interest, why not Azure SQL Data Warehouse?
    – wBob
    Dec 5 '16 at 22:50
  • I think I'm gonna move all the data to a single database and separate it using different schemas. It will be easier and cheaper (Azure charges per database).
    – BuahahaXD
    Dec 6 '16 at 13:09
  • I'm not using Azure SQL Data Warehouse because I'm not familiar with this technology. Does it offer any advantages compared to a regular data warehouse apart from parallel processing?
    – BuahahaXD
    Dec 6 '16 at 13:11
  • 1
    Well if you have got the volume (I would suggest > 10TB) then it is very powerful bit of kit, with the unique ability to pause compute too.
    – wBob
    Dec 6 '16 at 13:55
  • My volume will be much smaller. Does it even make sense to think about it for small systems?
    – BuahahaXD
    Dec 6 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.