Streamline Your Document Signing Process with airSlate SignNow's Pipeline Integrity Data for Financial Services

How to create outlook signature

hello and thank you for joining us at coals my name is elise papineau and i am a senior analytics engineer at dpt labs and i'll be the host for this session the title of this session is dbt for financial services how to boost your returns on your sql pipelines using dbt databricks and delta lake and we'll be joined by ricardo patilla from who is a lead solutions architect at databricks before we get started i want to point out that all chat conversation is taking place in the coalesc data bricks channel of dbt community slack if you're not part of the chat you have time to join right now please visit community.gitdb and search for coalesce databricks when you enter the space we encourage you to ask other attendees questions make comments react to the session as it's going on at any point during the talk we're all here together on slack while we watch this session afterwards the speaker will have a couple of minutes for a live q a and then we'll be available in the slack channel to answer any remaining questions we encourage you to ask your questions throughout the session so that we can get them queued up for the end and now let's get started over to you ricardo awesome thanks elise so um i'm really excited thank you guys for having me to talk today about dbt delta lake and data bricks in the context of an industry use case so today i'm actually going to focus specifically on investment management but if you're coming to this talk from another area of financial services if you're doing anything with alternative data i still think a lot of the same concepts are applicable so i think it'll be useful especially if you're a dbt or databricks fan okay so just to give an idea of agenda what we're going to cover today i am going to talk about data silos specifically again for financial services and practitioners who run into this all the time talk about why a lake house is important for for the financial services industry architect and specifically why dbt with lake house i think these are two really powerful architectural concepts or platforms but i think the combination is really key to simplifying a lot of a lot of industry workflows we'll talk about ideal state architecture and then lastly i'll close with a day in the life of what real practitioners might do at a investment management firm or anybody dealing with a blend of alternative data unstructured data structured data and how to bring it all together under the same umbrella so we'll do that and i'll cover a little bit of a technical demo all of the things that i'm going to be talking about today in terms of the dbt project i have a public link for the project so i'll share it after this and it should be publicly available if in case anybody wants to go and run that and then see how all this stuff is working practice all right a little bit about me i i spent time at university of michigan getting a math degree and towards the end of my time there actually focused on quantitative finance it was completely outside of the realm of what i studied in in my doctorate program but it was fascinating because it was a blend financial services industry we just come off of the a lot of trouble in the financial services industry and so got the chance to learn more about practicality there and then i spent seven years at finra doing everything from data intake to a lot of analytics engineering wasn't called that back then but a lot of sql pipelines and applying rigor to those for market regulation market surveillance and then more recently at databricks i've focused largely on the fintech space and helping financial services customers with everything from architectural guidance to use case deep dives and things like that that's a little bit about my background i've had tons of experience with sql pipelines and a lot of the pains that have gone you know through that and applying reach for that so we're going to talk about how to simplify that today all right so i think this quote has been you know repurposed for for many different areas i really love this quote um and i'm presenting it here in the context of an asset manager because i think largely if you look at history the industry has evolved quite a bit and i think now more than ever with the rise of a lot of retail investing a lot of support for retail investors and really the democratization of finance that you know what got data platforms trading platforms anybody supporting fintechs on the investment management side what got us won't get us to the future and what i mean specifically is using tools like spreadsheets for for data science or just general bi using hadoop for any kind of big data workloads or analytics engineering or even brute force methods so common example or primary example would be high frequency trading firms right today in today's world there's lots of open source technology there's lots of options for really rich analysis dbt like lowers that cost of curiosity also to do some pretty nice sophisticated sql analytics a lot of the high frequency trading firm use cases i think are are not as in the forefront and i think the revenue has actually decreased quite a bit just in terms of total folks pursuing that market so i think there's a huge opportunity for open source technology and a big opportunity for figuring out the right architecture that's open and simple and a lot of these older methods aren't quite going to get us to the future especially if you talk about things like hadoop that are extremely complex to code and deal with personally in prior roles i've moved completely away from hadoop and a lot of those same constructs today's really about how to make that simple and of course i have to include a requisite warren buffett quote here i think this is a really nice quote again because it's you can repurpose this across lots of areas in the context of finance he said i don't look to jump over seven foot bars i look around for one foot bars that i can step over and i think that the key to this methodology is to focus on things like understandability competitive advantage price this was mostly in context of investing but i think as a data team who's trying to build the future-proof platform that can process vast amounts of market data vast amount amounts of alternative data that really this quote is about keeping things simple and removing complexity so that's something that i think dbt and databricks do actually really well personally i think there's always a big challenge in the financial services industry to get your hands on all sorts of data join together get insights and so you want to do as much as possible to lower the cost of curiosity and i think dbt does a great job in that area removes a lot of the or adds a lot of data quality measures for all of the pipelines that you create and then what i'm going to show in a little bit is the databricks really helps leveling up and being able to join or marry a lot of the alternative data sets that you might get that are highly unstructured with more structured data so a match made in heaven there but i think that's the key from this goal so can we reinvent asset management take it to the next level use open source technologies but still keep things simple so i think the answer is a resounding yes and i'll go through a few patterns to do this that i think will resonate with again with practitioners who are dealing with market data dealing with sick data but at the same time trying to figure out how are we going to apply rigor around our you know unstructured data sets how do we apply rigor around sql pipelines a lot of times at least in my experience there's been so much diy in the financial services industry that it's really hard to keep things simple and so leveraging really robust open source frameworks i think is the key to solving a lot of problems and collaborating as well all right so why are trading systems in particular so hard to govern and like i said before i think this applies to anybody who's who's analyzing structured data and then real-time data mixing that with batch data and consuming any kind of alternative data this doesn't have to be the investment management space particularly it could be if you're doing any kind of cryptocurrency or your fintech who's managing credit risk use cases same story so i think what's really difficult is that trading systems are siloed you'll have the the top line going through your more very low latency real-time pipelines a lot of times there's a lot of custom java etl that's actually going setting up your streaming apps piping things like real-time market event data quotes and then trading analysts may be viewing those on operational apps or via other web apps and those tend to get prioritized and then everything else that's a large scale batch data set think of your vendors like and other vendors there they'll give you batch quotes this is the bottom line this is more of your batch pipeline those are complete analysis analyze a lot of times completely separately so you may store those in specialized time series databases because a lot of market data is very naturally time indexed you need to query as of particular times you need to join quotes and options data all sorts of data together to figure out a holistic picture so the problems with things like time series databases a lot of times is that they are limited in what they can support from a data size perspective and cost can rise very quickly in a lot of these databases so you pull out some data to a data lake but then it gets hard to to constantly keep a consistent view of your data and to have one single source of truth so that you can apply statistics and other advanced use cases around risk to your data and then the middle section is completely separate a lot of times the alternative data that you get comes into a completely separate either a cloud storage location sometimes somebody will download it to their laptop and so even if you do store it in a data lake something central a lot of analysts or data scientists may pull those results to their desktop where it's very hard to collaborate with anybody whether that's a data engineer analytics engineer any kind of bi analyst trading analyst and so it's just again not a lot of operationalization going on sorry or just consistency in terms of data that people are analyzing collaborating on so these tend to be major challenges and things that we hear all the time and this is why there's not this there's a very high degree of data sprawl tool sprawl because you can see that there's a different process or even code base for a lot of these different pipelines enter lake house i think the data bricks lake house concept is really simple to grasp it's a combination of all the best features of data warehouse and then all the best features of a data lake personally we in in my prior roles in private companies we had invested very heavily in data lake because they could support vast amounts of unstructured data so things like text really important for analyzing earnings reports and just understanding the profile and at the same time they support machine learning and what databricks has done is basically optimize this concept so that you can query data directly on cloud object storage we have thousands of customers globally doing this and we are we have extended open source technology to make this the best possible experience for all different personas whether you're you know trading analysts data analysts to data engineers and machine learning practitioners i think the proof in adoption is here really in terms of how many monthly downloads our customers basically or everybody around the world pulls all these different technologies and so what we've done is basically just tried to make the whole ecosystem as open and possible providing support for open source standards and formats so why lake house with dbt so i think that from a collaboration perspective dbt opens the door for analysts and data scientists and engineers to work together very well the integration with things like git is really important for collaboration but apart from that the design of the lake house is also open by nature similar to the foundation for dbt whether you're analyzing unstructured data like text images reports or you're doing more structured data like market data semi-structured that you might get from your operational apps you can sort all those centrally in a data lake and you can see here from all these different personas everybody's operating in the same copy of the data and so that's what we mean by open design delta lake provides asset semantics it gives you ways to merge data together account for late order arrivals account for late corrections and which is extremely frequent for trade data and any kind of market data so it allows you to reconcile those really easily and basically the lake house provides you with a nice performance engine so that if you're a quant and you're querying the data you're going to get the same experience as an analyst and you're going to get the consistent view if you're doing any sort of statistical or machine learning analysis as well so that's when we by combining data in ai in one space so everybody can use it this is a snapshot of the architect with dbt highlighted here so i think what's such a big part of why i love dbt especially for financial services use cases is because i've seen the same kind of framework built from scratch and it's a huge operational burden on data teams there's testing which typically has to be supported in a completely different framework from the coding framework that's one thing that's built in there's also documentation so documentation of testing itself and the documentation of code and lineage is something that's generally again have you have to either code yourself or use other tools which may be proprietary and then lastly just having all the the rigor and governance around alerting logging when you need to do runs or schedule any jobs all that's available in the same place and so from the database perspective you can do any kind of sql pipeline orchestration this way you would use data bricks as a back end so databricks has a lot of features that support good performance for bi including our product databrick sql and so dbt is basically a layer on top of that where you're executing your sql pipelines and in the background those are just executing against data bricks optimized sql endpoints which include photon and generally operate the best on delta lake so that is your single source of truth here and then when hooking up to any of these different databrick sql endpoints you can of course use other tools like tableau or power bi or whatever bi tool that you're comfortable with but generally here we're just going to be talking about dbt's interface dbt cloud so one of the things i want to talk about here which i'm going to show is that there is a great model i think which is really understandable and that's that moving the data into cloud object storage is extremely frequent a lot especially from a lot of data vendors who will provide the data in cloud storage like s3 buckets or adls or whatever the the cloud specific storage mechanism is so i think the key is that as much as you can dump it into the data lake that's a highly automatable process and i'm going to show a process that automates dumping of text data with a little bit of sentiment tagging into the lake market data typically arrives in the lake first as well so the idea here is to leave it where it arrives so that you can have all these breadth of use cases operating directly against it and give analytics engineers the power to write all of the business logic a lot of times the engineering team actually has a lot of control over the business which doesn't make sense 100 of the time but i think this is a great construct for again lowering their cost of curiosity figuring out exactly what surveillances need to run what trading strategies you want to implement looking at benchmarks so that's a little bit of what i'm going to show today so to show the optimized architecture instead of having completely separate silo data pipelines or silo data in general the idea here is that you can actually land everything into delta lake whether that's coming from a streaming source that's your first party data third party data you can see here market feeds coming into delta from stream resources you also have things like autoloader so that anytime you land any file type in your cloud storage bucket you can actually just land that into delta lake with some automation and everything beyond that point comes under the purview of dbt for example so if i wanted to compute a real-time view of my portfolio then as long as my market data is in my lake as long as i have quote data in the lake my first party transaction data my portfolio data security master i can do all of this or most of this in sql and then use dbt as that layer for testing lineage any kind of incremental modeling which is going to be key on the underneath all of that is all of the databricks compute so it'll support high concurrency it will support serverless compute and under the hood all in terms of all the output use cases this is just a very small subset of what you're going to get with this kind of architecture like i said you have your more bi or sql centric outputs here or bi tools like liquor tableau and you'll be able to do things like market intelligence you'll be able to do things like trading benchmarks whether that's simple moving averages view op all of those outputs will be available for you and even things like market impact where again you're lowering cost of curiosity you can analyze the full data sets with the power of the sql endpoints and the dbt apis so that's one half the second half of the whole lake house architecture advantages is that again data scientists can come anybody can come really and implement a value at risk use case they can implement uh statistical arbitrage or anomaly detection there's tons of rich open source libraries which allow you to do this stuff and so you don't really have to sacrifice this and you're hitting those same underlying delta lake tables that's where i wanted to head for the streaming architecture or for the overall lakehouse architecture okay so right now i'm just going to walk through a quick day in the life and then i'm going to hop into the technical demo and anybody's happy or you can feel free to check out that after the fact as well i'm not going to get too far into the code but suppose i'm a trading analyst or an engineer and i want to understand if i can execute a large trade at the beginning of the day let's walk through a few different things that you can compute with dbt as you go i likely want to know things like what is the intraday position for a stock for a portfolio for whatever instrument i'm analyzing and suppose i want to have hourly refreshes for my positions for what i look at throughout the day ideally also i want to keep things simple and i want to avoid writing complex merge statements or logic so this is the the true value of dbt is that the api gives you a really easy way to do incremental modeling in particular if i wanted to compute this real time or intraday view if my trades are landing my first party execution data is landing somewhere or that's stored in the data lake and then my bid and ask for real-time third-party stock date or equities is coming into my data lake as well those are representing the green rectangles so dbt can through incremental modeling basically consume all these data sets compute all my positions and then using that real-time view of how the prices are changing compute my book value or compute my portfolio value so this is a really powerful concept and the fact that i can do all this incrementally is is really amazing especially with just the the simple apis in dbt under the hood a lot of this is coming from the capabilities of delta lake to be able to merge data and do that in a performant way we've gotten lots of optimizations in databricks for that kind of workflow specifically so that's what's happening under the hood in the middle of the train day suppose i'm going to want to you know do visualizations i'm going to want to basically look at things like alternative data like my sentiment and then join that with market data and so all that's possible with this tool called data brick sql which i was talking about earlier so that's where these visualizations are coming from and i want to show everybody how this works in practice so i do have my my dbt project here a lot of the models again are doing that incremental ingestion so just to give an idea of how that's working we can go to here's a really quick example of let's say quote data that i want to incrementally add into my my data lake tables so you can see here that i am specifying incremental merges and delta lake is the the key to doing this that's the foundation for the lake health and in all of my models here for all the the market data basically i'm defining my own surrogate or unique key so that i can keep track of let's say ticker and time stamp for different instruments you could define your own key but this is the heart of being able to join all these data sets together and do this incrementally so this is one of the the basic pipelines or something a little bit more complex like i mentioned there is a are nice ways to basically just ingest not only textual data but apply a really quick sentiment analysis on those and so this pipeline specifically takes all of that intraday book value data it puts unstructured data with a little bit of a sentiment polarity score in there and this is actually mirroring that data together so what is my average sentiment and how do i overlay that with for example my market data or summaries of market data my minute bars or some of my trading benchmarks and so this model goes through all that and that final piece of being able to display this in the dashboard all the hard work or heavy lifting is being done by dbt of course so all those data models are created but once i'm ready to actually share this with somebody this is a databrick sql dashboard i can share this with other users i can schedule this for refreshing every hour which this actually is you can see this refreshed 20 minutes ago probably at the beginning of this talk and then you have other capabilities like exporting to pdf and sharing with other users but yeah from an internal reporting perspective this is a great tool to use you can see here i've got my real-time book value that was created from one model i've got some raw textual data and the sentiment applied here which shouldn't be surprised for comments like these on the tesla shorts in addition to that i have benchmarks like view up incorporated the sentiment overlaid with actual normalized value and then things like minute bars normally tools like this where you need a proprietary you know trading platform to provide this for you time series database this is all made possible just the two tools and delta lake that i had mentioned earlier and all that integration i really want to just showcase how simple all this is to put together using these two tools the actual project is stored here in this git repository so again this will be shared out for anybody who's interested but um i did want to leave a little bit of time for questions so let me let me check the slack yeah i can go ahead and read we have one question right now from kelvin louis please forgive me if i mispronounce your name there they ask databricks delta live tables and dvt plus data bricks seem to have some similarity with each other when should we use each yeah that's a great question and we have a lot of customers or customers that we talked to that they're using both so we we actually published a blog with bread finance recently who are using both dlts being used to do some streaming work that supports machine learning whereas dbt there was already an investment in dbt for a lot of the customers that we talked to and so i would say if you already are using dbt you have hundreds of models in dbt absolutely continue to use it and all you have to do is switch the backend to use databrick sql endpoints as the compute layer i think it kind of comes down to that level of what you're already invested in and then you can make the decision and kind of explore dlt where it's appropriate for um more than just sql support dlt is going to support python and other things too great i have a quick question as someone who has zero background in finance are there any unique challenges with financial data sets that you don't see working with maybe other traditional more bi oriented data sets yeah that's a really good question so i i think the ability to join unstructured and structured data together is such a big one in the past when i've had to do analysis or data science and try to get my hands on unstructured data it's always been in a completely separate place so keeping it centralized in the lake i think is a big challenge but i think more on the domain specific side later writing data and corrections is enormous in financial services so the ability to do corrections and merges and things like is really key here so i think that's why we we highlight the the delta like pattern and some of this like incremental modeling as well