You may have read my previous post about lakebases, this post digs a little bit deeper into the modern Business Intelligence (BI) platform, delineating the roles and optimal applications of three pivotal architectural paradigms: the Data Warehouse (DW), the Data Lakehouse (DLH), and the emerging Lakebase. While Data Warehouses traditionally excel in processing structured data for conventional BI, Data Lakehouses offer a unified approach for managing both structured and unstructured data, thereby supporting a broader spectrum of analytics, including advanced AI/ML workloads. Lakebase further extends this integration by embedding high-throughput transactional capabilities directly within the lakehouse environment, blurring the lines between operational and analytical systems.

Strategic architectural decisions hinge on a precise understanding of specific workload requirements—ranging from low-latency operational transactions to complex historical analysis and real-time AI inference. This report provides a comprehensive framework for selecting the most appropriate architecture to maximize business value, operational efficiency, and future adaptability.

Introduction to Modern Data Architectures

Evolution from Traditional to Modern

Historically, organizations relied on siloed and fragmented data systems, which presented significant challenges for efficient data integration and analysis. These traditional architectures were inherently limited in their ability to handle the escalating volume, variety, and velocity of modern data. The sheer abundance and heterogeneity of data, encompassing structured, unstructured, and semi-structured formats, necessitated a departure from the rigid, schema-on-write approaches common in older data warehouses. This fundamental shift in the data landscape is the primary catalyst for the emergence of data lakes and, subsequently, data lakehouses. It signifies a profound paradigm shift in how businesses perceive and leverage data, moving beyond retrospective reporting to embrace proactive, predictive analytics and real-time operational intelligence. This evolution also necessitates a significant re-skilling and up-skilling of data professionals within organizations.

A modern data architecture is designed to overcome these limitations by providing a scalable, integrated, and governed approach to data management. This paradigm enables organizations to seamlessly move data between a central data lake and various purpose-built data services, ensuring that data is accessible and usable across diverse applications and analytical needs.

Core Components of a Modern Data Platform

A modern data platform, often conceptualized as a “data stack,” comprises several foundational layers that operate in concert to deliver end-to-end data capabilities. The success of such a platform is contingent not just on the individual capabilities of its components, but crucially on their seamless integration and sophisticated orchestration. This necessitates a holistic architectural approach, robust and automated data pipelines, and strong data governance to ensure data quality, consistency, and trustworthiness across the entire ecosystem. The recent trend towards unified data platforms, such as Databricks’ Lakehouse and Lakebase offerings, is a direct response to the inherent complexity and operational overhead of managing disparate tools for each component, aiming to simplify the data landscape.

• Data Ingestion: This is the initial process of collecting data from diverse sources, such as databases, SaaS applications, or files, and loading it into a designated target like a data lake or data warehouse. Ingestion can be executed in various modes, including batch, real-time, or stream processing. While real-time processing offers immediate data availability, it typically incurs higher costs due to the continuous monitoring of data sources.
• Data Storage and Processing: This layer is paramount for the effective and efficient utilization of data. It encompasses various data stores—including data warehouses, data lakes, and data lakehouses—each tailored to specific data characteristics and use cases.
• Data Transformation: This crucial stage involves refining raw data to align with desired business logic and enhance its utility for downstream applications. Techniques include data cleaning (identifying and correcting errors), data normalization (standardizing data to a common structure), data aggregation (combining multiple data points into summary statistics), and feature engineering (creating new variables to boost predictive power for machine learning algorithms). While traditional ETL (Extract, Transform, Load) processes were common, modern cloud environments increasingly favor ELT (Extract, Load, Transform) due to the cloud’s scalable processing power, allowing raw data to be loaded first and transformed as needed.
• Data Orchestration: This component ensures the smooth and automated flow of data throughout its entire lifecycle. Key elements include data integration, transformation workflow automation, data monitoring, robust error handling, and comprehensive data security.
• Data Governance: A critical aspect for securing, monitoring, and managing access to data in a unified and compliant manner. Effective governance involves maintaining consistent common vocabularies, curating data for optimal use, and establishing central frameworks for schema evolution, particularly vital for streaming use cases.
• Business Intelligence (BI) and Analytics/Data Visualization: This involves the visual representation of data and information through elements such as charts, graphs, maps, and dashboards to reveal insights and patterns. Analytics capabilities in modern architectures range from traditional data warehousing and batch reporting to real-time analytics, near real-time alerting, and advanced ML-based use cases.
• Data Observability: A newer, yet increasingly vital, layer focused on monitoring the health, performance, and reliability of the entire data platform. The interconnectedness of these components means that if problems occur during ingestion, for example, every subsequent step of the downstream analytical workflows might suffer. This highlights a systemic dependency where the failure or inefficiency of one component can propagate and negatively impact the entire data pipeline.

The Foundation: Data Warehouses

Characteristics and Traditional Use Cases

A data warehouse (DW) is fundamentally a system designed to collate data from a wide range of sources within an organization, serving as a centralized data repository primarily for analytical and reporting purposes. They are specifically engineered to manage organized data with well-defined use cases. Historically, traditional DWs were deployed on-premises, relying on physical hardware such as multiple servers, CPUs, and disks located in dedicated server rooms, requiring significant in-house IT teams for installation, maintenance, and cooling.

These systems are optimized for structured data, storing information in a relational format to support complex queries and Business Intelligence (BI). Data within a DW is typically organized into a star or snowflake schema, utilizing fact and dimension tables, and is designed through conceptual, logical, and physical data models. Traditional DWs are commonly structured in a three-tier architecture: a bottom-tier database server (typically an RDBMS) for data extraction, a middle-tier OLAP (Online Analytical Processing) server for multidimensional data analysis, and a top tier of querying and reporting tools for data analysis and BI. Primary use cases include enterprise data warehousing (EDW) for consolidating data from all subject areas across an enterprise, and supporting data analysis, data mining, AI, and machine learning efforts on structured data.

Limitations of Traditional Data Warehouses

Traditional DWs, built on outdated hardware and rigid architectures, increasingly struggle to handle the soaring volume, variety, and velocity of modern data. These limitations are not merely technical inconveniences; they represent fundamental architectural incompatibilities with the dynamic and expansive demands of the digital age.

• Scalability Constraints: On-premises infrastructure faces inherent limitations in supporting increasing data sources, users, and workloads. Once the system reaches its physical capacity, further scaling becomes impractical and prohibitively expensive.
• Performance Bottlenecks: As data volumes and user concurrency expand, query speeds decline significantly due to hardware limitations and the inability to dynamically allocate resources. This leads to performance degradation under heavy use.
• Latency Issues: Traditional DWs are heavily dependent on batch ETL (Extract, Transform, Load) processes, which introduce delays during data extraction and transformation, slowing data refresh rates. Furthermore, the ETL process often necessitates the rejection of some raw data that could potentially be valuable for future, unforeseen analysis.
• High Costs: Traditional solutions involve significant upfront capital expenditure for hardware and software licenses, compounded by ongoing maintenance costs for infrastructure, software, and dedicated IT staff. Long-term storage of historical data can also become prohibitively expensive.
• Data Silos: Despite being centralized, traditional DW implementations can still lead to data fragmentation across multiple independent data marts, hindering a holistic enterprise view.
• Limited Data Types: A primary disadvantage is their limitation to storing only structured and semi-structured data, severely restricting the types of data that can be included and analyzed.

The physical constraints and architectural inflexibility of traditional data warehouses, with their reliance on dedicated server rooms and fixed hardware, stand in stark contrast to the demands imposed by the “soaring volume, variety, and velocity of modern data”. This indicates a fundamental mismatch. For any organization experiencing significant data growth or diversification of data types, traditional on-premises data warehouses are no longer a viable long-term strategic solution.

Advantages of Cloud Data Warehouses

Cloud-based data warehouse solutions fundamentally leverage the power of cloud computing to offer significantly enhanced scalability, performance, and accessibility compared to their traditional counterparts. Cloud computing is more than just an alternative deployment model for data warehouses; it is the fundamental technological bedrock that enables the entire modern data architecture paradigm. Without the inherent elasticity, comprehensive managed services, and flexible cost models offered by cloud platforms, subsequent innovations such as data lakes and data lakehouses would be far less practical, if not entirely unfeasible, for the majority of organizations. This signifies a profound shift from a capital expenditure model to an operational expenditure model, and from IT-managed infrastructure to provider-managed services, thereby liberating internal teams to concentrate on extracting business value from data rather than being burdened by infrastructure upkeep.

• Scalability and Flexibility: Cloud DWs enable organizations to effortlessly scale their data systems to accommodate growing data needs.¹ They provide instant and cost-effective scaling with cloud infrastructure, allowing organizations to quickly expand resources without a long-term commitment.
• Cost Efficiency: Cloud data warehouses typically operate on a pay-as-you-go model, which substantially reduces initial capital investments in hardware and software licenses, as well as ongoing maintenance costs. The adoption of serverless techniques can further optimize and lower total costs.
• Optimized Performance: Cloud resources dynamically scale to provide optimized performance. They enable instant data processing through streaming ingestion pipelines and achieve faster query performance via parallel processing and columnar storage. Cloud DWs can deliver sub-second query performance and handle thousands of concurrent queries per second, preventing delays that hinder productivity.
• Managed Services: Cloud data warehouses are hosted and fully managed by cloud service providers, thereby eliminating the need for in-house hardware management and maintenance. Automatic updates and security patches are handled by the providers, ensuring access to the latest features and security measures.
• Accessibility and Collaboration: Data stored in cloud data warehouses can be accessed from anywhere with an internet connection, significantly facilitating remote work and enhancing global team collaboration.
• Unified Data Storage: Cloud data warehouses help overcome data fragmentation by providing unified data storage in one centralized cloud platform.
• Robust Security and Compliance: Cloud providers offer enterprise-grade security features, including encryption at rest and in transit, fine-grained access controls, and adherence to various compliance certifications, often surpassing the security capabilities of on-premises systems.

Embracing Flexibility: Data Lakes

Concept and Purpose

Data lakes represent a pivotal component of modern data architecture, serving as a centralized repository designed to store vast amounts of both structured and unstructured data. A key differentiator from traditional data warehouses is their ability to allow organizations to store data in its raw, native form, without the necessity for extensive upfront data transformation. This inherent flexibility facilitates the capture and storage of data from a multitude of diverse sources. Data lakes are typically built upon inexpensive object storage platforms, such as Amazon S3, making them cost-effective for large volumes.

Advantages

• Cost-Effectiveness: They offer a lower-cost storage environment, particularly advantageous for housing petabytes of raw data.
• Flexibility for Diverse Data: Data lakes can store all types of data—structured, unstructured, and semi-structured—in various native formats. This versatility empowers researchers and data scientists to work with a broad and diverse range of data more easily.
• Support for Unknown Future Uses: The ability to store data in its raw form provides significant flexibility for unforeseen analytical needs or future use cases that may not be immediately apparent.
• Removes Data Silos: By centralizing data from disparate sources, data lakes effectively help to break down organizational data silos, leading to improved decision-making and more comprehensive insights.
• AI and ML Support: Data lakes are crucial for a modern data strategy, enabling organizations to capture large amounts of raw data in real-time from diverse ML and IoT devices. This raw data is essential for machine learning and artificial intelligence, helping organizations predict future events and build intelligence into their systems. While the provided information does not explicitly detail feature engineering within data lakes, the fundamental ability to store raw, diverse data is a prerequisite for such processes.

Potential for “Data Swamps”

