ELT data pipeline pattern

Extract, Load, and Transform (ELT) Pipeline Pattern

• The Extract, Load, and Transform (ELT) architecture diagram is reviewed.
• A common ELT pipeline on Google Cloud is examined.
• BigQuery's SQL scripting and scheduling capabilities are described.
• The functionality and use cases for Dataform are explained.

Exploring ELT Architecture, SQL Scripting, and Dataform

• The Extract, Load, and Transform (ELT) architecture, SQL scripting and scheduling with BigQuery, and Dataform are explored.
• Data is first loaded into BigQuery.
• There are multiple ways to transform data, including procedural languages like SQL, scheduled queries, scripting, and programming languages.
• Dataform simplifies transformations beyond basic programming.

ELT Pipeline Transformations in BigQuery

• Structured data is loaded into staging tables in BigQuery.
• Transformations are applied within BigQuery using SQL scripts or tools like Dataform with SQL workflows.
• Transformed data is moved to production tables for use.
• This approach leverages BigQuery's processing power for efficient data transformation.

BigQuery Procedural Language Queries

• BigQuery supports procedural language queries for executing multiple SQL statements in sequence.
• Multiple statements run in a sequence with shared state.
• Management tasks (e.g., creating or dropping tables) are automated.
• Complex logic is implemented using programming constructs like IF and WHILE.
• User-created variables or existing BigQuery system variables can be declared and referenced.
• BigQuery supports transactions (COMMIT, ROLLBACK).

User-Defined Functions (UDFs)

• BigQuery supports user-defined functions (UDFs) or custom data transformations in SQL or JavaScript.
• UDFs can be persistent (CREATE FUNCTION) or temporary (CREATE TEMPORARY FUNCTION).
• SQL is preferred for UDFs when possible.
• JavaScript functions can utilize additional input libraries.
• Community-contributed UDFs are available for reuse in BigQuery.

Stored Procedures

• Stored procedures are pre-compiled SQL statement collections used to streamline database operations.
• They enhance performance and maintainability.
• They are reusable, customizable (through parameters), and capable of handling transactions.
• Stored procedures are called from applications or within SQL scripts.

Running Stored Procedures on Apache Spark in BigQuery

• Stored procedures for Apache Spark can be defined using the BigQuery PySpark editor or the CREATE PROCEDURE statement with Python, Java, or Scala code.
• Code can be stored in Cloud Storage or defined inline within the BigQuery SQL editor.

Remote Functions for Complex Transformations

• Remote functions extend BigQuery's capabilities using Python code.
• Integrate with Cloud Run functions for complex data transformations.
• Functions are defined in BigQuery, specifying connection and endpoints to Cloud Run functions. -Direct integration to code hosted on Cloud Run functions.
• Function calls occur in a way that's analogous to UDFs.

Jupyter Notebooks for Exploration and Transformation

• Jupyter Notebooks coupled with BigQuery DataFrames facilitates efficient data exploration and transformations.
• Handles large datasets that exceed runtime memory using SQL or Python.
• Schedules notebook executions.
• Integration with visualization libraries like matplotlib, seaborn, and others.

Saving and Scheduling Queries

• BigQuery allows saving and scheduling queries for repeated use.
• Version control of queries is supported.
• Queries can be shared with users or groups.
• Downloading as .sql files or uploading other queries from similar files is possible.

Post-Query Operations

• Additional tasks (e.g., SQL scripts, data quality tests, security measures) after a scheduled query in BigQuery can be automated.

Dataform for ELT Pipelines

• Dataform is a serverless framework to develop and operationalize ELT pipelines in BigQuery using SQL.
• It streamlines data transformation, assertion, and automation.
• It ensures the quality of data and builds data transformations efficiently and manages the data transformation process using SQL.
• It reduces the amount of error and the time that is required.
• Tables and views are created to compile into SQL statements.
• Key configuration types include declaration, table, incremental, and view.

Dataform SQL Workflows

• Dataform compiles SQL definitions and chains them into workflows.
• The compiled graph visualizes the SQL workflow, including definitions and dependencies.
• Workflows can be scheduled and executed on a recurring basis.

Dataform SQL Workflows and Scripts

• SQL workflows within Dataform are visualized through graphs.
• Dataform compiles definitions into executable SQL scripts and organizes them in a workflow.
• SQL workflows can be handled through scheduled runs or manually executed in Dataform (UI).

SQL Development in Dataform

• SQL code is reorganized and simplified using definitions.
• The example showcases boilerplate code replacement with more concise definitions.
• Streamlines SQL code by replacing repetitive patterns.
• The approach helps enhance code readability and promotes reusability.

Assertion and Operations in Dataform

• Dataform utilizes assertions for data quality testing to ensure consistency and accuracy.
• Operations let you run custom SQL statements before, after, or during pipeline execution, for flexibility and custom data transformations.

Dependencies in Dataform

• Dataform offers implicit and explicit declaration methods to manage dependencies between objects.

Lab: Creating and Executing a SQL Workflow in Dataform

• The Lab involves creating DatForm repository, workspace, executions, and logs.