Meta tags:
description=A real-world ELT pipeline with Dataform and BigQuery — staging and mart layers, JavaScript includes, dynamic tables and views, GitHub sync over SSH and Terraform provisioning. Tagged with dataform, bigquery, googlecloud, dataengineering.;
keywords=dataform, bigquery, googlecloud, dataengineering, software, coding, development, engineering, inclusive, community;

Headings (most frequently used words):

dataform, the, elt, repo, to, mart, with, google, cloud, of, use, case, in, project, layer, step, world, cup, qatar, presented, this, structure, connect, github, from, airflow, dag, pipeline, and, on, dev, community, explanation, article, usual, console, terraform, conclusion, tosun, si, orchestration, deploy, composer, top, comments, ci, cd, applicative, configuration, definitions, models, staging, column, descriptions, reusable, function, compute, player, statistics, dynamic, tables, views, showing, an, using, more, developer, experts,

Text of the page (most frequently used words):
the (193), and (58), dataform (50), #fullscreen (34), mode (34), for (33), with (32), this (28), github (26), ssh (22), #google (21), file (21), from (20), you (19), repository (19), key (18), cloud (17), bigquery (17), data (17), exit (17), enter (17), appearances (16), use (14), more (14), player (14), dev (13), using (13), that (12), are (12), table (12), elt (12), models (12), playername (12), position (12), club (12), project (11), javascript (11), terraform (11), playerdob (11), brandsponsorandused (11), your (10), one (10), view (10), pipeline (10), type (10), build_player_stats (10), create (9), code (9), model (9), article (9), case (9), logic (9), mart (9), secret (9), description (9), columns (9), configuration (8), config (8), repo (8), world (8), click (8), select (8), sqlx (8), team_players_stat_functions (8), nationality (8), where (7), share (7), community (7), workflow (7), when (7), structure (7), descriptions (7), safe_cast (7), account (6), top (6), into (6), raw (6), all (6), can (6), staging (6), git (6), com (6), var (6), private (6), folder (6), link (6), then (6), function (6), step (6), statindicator (6), layer (6), each (5), dag (5), managed (5), variables (5), files (5), stats (5), real (5), dynamic (5), also (5), column (5), main (5), region (5), name (5), manager (5), version (5), service (5), which (5), views (5), tables (5), const (5), teamname (5), same (5), includes (5), statistics (5), columns_descriptions (5), goalkeeperstatsperteam (5), goalsscored (5), execution (5), open (4), source (4), experts (4), actions (4), store (4), airflow (4), players (4), transformations (4), presented (4), default (4), across (4), repositories (4), gitops (4), gcp (4), such (4), connection (4), rsa (4), used (4), public (4), host (4), variable (4), run (4), defines (4), workspace (4), typically (4), created (4), generated (4), example (4), how (4), dynamically (4), ctx (4), columntoselect (4), viewname (4), domain (4), dbt (4), them (4), sql (4), struct (4), reusable (4), team (4), nationalteamkitsponsor (4), fifaranking (4), team_player_stat_columns_descriptions (4), block (4), statraw (4), team_players_stat_raw (4), assistsprovided (4), savepercentage (4), specific (4), float64 (4), built (3), software (3), agent (3), adk (3), googlecloud (3), content (3), developer (3), explore (3), abuse (3), comments (3), but (3), via (3), medium (3), input (3), gcs (3), load (3), https (3), video (3), qatar (3), cup (3), tosun (3), its (3), single (3), truth (3), projects (3), fully (3), best (3), layers (3), dataform_repo_name (3), project_id (3), provider (3), over (3), versions (3), locals (3), string (3), creation (3), europe (3), west1 (3), declared (3), backend (3), instead (3), connect (3), containing (3), next (3), copy (3), way (3), managing (3), publish (3), foreach (3), countryname (3), team_players_stat (3), ref (3), query (3), tablename (3), bestpassers (3), topscorers (3), goalkeeper (3), here (3), generate (3), country (3), details (3), module (3), exports (3), order (3), scorers (3), top_scorers_columns (3), national (3), number (3), goals (3), scored (3), directly (3), ingestiondate (3), tacklesperninety (3), interceptionsperninety (3), totalduelswonperninety (3), dribblesperninety (3), goalkeeperstatsstruct (3), int64 (3), definitions (3), workflow_settings (3), yaml (3), start (3), log (2), date (2), their (2), other (2), conduct (2), free (2), keep (2), development (2), docker (2), jev (2), iceberg (2), mcp (2), what (2), takes (2), dataengineering (2), become (2), join (2), network (2), than (2), get (2), follow (2), may (2), hide (2), well (2), sure (2), comment (2), will (2), post (2), report (2), let (2), quickly (2), subscribe (2), linkedin (2), youtube (2), devops (2), deploy (2), composer (2), loaded (2), cold (2), move (2), storage (2), following (2), youtu (2), french (2), english (2), fifa (2), pattern (2), business (2), showing (2), available (2), choice (2), readability (2), like (2), support (2), makes (2), production (2), iac (2), hands (2), setup (2), practices (2), functions (2), documentation (2), github_account_host_public_ssh_key_value (2), local (2), github_account_private_ssh_key_secret_version (2), url (2), service_account_email (2), beta (2), creates (2), aaaab3nzac1yc2eaaaadaqabaaabgqcj7ndnxqowgcqnjshclrqpeiiphnt (2), vttvdp6mhbl9j1anuky4ue1gvwnglvlohgeyrnzamgrk6 (2), pkcuxadbc7qtbw8gikhl7agcsor (2), c56sjmy (2), bczfxd1nwzaoxsdpgvsmerobyfnqltv9 (2), hwcqbywinir (2), 5dig6jtj72pcepejcygxke2yefxv1jhnskgblwnlhscqb2umyrkqyytrltl (2), 38tgxkxcflmo (2), 5z8cssny7gidjmiz7q4zmja2n1ngrltdkzwdcsw (2), wqfpgqa179cnfgwowrvruj16z6xyvxvjjwbz0wqz75xk5tksb7fnyeies4tt4jk (2), s4dhpeauc5y (2), bdyirygm4gc7uenztnzyavwq7b381ak4qdrwt51zqexkbqptunn (2), ejqotwvqnj4kqx5quci0ths (2), ykoxjcxmpuwzbhjpcg56i (2), 2ab6cmk2jghn57k5mj0mndbxa4 (2), wnwh6xopwjzk5nyu2zb3nazp (2), s5hpqs (2), p1vn1 (2), wsjk (2), declares (2), location (2), resources (2), uses (2), state (2), remote (2), contains (2), section (2), automate (2), manually (2), infrastructure (2), new (2), branch (2), successful (2), roles (2), format (2), keyscan (2), known_hosts (2), there (2), settings (2), reads (2), must (2), secure (2), cat (2), need (2), make (2), entire (2), both (2), added (2), lets (2), add (2), level (2), either (2), console (2), diagram (2), two (2), _stat (2), statpercountrytables (2), statviews (2), after (2), per (2), particularly (2), useful (2), parameters (2), desc (2), limit (2), offset (2), array_agg (2), define (2), holds (2), playersmostsuccessfultackles (2), playersmostinterception (2), playersmostduelswon (2), playersmostappearances (2), bestdribblers (2), best_passers_columns (2), sponsor (2), total (2), teamtotalgoals (2), football (2), scoring (2), fields (2), constant (2), options (2), clusterby (2), day (2), granularity (2), timestamp (2), datatype (2), field (2), partitionby (2), group (2), goalkeepersstats (2), true (2), isgoalkeeperstatsexist (2), cleansheets (2), team_players_stat_raw_cleaned (2), qatar_fifa_world_cup_dataform (2), applies (2), dataset (2), walk (2), through (2), compiled (2), clicking (2), generates (2), execute (2), boilerplate (2), ready (2), menu (2), started (2), editor (2), integrated (2), generation (2), build (2), channel (2), originally (2), published (2), search (2), place, coders, stay, grow, careers, made, love, 2016, 2026, ruby, rails, powers, inclusive, communities, forem, terms, privacy, policy, mlh, shop, postgres, database, contact, about, showcase, organization, accounts, advertise, help, education, tracks, videos, challenges, home, space, discuss, manage, career, running, locally, gemma, runner, llm, valid, schema, wrong, guard, server, seven, catalogs, reach, python, expert, global, 000, professionals, meet, experienced, technology, influencers, thought, leaders, advice, further, consider, blocking, person, reporting, confirm, child, want, hidden, still, visible, permalink, dismiss, preview, submit, templates, answer, faqs, snippets, template, trusted, user, personal, enjoyed, engineering, world_cup_qatar_elt_dataform_dags, env, json, moves, bucket, gcstogcsoperator, processed, invokes, environment, released, dataformcreateworkflowinvocationoperator, invoke, loads, ndjson, gcstobigqueryoperator, orchestrated, steps, orchestration, 6nax68yrg, c70ry7rrm6w, shows, represented, some, applied, apply, aggregation, personally, find, far, effective, jinja, templating, not, engineers, offers, better, power, implementing, complex, repetitive, programming, language, powerful, teams, working, thanks, even, appealing, collaborative, grade, tight, integration, experience, included, part, reproduce, complete, follows, modern, covering, concepts, concrete, conclusion, host_public_key, user_private_key_secret_version, ssh_authentication_config, default_branch, git_remote_settings, default_database, workspace_compilation_overrides, service_account, world_cup_elt_dataform_repo, google_dataform_repository, resource, configures, 975119474255, secrets, github_account_mazlum_tosun_private_key, latest, constants, these, centralize, values, email, balancer, flexible, required_providers, required_version, pins, persist, infra, configuring, aligns, automatically, pulls, linked, configured, project_number, iam, gserviceaccount, read, grant, role, secretmanager, secretaccessor, accessor, fill, form, earlier, tab, establish, never, kept, times, careful, id_rsa, id_ed25519, commands, depending, pair, authenticate, value, starting, end, line, without, hostname, ed25519, 4c545346, displays, retrieve, expected, prefer, convenient, had, already, reuse, multiple, could, practical, synchronize, below, lineage, component, connects, declare, arrays, loop, argentina_players, argentina, france_players, france, best_passers, top_scorers, goal_keeper, stat_dynamic_tables_and_views, exactly, demonstrate, computing, statistic, suited, kind, letting, programmatically, flexibility, great, feature, ability, write, needed, gave, modeling, covers, context, previously, wrote, names, injects, exported, null, max, return, most, passers, computed, avoid, duplicating, encapsulates, compute, players_most_successful_tackles_columns, players_most_interceptions_columns, players_most_duels_won_columns, players_most_appearances_columns, goal_keeper_columns, best_dribblers_columns, official, providing, kits, current, ranking, tournament, matches, has, played, brand, gear, affiliated, playing, birth, list, object, nested, objects, describe, parameter, references, have, embed, verbose, recommend, clarity, maintainability, embedding, extracted, separate, long, keeps, clean, readable, clustering, partitioning, improve, performance, reduce, cost, current_timestamp, sum, rest, standard, transformation, materialized, similar, nationalteamjerseynumber, false, assists, provided, goal, cleans, light, producing, output, defined, organized, dataformcoreversion, qatar_fifa_world_cup_dataform_assertions, defaultassertiondataset, defaultdataset, defaultlocation, poc, 373711, defaultproject, core, assertion, results, runs, located, root, our, realistic, scenario, visualizes, relationships, between, dependency, graph, access, queries, panel, choose, selection, sample, provides, out, box, set, required, dependencies, install, packages, initialize, assign, any, specify, necessary, permissions, setups, restricted, aligned, principle, least, privilege, page, first, button, deeply, making, seamless, within, ecosystem, getting, easy, helping, ramp, usual, builds, expose, transforms, cleaned, applying, initial, cleaning, standardization, ingestion, applicative, automates, establishes, enabling, driven, implemented, topic, feel, work, focused, related, approach, enables, becomes, advantages, serverless, natively, previous, demonstrated, time, native, solution, based, workflows, explanation, was, posted, sep, mazlum, mastodon, facebook, copied, clipboard, pick, gem, boost, save, jump, fire, raised, exploding, head, unicorn, reaction, close, powered, algolia, navigation, skip,