The primary challenge with data lakes lies in their inherent flexibility: without proper governance, metadata management, and organization, they can easily become disorganized and devolve into a “dreaded, inefficient data swamp” where extracting useful information becomes exceedingly difficult. Due to the raw and potentially unstructured nature of the data, data lakes often require advanced tools and specialized skills for effective data querying and analysis.

The data lake represents a crucial evolutionary step in democratizing data access and enabling novel forms of analytics, particularly in AI and ML, which often necessitate access to raw, diverse datasets. However, its emergence also starkly highlights the critical importance of robust data governance and metadata management. Without a comprehensive framework for cataloging, understanding, and curating the vast amounts of raw data, the transformative promise of the data lake can quickly turn into a significant organizational liability. This inherent challenge directly paved the way for the development of the data lakehouse, which attempts to impose order and structure upon the flexibility of the data lake.

The Unified Approach: Data Lakehouses

Combining Strengths of Data Lakes and Data Warehouses

A data lakehouse represents a significant architectural evolution, combining the capabilities of traditional data warehouses and modern data lakes into a single, cohesive data management solution. Its fundamental goal is to address the limitations inherent in both previous paradigms, offering a truly unified approach to data storage and processing. The data lakehouse architecture blends the flexibility, open format, and cost-effectiveness characteristic of data lakes with the accessibility, robust management features, and advanced analytics support typically found in data warehouses.

The data lakehouse is consistently described as an architecture that combines the “flexibility, open format, and cost-effectiveness of data lakes” with the “accessibility, management, and advanced analytics support of data warehouses”. It is explicitly stated to address the limitations of both preceding architectures. The critical enabling technology for this convergence is Delta Lake, which brings traditional data warehouse-like features (such as ACID transactions and implicit schema enforcement) to the cost-effective, flexible storage environment of data lakes.

The data lakehouse represents a significant maturation of the data architecture landscape. It acknowledges that neither the rigid structure of a data warehouse nor the unbridled flexibility of a raw data lake is sufficient on its own to meet the diverse and evolving data needs of modern enterprises. Its emergence signifies a strong industry trend towards unification and simplification of the data stack, aiming to reduce operational complexity and improve data accessibility for a much wider range of use cases, from traditional business intelligence to cutting-edge artificial intelligence. This also implies a potential consolidation of tooling and a reduction in the overall data engineering overhead.

Key Features and Advantages

• Unified Data Storage: A data lakehouse can store unlimited amounts of both structured and unstructured data in a single, centralized location, without the limitations often found in other systems. It effectively acts as a single-view repository for all types of organizational data.
• ACID Transactions via Delta Lake: A cornerstone of the lakehouse architecture is Delta Lake, an optimized storage layer that provides ACID (Atomicity, Consistency, Isolation, Durability) transactions for big data workloads. This capability is crucial for ensuring data reliability, preventing data corruption, and maintaining data integrity, making it the default storage format in modern lakehouse implementations like Fabric.
• Schema Enforcement and Evolution: The concept of “validated data” in the silver layer and “refined data” in the gold layer of the Medallion Architecture (discussed below) inherently implies a robust level of schema management and enforcement as data progresses through the pipeline. Delta Lake’s internal structure and transaction logs also contribute to faster read queries, increased data freshness, and the ability to perform data rollbacks.
• Cost-Effective Scalability: By leveraging cloud object storage, data lakehouses provide a cost-effective solution for storing large volumes of new data in real-time, offering scalability without the prohibitive costs associated with traditional data warehouses.
• Broad Analytics Support: Data lakehouses are designed to accommodate a wide range of analytical needs, from traditional Business Intelligence (BI) to sophisticated AI and Machine Learning (ML)-driven analytics. They empower data scientists to quickly extract insights from raw data using advanced AI tools and support both prescriptive analytics (akin to data warehouses) and predictive analytics (akin to data lakes).
• Reduced Data Movement and Duplication: A core objective of the lakehouse paradigm is to minimize data movement and duplication by striving to store only one copy of data. This is further enhanced by the ability to use shortcuts to reference data stored in other locations rather than physically copying it.
• Open Format: Data within a lakehouse is typically stored in an open format, ensuring interoperability. This allows the data to be queried by various analytical engines (e.g., Power BI, T-SQL, Apache Spark) and accessed by non-Fabric applications via standard APIs and SDKs.

Vendor Specific Lakehouse Implementations

Leading cloud providers and data platforms have embraced the lakehouse paradigm, offering their own integrated solutions:

• Databricks Lakehouse Platform: Pioneered by Databricks, their Lakehouse Platform is built on Delta Lake, providing a unified platform for data engineering, machine learning, and BI. It offers a platform experience that allows enterprises to adapt to open-source Delta Lake architecture.
• Microsoft Fabric Lakehouse: Microsoft Fabric provides a data architecture platform for storing, managing, and analyzing structured and unstructured data in a single location. It combines the scalability of a data lake with the performance and structure of a data warehouse. When a Lakehouse is created in Fabric, a SQL analytics endpoint is automatically generated, providing a read-only, relational interface over Delta tables using T-SQL. Fabric’s OneLake serves as the data lake, aiming to remove silos, reduce data movement and duplication, and support open formats (like Delta and Apache Iceberg) for various analytical engines. Snowflake has also added the ability to write Iceberg tables directly to OneLake.
• Snowflake Data Cloud (Lakehouse Analytics): Snowflake‘s Data Cloud is a self-managed platform that supports data storage, processing, and analytics. It uses a hybrid architecture with separated storage and compute (virtual warehouses) and is designed for fast query performance on open table formats, including Apache Iceberg and Delta tables, with zero data movement. Snowflake allows connecting data silos across architectures without data movement, simplifying the data lifecycle and enabling advanced analytics on diverse data types. It provides robust governance through Snowflake Horizon Catalog and supports building transactional data lakehouse patterns for unified analytics and AI/ML workloads.
• AWS Modern Data Architecture (Lakehouse): AWS‘s modern data architecture allows building a scalable data lake (on Amazon S3) and leveraging a broad collection of purpose-built data services for analytics and machine learning. AWS SageMaker Lakehouse unifies access to data across Amazon S3 data lakes and Amazon Redshift data warehouses. It supports “zero-ETL” integrations to bring transactional data from operational databases (like Amazon RDS and Amazon Aurora) into the lakehouse in near real-time, exposing it via Apache Iceberg APIs for comprehensive analysis. AWS Glue Data Catalog provides a uniform repository for metadata and a centralized framework for schema management.
• Google Cloud Data Lakehouse: Google Cloud offers a cloud-native, scalable, and secure data lakehouse solution. It combines low-cost cloud object storage with serverless compute engines and powerful data management offerings. The lakehouse integrates metadata layers over raw data storage to provide warehouse-like capabilities such as structured schemas, ACID transactions, and data governance. It supports diverse workloads including analytics, SQL, machine learning, and data science from the same repository. Key components include Cloud Storage, BigQuery, Dataproc, Dataflow, Dataplex, and Vertex AI.

Data Warehouse vs. Data Lake vs. Data Lakehouse

The Medallion Architecture (Bronze, Silver, Gold Layers) for Data Quality and Governance

The medallion lakehouse architecture is a widely recommended design pattern for logically organizing data within a lakehouse environment. Its core objective is to incrementally and progressively improve the structure, quality, and usability of data as it flows through distinct layers. This structured progression is key to ensuring data reliability and auditability.

The Medallion Architecture is far more than just a data organization pattern; it functions as a critical governance framework within the lakehouse environment. It directly addresses the “data swamp” problem often associated with raw data lakes by imposing a structured, quality-driven pipeline for data refinement. This multi-layered approach ensures data reliability, auditability, and usability for a diverse range of consumers, from data scientists requiring access to raw data to business analysts who need highly curated, performant datasets for reporting. By systematically refining data, it establishes a “single source of truth” , which is absolutely essential for fostering trust in analytical outputs and AI models.

• Bronze Layer (Raw Zone): This initial layer stores source data in its original, raw format, accommodating unstructured, semi-structured, or structured data types. Data in this layer is typically append-only and immutable, serving as a foundational source of truth that enables future reprocessing and auditing. It captures data “as-is” along with any relevant metadata.
• Silver Layer (Enriched Zone): Data in this layer is sourced from the Bronze layer and undergoes cleansing, standardization, and initial structuring into tables (rows and columns). It may also be integrated with other datasets to provide a unified “Enterprise view” of key business entities, such as master customers, products, and non-duplicated transactions. This layer is designed to enable self-service analytics and serves as a primary source for further refinement in the Gold layer. Only minimal or “just-enough” transformations are applied at this stage to maintain agility.
• Gold Layer (Curated Zone): This final layer stores data sourced from the Silver layer, which has been highly refined and aggregated to meet specific downstream business and analytical requirements. Tables in the Gold layer typically conform to star schema designs, optimized for high performance and usability for Business Intelligence (BI) and Machine Learning (ML) applications. More complex, project-specific transformations and business rules are applied during the loading of data from Silver to Gold.

In a typical implementation, each zone should ideally be separated into its own lakehouse or data warehouse within a unified data lake like OneLake, facilitating controlled data movement and transformation between zones.

Operationalizing Data: Lakebase (OLTP on the Lakehouse)

Introduction to Lakebase

The concept of “Lakebase” refers to an Online Transaction Processing (OLTP) database engine that is deeply integrated with a data lakehouse, designed to handle low-latency, high-concurrency transactions for operational applications while seamlessly syncing with analytical workflows.

Databricks Lakebase (PostgreSQL)

Databricks Lakebase is a fully managed OLTP database engine built directly into the Databricks Data Intelligence Platform, leveraging a PostgreSQL foundation. It allows organizations to create and manage OLTP databases directly on Azure Databricks, utilizing Databricks-managed storage and compute resources.

Microsoft Fabric SQL Database (SQL DB in Fabric)

Microsoft Fabric’s SQL database is a developer-friendly transactional database, based on Azure SQL Database, designed as the home for OLTP workloads within Fabric. It automatically replicates data into OneLake in near real-time, converting it to Parquet in an analytics-ready format. This enables downstream scenarios like data engineering, data science, and Power BI reporting. It uses the same SQL Database Engine as Azure SQL Database and supports intelligent performance features like automatic index creation. SQL database in Fabric allows cross-database queries, joining data from other SQL databases, mirrored databases, and warehouses within a single T-SQL query. This feature is currently in preview.

Bridging the Gap between OLTP and OLAP Workloads

For decades, OLTP systems, characterized by fast, row-level transactions, and OLAP systems, designed for large-scale analytical queries, have operated in separate, distinct environments. This traditional separation inherently created data silos between transactional and analytical data, leading to complex data movement and potential staleness.

Lakebase solutions fundamentally “collapse this long-standing wall between OLTP and analytics”. By fusing transactional database semantics with lakehouse governance, they enable organizations to run high-throughput, low-latency transactional workloads while simultaneously keeping this operational data in real-time synchronization with the analytical Lakehouse environment. This deep integration allows for the seamless combination of operational, analytical, and AI workloads without the need for custom, often brittle, ETL pipelines, thereby significantly reducing application complexity.

The historical architectural separation of OLTP and OLAP systems has been a major impediment to achieving true real-time intelligence within enterprises. This separation often necessitated complex, brittle ETL processes, introduced data staleness, and made it exceedingly difficult to build truly real-time intelligent applications. Lakebase’s emergence signifies a critical leap towards a genuinely unified data platform where operational data is immediately available for comprehensive analytics and AI, and conversely, analytical insights can directly inform and influence operational systems. This capability unlocks transformative use cases such as real-time fraud detection, hyper-personalized recommendations based on live customer behavior, and dynamic pricing adjustments, which were previously either technically challenging or economically unfeasible. It streamlines the entire data lifecycle, drastically reduces data latency, and empowers developers to build sophisticated “intelligent applications” with unprecedented efficiency.

