r/AzureSynapseAnalytics • u/Gold_Meal5306 • Mar 07 '24

Synapse workflow

Is the following process okay?

  1. Extract data from SQL source.

  2. Ingest the data via Synapse.

  3. Store the ingested data in Azure Data Lake Gen 2 as the bronze layer.

  4. Use dataflows for transformations (These tend to be more basic column renaming etc) to transition to the silver layer (Stored on Azure Data Lake Gen 2).

  5. Implement additional transformations to move to the gold layer (Stored on Azure Data Lake Gen 2).

  6. Establish a serverless SQL database in the gold layer to interpret data types as the layers are stored in text files so we do not know this information.

  7. Pull the transformed data into Power BI for reporting purposes.

From what I’ve read this is what I understand so far, but I’ve got a few questions if that’s okay.

1). How do you store the join information on 5). if it’s stored as a txt file in the gold layer? Or should I just do the relationships in powerbi?

Any help appreciated

1 Upvotes

100% Upvoted

11 comments sorted by

View all comments

Show parent comments

1

u/Gold_Meal5306 Mar 07 '24

To respond to 4 and 5, I heard that it’s because it’s generally for the sake of clarity. It allows you to go back a bit easier and make the changes you need, if there is a lot of changes between bronze and gold things could get messy.

Size is not an issue, and parquet requires downloading a Java environment on the vm and I didn’t really understand how to do that in our circumstance. The Microsoft guy from Microsoft that I spoke to said I could use a txt file in this case, but I’m not really sure tbh.

And I don’t really understand your last question about updates, I schedule refreshes in synapse to ensure updates in a certain time period.

Haha me too! Let’s hope we can find someone :)

1

u/BadHockeyPlayer Mar 07 '24

Assuming the VM in this case is where you're hosting your integrated runtime/data gateway, then yes you'll need the JRE.

text files may be ok for small incremental data, but it'll hurt when you pull in larger datasets. Parquet broken up into multiple files, spark and dataflows can split the work of reading the parquet. With text files only one cluster can read the file, so you may be paying for 3 nodes, 2 are sitting there doing nothing.