Cloud Intelligence™
Airflow and superQuery
This page is also available in Deutsch, Español, Français, Italiano, 日本語, and Português.
About Eben Du Toit
I lead engineering teams in the Partner Channel space, building the tooling that helps customers make sense of their revenue — turning complex partner and billing data into something they can actually see, trust, and act on. The work sits at the intersection of cloud cost management, FinOps, and billing integrations, with data flowing across GCP, BigQuery, MongoDB Atlas, Snowflake, and the wider cloud ecosystem. It's technically meaty, and I love that about it.
My days are split between people and systems — growing and aligning multiple teams, helping the team navigate the environment, hiring, and partnering with stakeholders across the org. I shift between maker mode and manager mode depending on what's needed: sometimes I'm deep in the code building features, sometimes I'm in the room helping my teams move forward. I care a lot about building teams where good engineers can do their best work.
What I work on
Partner Channel engineering and revenue management tooling · Cloud cost management and FinOps · Billing data pipelines and integrations · Engineering leadership, hiring, and team process
Good topics to find me for
Anything Partner Channel, revenue, FinOps, or billing-related · Data engineering and pipeline architecture · Engineering management, hiring, or team culture
Outside work
When I'm not in a terminal, I'm usually behind a camera — shooting on a Nikon Z or whatever vintage film body I'm currently infatuated with. I follow mountain biking and trail running closely, have strong opinions about mechanical keyboards and good stationery, and watch more anime than I probably admit. I also play Rocket League with more optimism than results.
Cape Town · SAST (UTC+2)
My personal page
Monitoring your BigQuery costs in real-time with the SuperQueryOperator
Photo by Soheb Zaidi on Unsplash
“What is the cost?” A question asked so frequently in the tech world that every person at a small start-up shudders slightly when it is asked. The answer of which invariably is: “We’re not sure”.
One of the best tools for scheduling workflows in the data engineering world is Apache Airflow. This tool has taken many a business out of the inflexible cron scheduling doldrums into riding the big data waves on the high seas of Directed Acyclic Graphs (DAGs).
Of course this means that large globs of data are being moved into and out of databases and with this glorious movement often comes unavoidable costs.
One such database, a supercomputer if you will, is called Google BigQuery. It is the flagship of the Google Cloud offering and allows data processing at the Petabyte scale. It is very good at making you worry less about the power of the database infrastructure and more about the quality of your analysis and data flow problems in need of solving.
One key factor to consider with BigQuery is how open any individual or organisation is to driving up costs spent on scanning data on this platform. Even the most savvy of data engineers will tell you in angst about their errors in scanning across data they didn’t really want to and pushing their business’ monthly analysis bill over the budget.
In steps superQuery. The philosophy offered by superQuery is that you don’t have to worry about your costs because you have all the information you need and some safeguards that give you the opportunity to make an informed decision.
PerfectScale™ for Kubernetes
Ready to optimize?
Get your free Kubernetes savings analysis
Knowing your costs in Airflow
While your Airflow DAGs are churning away happily and pushing data to your processing systems of choice, a heap of logging happens in the background. Airflows logs are easily accessible, simple to read and give you a good overview of what your DAG is doing. Wouldn’t it be great if the log files could also show you information on your query execution plan and specifically what the cost and total data scanned was? For sure it would! Something like so:
--------------------------------------------------------------------Starting attempt 1 of 4--------------------------------------------------------------------
[2019-03-11 21:12:02,129] {models.py:1593} INFO - Executing <Task(SuperQueryOperator): connect_to_superquery_proxy> on 2019-03-01T00:00:00+00:00[2019-03-11 21:12:03,836] {superq_operators.py:54} INFO - Executing: #standardSQLSELECT COUNT(testField) FROM `mydata.PROD.myTable`;[2019-03-11 21:12:03,844] {logging_mixin.py:95} INFO - [2019-03-11 21:12:03,843] {base_hook.py:83} INFO - Using connection to: id: mysql_default. Host: superproxy.system.io, Port: 3306, Schema: None, Login: XXXXXX, Password: XXXXXXXX, extra: {}[2019-03-11 21:12:15,172] {superq_operators.py:68} INFO - (('{ "startTime":1552331525642, "endTime":1552331534624, "executionTime":"8988", "bigQueryTotalBytesProcessed":26388279066, "bigQueryTotalCost":"0.12", "superQueryTotalBytesProcessed":0, "superQueryTotalCost":"0.00", "saving":0, "totalRows":"1",}', '', '1', 'true'),)[2019-03-11 21:12:17,121] {logging_mixin.py:95} INFO - [2019-03-11 21:12:17,119] {jobs.py:2527} INFO - Task exited with return code 0This chunk of log tells you that your BigQuery operator in Airflow scanned across 24Gb of data and it cost you $0.12. Simple. You can also go ahead and parse the log files in a 3rd party tool or a bash script and create a summary of the costs originating from DAGs that scan data from BigQuery.
How does this all work?
SuperQuery uses a MySql proxy in order to make connectivity universal and offer a SQL interface to get information.
What you need next: The SuperQueryOperator
There are the steps involved in order to get the same functionality described above:
- Add the superquery plugin to Airflow in order to use the SuperQueryOperator.
- Subscribe to superQuery for a trial and get login details for the superQuery MySql proxy
- Test your connection to the proxy with the DAG provided below.
- Replace your BigQuery operators with the SuperQuery operator in your own DAGs when you want to use this functionality.
Your cloud bill shouldn't be a mystery
Optimization, automation, expertise. In one platform.
Using the SuperQueryOperator
This is the interface for the SuperQuery operator:
TEST_SQL = """#standardSQLSELECT COUNT(*) FROM `mydata.PROD.myTable`;"""SuperQueryOperator( task_id="connect_to_superquery_proxy", sql=TEST_SQL, database="", explain=True, # False if you don't want information dag=dag)Here is the code for the operator which you should copy into the pluginsfolder in Airflow:
https://gist.github.com/super-eben/b1d49538b46fa3f55c5bddfe73405b34
Here is some code to test your connection to SuperQuery with:
https://gist.github.com/super-eben/dcfa4420c419331e5a12a8b23e0a088c
In summary
This post describes how you can get a view of your Airflow costs when you connect and implement Airflow tasks to and from BigQuery. The use of the SuperQuery proxy can be extended to include a wide variety of detailed execution plan information and draw on the benefits the system provides.
Happy cost monitoring (and saving)!