Key Features of Lakebase Solutions

• Fully Managed and Serverless: Both Databricks Lakebase and Microsoft Fabric SQL DB are fully managed, handling all aspects of storage, compute, provisioning, and scaling, removing significant operational burden. They leverage architectures that separate compute and storage, enabling independent scaling while supporting ultra-low latency and high concurrency transactions.
• PostgreSQL/SQL Compatibility: Databricks Lakebase offers standard PostgreSQL semantics, including support for row-level transactions, indexes, and access via JDBC/psql drivers, along with pgvector for GenAI/RAG agents. Microsoft Fabric SQL DB uses the Azure SQL Database Engine, providing T-SQL compatibility.
• Copy-on-Write Branching (Databricks): Built on Neon technology, Databricks Lakebase introduces “copy-on-write” branching, allowing for instant, zero-copy clones of databases (branches). These branches are lightweight, operate independently, and are economically efficient, ideal for isolated development, testing, or point-in-time recovery.
• Real-time Sync with Lakehouse: Both solutions provide managed Change Data Capture (CDC) into their respective lakehouse environments (Delta Lake for Databricks, OneLake for Fabric), ensuring transactional rows are kept in real-time synchronization with BI models and the broader analytical layer. This simplifies data synchronization between OLTP and OLAP workloads.
• Unified Governance: Databricks Lakebase integrates with Unity Catalog for consistent security policies and access control across OLTP and OLAP data. Microsoft Fabric SQL DB integrates with OneLake’s centralized data governance, allowing data to be shared and accessed across Fabric items.
• High Availability: Both offer multi-zone high availability to protect against zonal failures, with Databricks Lakebase supporting readable secondaries for read workload scaling.

Lakebase vs. Traditional OLTP Databases

This comparison starkly contrasts Lakebase’s modern, integrated approach with the more traditional, often siloed nature of standalone OLTP databases. It highlights Lakebase’s unique value proposition, particularly its “AI-native” features, seamless integration with the broader lakehouse ecosystem, and operational efficiencies. For a technical leader, this comparative analysis is invaluable in evaluating whether to adopt an innovative, integrated solution like Lakebase or to continue relying on traditional OLTP databases, based on critical factors such as operational overhead, real-time analytical needs, strategic AI initiatives, and overall platform consolidation goals.

When to Use Each Architecture

The selection of the optimal data architecture is not a one-size-fits-all decision; rather, it is a strategic choice that must align with an organization’s specific business requirements, the characteristics of its data, and the nature of its anticipated workloads.

Data Warehouse (Cloud-based)

A cloud-based data warehouse is ideal for organizations primarily dealing with highly structured and semi-structured data that requires high-performance Business Intelligence (BI) and standardized reporting. It is particularly well-suited for well-defined analytical use cases where data is consistently transformed before loading (via ETL or ELT processes). Cloud data warehouses excel in historical analysis and generating aggregated views for executive dashboards. While cloud DWs offer significant scalability and cost benefits over their traditional on-premises counterparts, they can still be more expensive for storing vast quantities of raw data compared to data lakes. Moreover, they may struggle to efficiently handle the sheer variety and volume of unstructured data that is increasingly prevalent in modern enterprises.

• Amazon Redshift (AWS): A fully managed, petabyte-scale cloud data warehouse service that is part of AWS’s broad portfolio of analytics services.
• Snowflake: While often positioned as a data lakehouse, Snowflake also functions as a highly scalable cloud data warehouse, optimized for structured and semi-structured data, with strong BI and reporting capabilities.
• Google BigQuery (GCP): A fully managed, serverless data warehouse that enables scalable analysis of petabytes of data using SQL. It can query data directly from Cloud Storage, making it a key component for analytical workloads.
• Microsoft Fabric Data Warehouse: Within Microsoft Fabric, a data warehouse can be created in OneLake, often used for the Gold layer of a medallion architecture, optimized for BI and reporting.

Data Lakehouse

The data lakehouse is the preferred choice for organizations that need to handle a vast and diverse array of data types—structured, semi-structured, and unstructured—in a unified, cost-effective, and scalable manner.⁴ It is optimal for scenarios requiring both traditional BI capabilities and advanced analytics, including comprehensive AI and Machine Learning (ML) model training and serving. The lakehouse is particularly valuable when there is a need to combine historical data analysis with the flexibility to explore raw data for new, unforeseen analytical opportunities. Its ability to provide ACID transactions and schema enforcement via Delta Lake makes it a robust platform for data quality and reliability, addressing the “data swamp” issues often associated with pure data lakes. Organizations that prioritize a single source of truth for all data, reduced data movement, and simplified governance across diverse workloads will find the lakehouse approach highly beneficial.

• Databricks Lakehouse Platform: A leading proponent of the lakehouse architecture, built on Delta Lake, offering a unified platform for data engineering, ML, and BI.
• Microsoft Fabric Lakehouse: Provides a unified platform for structured and unstructured data, with automatic SQL analytics endpoints and integration with OneLake for open formats and reduced data movement.
• Snowflake Data Cloud (Lakehouse Analytics): Offers powerful lakehouse analytics on open table formats (Iceberg, Delta), allowing users to connect data silos with zero data movement and perform advanced analytics and AI/ML.
• AWS SageMaker Lakehouse: Unifies access to data across S3 data lakes and Redshift data warehouses, supporting zero-ETL integrations from operational databases and leveraging Apache Iceberg for open access.
• Google Cloud Data Lakehouse: Leverages Cloud Storage, BigQuery, and other services to provide a scalable, secure data lakehouse solution with structured schemas, ACID transactions, and governance over diverse data types.
• Oracle Modern Data Platform: Combines Oracle transactional databases, data lake (OCI Object Storage), data warehouse (Autonomous Data Warehouse), and OCI AI/ML services to provide a comprehensive data management solution, supporting a data lakehouse approach for business analysis and machine learning.

Lakebase (OLTP on the Lakehouse)

Lakebase is specifically designed for organizations that require real-time operational data processing (OLTP) seamlessly integrated with their analytical and AI workloads within a unified data platform. It is the optimal choice for applications demanding low-latency, high-throughput transactional capabilities, such as customer-facing applications requiring up-to-date information, online feature stores for real-time AI inference, or enterprise transactional workloads that need reliable, concurrent processing. Lakebase eliminates the traditional silos between operational and analytical data, allowing for real-time insights by analyzing transactional and historical data together. This is crucial for use cases like instant fraud detection, personalized product recommendations based on live shopping cart contents, or automated market trading driven by streaming data.

• Databricks Lakebase (PostgreSQL): A fully managed PostgreSQL OLTP engine natively integrated into the Databricks Lakehouse Platform, providing transactional strength with lakehouse elasticity, analytics, and governance.
• Microsoft Fabric SQL Database: A developer-friendly transactional database based on Azure SQL Database, serving as the home for OLTP workloads in Fabric, with automatic near real-time replication to OneLake for analytics. It’s worth noting that Fabric’s Lakehouse SQL Endpoint is read-only. If you’re wanting to write back data to the Lakehouse using a SQL Endpoint this cannot be done; However, a Fabric Warehouse SQL Endpoint allows DML.
• Google Cloud AlloyDB: A PostgreSQL-compatible database designed for demanding enterprise OLTP workloads. It offers built-in analytics capabilities (HTAP) and can stream data or use federated queries with BigQuery and Vertex AI for real-time insights. Google Cloud Spanner is another highly scalable, globally distributed transactional database that can be integrated with BigQuery for analytical queries.
• AWS Purpose-Built Databases: While AWS doesn’t use the term “Lakebase” directly, its modern data architecture emphasizes purpose-built databases (like Amazon RDS, Amazon Aurora for relational OLTP, and various NoSQL options) that integrate with the data lake (S3) and data warehouse (Redshift) via services like AWS Glue and zero-ETL integrations to enable operational analytics and feed data into the lakehouse for unified access.
• Snowflake: Primarily an OLAP system, Snowflake is not designed for direct OLTP workloads. However, it integrates with external OLTP databases via ETL/ELT pipelines (e.g., Snowpipe for continuous loading) to pull transactional data for analytical processing.
• Oracle Modern Data Platform: Integrates Oracle transactional databases with its data lake and data warehouse components, enabling live data analysis by integrating transactional and analytical solutions for near real-time insights.

Decision Framework: Optimal Architecture for Specific Workloads

Integrating Common Tasks and Workloads

Modern data architectures are designed to support a wide array of enterprise tasks, moving beyond simple reporting to encompass advanced analytics and intelligent applications. The choice of architecture significantly influences how seamlessly these tasks are integrated and performed.

AI and Machine Learning

AI and ML are critical for a modern data strategy, enabling organizations to predict future events and embed intelligence into their systems and applications.

• Data Warehouses: While traditional data warehouses can support some AI/ML workloads, they are primarily limited to structured data. Feature engineering and model training on unstructured or semi-structured data would necessitate moving data to other platforms, introducing complexity and latency. Modern cloud data warehouses like Snowflake also offer AI capabilities, such as Snowflake Cortex AI for pre-built models and LLM functions, and Snowpark ML for custom model building within the platform.
• Data Lakehouses: The data lakehouse is uniquely positioned to support comprehensive AI/ML workflows. Its ability to store all forms of raw and structured data from diverse sources makes it ideal for data preparation and feature engineering. Data scientists can access vast amounts of raw data directly from the Bronze layer, cleanse and transform it in the Silver layer, and create highly curated datasets for model training in the Gold layer. This unified environment allows for quick extraction of insights from raw data using advanced AI tools.
  • Databricks: Offers a unified platform for data engineering, machine learning, and BI, with native support for AI/ML workflows.
  • Microsoft Fabric: Provides AI functions for data engineering (summarization, classification, text generation) and integrates FLAML for hyperparameter tuning. Data Science in Fabric supports end-to-end data science workflows from exploration to model serving.
  • Snowflake: Offers Snowflake Cortex AI for pre-built models and LLM functions (e.g., text summarization, sentiment analysis, natural language queries, Document AI, forecasting, anomaly detection) and Snowpark ML for building, training, and deploying custom ML models using Python directly within Snowflake. It also supports developing AI data agents and defining ML workflows.
  • AWS: Offers a broad and deep set of machine learning services (AI services, ML services, ML frameworks and infrastructure) to help organizations predict future events and build intelligence into their systems and applications. AWS SageMaker Lakehouse supports AI initiatives with unified data access.
  • GCP: Leverages Vertex AI for machine learning, allowing users to integrate ML models directly with data in BigQuery and other data lake components.
  • Oracle: The Oracle Modern Data Platform includes OCI AI/ML services to enrich data with embedded intelligence and surface insights directly inside applications.
• Lakebase: Lakebase extends the AI/ML capabilities of the lakehouse by providing a transactional layer for real-time inference and feedback loops. It can serve low-latency features for real-time inference and write model feedback directly back to the lakehouse. This integration is crucial for building intelligent applications and RAG agents that require up-to-the-second structured data to ground LLM prompts. The seamless sync with Unity Catalog managed tables (Databricks) or OneLake (Fabric) allows for fast and easy combination of operational, analytical, and AI workloads without custom ETL pipelines. Google Cloud’s AlloyDB also offers a fully featured vector database (AlloyDB AI) for GenAI agents and apps, with faster vector queries and integration with Vertex AI.

