วีดีโอ

In this scenario, it would make more sense to run Spark locally. There are a few ways you can do that, but as you'd expect it's not entirely straightforward, and not something easily addressed in a comment.

There's a couple of ways to achieve this - neither are immediately obvious but definitely possible! There's no API for just running a notebook in Synapse, but you can submit a Spark batch job via the API. However, this requires a Python file as input, so it might mean pulling your tests out of a Notebook and writing and storing them separately in an associated ADO repo: learn.microsoft.com/en-us/rest/api/synapse/data-plane/spark-batch/create-spark-batch-job?view=rest-synapse-data-plane-2020-12-01&tabs=HTTP Possibly an easier route would be to create a separate Synapse Pipeline definition that runs your test notebook(s) and use the API to trigger that pipeline run from your ADO pipeline. This is a straightforward REST API but operates asynchronously, so you'd need to poll for completion as the pipeline/tests are running: learn.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run?view=rest-synapse-data-plane-2020-12-01&tabs=HTTP Hope that helps!

Hi - I don't believe there's a way in Synapse Studio to automatically script out the definitions like you can do in, say SQL Server Management Studio. But you can see the column definitions for you View if you find your database under the Data tab and expand the nodes in the explorer. Hope that helps!

Barry has ~9 parts planned!

Thanks for the feedback. Glad to hear you are enjoying it. Yes - we are planning to release the code for this project on Git at some point soon.

Sadly you don't have that many options here without having to fall back to Python/Scala. You can modularize at a very basic level using notebooks as the "modules", containing a bunch of cells which contain Spark SQL commands. Then call these notebooks from the parent notebook. Otherwise, as you say, one step further would be defining UDFs using some Python and then using spark.udf.register to be able to invoke them from SQL. Ed

The HPA prefix stands for "House Price Analytics", although the architecture diagram on the second video has slightly old names, as you've probably noticed. The full version uses <medallion_layer>_Demo_LR, where LR stands for "Land Registry". Ed

@@endjin - Thanks for the clarification Ben.

Hi - thanks for the comment! Which Synapse Link are you using? Dataverse? If so, this uses the CDM model.json format which doesn't include header rows in the underlying CSV files. You would have to read the shortcut data, apply the schema manually, and then write the data out to another table (inside a Fabric notebook or something) if you wanted to use that existing data. However, if you're using Synapse Link for Dataverse, you should instead consider using the new "Link to Microsoft Fabric" feature available in Dataverse: learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric. This will include the correct schema.