Text of the page (random words):
stat_raw_cleaned goalkeepersstats as select nationality struct playername appearances savepercentage cleansheets as goalkeeperstatsstruct from team_players_stat_raw where isgoalkeeperstatsexist is true goalkeeperstatsperteam as select nationality array_agg goalkeeperstatsstruct order by goalkeeperstatsstruct savepercentage desc limit 1 offset 0 as stats from goalkeepersstats group by nationality select statraw nationality as teamname nationalteamkitsponsor fifaranking sum goalsscored as teamtotalgoals current_timestamp as ingestiondate goalkeeperstatsperteam stats as goalkeeper team_players_stat_functions build_player_stats goalsscored appearances brandsponsorandused club position playerdob playername as topscorers team_players_stat_functions build_player_stats assistsprovided appearances brandsponsorandused club position playerdob playername as bestpassers team_players_stat_functions build_player_stats dribblesperninety appearances brandsponsorandused club position playerdob playername as bestdribblers team_players_stat_functions build_player_stats appearances appearances brandsponsorandused club position playerdob playername as playersmostappearances team_players_stat_functions build_player_stats totalduelswonperninety appearances brandsponsorandused club position playerdob playername as playersmostduelswon team_players_stat_functions build_player_stats interceptionsperninety appearances brandsponsorandused club position playerdob playername as playersmostinterception team_players_stat_functions build_player_stats tacklesperninety appearances brandsponsorandused club position playerdob playername as playersmostsuccessfultackles from team_players_stat_raw statraw join goalkeeperstatsperteam on statraw nationality goalkeeperstatsperteam nationality group by statraw nationality nationalteamkitsponsor fifaranking goalkeeperstatsperteam stats enter fullscreen mode exit fullscreen mode for the mart step the config block declares a table as the model type config type table description description of the table columns team_player_stat_columns_descriptions columns_descriptions bigquery partitionby field ingestiondate datatype timestamp granularity day clusterby teamname enter fullscreen mode exit fullscreen mode we also added clustering and partitioning options in the bigquery block to improve performance and reduce cost 3 5 mart step column descriptions instead of embedding the column descriptions directly in the sqlx model we extracted them into a separate javascript file this is particularly useful when descriptions are long as it keeps the model clean and readable you have two options for column descriptions embed them in the sqlx model or move them to a js file when descriptions are verbose i recommend the js file for clarity maintainability and readability in the config block the columns parameter directly references the javascript constant that holds the descriptions columns team_player_stat_columns_descriptions columns_descriptions enter fullscreen mode exit fullscreen mode this constant is declared in the team_player_stat_columns_descriptions js file in the includes folder the nested objects top_scorers_columns best_passers_columns are declared in the same file and describe the fields of each struct const top_scorers_columns description an object containing the top scorers fields columns goals total number of goals scored by the top scorers players description list of top scoring players columns playername name of the top scoring player playerdob date of birth of the player position playing position of the player club club that the player is affiliated with brandsponsorandused brand sponsor of the player s gear appearances number of matches the player has played in same pattern for the other statistics const columns_descriptions teamname name of the national football team teamtotalgoals total number of goals scored by the team in the tournament fifaranking current fifa ranking of the national team nationalteamkitsponsor official sponsor providing kits for the national team topscorers top_scorers_columns bestpassers best_passers_columns bestdribblers best_dribblers_columns goalkeeper goal_keeper_columns playersmostappearances players_most_appearances_columns playersmostduelswon players_most_duels_won_columns playersmostinterception players_most_interceptions_columns playersmostsuccessfultackles players_most_successful_tackles_columns module exports columns_descriptions enter fullscreen mode exit fullscreen mode 3 6 mart step reusable function to compute player statistics most player statistics such as top scorers and best passers are computed the same way to avoid duplicating code i created a reusable function that encapsulates this logic in dataform one way to define reusable logic is to create a javascript file in the includes folder here the team_players_stat_functions js file holds this function function build_player_stats statindicator appearances brandsponsorandused club position playerdob playername return struct max statindicator as statindicator array_agg if statindicator 0 or statindicator 0 00 null struct appearances brandsponsorandused club position playerdob playername order by statindicator desc limit 1 offset 0 as players module exports build_player_stats enter fullscreen mode exit fullscreen mode the build_player_stats function takes the column names as parameters and injects them into the generated sql to make it available in sqlx or javascript models it must be exported with module exports i gave more details on this use case and its data modeling in the article i previously wrote on dbt which covers the same context 3 7 mart step dynamic tables and views one great feature of dataform is the ability to write models in javascript instead of sqlx when needed this is particularly useful when you need to generate logic or structure dynamically that s exactly what we demonstrate here after computing the domain data we dynamically generate one view per statistic and one table per country javascript models are well suited for this kind of logic letting us create models programmatically with more flexibility the stat_dynamic_tables_and_views js file const statviews columntoselect goalkeeper viewname goal_keeper columntoselect topscorers viewname top_scorers columntoselect bestpassers viewname best_passers const statpercountrytables countryname france tablename france_players countryname argentina tablename argentina_players statviews foreach view publish view viewname _stat query ctx select view columntoselect from ctx ref team_players_stat statpercountrytables foreach table publish table tablename _stat type table query ctx select from ctx ref team_players_stat where teamname table countryname enter fullscreen mode exit fullscreen mode we declare two const arrays one for the views and one for the tables and loop over each with foreach the publish function then defines each view and table dynamically below is the diagram of the elt pipeline built with dataform with the data lineage showing how each component connects across the workflow 4 connect the github repo to the dataform repo from the console in real world projects dataform code is typically managed in github repositories dataform can synchronize a repository over either https or ssh i prefer ssh as it s both more secure and more convenient with github in this example i had already added an ssh key to my github account which lets me reuse it across multiple repositories you could also add a key at the repository level deploy key but managing it at the account level is more practical in my case to retrieve the github ssh public host key in the format expected by a known_hosts file run ssh keyscan t rsa github com enter fullscreen mode exit fullscreen mode it displays github s public host key github com 22 ssh 2 0 4c545346 github com ssh rsa aaaab3nzac1yc2eaaaadaqabaaabgqcj7ndnxqowgcqnjshclrqpeiiphnt vttvdp6mhbl9j1anuky4ue1gvwnglvlohgeyrnzamgrk6 pkcuxadbc7qtbw8gikhl7agcsor c56sjmy bczfxd1nwzaoxsdpgvsmerobyfnqltv9 hwcqbywinir 5dig6jtj72pcepejcygxke2yefxv1jhnskgblwnlhscqb2umyrkqyytrltl 38tgxkxcflmo 5z8cssny7gidjmiz7q4zmja2n1ngrltdkzwdcsw wqfpgqa179cnfgwowrvruj16z6xyvxvjjwbz0wqz75xk5tksb7fnyeies4tt4jk s4dhpeauc5y bdyirygm4gc7uenztnzyavwq7b381ak4qdrwt51zqexkbqptunn ejqotwvqnj4kqx5quci0ths ykoxjcxmpuwzbhjpcg56i 2ab6cmk2jghn57k5mj0mndbxa4 wnwh6xopwjzk5nyu2zb3nazp s5hpqs p1vn1 wsjk enter fullscreen mode exit fullscreen mode make sure to copy the entire value starting from ssh rsa or ssh ed25519 all the way to the end of the line without the github com hostname next you need the private key of your ssh key pair which dataform uses to authenticate with your github repository use one of the following commands depending on the type of key you generated cat ssh id_rsa or cat ssh id_ed25519 enter fullscreen mode exit fullscreen mode ️ be careful never share this private key it must be kept secure at all times store the private key as a secret in secret manager dataform reads it from there to establish the ssh connection with your github repository open the dataform repository you created earlier and go to the settings tab from there click on connect with git to link your repository to a git provider such as github then fill in the git connection form the ssh url of the remote github repository the default branch e g main the secret version containing your private ssh key from secret manager the ssh public host key in known_hosts format from ssh keyscan to let dataform read the private key from secret manager grant the secret manager secret accessor role roles secretmanager secretaccessor to the dataform service agent service project_number gcp sa dataform iam gserviceaccount com enter fullscreen mode exit fullscreen mode the connection is successful when you create a new dataform workspace it automatically pulls the files and project structure from the linked github repository using the configured default branch typically main 5 connect the github repo to the dataform repo with terraform in this section we automate the github repository link with terraform instead of configuring it manually this aligns with gitops best practices where git is the single source of truth for infrastructure and configuration the terraform code structure the infra folder contains all the terraform configuration files the backend tf file defines the remote state backend which uses google cloud storage gcs to persist the terraform state terraform backend gcs enter fullscreen mode exit fullscreen mode the versions tf file pins the terraform and google cloud provider versions terraform required_version 1 9 8 required_providers google 6 14 0 google beta 6 14 0 enter fullscreen mode exit fullscreen mode input variables are declared in the variables tf file to keep the configuration flexible variable project_id type string variable region description location for load balancer and cloud run resources default europe west1 variable dataform_repo_name description dataform repo name type string variable service_account_email description service account email for the creation of the dataform repo type string enter fullscreen mode exit fullscreen mode the locals tf file declares constants such as the ssh public host key and the secret manager secret version of the private ssh key these locals centralize values used across the terraform configuration locals github_account_host_public_ssh_key_value ssh rsa aaaab3nzac1yc2eaaaadaqabaaabgqcj7ndnxqowgcqnjshclrqpeiiphnt vttvdp6mhbl9j1anuky4ue1gvwnglvlohgeyrnzamgrk6 pkcuxadbc7qtbw8gikhl7agcsor c56sjmy bczfxd1nwzaoxsdpgvsmerobyfnqltv9 hwcqbywinir 5dig6jtj72pcepejcygxke2yefxv1jhnskgblwnlhscqb2umyrkqyytrltl 38tgxkxcflmo 5z8cssny7gidjmiz7q4zmja2n1ngrltdkzwdcsw wqfpgqa179cnfgwowrvruj16z6xyvxvjjwbz0wqz75xk5tksb7fnyeies4tt4jk s4dhpeauc5y bdyirygm4gc7uenztnzyavwq7b381ak4qdrwt51zqexkbqptunn ejqotwvqnj4kqx5quci0ths ykoxjcxmpuwzbhjpcg56i 2ab6cmk2jghn57k5mj0mndbxa4 wnwh6xopwjzk5nyu2zb3nazp s5hpqs p1vn1 wsjk github_account_private_ssh_key_secret_version projects 975119474255 secrets github_account_mazlum_tosun_private_key versions latest enter fullscreen mode exit fullscreen mode the main tf file creates the dataform repository and configures the connection to the github repository over ssh resource google_dataform_repository world_cup_elt_dataform_repo provider google beta project var project_id name var dataform_repo_name region var region service_account var service_account_email workspace_compilation_overrides default_database var project_id git_remote_settings url ssh git github com tosun si var dataform_repo_name git default_branch main ssh_authentication_config user_private_key_secret_version local github_account_private_ssh_key_secret_version host_public_key local github_account_host_public_ssh_key_value enter fullscreen mode exit fullscreen mode conclusion this article presented a real world elt pipeline built with dataform covering key concepts such as staging and mart layers concrete data transformations column documentation and javascript functions for dynamic logic it also included a devops and iac part so you can reproduce a complete hands on setup that follows modern best practices dataform is a powerful choice for teams working with bigquery thanks to its fully managed experience and tight integration with gcp its support for a gitops workflow where github repositories are the single source of truth makes it even more appealing for collaborative production grade data projects personally i find using a programming language like javascript for dynamic logic far more effective than jinja templating javascript may not be the default choice for data engineers but it offers better readability and more power when implementing complex or repetitive logic across your models all the code presented in this article is available in this github repository tosun si world cup qatar elt dataform project showing a use case with an elt using dataform in google cloud world cup qatar elt dataform this repo shows a real world use case with dataform bigquery and google cloud the raw and input data are represented by the qatar fifa world cup players stats some transformations are applied with the elt pattern and dataform to apply aggregation and business transformations the video in english https youtu be c70ry7rrm6w the video in french https youtu be b 6nax68yrg airflow dag elt pipeline orchestration the pipeline is orchestrated by an airflow dag cloud composer with the following steps load raw data to bigquery loads ndjson player stats from gcs into a bigquery raw table using gcstobigqueryoperator invoke dataform workflow invokes the dataform workflow config of the environment released by ci cd using dataformcreateworkflowinvocationoperator move processed files to cold storage moves the input file to a cold bucket using gcstogcsoperator the dag configuration is managed via airflow variables loaded from world_cup_qatar_elt_dataform_dags config variables env variables js...