Business Intelligence and Reporting

BI and reporting involve the visual representation of data to reveal insights and patterns over time.

• Data Warehouses: Traditional and cloud data warehouses are purpose-built for BI and reporting on structured data. They provide highly optimized environments for complex SQL queries and are the backbone for dashboards and standard analytical reports.
• Data Lakehouses: Data lakehouses support traditional BI by providing highly refined and aggregated data in the Gold layer, often conforming to star schema designs optimized for performance and usability. This allows organizations to leverage data lakehouses for both traditional BI and more advanced analytics, providing a single source of truth for reporting.
  • Microsoft Fabric Lakehouse: Automatically generates a SQL analytics endpoint for T-SQL queries, enabling BI tools like Power BI to connect directly to the lakehouse.
  • Snowflake: Ensures consistent results across BI platforms and eliminates data extracts for truly interactive insights.
  • AWS: Provides purpose-built analytics services, including Amazon Redshift, for traditional data warehousing and batch reporting, as well as real-time analytics for dashboards.
  • GCP: BigQuery is used for powerful analytics and can be integrated with visualization tools to provide insights.
• Lakebase: While not a primary BI tool itself, Lakebase solutions can feed real-time operational data into the lakehouse environment, which then becomes available for near real-time BI and reporting. This allows dashboards to reflect the most current transactional data, enabling instant decision-making. Google Cloud’s AlloyDB, with its built-in columnar engine, is ideal for real-time BI dashboards.

Custom Applications (Real-time and Analytical Workloads)

Modern applications often require a blend of low-latency operational data and historical analytical insights.

• Data Warehouses: Building custom applications with traditional data warehouses for real-time workloads is challenging due to their OLAP-centric design and latency issues. They are better suited for batch analytical workloads that inform application logic rather than directly powering real-time features.
• Data Lakehouses: Data lakehouses provide the foundation for analytical workloads within custom applications, allowing developers to access and process large volumes of diverse data. They enable a holistic view of data, facilitating seamless data flow and integration between different systems.
  • Snowflake: Supports building, distributing, and commercializing scalable, data-intensive applications, and allows for easy integration of data and apps in client environments.
  • AWS: Its modern data architecture supports purpose-built databases for modern applications and allows for building real-time analytics and ML-based use cases.
  • GCP: Dataflow is used for real-time ETL pipelines, and BigQuery can be used for real-time analytics, supporting custom applications.
• Lakebase: Lakebase solutions are game-changers for custom applications requiring both real-time and analytical workloads. They allow organizations to serve up-to-date information quickly to users in apps and dashboards, making their experience smoother and more responsive. They can manage application state in a transactional data store, serve featurized data at low latency to models, and enable integrated analytics by capturing and analyzing transactional and historical data together. This empowers the creation of intelligent applications that consume features or predictions generated in the lakehouse and update the analytical layer with fresh operational data, all within a unified platform. Examples include e-commerce applications with personalized recommendations, healthcare systems managing clinical trial data, or manufacturing solutions tracking IoT data for automated maintenance. Google Cloud’s AlloyDB is designed for demanding enterprise workloads and can accelerate agent and app development, supporting real-time business intelligence dashboards and fraud detection.

Summing it all up

The evolution of data architectures reflects a continuous pursuit of greater flexibility, scalability, and real-time intelligence. Traditional data warehouses, while foundational for structured BI, faced inherent limitations in handling the burgeoning volume and variety of modern data. The advent of cloud data warehouses significantly alleviated these constraints by offering elastic, cost-effective, and managed solutions.

Data lakes emerged as a response to the need for storing all data types in their raw form, enabling broad data exploration and foundational support for AI/ML. However, their inherent lack of structure presented challenges, leading to the potential for “data swamps.”

The data lakehouse represents a pivotal architectural convergence, successfully blending the strengths of data lakes (flexibility, cost-effectiveness, diverse data types) with those of data warehouses (structure, governance, performance for BI). Technologies like Delta Lake, with its ACID transactions and schema capabilities, are central to the lakehouse’s ability to provide a reliable, single source of truth for both traditional analytics and advanced AI/ML workloads. The Medallion Architecture further reinforces this by providing a systematic approach to data quality and governance across raw, cleansed, and curated layers. Leading cloud providers like Microsoft, AWS, Google Cloud, and data platforms like Snowflake and Oracle have all developed their own robust lakehouse offerings, demonstrating the widespread adoption and strategic importance of this paradigm.

The introduction of Lakebase solutions (such as Databricks Lakebase and Microsoft Fabric SQL DB) marks the latest significant step in this architectural evolution, directly addressing the historical separation between operational (OLTP) and analytical (OLAP) workloads. By embedding fully managed transactional engines directly within the lakehouse environment, these solutions enable real-time synchronization of operational data with analytical platforms. This unification streamlines data pipelines, reduces latency, and unlocks the potential for truly intelligent, real-time applications that can both consume and generate insights within a single, governed ecosystem. Other providers like Google Cloud (AlloyDB, Spanner) and AWS (purpose-built databases with zero-ETL integrations) also offer robust solutions for integrating transactional data with their analytical platforms, achieving similar outcomes.

The trend is clear: organizations are moving towards unified data platforms that minimize data movement, reduce operational complexity, and accelerate the path from raw data to actionable intelligence and AI-driven applications. Strategic architectural decisions will increasingly favor solutions that offer this holistic capability, allowing data professionals to focus less on infrastructure plumbing and more on extracting transformative value from their data assets. The future of data architecture lies in seamless integration, real-time capabilities, and native support for AI across the entire data lifecycle.

In a future post, we’ll dive into creating a sample solution with Microsoft Fabric and follow up with AWS and GCP. Be sure to check out my other posts at SQLTechBlog.com.

SQLTechBlog.com 2025 ©

When it comes to AI assistants, there are a lot of topics, from coding to image creation to recipes. That’s right… I ask CoPilot how to make a cake and other things. In fact AI is so good at baking that it’s crazy. Don’t believe me, I prompted CoPilot for a chocolate that is moist. I don’t have the exact prompt anymore but this has become my go-to recipe for birthdays. I would have never thought that adding boiling water to a cake batter would be a good idea. Thanks CoPilot! Check out this cake:

As said, recipes are not the only reason to use an AI Assistant. The help us speed up our work with coding and help those of us who are not artistically inclined be better artists. My son really gets grumbly over this one. He says AI cheapens artist’s work; however, I feel like you need to look at this topic from a new viewpoint. Remember these are tools we use to enhance what we do, not to replace us!

Comparing image generation across different AI assistants

AA
Using a simple prompt, I asked CoPilot, Gemini, and Claude to create a universal icon for a data warehouse.

Prompt: “Create a universal icon, to be used in a blog post, that represents a data warehouse in the cloud. The icon should not copy Microsoft, AWS, Oracle, Databricks, Snowflake, or GCP.“

Copilot

Gemini

Claude (Sonnet 4)

Summary & How image generation works in AI

I feel like Gemini is the clear winner, you could argue that CoPilot did a decent job; but, it goes without saying that Claude fell on face first and far short. Why is this? For image generation, we’re talking about Diffusion models. These models work by gradually adding noise to training images until they become pure noise, then learning to reverse this process. During generation, the model starts with random noise and iteratively removes it to create a coherent image.

Microsoft Copilot

Copilot utilizes DallE3 from OpenAI to power its image generation capabilities. This means Copilot doesn’t have its own image generation model but integrates OpenAI’s DALL-E 3, which is indeed a diffusion model. Copilot uses OpenAI’s Dall-E 3 for its image generation features.

Google Gemini

Gemini uses Imagen 3, recently made generally available, and Gemini uses Imagen 3, one of the most advanced image generation models available. Additionally, for Gemini, use Gemini 2.0 Flash Preview Image Generation. For Imagen, use one of the Imagen models (Imagen 3, Imagen 4 or Imagen 4 Ultra).

Claude (Anthropic)

Claude is limited to text-based output via the chat and does not have any image‐generation module in the API. Claude can analyze and work with uploaded images but cannot generate new images. This remains true even with the latest Claude 4 models – Anthropic has focused on reasoning capabilities rather than image generation.

Overview

Summary:

Copilot and Gemini are strong contenders for general text-to-image generation and offer features for editing and refining those images. They are designed for creative image generation across various styles.

Claude Sonnet 4, on the other hand, is not a dedicated text-to-image generator in the same vein. Its “image generation” capabilities are primarily tied to its ability to process visual information (like charts or diagrams) and to generate visualizations or plots when executing code, making it more of a tool for data interpretation and visual representation from structured data.

What about code generation?

Try this prompt in CoPilot, Gemini, and Claude:
“Make a game with red, blue, green and yellow bubbles, 10 columns, 20 rows. When you click a bubble the bubble should highlight and each bubble of the same color should also highlight. this should continue until no adjacent bubbles have a matching color. if the highlighted bubbles are clicked again, pop them and fill the empty space with bubbles from the top. If a bubble of a different color is clicked then unhighlight all the bubbles and start the hightlight process for the new color bubbles.“

You’ll be amazed at how quickly a MVP of a game can be created when it would take a developer a few hours or a poor developer a few days to create the same code. This is the core conflict with AI today and relates directly job loss; however, we’re not there with AI yet. You still need a developer to turn this game into a real application and ensure that it works properly and can scale. AI in its current state is a job aid that helps us rapidly develop code. Who knows what this will look like in 5 years though.
Did you notice that Gemini and Claude generated a finished product that was the same but CoPilot’s output is broken? Click some bubbles at the top and you’ll see that the CoPilot generated code changes bubbles at the bottom of the playboard instead of just filling new bubbles from the top.

Gemini and Claude are the winners here. It’s worth noting that CoPilot the assistant isn’t quite the same as GitHub CoPilot, which may do a better job.

In a future post, we’ll dive into coding with ai assistants and how this affects both application and database code. Be sure to check out my other posts at SQLTechBlog.com.

SQLTechBlog.com 2025 ©

Are your Azure Fabric pipelines with multiple notebooks running slower than you’d like? Are you paying for more Spark compute time than you should be? The culprit might be a simple setting that’s easy to miss. In this blog post, we’ll dive into the “For pipeline running multiple notebooks” setting in Azure Fabric and explain why enabling it can significantly improve your pipeline’s performance and reduce your costs.

The Challenge: Multiple Notebooks, Multiple Spark Sessions

Imagine a common scenario: a data pipeline that needs to get data from multiple sources, process it, and then use that data for a final task, like generating an email. A typical pipeline for this might look something like this:

This pipeline, for example, has three notebooks running in parallel to fetch different datasets, and a fourth notebook that uses this data to generate an email.

Without a specific setting enabled, each time a new notebook activity starts, it spins up a brand-new Spark application. This process of starting a new Spark session takes time. For a pipeline with four notebook activities like the one above, that’s four separate Spark application startup times. These redundant startups add up, creating a performance bottleneck and extending the overall runtime of your pipeline.

Even worse, each new Spark session consumes its own compute resources, meaning you are being charged for the time it takes for each session to initialize and for the resources it uses, even if the other sessions are idle.

The Solution: Sharing Spark Sessions

This is where the “For pipeline running multiple notebooks” setting comes in. This powerful feature allows multiple notebooks within the same pipeline to share a single Spark application. Instead of each notebook starting its own session, they all connect to and use the same underlying Spark cluster.

To access this setting, you need to go to your workspace‘s settings.