Hi Graham. Thanks for your comment! By "do this within the notebook" do you mean "attach a Lakehouse programmatically"? If so, take a look at this: community.fabric.microsoft.com/t5/General-Discussion/How-to-set-default-Lakehouse-in-the-notebook-programmatically/m-p/3732975 By my understanding, a notebook needs to have at least one Lakehouse attached to it in order to run Spark SQL statements that read from Lakehouses. Once it has one Lakehouse, remember that you can reference other Lakehouses in your workspace by using two-part naming (SELECT * FROM <lakehouse_name>.<table_name>`) without having to explicitly attach the other Lakehouses. And if you need to reference Lakehouses from other workspaces, you'll need to add a shortcut first and then use two part naming. Ed

Not yet, but I believe that is the plan.

All great questions! I'll be covering most of your points in upcoming videos, but for now I'll try to answer them here.. > Do you ever use a master notebook or pipeline to run the 3 stages one after the other for the initial upload or subsequent incremental uploads? Yes, we either use a single orchestration notebook or a pipeline to chain the stages together. On the notebook side, more recently we've been experimenting with the "new" mssparkutils.notebook.runMultiple() utility function to create our logical DAG for the pipeline. We've found this to be quite powerful so far. On the pipeline side, the simplest thing to do is to have multiple notebook activities chained together in the correct dependency tree. The benefit of the initial method is that the same Spark Session is used. This is particularly appealing in Azure Synapse Analytics where Spark Sessions take a while to provision (although this is less significant in Fabric since sessions are provisioned much more quickly). The benefit of the pipeline approach is that it's nicer to visualise, but arguably harder to code review given its underlying structure. One thing we do in either option is make the process metadata-driven. So we'd have an input parameter object which captures the variable bits of configuration about how our pipeline should run. E.g. ingestToBronze = [true/false], ingestToBronzeConfig = {...}, processToSilver = [true/false],processToSilverConfig = {...}, .... This contains all the information we need to control the flow of the various permutations of processing we need. Stay tuned - there'll be a video on this later on in the series! > Why not use python files or spark job definitions instead of some of the notebooks that only have classes and methods? We could and we sometimes do! But the reality is that managing custom Python libraries and SJDs are a step up the maturity ladder that not every org is ready to adopt. So this video was meant to provide some inspiration about a happy middle-ground - still using Notebooks, but structuring them in such a way that follows good development practices and would make it easier to migrate to custom libraries in the future should that be necessary. > How do you integrate these notebooks with testing in CD/CI? Generally we create a separate set of notebooks that serve as our tests. See endjin.com/blog/2021/05/how-to-test-azure-synapse-notebooks. Naturally, development of these tests isn't great within a notebook, and it's a bit cumbersome to take advantage of some of the popular testing frameworks out there (pytest/behave). But some tests are better than no tests. Then, to integrate into CICD, we'd wrap our test notebooks in a Data Factory pipeline, and call that pipeline from ADO/GitHub: learn.microsoft.com/en-us/fabric/data-factory/pipeline-rest-api#run-on-demand-item-job. --- Sadly I can't cover absolutely everything in this series, so I hope this comment helps!

@@endjin Thanks a lot Ed. You guys do a great job with your videos and posts. Super helpful and inspiring :)

Thanks for the comment! Yes, the orchestrator pipeline is a separate pipeline that includes the configuration that controls the flow of the pipeline. I'll be showing that in an upcoming video, so stay tuned!

@@endjinawesome. Awaiting for it.

It's coming in the next week or so. We've just started a new series you might be interested in th-cam.com/video/uaRePHeqvQU/w-d-xo.html

Thank you so much for replying quickly.

Part 8 - Good Notebook Development Practices - is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html

Glad you enjoyed it. 7 more parts to come. Don't forget the "d" in endjin!

@@endjin hah Sorry!! Endjin* :) :)

We're just editing part 2, which should be published on Thursday.

This is the way we like to work based on a background of software engineering within the company. We believe data solutions should get the same treatment as software solutions, and for that reason we're huge proponents of applying DataOps practices when building data products. In fact, we've presented at Big Data LDN and SQLBits on this very topic: endjin.com/news/sqlbits-2024-workshop-dataops-how-to-deliver-data-faster-and-better-with-microsoft-cloud Sadly we don't have a specific recording or blog we can share with you (yet!), but part of the point of this series is to expose you to some of our guidance in this area. But stay tuned! Check out "Part 8 - Good Notebook Development Practices" which is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html --- And you're right that Environments in Fabric support custom libraries. This is another way you can package up your common code and more easily use it across workloads. But this increases the CICD complexity a little bit, so take that into account! Ed

Part 8 - Good Notebook Development Practices - is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html

The principle we like to take is "do as little as necessary to land your data into bronze". Any conversions/transformations/parsing you do when ingesting into bronze introduces scope for error, and ultimately modifies the "raw" state of the dataset. The reason "bronze" has also been known as "raw" in the past (i.e. before Databricks coined the Medallion Architecture) is because the data in that layer was an unadulterated form of your source dataset. The main benefits of this being: auditability, reprocessing capability, error recovery, and the flexibility it provides where different users can consume the same data in different ways. Naturally, you can't always store your Bronze data in the same format it's stored in the underlying source, especially if your source is a database, for example. So in this instance, some sort of conversion is necessary. What you convert it to is up to you: csv is popular, as is parquet. Ingesting into Delta tables goes a step further - you're applying a schema to the incoming data and enforcing a specific read/write protocol in the Bronze zone. I would argue this makes the Bronze data less accessible for people that need to consume it in the future (which may not be an issue for your use-case). If you have a valid reason to use Tables instead of Files (which I'm sure you do!), I would still recommend storing a copy of the raw file in the Files section in some format that suits you. Imagine if your Delta table gets corrupted for some reason, and the historical data is no longer available at source - what would you do then? Hope this helps! Ed

Yes, very much so! Ed (and Barry) are just heads down busy preparing for their workshops and talks at the SQLBits conference next week. As soon as that's done they'll have more capacity to finish off this series of videos.

Part 8 - Good Notebook Development Practices - is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html

Thank you! Glad you enjoyed it! There are more coming!

Part 8 - Good Notebook Development Practices - is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html

That's great to hear! There are a few more videos dropping soon.

Part 8 - Good Notebook Development Practices - is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html

Glad you're enjoying the videos

Part 8 - Good Notebook Development Practices - is now available: th-cam.com/video/UyS6ZUgh-Wc/w-d-xo.html

We're impressed you're making your way through the series!

There should be a new video dropping soon, seeing that you've binged everything so far!

You're most welcome. Check out some of the other videos on the channel. There are some really good talk about gathering requirements and testing.