Speaker 1: Okay, let's unpack this. You've given us well, quite a
Speaker 1: stack of sources here, all focused on data virtualization, specifically
Speaker 1: digging into Microsoft's poll based tool.
Speaker 2: That's right, And our mission today really is to cut
Speaker 2: through all that complexity for you. We want to show
Speaker 2: you how this kind of technology tackles what's probably the
Speaker 2: biggest challenge for modern businesses yea analyzing these huge, scattered
Speaker 2: data sets.
Speaker 1: We're talking big data, IoT streams, data mining stuff. Yeah,
Speaker 1: massive amount.
Speaker 2: Exactly petabytes of it, and doing it quickly, affordably, and
Speaker 2: crucially without making your team learn every single complex system
Speaker 2: your company happens to use.
Speaker 1: Yeah, it starts with just grappling with the sheer size,
Speaker 1: doesn't it. That gravity of big data. You imagine your
Speaker 1: data is like a small notebook manageable, right, but then
Speaker 1: it explodes. Suddenly it's not a notebook. It's like a
Speaker 1: million books scattered everywhere. When you hit petabyte scale, your
Speaker 1: normal ways of working just they can't keep up. They
Speaker 1: get swamped.
Speaker 2: Absolutely, the systems slow down, costs spiral, it's a mess.
Speaker 2: The old problem was if you wanted to analyze that
Speaker 2: giant library, you literally had to move every single book
Speaker 2: onto one enormous table your data warehouse before you could
Speaker 2: even start reading, a.
Speaker 1: Huge costly effort upfront, totally.
Speaker 2: So the whole point of virtualization is to find a
Speaker 2: way to analyze these massive, sprawling data sets fast using
Speaker 2: sensible resources, without that gigantic data moving phase first.
Speaker 1: Okay, so that gets us right to the heart of
Speaker 1: data virtualization. It's about solving that movement problem. But maybe
Speaker 1: we should quickly touch on why the data is so
Speaker 1: scattered to begin with. Yeah, why not just one big
Speaker 1: system for everything.
Speaker 2: Well, it really boils down to different tools for different jobs.
Speaker 2: It's this conflict between say, speed and data integrity. Relational databases,
Speaker 2: things like SQL server. They're fantastic for transactional stuff, data
Speaker 2: to day business ops.
Speaker 1: Updates, deletes, making sure the customer record is accurate precisely.
Speaker 2: They guarantee that integrity, but that comes with overhead, especially
Speaker 2: when you're just dumping massive amounts of sequential data like logs.
Speaker 1: Right for just raw logging or archiving old stuff, you'd
Speaker 1: look at file systems, maybe hdfs or cloud storage like
Speaker 1: Azure blobs.
Speaker 2: Yeah, they give you super fast reads and writes because
Speaker 2: they kind of sacrifice that strict integrity and transaction control.
Speaker 1: Great for archiving, yeah, terrible for updates. Right.
Speaker 2: I think one source mentioned updating a single customer record
Speaker 2: and a file system could mean touching hundreds of different pieces, slow,
Speaker 2: costly exactly.
Speaker 1: So now you've got this split. Your really valuable transactional
Speaker 1: data lives in the relational system and this huge bulk
Speaker 1: of sequential, maybe less frequently updated data is out in
Speaker 1: the file system or the cloud, and you.
Speaker 2: Need to join them together for analysis. That's where the
Speaker 2: pain starts.
Speaker 1: That's where the movement problem really bites. Think about that analogy.
Speaker 1: Your source is used computer A and computer B.
Speaker 2: Ah. Yes, computer A has the million entries the big archive, and.
Speaker 1: Computer B has just one thousand entries, maybe the current
Speaker 1: customers from the relational database. Okay, so historically the default
Speaker 1: way to join these you'd try to move all one
Speaker 1: million entries from A over to B.
Speaker 2: Yeah, instantly your network gets hammered. You're trying to ship
Speaker 2: potentially petabytes across the wire.
Speaker 1: And then poor computer B is struggling to even store
Speaker 1: it all, let alone process it.
Speaker 2: Exactly, it's CPU memory disc Everything is strained trying to
Speaker 2: sift through all this data. You mostly don't even need
Speaker 2: just to find maybe ten relevant records. It's just incredibly inefficient.
Speaker 1: Slow, expensive duplicates data.
Speaker 2: Yeah, great so data visualization and specifically the thinking behind
Speaker 2: poly base. It just flips that whole idea on its head. Also,
Speaker 2: the smart way, the efficient way, is to move the
Speaker 2: small data, those thousand entries from B over to the
Speaker 2: large environment on A.
Speaker 1: Ah okay, send the query to where the data lives.
Speaker 2: Precisely, you push the query logic, the filtering, the joining
Speaker 2: down to the system that has the bulk of the
Speaker 2: data and the resources to handle it, do the work there,
Speaker 2: and then you only bring back the final small relevant
Speaker 2: results set to B.
Speaker 1: Got it. So no network saguration, no data duplication on
Speaker 1: B and you let the heavy duty system do the
Speaker 1: heavy lifting.
Speaker 2: You got it. It's a fundamental shift in where the
Speaker 2: computation happens.
Speaker 1: That efficiency game is huge, and that leads us neatly
Speaker 1: into polybased itself, because this is where that technical integration
Speaker 1: gets really clever. What's the core promise of polybase for
Speaker 1: someone using say SQL server.
Speaker 2: The promise is really about seamless power through familiarity. That's
Speaker 2: the key. Polybase lets you query pretty much any external
Speaker 2: data source hdfs, azure blobs, even other databases using the
Speaker 2: tool you already know, SQL server and the language you
Speaker 2: already know, t sql.
Speaker 1: Okay, so I write my standard t SQL query, yep.
Speaker 2: But here's the clever bit.
Speaker 1: Yep.
Speaker 2: While you're writing familiar t sql, Polybase is working behind
Speaker 2: the scenes translating that query and leveraging the native capabilities
Speaker 2: of that external system, especially things like its parallel processing
Speaker 2: power or its optimized story access.
Speaker 1: So it's like a universal translator for data queries. You
Speaker 1: speak t sql and Polybase figures out how to ask
Speaker 1: the question and Hadoop speak or whatever.
Speaker 2: That's a great way to put it. Yeah, it handles
Speaker 2: that translation and execution.
Speaker 1: Now, this wasn't an overnight thing you mentioned. It has
Speaker 1: roots in Microsoft's earlier efforts, particularly with Parallel Data Warehouse.
Speaker 2: Absolutely essential context. Polybase was officially announced I think it
Speaker 2: was November twenty twelve at the Sequel Pass summit, But
Speaker 2: the underlying tech, the architecture, it really relied on the
Speaker 2: groundwork laid by Parallel Data Warehouse or PDW, which came
Speaker 2: out back in twenty ten.
Speaker 1: And the sources really emphasize how quickly that PDW tech
Speaker 1: evolved PDW version two in twenty thirteen. The performance jump
Speaker 1: was apparently staggered.
Speaker 2: It was revolutionary. We're talking like one hundred times faster
Speaker 2: query performance compared to view one. That's not incremental, that's
Speaker 2: a different class of machine.
Speaker 1: Wow.
Speaker 2: And at the same time they slashed the price per petabite.
Speaker 2: It proved that this massively parallel processing or MPP architecture,
Speaker 2: which is the foundation for PAUL two, was really the
Speaker 2: way forward for handling big data within a relational database context.
Speaker 1: And that investment paid off by sql server twenty sixteen.
Speaker 1: Polybase wasn't just a PDW thing, It was generally available
Speaker 1: in standard sql server editions.
Speaker 2: Yeah, that move really cemented it. Microsoft was clearly using
Speaker 2: the same core codebase, bringing that big data query power
Speaker 2: to its mainstream database product.
Speaker 1: Which brings us nicely to the technical secret sauce this
Speaker 1: idea of push down computation.
Speaker 2: Yes, this is critical to understanding why polybase is so
Speaker 2: much better than the older ways.
Speaker 1: We touch on the older ways failing, specifically SQL server's
Speaker 1: link servers. Can you elaborate on how they fell short
Speaker 1: with big data?
Speaker 2: Sure, they were let's just say not very optimized for
Speaker 2: remote filtering. If you wrote a standard query like select
Speaker 2: from my link server ducts give me not table where
Speaker 2: filter column ten, the link server wouldn't push that wear
Speaker 2: filter column ten part down to the remote system. It
Speaker 2: would actually read the entire table from the remote serce
Speaker 2: the whole.
Speaker 1: Thing, even if it was billions of rows, the whole.
Speaker 2: Thing, pulled it all across the network, then applied the
Speaker 2: filter locally on your SQL server instace.
Speaker 1: Oh my goodness. So if I just wanted ten records,
Speaker 1: I might still be pulling gigabytes or terabytes across the network.
Speaker 2: First, precisely a guaranteed performance killer for large data sets.
Speaker 2: To actually force the filter to run remotely, you had
Speaker 2: to jump through hoops using things like open query embedding
Speaker 2: your remote query as a string. It was awkward, error prone,
Speaker 2: and didn't scale well for complex logic. Right.
Speaker 1: That sounds painful. So the magic, as the sources call it,
Speaker 1: of predicate push down in polybase. It fixes that.
Speaker 2: It fixes exactly that. Polybase enables intelligent pushdown. The query
Speaker 2: optimizer looks at your t SQL query and figures out
Speaker 2: which parts the filtering predicate is. Maybe some joins, maybe
Speaker 2: aggregations can actually be executed on the external data source.
Speaker 1: Itself, so it does the work remotely, and then.
Speaker 2: It only brings back the much smaller pre filtered, maybe
Speaker 2: even pre aggregated results set. You only get the ten
Speaker 2: customers you ask for the billion stay put.
Speaker 1: That must completely change the game for data warehousing.
Speaker 2: Oh massively think about traditional ETL extract, transform load, huge
Speaker 2: complex processes, often running for hours overnight just to move
Speaker 2: and reshape data before you can even query it.
Speaker 1: Right the daily or nightly load window.
Speaker 2: Polybase lets you potentially bypass a lot of that heavy lifting.
Speaker 2: You don't necessarily need to physically load all the external
Speaker 2: data into the warehouse first, you can query it in place.
Speaker 2: You focus on the analysis, the queries, the calculations, not
Speaker 2: the complex data plumbing all through one connection point in
Speaker 2: SQL server and I guess this pushdown benefit is amplified
Speaker 2: by parallelism. Definitely polydase, especially when you set up scale
Speaker 2: out groups with multiple SQL server nodes, is designed for
Speaker 2: parallel data transfer. It can read data from multiple nodes
Speaker 2: in a Hadoop cluster or multiple partitions in cloud storage simultaneously.
Speaker 1: So it's pulling data in parallel, professing parts of the
Speaker 1: query in parallel on the remote system exactly.
Speaker 2: That parallel operation capability is a hallmark of those high
Speaker 2: end MPP systems, and polybaseed brings that capability to SQL
Speaker 2: server interacting with external data.
Speaker 1: Fantastic. Okay, let's broaden the view of bit section four
Speaker 1: Polybase within the wider modern data ecosystem. Interoperability seems key here, it.
Speaker 2: Really is its main strength. It has those native, highly
Speaker 2: optimized connectors for the big ones Hadoop, hdfs and Azure
Speaker 2: blob storage using the WASB protocol WSB.
Speaker 1: That's Windows Azure Storage Blob yep.
Speaker 2: The standard way to talk to Azure blobs for a while.
Speaker 1: But what about everything else the world isn't just Microsoft
Speaker 1: and hadoob. What if you need data from say, Cassandra
Speaker 1: or Mango dB, or even other relational systems like mysuquel
Speaker 1: or postgres School.
Speaker 2: Good question. For many of those other systems, Polybase relies
Speaker 2: on ODDC drivers open database connectivity. It's like a standard adapter.
Speaker 1: Okay, ODBC. So you can still use.
Speaker 2: T sql, Yes, and that's the big win. You still
Speaker 2: get to query those diverse sources using familiar t SQL
Speaker 2: from within sql server. Huge for developer productivity. And ease
Speaker 2: of adoption.
Speaker 1: But there's always a butt, isn't there. What's the trade
Speaker 1: off with ODBC.
Speaker 2: Well, using a generic bridge like ODBC can sometimes introduce
Speaker 2: a bit of overhead, and more importantly, it can sometimes
Speaker 2: limit those powerful pushdown capabilities we just talked about.
Speaker 1: Uh, so the intelligence might not always translate perfectly through
Speaker 1: the ODBC layer.
Speaker 2: Exactly. One of your sources had a perfect, if slightly
Speaker 2: worrying example with mycequel. A simple count aggregation failed to
Speaker 2: push down because of some subtle difference in how white
Speaker 2: space was handled by the ODBC driver.
Speaker 1: Suriously, a count query failed to push down.
Speaker 2: Yeah, and the workaround they had to explicitly disable push
Speaker 2: down for that query, meaning it fell back to pulling
Speaker 2: more data than necessary.
Speaker 1: So while ODBC gives you broad connectivity, you might occasionally
Speaker 1: lose some of that peak performance or intelligent push down
Speaker 1: you get with the native connectors.
Speaker 2: That's the trade off essentially. It highlights why systems with
Speaker 2: truly native, deeply integrated connections often performed best.
Speaker 1: Speaking best performers, the sources bring up Terra data quite
Speaker 1: a bit as a kind of gold standard in this
Speaker 1: MPP world.
Speaker 2: Yeah, Terra data is often seen as the benchmark, especially
Speaker 2: for petabyte scale warehousing. Their architecture goes way back nineteen
Speaker 2: seventy nine. They really pioneered many of these MPP concepts
Speaker 2: like shared nothing architecture, so they've.
Speaker 1: Been doing native pushed down in parallel data movement for
Speaker 1: decades pretty much.
Speaker 2: Their maturity and optimization are their big strengths polybases in
Speaker 2: many ways, Bringing those proven MPP concepts refined over years
Speaker 2: by systems like Terra Data into the more mainstream SQL
Speaker 2: server ecosystem.
Speaker 1: Makes sense, and shifting to the cloud polybases vital there too. Right,
Speaker 1: Connecting SQL server.
Speaker 2: To cloud storage absolutely essential. We mentioned Azure blobs via WASB.
Speaker 2: It also supports reading from Azure Data lakes store both
Speaker 2: Gen one and Gen two.
Speaker 1: Does it use the newer native protocols for ady ls
Speaker 1: Gen two Like.
Speaker 2: ABFs, often it still relies on the WISP protocol compatibility
Speaker 2: layer even for Gen two. Depending on the specific sequel
Speaker 2: server or synapse version. The native ABFs support is getting better,
Speaker 2: but WASB is often the fallback.
Speaker 1: And another key point mentioned was read versus right right now.
Speaker 2: Yes, that's an important current limitation to be aware of,
Speaker 2: especially in cloud scenarios like Azure, synaps analytics. Polybase is
Speaker 2: primarily fantastic for reading data from these external sources hood ADLs, blobs,
Speaker 2: but writing data back out to them via Polybase is
Speaker 2: often not supported or more limited. It's mainly a consumption
Speaker 2: a virtualization tool, not necessarily a two way synchronization engine yet.
Speaker 1: Okay, good clarification. So let's tie this all together. Why
Speaker 1: should you, our listener, really care about this? What are
Speaker 1: the killer real world use cases?
Speaker 2: Well, there are two immediate ones that jump out, offering
Speaker 2: huge savings and capabilities. First, aging and archiving.
Speaker 1: Moving old data out of expensive databases exactly.
Speaker 2: Think about old log files, transaction history older than say
Speaker 2: five years, data you need to keep for compliance but
Speaker 2: don't query often. You can set up part titioning in
Speaker 2: sql server to automatically move those old partitions to cheaper
Speaker 2: storage hdfs as your data lak. And the beauty is
Speaker 2: Polybase makes that archive data still look like it's part
Speaker 2: of the original table. Your legacy applications can query it
Speaker 2: using the same tseql, no code changes needed, instant cost
Speaker 2: savings on primary storage.
Speaker 1: That's incredibly practical. Okay, what's the second big one?
Speaker 2: The second one is maybe more transformational creating those three
Speaker 2: hundred and sixty degree customer views, especially for things like
Speaker 2: AI and machine.
Speaker 1: Learning, combining different data types.
Speaker 2: Right, imagine joining your core customer data from your relational
Speaker 2: database names addresses purchase history with massive unstructured or semi
Speaker 2: structured data streams. Right, what like web clickstream data, social
Speaker 2: media interactions, maybe sensor data from devices, even anonymized location data,
Speaker 2: stuff that lives outside your traditional database. Polybase lets you
Speaker 2: bring all that disparate data together virtually. You can then
Speaker 2: run mL models across that unified view to do really
Speaker 2: powerful things customers with incredible accuracy, predict churn, detect fraud,
Speaker 2: personalized offers, things you just couldn't do easily when the
Speaker 2: data was siloed.
Speaker 1: That opens up a lot of possibilities. Yeah, so wrapping
Speaker 1: things up, then, what's the big takeaway here?
Speaker 2: The big takeaway is that data virtualization, with tools like
Speaker 2: Polybase leading the charge in the Microsoft world, fundamentally changes
Speaker 2: the role of the relational database.
Speaker 1: It's not just a container anymore, exactly.
Speaker 2: It becomes more of a central hub and analytical control plane.
Speaker 2: By giving you familiar t SQL access to these vast
Speaker 2: varied external data sets and using clever tech like predicate
Speaker 2: pushdown to do it efficiently. It saves potentially huge amounts
Speaker 2: of time and money.
Speaker 1: Less complax etl less need for specialized skills for every
Speaker 1: single data source.
Speaker 2: Precisely, it makes leveraging diverse data much more accessible.
Speaker 1: Okay, a powerful shift. So we've seen how polybase makes
Speaker 1: reading and linking data from dozens of sources much easier.
Speaker 1: It makes dealing with massive static files almost trivial compared
Speaker 1: to the old ways.
Speaker 2: Yeah, the read side is pre well tackled, but you.
Speaker 1: Alluded to the difficulty of updating data in those distributed
Speaker 1: file systems earlier, which leaves us with a final thought
Speaker 1: few to chew on. If polybase has made reading and
Speaker 1: analyzing virtualized data so seamless, how long will it be
Speaker 1: until that other major big data headache, the complexity and
Speaker 1: cost of ensuring real time consistency and updates across all
Speaker 1: these different virtualized sources, is also virtualized away just as elegantly.
Speaker 1: When can we update that archive record as easily as
Speaker 1: we can query it?
Speaker 2: That's the multi billion dollar question, isn't it. How do
Speaker 2: you handle distributed transactions and consistency at scale in a
Speaker 2: virtualized world. That's the next frontier