1. Click the Workspace settings icon in the top right of your Azure Fabric workspace.
2. In the menu on the left, navigate to Data Engineering/Science and then Spark settings.
3. Under the High concurrency tab, you will find a toggle labeled For pipeline running multiple notebooks. Make sure to switch this toggle On.
4. Ensure that each notebook uses a common session tag.

When this setting is enabled, all notebooks in your pipeline will share a single, long-running Spark application. The Spark application starts once at the beginning of the pipeline run and remains active until the entire pipeline is complete.

The Benefits: Performance and Cost Gains

Enabling this setting provides two major advantages:

• Improved Performance: The most immediate benefit is a significant reduction in pipeline runtime. By eliminating the overhead of multiple Spark session startups, your notebooks can execute much more quickly. In a parallel pipeline like our example, the three Get Data notebooks can all begin their work immediately without waiting for their own dedicated Spark cluster to spin up.
• Reduced Costs: Since your pipeline is only using one Spark application for its entire duration, you’ll be paying for fewer resources and less time. The cost savings can be substantial, especially for complex pipelines that run frequently or have many notebook activities.

In a world where data pipelines are the backbone of modern data platforms, optimizing their performance is critical. By taking a moment to enable the “For pipeline running multiple notebooks” setting, you can ensure your Microsoft Azure Fabric pipelines are not only faster but also more cost-effective. Don’t let unnecessary Spark session startups slow you down—turn on this setting and watch your pipelines fly!

Comparing this setting in Fabric to AWS’ Glue

In a previous post, I mentioned comparing Fabric with other cloud platforms. Let’s kick that off by discussing how this may look in AWS.

AWS Glue is a serverless ETL service where a “job” is the primary unit of execution. Each Glue job, whether it’s a Spark ETL job or a Python Shell job, is designed to run in its own, isolated environment. A single Glue job can contain complex logic, including the ability to run multiple scripts or stages, but it’s all within the context of that one job’s execution environment and Spark session.

While you don’t have a toggle for sharing sessions across multiple, distinct Glue jobs in the same way as Azure Fabric, there are ways to achieve similar goals:

• Job Chaining/Workflows: You can use AWS Glue Workflows or other orchestration tools like AWS Step Functions to chain together multiple Glue jobs. This allows for a sequence of tasks, where the output of one job becomes the input of the next. However, each job in the workflow will still spin up its own, separate Spark environment.
• Single, Monolithic Job: A more common and efficient approach in Glue is to consolidate your data processing logic into a single Glue job script. Instead of having separate jobs for GetProductData, GetSalesData, and GetCustomerData, you would write a single Python script within one Glue job that performs all three tasks and any subsequent processing. This job would start one Spark session and use it for the entire duration of the script, thus eliminating the overhead of multiple Spark session startups. This approach is the closest conceptual equivalent to what the Fabric setting accomplishes.
• Glue Streaming Jobs: For streaming data, Glue has continuous streaming jobs. These jobs run continuously and maintain a persistent Spark environment, similar to a long-running session. However, this is for streaming use cases, not batch processing with multiple discrete jobs.

In summary, while there isn’t a simple toggle, the recommended practice in AWS Glue to achieve similar performance and cost benefits is to structure your code into a single, comprehensive job rather than using multiple, small jobs that would each incur Spark startup overhead. This is a key difference in how these two platforms are designed and how you would optimize your data processing workflows on each.

Maybe you’ve noticed all the twenty somethings tight rolling their jeans or people with bellbottoms or the 80’s music playing in grocery stores… It’s true, fashion and art are cyclical. I’m certain we’ll be seeing MC Hamer pants as a new trend around 2030.

Technology, much like fashion, often operates in an ever-moving circle. Trends emerge, fade, and then, with a fresh coat of paint and some innovative twists, reappear as the next big thing. In the world of data and Business Intelligence (BI), we’re witnessing a fascinating full circle moment.

For years, the narrative pushed was that traditional relational databases were ill-suited for the scale and complexity of modern BI solutions. The marketing was something like: “Databases don’t belong in BI; use Spark!” We embraced distributed computing frameworks, data lakes, and complex ETL pipelines to move data from operational databases into analytical engines. The idea was to separate transactional workloads from analytical ones to ensure performance and scalability. Spark, with its ability to handle massive datasets and flexible processing, became the darling of the data world.

Spark revolutionized how we processed big data, enabling sophisticated analytics and machine learning on previously unwieldy datasets. But the cost was often architectural complexity, increased data movement, and a fragmented view of an organization’s data.

Fast forward to today, and the pendulum is swinging back. Only this time, it’s not a mere return; it’s an evolution. The latest innovations from Databricks, Snowflake, and Microsoft are demonstrating that databases, particularly relational ones, are not just relevant but are becoming innovative and cool again for BI solutions. They’re doing this by integrating transactional capabilities directly into the analytical data layer, offering a unified platform for both operational and analytical workloads. This new breed of “lakebases” (or “unistores“) aims to simplify data architectures, reduce latency, and provide a single source of truth for both real-time operations and long-term analysis.

The “old is new again” isn’t about discarding Spark or data lakes; it’s about integrating the best of both worlds. It’s about bringing the familiarity and transactional integrity of databases to the scalability and flexibility of lakehouse architectures.

Let’s explore these exciting new developments:

Databricks Lakebase, Snowflake Postgres, and Azure Fabric SQL Database

These three offerings are leading the charge in this new era of converged data platforms. While each has its unique flavor and strategic positioning, they all share a common goal: simplifying the data landscape for organizations.

Why This Matters: The Return to Simplicity and Velocity

This “old is new again” phenomenon isn’t just a marketing ploy; it’s a response to real-world challenges faced by organizations building modern data-driven applications:

1. Complexity Reduction: Gone are the days of needing separate teams managing distinct operational databases and analytical data warehouses, along with the complex ETL/ELT pipelines between them. These new offerings promise a more unified, simplified architecture.
2. Real-time Insights: By bringing transactional data directly into the analytical platform, the latency between operational events and actionable insights is drastically reduced. This is crucial for real-time BI, personalized experiences, and AI applications.
3. Data Consistency and Governance: A unified platform means a single source of truth and consistent governance across all data, reducing data discrepancies and compliance headaches.
4. AI Acceleration: With transactional data directly accessible within the analytical and AI platform, building and deploying intelligent applications (e.g., real-time fraud detection, personalized recommendations, AI agents) becomes significantly faster and more efficient.
5. Developer Productivity: Developers can leverage familiar relational database concepts and tools while benefiting from the scalability and modern features of cloud-native platforms.

The journey of data technology is cyclical, but each revolution builds upon the last. We’re not abandoning the lessons learned from the big data era; rather, we’re integrating them with the enduring strengths of relational databases. The result is a more elegant, efficient, and powerful approach to BI and AI solutions; proving once again that sometimes, the most innovative solutions are those that cleverly re-imagine what we thought we already knew.

Let’s say you aren’t familiar with Azure or are migrating away from Microsoft’s platform to the market share leader AWS or GCP or dare I say OCI (Oracle Cloud). Is there a comparable toolset in these clouds?

The short answer is no. These take AWS, GCP, and OCI all take a modular approach to data engineering. This means that AWS, GCP, and OCI do not have a single, direct, “one-to-one” equivalent to Microsoft Fabric because their approach is offering a collection of specialized services that, when combined, can achieve similar capabilities. To build a “Fabric-like” environment for data engineers in these cloud platforms, you would typically use a combination of these services:

AWS (AMAZON WEB SERVICES)

Let’s go over the components you’d need to have all the functionality of Microsoft Fabric.

1. Data Lake Storage (OneLake equivalent):

• Amazon S3 (Simple Storage Service): This is the foundational object storage service in AWS and serves as the primary building block for a data lake. It’s highly scalable, durable, and cost-effective for storing raw and processed data.

2. Data Integration & ETL (Data Factory equivalent):

• AWS Glue: This is a serverless ETL (Extract, Transform, Load) service that can visually develop, run, and monitor ETL workflows. It includes a Data Catalog (a metadata repository) and Glue Studio for building visual ETL jobs.
• AWS Data Pipeline: A web service that helps you reliably process and move data between different AWS compute and storage services, as well as on-premises data sources, at specified intervals.
• AWS Step Functions: A serverless orchestration service that allows you to define workflows for complex ETL processes, integrating various AWS services.
• Amazon Managed Workflows for Apache Airflow (MWAA): A managed service for Apache Airflow, a popular open-source orchestration tool for programmatically authoring, scheduling, and monitoring data workflows (DAGs).

3. Data Engineering / Big Data Processing (Synapse Spark / Databricks Notebooks equivalent):

• Amazon EMR (Elastic MapReduce): A managed cluster platform that simplifies running big data frameworks like Apache Spark, Hadoop, Hive, Presto, and Flink on AWS. It’s used for large-scale data processing and analytics.
• AWS Glue (with Spark): As mentioned, Glue uses Apache Spark under the hood for its ETL jobs, providing serverless Spark execution.
• Amazon Athena: A serverless interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL. It’s often used for ad-hoc analysis on data lakes.
• AWS Lake Formation: A service that helps you build, secure, and manage data lakes by simplifying the process of collecting, cleaning, and cataloging data, and centralizing security and access control.
• Databricks on AWS: While not a native AWS service, Databricks runs seamlessly on AWS, offering a unified platform for data engineering, data science, and machine learning, built on Apache Spark and Delta Lake. This is often chosen when users want a more managed Spark experience with the benefits of Delta Lake for a data lakehouse.

4. Data Warehousing (Synapse Data Warehouse equivalent):

• Amazon Redshift: A petabyte-scale cloud data warehouse service optimized for analytical workloads, providing fast query performance. It uses massively parallel processing (MPP) for fast query performance. Redshift also offers AI-driven scaling.
• Redshift Spectrum: Allows Redshift to query data directly from S3, enabling a data lakehouse pattern.

5. Real-time Analytics (Synapse Real-time Analytics equivalent):

• Amazon Kinesis: A family of services for real-time streaming data, including Kinesis Data Streams (for collecting and processing large streams of data records), Kinesis Data Firehose (for delivering streaming data to destinations), and Kinesis Data Analytics (for processing streaming data with SQL or Apache Flink).
• Amazon Managed Streaming for Apache Kafka (MSK): A fully managed service for Apache Kafka, allowing you to build and run applications that use Kafka for streaming data.

6. Business Intelligence (Power BI equivalent):

• Amazon QuickSight: A cloud-native business intelligence service that allows you to create interactive dashboards and reports.

7. Data Governance & Security:

• AWS Lake Formation: Centralizes security and access control for your data lake.
• AWS Identity and Access Management (IAM): Manages user access and permissions across all AWS services.
• Amazon Macie: Uses machine learning to discover and protect sensitive data in S3.

Summary:

AWS, offers a modular and highly customizable approach. While it requires you to integrate multiple services, this provides immense flexibility and allows organizations to build highly specialized data architectures tailored to their exact needs. AWS also has a broader global footprint and more mature multi-region support.

GCP (GOOGLE CLOUD PLATFORM)

Like AWS, Google Cloud Platform (GCP) also takes a modular approach rather than having a single, unified “Microsoft Fabric” equivalent. For data engineers, you would combine several services to achieve similar capabilities for data integration, processing, warehousing, and analytics.

Here’s the breakdown of GCP services that collectively form a robust data engineering and analytics platform:

1. Data Lake Storage (OneLake equivalent):

• Cloud Storage: GCP’s highly scalable and durable object storage. It serves as the primary data lake for raw and processed data, similar to S3 in AWS. You can configure buckets with different storage classes (Standard, Nearline, Coldline, Archive) to optimize costs.

2. Data Integration & ETL (Data Factory equivalent):

• Cloud Data Fusion: A fully managed, cloud-native data integration service built on the open-source Cloud Data Fusion. It provides a visual, code-optional interface for building and managing ETL/ELT pipelines, with pre-built connectors and transformations.
• Cloud Dataflow: A fully managed service for executing Apache Beam pipelines. It’s excellent for both batch and stream processing, making it suitable for complex transformations and large-scale data movement. It’s highly scalable and serverless.
• Cloud Composer: A managed Apache Airflow service. This is ideal for orchestrating complex, multi-service data workflows (DAGs) across different GCP services and external systems.
• BigQuery Data Transfer Service: A service specifically designed for automating data transfers from various SaaS applications (like Google Ads, YouTube Analytics, Salesforce) and other cloud storage services directly into BigQuery.

3. Data Engineering / Big Data Processing (Synapse Spark / Databricks Notebooks equivalent):

• Dataproc: A fully managed service for running Apache Spark, Hadoop, Hive, Presto, and other open-source big data frameworks. It allows you to create clusters quickly and run large-scale processing jobs.
• BigQuery: While primarily a data warehouse, BigQuery has significant data engineering capabilities. Its SQL interface allows for complex transformations (CREATE TABLE AS SELECT, MERGE, etc.) on massive datasets directly within the data warehouse. It’s serverless and highly scalable.
• Databricks on GCP: Similar to AWS, Databricks also offers its platform on GCP, providing a unified workspace for data engineering, data science, and machine learning, leveraging Apache Spark and Delta Lake on GCP’s infrastructure.

4. Data Warehousing (Synapse Data Warehouse equivalent):

• BigQuery: GCP’s flagship serverless, highly scalable, and cost-effective data warehouse. It’s designed for petabyte-scale analytics and offers features like columnar storage, automatic scaling, and machine learning capabilities directly within SQL. It’s a key component of the “Lakehouse” architecture on GCP.

5. Real-time Analytics (Synapse Real-time Analytics equivalent):

• Cloud Pub/Sub: A fully managed, real-time messaging service that allows you to ingest and deliver high volumes of events and data streams. It’s often used as the backbone for streaming analytics.
• Cloud Dataflow (Streaming mode): As mentioned, Dataflow can process real-time streams using Apache Beam, enabling continuous ETL and analytics on incoming data.
• BigQuery (Streaming Inserts): BigQuery supports streaming data inserts, allowing you to append new records to tables in near real-time for immediate querying.

6. Business Intelligence (Power BI equivalent):

• Looker (Google Cloud’s BI Platform): Acquired by Google, Looker is a powerful BI and data analytics platform that allows users to explore data, build dashboards, and create data applications. It integrates natively with BigQuery.
• Google Data Studio (Looker Studio): A free tool for creating interactive dashboards and reports from various data sources.

7. Data Governance & Security:

• Cloud Data Catalog: A fully managed metadata management service that helps discover, understand, and manage all your data assets across GCP and hybrid environments.
• Identity and Access Management (IAM): Manages user access and permissions across all GCP services.
• Data Loss Prevention (DLP): Helps discover, classify, and protect sensitive data across your GCP services.

Summary:

GCP’s strength lies in its serverless offerings (BigQuery, Dataflow, Pub/Sub), which significantly reduce operational overhead and allow data engineers to focus more on data logic rather than infrastructure management. BigQuery is a standout feature, offering both data warehousing and powerful data engineering capabilities within a single, highly performant service.

While AWS provides deep customization, GCP often offers a slightly more integrated feel among its separate services, especially with BigQuery serving as a central hub for many analytics workflows. Both clouds provide robust ecosystems for building sophisticated data platforms.

OCI (Oracle Cloud Infrastructure)

When considering Oracle Cloud Infrastructure (OCI) for data engineering and a “Microsoft Fabric-like” environment, it’s important to note that OCI’s approach, like AWS and GCP, is also modular. Oracle leverages its deep expertise in databases and enterprise applications to offer a suite of services for data ingestion, processing, warehousing, and analytics.

Here’s a breakdown of OCI services that, when combined, can create a comprehensive data platform for data engineers:

1. Data Lake Storage (OneLake equivalent):

• Oracle Cloud Infrastructure Object Storage: This is OCI’s highly scalable, durable, and cost-effective object storage service. It serves as the foundation for your data lake, storing raw and processed data in various formats. It’s comparable to Amazon S3 or Google Cloud Storage.

2. Data Integration & ETL (Data Factory equivalent):

• Oracle Cloud Infrastructure Data Integration: A fully managed, serverless ETL service that allows you to design, deploy, and manage data integration processes. It offers both a visual interface and code-based transformations, with connectors to various data sources, both on-premises and in the cloud.
• Oracle GoldenGate: A comprehensive software package for real-time data integration and replication. It’s often used for Change Data Capture (CDC) to ingest transactional data into a data lake or data warehouse in near real-time.
• Oracle Data Safe: While primarily a security service, it has features for data masking and discovery that can be part of an ETL pipeline for sensitive data.

3. Data Engineering / Big Data Processing (Synapse Spark / Databricks Notebooks equivalent):

• Oracle Cloud Infrastructure Data Flow: A fully managed Apache Spark service within OCI. It allows data engineers to run Spark applications on demand without managing the underlying infrastructure, ideal for large-scale data processing and transformations.
• Oracle Cloud Infrastructure Big Data Service: A managed service for Apache Hadoop, Spark, Kafka, and other big data components. This provides more control over the big data ecosystem for those who need it.
• Databricks on OCI: Databricks also has a partnership with Oracle, offering its unified analytics platform (including Apache Spark and Delta Lake) to run on OCI’s infrastructure. This provides the familiar Databricks experience within the Oracle Cloud.

4. Data Warehousing (Synapse Data Warehouse equivalent):

• Oracle Autonomous Data Warehouse (ADW): A fully managed, self-driving data warehouse service. It’s optimized for analytical workloads, scales automatically, and handles patching, backups, and tuning without manual intervention. It’s built on Oracle’s strong database foundation.
• Oracle Exadata Database Service: While not solely a data warehouse, for very large and demanding on-premises or cloud-based data warehouse deployments, Exadata offers extreme performance and scalability.

5. Real-time Analytics (Synapse Real-time Analytics equivalent):

• Oracle Cloud Infrastructure Streaming: A fully managed, scalable, and durable streaming service compatible with Apache Kafka APIs. It’s used for ingesting and processing high-volume, real-time data streams.
• Oracle GoldenGate Stream Analytics: Provides real-time analytics on streaming data, allowing for immediate insights and actions.

6. Business Intelligence (Power BI equivalent):

• Oracle Analytics Cloud (OAC): A comprehensive cloud-based platform that provides self-service data visualization, enterprise reporting, and advanced analytics capabilities. It integrates well with Oracle databases and other data sources.

7. Data Governance & Security:

• Oracle Cloud Infrastructure Data Catalog: A metadata management service that helps discover, organize, and govern data assets across OCI and other data sources.
• Oracle Cloud Infrastructure Identity and Access Management (IAM): Manages user access and permissions across all OCI services.
• Oracle Data Safe: Provides data security, including auditing, data masking, and sensitive data discovery.

Summary:

OCI’s strength for data engineers lies in its strong database heritage, particularly with Autonomous Data Warehouse, which offers a robust and easy-to-manage solution for analytical workloads. OCI also provides a good set of managed services for Spark and data integration.

While OCI has been growing its cloud offerings, it generally has a smaller market share compared to AWS, Azure, and GCP, which might mean a smaller community or fewer third-party integrations for some niche tools. However, for organizations already heavily invested in Oracle technologies or looking for a potentially more cost-effective cloud solution for specific workloads, OCI presents a viable and increasingly competitive option.

What’s next

Over the next few weeks we’ll be diving into AWS, creating sample data engineering projects and integrating them with AWS hosted AI solutions in Bedrock. Be sure to like and subscribe to the blog for the latest updates!

Like the content? Donations are welcome.

Many of us manage databases or applications that contain regulated data that can’t leave a production environment. This means that we need to “clean” the data if it’s going to be used in QA or development work and one common way to de-identify the data is to simply update columns like firstname and lastname with a simple format “firstname” + counter; however, this results in all the data being unique and sequential. Firstname1, firstname2, firstname3, …
This isn’t good for getting like for like results with a production database and can lead to questions we’ve heard before in the workplace like “Why didn’t we catch this in QA?”

Dawn of AI

We have so many new tools at our fingers with prompt AI like ChatGPT being able to give suggestions on email formatting and content, research detail on Azure or other topics, creating stories or even poems. I asked myself, “Can ChatGPT generate data for SQL Server (and other databases)?”

The answer is yes; but, it comes with limitations. Let’s discuss…

Generating Realistic Test Data with AI

At first, I questioned if ChatGPT could create test data for a table schema at all, since it uses a generic model that isn’t specific to SQL code. I prompted “Create test data for my table with the following schema: create table x (id int, firstname varchar(20), lastname varchar(20), lastpurchasedate datetime2(0))” and the result was surprisingly good!!

Next I wondered how fancy I could get and asked for some of the values in date columns to be null. Yet again I was quite pleased with the result.

Knowing that the AI can generate test data I figured, why not create 5000 rows that I could dump into my table and get straight to testing. Let’s push this thing and see what we get. The result was not quite what I wanted. In fact it produced code that creates data with the exact issue described in the opening paragraphs of this post. Non-realistic, sequential test data is not what we’re after.

Why not prompt it to create “realistic data”?

OK This code is good but there’s a big problem with it. It doesn’t actually generate any test data it assumes there is a firstname and lastname table to pull values from. But wait, We can use this code if we generate smaller batches of names using the prompts, right? Let’s see.  How many names can we generate at one time? The answer is 50. I tried 5000, 100, and then 50. Anything after 50 will result in 50 names and a comment saying “Add more data here”, lol. We can work with this though!

Prompt ChatGPT to “generate 50 realistic first names not based on a counter”.

We can use the strength of this AI to give us many diverse names to use by prompting different countries or regions like “US, France, Germany” or “American, French, German, …” names. Just remember that the real limit is 50. You can even prompt the AI to give you 50 names that are not in a list of names, giving you new names to work with. So if you want 100 Australian names, prompt it for the first 50 then prompt it for another 50 but ask it not to include the names in the original 50 you just got.

Now that we have a few hundred first and last names we can use the code we got earlier to generate test data for our application dev and qa work.

Here’s the simple code I came up with. Keep in mind you can use the first names and last names that you generate to randomly assign data in your “de-identification” process as well. This code is just a very simple example to get you thinking of the possibilities.

— random data generation
SET NOCOUNT ON;

— Create a temporary table for German first names
CREATE TABLE #FirstNames (firstname varchar(20));

— Enable random data generation
SET NOCOUNT ON;

— Insert 50 random German first names
INSERT INTO #FirstNames (firstname)
VALUES
(‘Maximilian’), (‘Sophie’), (‘Lukas’), (‘Mia’), (‘Finn’),
(‘Lena’), (‘Leon’), (‘Emma’), (‘Luca’), (‘Hannah’),
(‘Benjamin’), (‘Marie’), (‘David’), (‘Anna’), (‘Elias’),
(‘Laura’), (‘Tim’), (‘Lea’), (‘Paul’), (‘Johanna’),
(‘Julian’), (‘Emilia’), (‘Jan’), (‘Sophia’), (‘Noah’),
(‘Amelie’), (‘Felix’), (‘Mila’), (‘Simon’), (‘Clara’),
(‘Moritz’), (‘Elena’), (‘Jonas’), (‘Victoria’), (‘Oskar’),
(‘Mara’), (‘Nico’), (‘Isabella’), (‘Erik’), (‘Maria’),
(‘Julius’), (‘Fiona’), (‘Tom’), (‘Maja’), (‘Anton’),
(‘Sofia’), (‘Vincent’), (‘Emily’), (‘Jakob’);

— Insert 50 random French first names into the existing temporary table #lastnames
INSERT INTO #FirstNames (firstname)
VALUES
(‘Léa’), (‘Lucas’), (‘Chloé’), (‘Louis’), (‘Emma’),
(‘Gabriel’), (‘Manon’), (‘Jules’), (‘Inès’), (‘Léo’),
(‘Camille’), (‘Hugo’), (‘Sarah’), (‘Arthur’), (‘Alice’),
(‘Raphaël’), (‘Zoé’), (‘Enzo’), (‘Louise’), (‘Nathan’),
(‘Eva’), (‘Théo’), (‘Clara’), (‘Maël’), (‘Charlotte’),
(‘Tom’), (‘Lola’), (‘Antoine’), (‘Julia’), (‘Mathis’),
(‘Lea’), (‘Paul’), (‘Mia’), (‘Nathan’), (‘Emma’),
(‘Alexandre’), (‘Lina’), (‘Matheo’), (‘Lou’), (‘Ethan’),
(‘Jade’), (‘Evan’), (‘Léna’), (‘Noah’), (‘Lilou’),
(‘Louis’), (‘Elena’), (‘Sacha’), (‘Mila’), (‘Leo’);

— Insert 50 random American first names into the existing temporary table #lastnames
INSERT INTO #firstNames (firstname)
VALUES
(‘Emma’), (‘Liam’), (‘Olivia’), (‘Noah’), (‘Ava’),
(‘Isabella’), (‘Sophia’), (‘Jackson’), (‘Lucas’), (‘Mia’),
(‘Oliver’), (‘Ethan’), (‘Aiden’), (‘Harper’), (‘Evelyn’),
(‘Abigail’), (‘Emily’), (‘Amelia’), (‘Benjamin’), (‘Ella’),
(‘Alexander’), (‘Henry’), (‘Jacob’), (‘Michael’), (‘Daniel’),
(‘William’), (‘James’), (‘Elijah’), (‘Avery’), (‘Sofia’),
(‘Grace’), (‘Scarlett’), (‘Jack’), (‘Chloe’), (‘Lily’),
(‘Luna’), (‘Zoe’), (‘Mila’), (‘Stella’), (‘Aria’),
(‘Eleanor’), (‘Hazel’), (‘Violet’), (‘Leo’), (‘Aurora’),
(‘Levi’), (‘Lucy’), (‘Nora’), (‘Ethan’), (‘Ava’);

— Select the generated German first names
SELECT * FROM #FirstNames;

— Create a temporary table for German last names
CREATE TABLE #LastNames (lastname varchar(20));

— Insert 50 random German last names
INSERT INTO #LastNames (lastname)
VALUES
(‘Schmidt’), (‘Müller’), (‘Schneider’), (‘Fischer’), (‘Weber’),
(‘Meyer’), (‘Wagner’), (‘Becker’), (‘Schulz’), (‘Hoffmann’),
(‘Koch’), (‘Bauer’), (‘Richter’), (‘Wolf’), (‘Schröder’),
(‘Schwarz’), (‘Hartmann’), (‘Lange’), (‘Schmitt’), (‘Werner’),
(‘Schmitz’), (‘Krause’), (‘Maier’), (‘Lehmann’), (‘Herrmann’),
(‘Köhler’), (‘Klein’), (‘Walter’), (‘Schuster’), (‘Böhm’),
(‘Seidel’), (‘Lorenz’), (‘Schwarz’), (‘Bergmann’), (‘Engel’),
(‘Vogel’), (‘Hahn’), (‘Kramer’), (‘Frank’), (‘Sommer’),
(‘Kühn’), (‘Barth’), (‘Zimmermann’), (‘Graf’), (‘Peters’),
(‘Krüger’), (‘Jäger’), (‘Gross’), (‘Voigt’);

— Insert 50 random French last names into the existing temporary table #lastnames
INSERT INTO #lastnames (lastname)
VALUES
(‘Martin’), (‘Bernard’), (‘Dubois’), (‘Thomas’), (‘Robert’),
(‘Richard’), (‘Petit’), (‘Durand’), (‘Leroy’), (‘Moreau’),
(‘Simon’), (‘Laurent’), (‘Lefevre’), (‘Michel’), (‘Garcia’),
(‘David’), (‘Bertrand’), (‘Roux’), (‘Vincent’), (‘Fournier’),
(‘Morel’), (‘Girard’), (‘Andre’), (‘Lefevre’), (‘Mercier’),
(‘Dupont’), (‘Lambert’), (‘Boucher’), (‘Colin’), (‘Caron’),
(‘Renaud’), (‘Picard’), (‘Lemoine’), (‘Lecomte’), (‘Leger’),
(‘Dufour’), (‘Perrin’), (‘Marchand’), (‘Leclerc’), (‘Begue’),
(‘Gauthier’), (‘Faure’), (‘Rousseau’), (‘Blanc’), (‘Guerin’),
(‘Bouvier’), (‘Riviere’), (‘Chevalier’), (‘Beaumont’), (‘Lemaire’);

— Insert 50 random American last names into the existing temporary table #lastnames
INSERT INTO #lastnames (lastname)
VALUES
(‘Smith’), (‘Johnson’), (‘Williams’), (‘Jones’), (‘Brown’),
(‘Davis’), (‘Miller’), (‘Wilson’), (‘Moore’), (‘Taylor’),
(‘Anderson’), (‘Thomas’), (‘Jackson’), (‘White’), (‘Harris’),
(‘Martin’), (‘Thompson’), (‘Garcia’), (‘Martinez’), (‘Robinson’),
(‘Clark’), (‘Rodriguez’), (‘Lewis’), (‘Lee’), (‘Walker’),
(‘Hall’), (‘Allen’), (‘Young’), (‘Hernandez’), (‘King’),
(‘Wright’), (‘Lopez’), (‘Hill’), (‘Scott’), (‘Green’),
(‘Adams’), (‘Baker’), (‘Gonzalez’), (‘Nelson’), (‘Carter’),
(‘Mitchell’), (‘Perez’), (‘Roberts’), (‘Turner’), (‘Phillips’),
(‘Campbell’), (‘Parker’), (‘Evans’), (‘Edwards’), (‘Collins’),
(‘Stewart’);

— Select the generated German last names
SELECT * FROM #LastNames;

— Enable random data generation
SET NOCOUNT ON;

create table #x (id int identity(1,1), firstname varchar(25), lastname varchar(25), lastpurchasedate datetime2(3))

— Declare variables
DECLARE @Counter INT = 1;

— Loop to insert 5000 rows
WHILE @Counter <= 5000
BEGIN
INSERT INTO #x (firstname, lastname, lastpurchasedate)
VALUES
(
(SELECT TOP 1 firstname FROM #FirstNames ORDER BY NEWID()),
(SELECT TOP 1 lastname FROM #LastNames ORDER BY NEWID()),
CASE WHEN @Counter % 5 = 0 THEN NULL
ELSE DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % (365*3) /*3 years*/, GETDATE()) END);

— Increment counter
SET @Counter = @Counter + 1;
END;

select * from #x

In one of my next posts, I hope to use Azure’s Open AI resource to implement this concept further. Keep an eye out for that post.

Back then the idea that computers could be used to identify objects was just an idea for the movies. Today is a different story. Cognitive services do what was the unthinkable of which identifying people, brands, and other objects, classifying videos, predicting outcomes with machine learning are just a few.

Let’s say you have a need to count cars going into and out of a parking garage. With Azure’s Computer Vision service this task is made easy.

What you’ll need

• An Azure subscription
• Computer vision service
• Simple understanding of python
• Visual Studio Code
• An image of a vehicle

The code

from azure.cognitiveservices.vision.computervision import ComputerVisionClient

from azure.cognitiveservices.vision.computervision.models import OperationStatusCodes

from azure.cognitiveservices.vision.computervision.models import VisualFeatureTypes

from msrest.authentication import CognitiveServicesCredentials

from array import array

import os

from PIL import Image

import sys

import time

subscription_key = “YOUR KEY HERE”

endpoint = “https://YOURSERVICEHERE.cognitiveservices.azure.com/&#8221;

computervision_client = ComputerVisionClient(endpoint, CognitiveServicesCredentials(subscription_key))

#print(“===== Setup =====”)

class Point:

def __init__(self, x, y):

self.x = x

self.y = y

# check if object intersects line

def doOverlap(l1, r1, l2, r2):

if((l1.x >= r2.x or l2.x >= r1.x) or (r1.y <= l2.y or r2.y <= l1.y)):

return False

return True

#print(“===== Read File – remote =====”)

remote_image_url = “https://images.squarespace-cdn.com/content/v1/59e53e59fe54efe0c0aa77a1/1509049359179-SM0VROIMGTI9H1IYNHF5/GateLot-Car-Entering.png?format=1500w&#8221;

#print(“===== Detect Objects – remote =====”)

detect_objects_results_remote = computervision_client.detect_objects(remote_image_url)

#lines for vehicles driving in/out.

EnterLine = ((813, 460), (1159,460))

ExitLine = ((25, 592), (358, 592))

if len(detect_objects_results_remote.objects) == 0:

print(“No objects detected.”)

else:

for object in detect_objects_results_remote.objects:

if object.object_property == “car” or object.parent.object_property == “car”:

rect = ((object.rectangle.x, object.rectangle.y), (object.rectangle.x + object.rectangle.w, object.rectangle.y + object.rectangle.h))

EnterPoint1 = Point(EnterLine[0][0], EnterLine[0][1])

EnterPoint2 = Point(EnterLine[1][0], EnterLine[1][1])

ExitPoint1 = Point(ExitLine[0][0], ExitLine[0][1])

ExitPoint2 = Point(ExitLine[1][0], ExitLine[1][1])

VehiclePoint1 = Point(rect[0][0], rect[0][1])

VehiclePoint2 = Point(rect[1][0], rect[1][1])           

if(doOverlap(EnterPoint1, EnterPoint2, VehiclePoint1, VehiclePoint2)):

print(object.object_property + ” Entering”)

elif(doOverlap(ExitPoint1, ExitPoint2, VehiclePoint1, VehiclePoint2)):

print(object.object_property + ” Exiting”)

else:

print(“Nothing to report”)

WordPress doesn’t do a great job with formatting code but the images at the end of the post may help with readability.

The objective of this code is to count cars coming and going from a parking garage. Using the sample image I found searching Bing.com, we see an SUV entering a garage, but how can we define that the vehicle is entering?
To do this, I’ve added some code to draw a line named EnterLine and another named ExitLine.

Then we detect the vehicle using the comupter vision service by calling detect_objects and checking the object_property for each object to validate if the object is a “car”.

It’s really important to know that this code is using the default model, this means that partial cars may not be picked up as well as if you had a custom trained model. For instance, If you have two cars in line to go in and one is partially blocking the other, would the vision service pick up both, one, or none? Give it a try and find out!

computervision_client.detect_objects(remote_image_url)

Finally, we run the doOverlap function we wrote above to see if any of the objects overlap the lines we drew and print the results.

# check if object intersects line

def doOverlap(l1, r1, l2, r2):

if((l1.x >= r2.x or l2.x >= r1.x) or (r1.y <= l2.y or r2.y <= l1.y)):

return False

return True

Now that we have some sample code that determines if a vehicle is entering or exiting the garage, we can add more code to either increment or decrement a value to determine how many spaces are available in the garage and report the value to a mobile app, website, or digital sign at the entrance; however, that’s a post for another day.

If you haven’t been a DBA that’s OK too. Years ago I came up with my own naming standard for SQL Agent artifacts and I’ve always felt better when the messy room was clean. No Really! That’s exactly what this is like. A messy room where you are pretty sure you put the item you’re looking for in but you just can’t seem to find it until you clean 95% of the mess and then you’re so exhausted that you don’t have time to do what you wanted to in the first place. Ever been there?

If you’re new to Azure Purview or don’t know what it is check out this resource which gives a great overview of this Data Catalog and Governance platform:  https://docs.microsoft.com/en-us/azure/purview/overview.

Scan Naming Best Practice

I’ve blogged previously on the SQL Agent naming standard which I applied to Azure Data Factory (ADF) triggers and you can find that here: https://sqltechblog.com/2020/04/30/azure-data-factory-trigger-tip/

The catch with Azure Purview is you can’t schedule a scan daily. To do this you’d need to setup a scan for each day of the week and that’s really unnecessary. Instead Purview allows you to configure a weekly, monthly, or on-demand (once) scan. Since we’re talking about meta data for assets that shouldn’t change frequently weekly or monthly is just fine; but, this breaks my naming standard for ADF and SQL Agent.

Let’s take a look at the messy room. To find scans that have been run you need to choose insights from the menu on the left and then click “Scans”, finally choose “View more” on the chart. Once clicked you’ll get a grid showing all scans for the last 30 days, which you can filter by name or change the time range. It should look like this but with extra columns for success, failed, canceled counts and more. I took those out because they aren’t needed for this topic. Let’s have a look!

The first thing you may notice is that Last scan run date and Scan name are not in any order. Yes, you can click the header and order the grid results but why?

Next you probably see that all these scans and the detail provided is really not helpful if you want to know what the resource is that was scanned. Which data lake was scanned? Are these all the same one? BUT, WAIT! That last line!! It’s different!!! Now’s the time where I say, I’ll give you $20 if you can’t tell me which server this scanned and when it runs.

Using a proper naming standard helps us keep the room clean. Just from that last line, I know this ran for a SQL Server named MySQLServer001 and it runs every Thursday at 2 PM.

I’m not saying you need to follow my standard or that you need one at all. What I am suggesting is that life will be happier if you identify a naming standard that works for you and adopt it for your organization.

Here’s what I’ve come up with:

Purview scans are Weekly, Monthly, or Once and scan names cannot exceed 63 characters. Knowing this we want to have a name that is easily readable from Purview Studio’s grid. My standard is [DataSource]-[day]-[time] for daily, [DataSource]-[DD]-[time] for monthly, and [DataSource]-[YYMMDDHH] for once.

MySQLServer001-Mon-2300 = Scans MySQLServer001 every Monday at 11 PM.

MySQLServer001-08-0500 = Scans MySQLServer001 on the 8th day of each month at 5 AM.

MySQLServer001-21081214 = Scanned MySQLServer001 once in the 2 PM hour on 8/12/21.

It’s worth noting that I didn’t account for scans that run every other month or every few months. I’d suggest running your scans more frequently than every quarter and with that said I didn’t bother thinking of anything past monthly but you “could” do [datasource]-[nM]-[DD]-[time] if you had a need to scan infrequently. In this case n represents the number of months (up to 5) and M is there for context.

Luckily Azure Purview has a number of different ways you can interact with its APIs. Purview CLI is a rich tool for the command line, PyApacheAtlas is a python based tool, and you can .NET or even Kafka. For this topic we’ll use PyApacheAtlas, which I’m sure you already knew based on the blog title.

What’s in this post?

This post is going to be restricted to only SQL Server Table Columns and only Extended Properties named MS_Description. Quite a few years ago I worked on a data catalog project where we added descriptions for many of the tables, views, and columns to the database using extended properties named MS_Description. Let’s assume you have some of these for this post keeping in mind that the Purview APIs provide so many functions beyond what this post covers and that the code here could be modified to do so much more as well.

Starting out I thought it would be great to import the sensitivity classifications that SSMS creates. Pre-SQL 2019 these were held in Extended Properties and now have their very own DMV (sys.sensitivity_classifications). While this sounded great in theory it wasn’t as exciting when I wrote the code. This is because Azure Purview already has system classifications at a more granular scale for each of the ones you find in SSMS and Purview also adds these as it executes a scan on the data source. It does a pretty good job too. With that said, I shifted my focus to adding descriptions instead.

Remember these in SSMS? 

Using PyApacheAtlas with Purview

There’s a bit of upfront work to get the script running, you’ll need at a minimum:

• Azure account
• Azure Purview installed and configured
• Python and PyApacheAtlas installed
• Credentials (Tenant ID, Client ID, Client Secret, and the name of your Purview instance)
• Likely you’ll also need a machine with internet access and a keyboard.

Once you’ve collected these items you should be good to go. There are plenty of guides on Bing.com to help with configuring a service principal or Purview; so, I’m not going into detail on those here.

Let’s jump into the code! First we’ll cover the different sections of code and then the whole script will be posted at the bottom.

This first section imports any necessary items and authenticates with Purview. Note the sections starting on line 14 and 19. This is where you’ll need to fill in your environment’s data to successfully authenticate. Each of these values is blank because you really don’t want my credentials, right?

You’ll also want to change line 5 to point to whatever SQL Server you want to pull descriptions from.

In this next bit of code, starting on line 24, we query The SQL Server instance and load all the database names into a list named searchdbs. We’ll loop over this list to query each individual database for extended properties.

Looping over each database, we run a T-SQL query to grab the extended properties. This query has been limited to only tables and just MS_Description but could easily be changed. Keep in mind we are only after the description for this sample.

Lastly, we review each row from the query and try to add the description with a partial update using partial_update_entity. Caution! This will overwrite the existing description.

Note: For this sample, we’re allowing the update to fail if the entity doesn’t exist in Purview. You could easily add a check before hand with an IF condition to evaluate if the asset existed and if not skip. Keep in mind that Purview is currently limited to 10 million or 40 million API calls per month depending on your SKU. Reducing API calls to a minimum and using bulk API operations is highly recommended.

To pull of a successful update you’ll need a couple things:  The qualifiedName and the Purview typeName.

Using Purview Studio we can see that the type of this column is “MS SQL Column”.  The typeName for “MS SQL Column” is “mssql_column”. That is exclusive for a table column. Yes, it sounds silly but view columns have their own type so your query would need to account for this and put the appropriate typeName for your asset. For the record, a view column is “mssql_view_column”.

In Purview the qualifiedName follows a naming standard that is fairly simple. For relational databases this is:  type://highest level/n level/n level/…#lowest level. Using an easy naming standard allows us to programmatically build the qualifiedName in our query.

For SQL Server this would be: mssql://server/instance/db/schema/table#column

Take a look at the following example for a real world qualifiedName.

Also noted in the picture above is the description that our python code is targeting. Once you’ve added all the credentials and have successfully added your SQL Server to Purview and scanned it you should be ready to run the code and add your descriptions. Yay!

Here’s the full code (without descriptions):

import json
import os
import pyodbc
#this is the location of your SQL Server
server = ‘DESKTOP’
database = ‘master’
driver= ‘{ODBC Driver 17 for SQL Server}’

#setup authentication for Azure Purview
from pyapacheatlas.auth import ServicePrincipalAuthentication
from pyapacheatlas.core.client import PurviewClient
from pyapacheatlas.core.typedef import TypeCategory, ClassificationTypeDef
from pyapacheatlas.core.entity import AtlasClassification
oauth = ServicePrincipalAuthentication(
     tenant_id=os.environ.get(“AZURE_TENANT_ID”, “”),
     client_id=os.environ.get(“AZURE_CLIENT_ID”, “”),
     client_secret=os.environ.get(“AZURE_CLIENT_SECRET”, “”)
)
client = PurviewClient(
     account_name=os.environ.get(“PURVIEW_NAME”, “”),
     authentication=oauth
)

#Query SQL Server for a list of databases
with pyodbc.connect(‘DRIVER=’+driver+’;SERVER=’+server+’;trusted_connection=Yes;’) as conn:
     with conn.cursor() as cursor:
         cursor.execute(“SELECT name FROM sys.databases where database_id > 4”)
         alldbrows = cursor.fetchall()

    databases = dict(enumerate(db[0] for db in alldbrows))
     searchdbs = list(databases.values())

    #For each database query extended properties and sensitivity labels
     for db in searchdbs:
         #print(db)
         #with pyodbc.connect(‘DRIVER=’+driver+’;SERVER=’+server+’;trusted_connection=Yes;’) as searchconn:
         with pyodbc.connect(‘DRIVER=’ + driver + ‘;SERVER=’ + server + ‘;PORT=1433;DATABASE=’ + db + ‘;trusted_connection=Yes;’) as searchconn:
             with searchconn.cursor() as dbcursor2:
                 #query only looks at tables. SQL Server columns in tables for Purview are type mssql_column. Views are mssql_view_column.
                 dbcursor2.execute(”    SELECT CONCAT(‘mssql://’, @@SERVERNAME, ‘/’, TABLE_CATALOG, ‘/’, TABLE_SCHEMA, ‘/’, TABLE_NAME, ‘#’, COLUMN_NAME) AS qualifiedName, “
                                 ”        ‘mssql_column’ as typeName, “
                                 ”        CONVERT(nvarchar(4000), DATA_TYPE) as dataType, “
                                 ”        CONVERT(nvarchar(4000), ex.value) as columndescription “
                                 ”    FROM INFORMATION_SCHEMA.COLUMNS ISC “
                                 ”    OUTER APPLY ::fn_listextendedproperty(null,’Schema’, isc.TABLE_SCHEMA, ‘Table’, isc.TABLE_NAME, ‘Column’, isc.COLUMN_NAME) ex “
                                 ”    WHERE ex.value is not null and ex.name = ‘MS_Description'” )
                 row = dbcursor2.fetchone()
                 while row:
                     print (“Processing:” + str(row[0]) + ” | ” + str(row[1]) + ” | ” + str(row[2]) + ” | ” + str(row[3]))
                     qName = str(row[0])
                     tName = str(row[1])
                     dName = str(row[2])
                     columndescription = str(row[3])

                    try:
                         client.partial_update_entity(
                             typeName= tName,
                             qualifiedName= qName,
                             attributes={“description”:columndescription}
                             )
                         print(f”\tUpdated {qName}”)
                     except Exception as e:
                         print (f”\t{e} : This asset may not exist in Purview. Setup the datasource and scan the database to add it. Validate it exists by searching the qualifiedName ({qName})”)

                    #Move to next row in resultset
                     row = dbcursor2.fetchone()

For many more sample scripts with PyApacheAtlas and Purview:

https://github.com/wjohnson/pyapacheatlas

I’m excited and happy to announce that I’m part of the AWS Community Builders.  Check out more on Twitter with the #AWScommunity tag. To learn more about the program and other cool things that AWS is doing in the cloud, check out this blog from AWS:

https://aws.amazon.com/blogs/aws/announcing-the-new-aws-community-builders-program/