We want to make PopSQL the best way for data analysts to work with BigQuery data. Many of our users have already connected their BigQuery instances because PopSQL makes it easier to collaborate in real-time and stay aligned. Add to that built-in features such as folders, autosave, dark mode and tab support, and it’s easy to see why they get more done.

That’s why we’re happy to share a few enhancements to the BigQuery + PopSQL experience, including a new built-in cost estimator tool, support for nested columns across schema and result views, and new project filtering capabilities to speed up data discovery.

## See how much a query costs before running it

With this new enhancement, as soon as you start writing a query you’ll see a cost estimate next to the Run button based on how many bytes of data your query will process. Estimates are based on a [standard on-demand pricing](https://cloud.google.com/bigquery/pricing#on_demand_pricing) of $5.00 per TB. 

![Cost](/static/images/blog/bigquery-cost.png)

This new feature has been enabled for all customers on our Business and Enterprise plans.

## Easily dive into nested and repeated column data in one click

We now support RECORD columns in BigQuery to make it easy to examine nested and repeated column data.

In the schema view, you'll see both the main RECORD column like "addresses" in the pictured example and the nested attributes (e.g. "status", “current”, "address", etc).

When you query the data, in the results view, you'll see a summary of the data in the column and from there you can expand to pull up a table of the records.

![Arrays](/static/images/blog/bigquery-arrays.png)

## Focus on what matters, faster, with project filtering

![Filters](/static/images/blog/bigquery-filters.png)

We know that some customers deal with dozens if not hundreds of projects at a time. The result? Connecting to BigQuery and refreshing your schemas can sometimes take a while. What if you could zero in on just the projects you need? Our new project filtering does just that, letting you focus on the data you care about as well as giving you faster load times.

To configure this, go to Preferences > Connections > pick your connection > Advanced > Filter projects.

We hope you enjoy these new enhancements to the overall PopSQL + BigQuery experience. Happy (big) querying!


New ways to focus, work faster and more efficiently with BigQuery using PopSQL

Connecting you to the data insights that power your business is nothing if we can’t help you keep that data secure. At PopSQL, we believe that our commitment to security is a commitment to you. That’s why we’re proud to announce that, as of this month, PopSQL has __achieved its SOC 2 Type II certification__.

## What is SOC 2 Type II compliance?

A SOC 2 Type II report is an audit of how a company’s internal controls safeguard customer data, and how well those controls are operating. With the proliferation of cloud services, businesses around the world rely on SOC 2 reports to assess and address the risks associated with 3rd party technology services and their providers. Completed by independent auditors, these reports cover several trust service criteria categories, [as denoted by the AICPA](https://us.aicpa.org/content/dam/aicpa/interestareas/frc/assuranceadvisoryservices/downloadabledocuments/trust-services-criteria.pdf), relevant to Security, Availability, Processing Integrity, Confidentiality and Privacy.

## Why SOC 2 certification matters

Our SOC 2 Type II audit provides not only an independent assessment of PopSQL’s security control environment, but also full transparency around describing our controls, the tests performed to assess them, the results of those tests, and an overall opinion on our controls’ design and operational effectiveness.

We’re proud to achieve this milestone, as well as of the dedication of our team around the processes, systems and operational rigor to maintain these controls over time. Because it’s not enough to make analyzing your data in PopSQL awesome, we also have to help you keep that data safe.

To learn more about PopSQL’s security posture, see our [Security page](https://popsql.com/security). And to request a copy of our  SOC 2 Type II report, please email us at <a href="mailto:security@popsql.com">security@popsql.com</a>.


PopSQL is now SOC 2 Type II Certified

We're excited to welcome [Databricks](http://databricks.com) to our collection of cloud database connection partners.

Databricks is an industry-leading, cloud-based data lakehouse platform used for processing and transforming massive quantities of data which organizations can then explore through notebooks, analyze with SQL, or develop into machine learning models.

Created by the original creators of Apache Spark™ and Delta Lake, which made having a unified framework for building data pipelines for businesses easy. Databricks pushes that innovation further by providing a cloud platform to help create and easily manage your clusters and explore your business data. As part of their platform, they offer interactive workspaces that expose Spark's native R, Scala, Python, and SQL interfaces.

“The new integration between Databricks and PopSQL makes it that much easier and collaborative for our joint customers across financial services, healthcare and retail to dynamically query, analyze and understand their data sources in Databricks.” says Bilal Aslam, Director of Product, Databricks.

With the addition of Databricks to our growing list of [cloud connections](https://popsql.com/connections), we’re continuing our push to make PopSQL your single platform to take stock of the data that power your business, as well as make collaboration between your technical and business teams a cinch.

So go ahead, connect PopSQL to your Databricks instance today, and let us know what you think.

![Quick look at Databricks](/static/blog/gifs/databricks.gif)

For more information, check out our docs on how to connect to Databricks in PopSQL [here](https://popsql.com/docs/connection-guides/connecting-to-databricks).

Announcing our Integration with Databricks

I'm excited to announce PopSQL's latest round of funding, a $14M Series A round led by [Tiger Global](https://www.tigerglobal.com/), with participation from [Gradient Ventures](https://www.gradient.com/) (Google's AI-focused venture fund), [Y Combinator](https://www.ycombinator.com/), and [FundersClub](https://fundersclub.com/).

This funding will allow us to accelerate our mission to help modern data teams turn business insights into action more efficiently, quickly, and accurately.

As part of this milestone, we are also excited to reflect on our momentum as a company. Since we announced our seed funding in September 2020, we have more than doubled our team and 5x'd our revenue. Now, over 2,000 companies use PopSQL globally – including household names such as Vroom, Redfin, Shipt, Udacity, Ramp, and doodle.

## Why SQL collaboration is broken

Back in 2017, while working at Instacart, I was heavily reliant on querying data to answer questions from stakeholders and to support any business case I had. At the time, legacy SQL editors had the basic functionality of running the query, but didn't help with the next tasks in my workflow: sharing, organizing, collaborating, and aligning on findings as a cross functional team.

Because everyone used their own SQL editor, we had to drop static SQL files over Slack to share our work, export results into Sheets to get charts, jump into data meetings only to find that another peer had already spent hours writing a similar query with different results, and end up storing hundreds of foundational .sql files on our desktops, which were unsearchable by anyone else. And when anyone on the team left, the company lost years worth of queries.

So, what's the impact? Gartner has [reported](https://www.gartner.com/smarterwithgartner/avoid-5-pitfalls-when-building-data-and-analytics-teams) that "through 2023, data scientists and analysts will lose 60% to 70% of their productive time to activities like finding, preparing, integrating and sharing datasets." If you're a VP of Analytics leading a 50-person team, that means you're operating more like a team of 15-20. Not great. What makes this even more costly is that this challenge is ubiquitous across all industries and departments.

Additionally, today's explosion of solutions in the data space – from ETL, warehousing, CDPs, data modeling, and BI tools – has led to an exponential increase in data volume, velocity, and variety. Coupled with remote and hybrid work environments, teams are even more isolated from each other, making it increasingly difficult to find data, understand it, and get answers.

## Bringing teams together with SQL

Many businesses have recognized that realtime collaboration has become critically important to help teams move faster and more cohesively. Designers have Figma, project managers have Notion, and engineers have GitHub. Data focused teams now have PopSQL.

![Screenshot of PopSQL product](/static/blog/series-a/product.png 'Collaborative SQL editor for teams')

With PopSQL, users can easily understand their data model, write version controlled SQL, collaborate with live presence, visualize data, share results, and organize foundational queries for search and discovery. Even if your team is already leveraging a BI tool or a variety of SQL editors, PopSQL enables seamless collaboration between your SQL power users, junior analysts, and even your less technical stakeholders who are hungry for data insights.

Given the gaps in the market, we have also recently added solutions to deliver additional business value for customers, including:

- [Data catalog](http://www.popsql.com/data-catalog) – onboard new hires faster, while keeping definitions at your fingertips
- [SQL notebooks (beta)](http://popsql.com/notebooks) – tell better data stories with the flexibility of cross database joins by referencing query results in subsequent queries
- [dbt integration (beta)](http://www.popsql.com/dbt) – reduce the amount of time required to model data and make dbt more accessible to stakeholders less experienced with git
- [Enterprise-grade security](https://popsql.com/security) – deploy PopSQL within your own VPC in less than 5 minutes with granular access controls, permissioning, SSO, audit logs, and approval workflows

## What's Next at PopSQL

At PopSQL, we believe your SQL editor should do more than just query and deliver results from your database. During this next chapter of growth, we are excited to help our customers continue to harness the power of their data.

Given our aspirations and the work ahead, we are investing heavily in our team. We're currently a tight-knit group of 12 and [hiring a ton](http://popsql.com/jobs)! This is just the beginning and an exciting time for us, so we are looking for a diverse group of people to join us on this journey to provide delightful experiences for our customers.

![Team photo during our retreat](/static/about/group/1.jpg 'Team photo during our retreat')

It's time to expect more from your SQL editor. We are just at the start of what's possible.

<em>If you'd like to learn more about how PopSQL can work with your organization, please email us at [hi@popsql.com](mailto:hi@popsql.com).</em>


PopSQL Raises $14M Series A from Tiger Global to Help Data Teams Collaborate

Importing your query results data into spreadsheets can be a tedious process that involves a lot of manual upkeep and file management. PopSQL's new CSV links allow enterprise customers to pull data directly from PopSQL into their spreadsheet programs like Google Sheets.

Using the Google Sheets `IMPORTDATA` function and a query's CSV link, Sheets will fetch your query results, import them into the spreadsheet, and refresh the results up to every hour. If you reference the imported results in formulas and charts, they will also update automatically as the results are refreshed. For more instructions on using this feature with Google Sheets, check out our [docs on CSV links](/docs/getting-more-out-of-popsql/csv-links).

![Google Sheets IMPORTDATA](/static/blog/gifs/20211208_google_sheets_import_data.gif)

CSV Links and Google Sheets Export

We're happy to announce that we've updated our connection management views. To access
it, you can hit `Manage Connections` in any connection chooser, or open
`Preferences > Connections`. As part of this new view, we have simplified some of the options and
improved wording to help you understand when you have a
[direct connection vs a cloud connection](/docs/getting-more-out-of-popsql/cloud-direct-connections),
finer grained control of [sharing a connection](/docs/popsql-for-teams/shared-connections), helpful
documentation links, as well as exposing enterprise features we offer for our various connectors.
As an added bonus, we now support private cloud connections.

Additionally, as we aim to make PopSQL the best SQL editor out there, we're proud to offer support
for ClickHouse for direct connections. Check our docs for more on how to connect to
[ClickHouse](/docs/getting-started/connecting-to-clickhouse).


New Connections Management Views and Integration with ClickHouse

We've launched a number of updates of our dbt™ Core integration, lifting a number of our [limitations](/docs/getting-more-out-of-popsql/dbt#known-limitations) when we started the beta.

## Branching

One of the most requested features was the ability to handle branching. Now, each user can work in whatever branch they want within your repository, with
any change they make automatically synced back up to your repository. As part of this, we now also fully support all file operations, including creating, renaming, moving, and deleting files and folders within your file structure. Additionally, you can now set custom commit messages when you save a file.

## Syncing dbt Documentation with Data Catalog

We now support a sync of your dbt documentation with your organization's [Data Catalog](/data-catalog). See the documentation you've written for your models show up in PopSQL and be available within autocomplete and the schema browser. Please contact us if you would like this feature enabled.

## Specify dbt run args

When running a dbt model, you can now specify arguments to be passed to the dbt instance when we got to execute the model. These arguments are the same as you would expect to pass if you were using the [dbt run cli command](https://docs.getdbt.com/reference/commands/run).

dbt Updates: Branching, Data Catalog, Dbt Run Args

We've added the ability to use conditional formatting and persistent filters to our data tables. Both of these features
apply anywhere a table is shown, with persistence aimed at tables shown within dashboards.

## Conditional Formatting

Conditional formatting helps you draw attention to certain values in your table. For example, if you have a chart of
net promotor scores, you can use this to color promoters green, detractors red, and passives as orange.

<script src="https://fast.wistia.com/embed/medias/bbzrhpocvc.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:80.83% 0 0 0;position:relative;margin-bottom: 40px;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_bbzrhpocvc videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/bbzrhpocvc/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

## Persistent Column Filters

Now when you use [filters](/docs/getting-started/query-results#results-table) on dashboard tables,
those filters are saved for subsequent users. For example, if your query returns 1000 rows, but you only want to show
rows where `lead_score > 0`, you can set that filter and it'll persist when the next user visits the dashboard.

Conditional Formatting and Persistent Table Filters

Currently when you share a connection, you only have the option to make it private to you, or shared with your whole organization.

Now our enterprise customers can specify exactly which people or teams can access a connection, rather than the whole organization. For example, `Marketing` can access `Data Warehouse`, and `Engineering` can access `Data Warehouse` **and** `Production Replica`.

![](/static/images/shared_connections/granular_permissions.gif)

Other recent improvements related to connections:
* We overhauled the UI for connections
* You can create cloud connections that are private to you


Granular Permissions for Connections

Since PopSQL introduced charting, we have provided a preset palette to use for
the colors of your series. However, many people have asked to be able to provide
their own colors so they can make the chart best represent their data.

We have now introduced the ability to set your own colors for charts, enabled across
all types. For bar, combo, line, and scatter charts, when selecting columns for the
Y-Axis, you will now see a color picker next to the column select. If you use the
"Group By" functionality or pie charts, there will be a new section on the settings
sidebar that gives a list of available series and the ability to select specific colors.

For big number charts, you may set specific colors to use for the chart by setting up some
conditions under the chart formatting menu. You may utilize a range of different numerical
operators over several different conditions to show the big number in the color that fits
your needs.

Please see our [docs](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data)
for more information on using charting in PopSQL.

Specifying your own colors for charts

The [dbt](https://getdbt.com)™ platform has become an important part of the data team's workflow. It was recently voted as the [most popular open source project in data](https://twitter.com/sspaeti/status/1379671780525273090).

For data teams that are using the dbt platform, there is an inefficient workflow between writing a model in your code editor (eg VS Code), using your terminal to compile the model, and then copying and pasting the compiled code into your SQL editor to test things out. With PopSQL's new dbt Core™ integration, **now you can write dbt code right in your SQL editor**.

<img class="rounded-lg" alt="workflow" src="/static/blog/images/dbt_workflow.png" />

For data teams not using the dbt platform yet, our integration is an easy way to get started. If you have SQL that you're often copying/pasting between queries, save that as a reusable snippet/macro and **eliminate repetitive SQL**.

<img class="rounded-lg" alt="macro" src="/static/blog/images/dbt_macro.png" />

Or if you have long SQL queries with multiple CTEs, you can break that down into a dbt model and save it in your database as a view or table to **simplify your BI queries**.

<img class="rounded-lg" alt="model" src="/static/blog/images/dbt_model.png" />

Next, we plan on showing your dbt documentation inside of PopSQL. So if you're writing table/column descriptions in your dbt source files, **we'll show those descriptions in autocomplete as you're writing SQL**, and in our schema browser.

<img class="rounded-lg" alt="dbt docs" src="/static/blog/images/dbt_docs.png" />

Our dbt Core integration is currently in beta and we're gradually rolling it out. If you're interested in trying it, please [sign up for the beta](https://n811kmuga5v.typeform.com/to/lCRSZYeC).

<script src="https://fast.wistia.com/embed/medias/p98ekaexiu.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 2em 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_p98ekaexiu videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/p98ekaexiu/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

More information:

- [popsql.com/docs/dbt](/docs/dbt) for documentation
- [popsql.com/dbt](/dbt) to learn more about the benefits
- [sign up for the beta](https://n811kmuga5v.typeform.com/to/lCRSZYeC?utm_source=updates)


Announcing PopSQL's dbt Core Integration

We recently added more control over how you share PopSQL dashboards. Bigger teams now have more granularity over who can edit or see their dashboards, making it easier to work together.

You can now search for one of your teams and give it edit or view access, or just choose a specific person within your company to share your dashboard with. See our [docs](/docs/getting-more-out-of-popsql/dashboards#sharing-a-dashboard) for the full information.

This feature is now available for our Enterprise customers, feel free to contact us if you want to enable this for your team.

![](/static/images/dashboards/granular-permissions.gif)

Granular Permissions for Dashboards

It is now possible to import spreadsheets from Google Sheets directly into your database using PopSQL!

This gives you the ability to query data from your spreadsheets using SQL, while also leveraging the full power of your database to enable joins across your other tables.

This feature is available now for Enterprise users on Athena database connections. Talk to us if you'd like to enable this on your plan or if you require support for other databases!

Please see our [docs](/docs/getting-more-out-of-popsql/sheets-import/) for full information - enjoy!


Google Sheets Import

We've just added support for markdown formatted text widgets to dashboards in PopSQL!

This gives you an easy way to add some additional context to your dashboards. Use them to add documentation, link to external resources, render images from a URL, organize your dashboard into sections, or anything else you can think of!

Check out the [dashboard docs](/docs/getting-more-out-of-popsql/dashboards) for more information!

Dashboard Text Widgets

Now when you share a [presentation link](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results#presentation-link-for-a-viewer), your stakeholders can refresh the data right from their browser, without needing to hop into the PopSQL editor. Your stakeholders can also edit [query variables](/docs/getting-more-out-of-popsql/how-to-use-variables-in-a-query) such as date ranges or customer IDs.

This works for queries **and** dashboards.

Here's a video demo for queries:

<script src="https://fast.wistia.com/embed/medias/8e28m4cd2i.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 2em 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_8e28m4cd2i videoFoam=true" style="height:100%;position:relative;width:100%">&nbsp;</div></div></div>

And a video demo for dashboards:

<script src="https://fast.wistia.com/embed/medias/v55ikisql4.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 2em 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_v55ikisql4 videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/v55ikisql4/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

For full info, check out our docs on [sharing a query/results](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results), and [dashboards](/docs/dashboards#sharing-a-dashboard).

Presentation Link Improvements

## Multiselect Query Variables

We added a new query variable type called `multiselect`. Use it when you want people to choose one or many values from a dropdown, like choosing regions, or choosing event names. See our [docs on query variables](/docs/getting-more-out-of-popsql/how-to-use-variables-in-a-query#multiselect--find-multiple-event-names) for more info.

<img src="/static/images/query_variables/multiselect.gif" />

## Sorting

As your list of PopSQL queries grows, it can become challenging to find what you're looking for. To help with that, we added sorting to the left sidebar so you can sort your queries alphabetically, created date, updated date, or last opened.

<img src="/static/images/blog/20210118_querylist_sorting.png" class="w-3/5" />

## Zoom

Now you can zoom in PopSQL by pressing `Cmd+`, zoom out with `Cmd-`, or reset your zoom with `Cmd+0`. You can find more keyboard shortcuts [here](/docs/getting-more-out-of-popsql/keyboard-shortcuts).

Multiselect Query Variables, Sorting, and Zoom

## Big number charts
If you just want to show a number in your chart, like the number of users that signed up yesterday, you can use the big number chart:

![](/static/blog/covers/big-number-chart.png)

## Point labels
Sometimes you want to know the exact value of a point in your chart. Now you can do that in PopSQL by checking the 'Show Bar Labels' option.

![](/static/images/blog/show-bar-labels.png)



## Combo charts
You can now create charts that have two y-axes. For example, this chart has a bars representing the number of orders on the left y-axis, and a line representing the number of ratings on the right y-axis.

![](/static/images/blog/combo-chart.png)


Chart improvements: big number charts, combo charts, and point labels

Now you can create comments on a SQL query to discuss changes with your teammates. To add a comment, highlight some text, right-click the text, and click `Comment SQL`. When you write a comment, the query author will get an email notification. Enjoy!

<script src="https://fast.wistia.com/embed/medias/z9w8s8etyn.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_z9w8s8etyn videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/z9w8s8etyn/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

Comments

I'm excited to announce that PopSQL has raised a $3.4M seed round led by Gradient Ventures (Google's AI venture fund), with participation from FundersClub, Y Combinator, and many great angel investors: Max Mullen (Co-Founder Instacart), Calvin French-Owen (CTO Segment), Guillermo Rauch (CEO Vercel), Rafael Corrales (Background.vc), Chris Nguyen (Co-Founder LogDNA), Immad Akhund (CEO Mercury), Oliver Jay (Asana), Jeff Chang (Pinterest), Brianne Kimmel (worklife.vc), and others. These people have helped us every step of the way and I'm immensely grateful to them. This round of financing will help us grow the team so we can pursue our vision of creating the best tool for teams to collaborate using data.

## History

I started PopSQL because I was frustrated with the existing tools on the market. My favorite SQL editor at the time was compatible with MySQL only, so I couldn't use it with our data warehouse. I was saving `.sql` files on my computer and copying/pasting SQL to people over Slack. When I had an insight to share, I had to export the query results to CSV, import to Google Sheets, and share the link with a coworker, but it was only a static snapshot of the data, and it was missing the actual SQL behind it. If the query or results changed, I had to repeat the entire tedious process. There had to be a better way.

So I started coding on the side while working as an engineer at Instacart and launched the initial product on Product Hunt. The results were exciting, and major companies like DoorDash, Redfin, and Shipt started to adopt the product. Next, PopSQL was accepted into Y Combinator, raised a seed round, and now, here we are.

Over the last few months, we've added a ton of value to the product:
* **We improved charting**: now you can create and customize charts right in PopSQL. No need to switch to Sheets to visualize your data.
* **Version history**: Restore your query to previous points in time if you make a mistake. Make bulk changes in your git repo and have them sync to PopSQL using our GitHub integration.
* **Scheduling and alerts**: Schedule your queries and dashboards to run every morning and send the results to Slack so you always have fresh data.
* **Dashboards**: Add multiple queries and charts to see your company metrics at a glance.
* **Search**: Instead of re-writing a query, search if someone else has already written it
* **Query variables**: Make it easy for business users to re-run your query with different variables, like user IDs or dates. Analysts can also use conditional logic using "if" statements.
* **Shared connections**: Make it safe and easy for teammates to access the database.
* **Web application**: Access PopSQL without having to download the desktop app at app.popsql.com.

## What's next?

We have big plans to take PopSQL further. We want to make the SQL editor more powerful: better autocomplete, snippets, and more to give data analysts superpowers. We want to take our collaborative features further with better storytelling, comments, and data dictionaries.

## We're hiring!

If you're passionate about data and want to shape the next generation of data tools, we want to hear from you! We're hiring for engineering: frontend, backend, and full stack. Check out <a href="/about">popsql.com/about</a> for more details.

Onwards! 🚀

Rahil<br />
CEO, PopSQL

Announcing PopSQL's $3.4M Seed Round

## GitHub Integration

We now have a GitHub integration! Once you connect a repo to PopSQL, if you make a change in PopSQL, it gets pushed to GitHub, and if you make a change on GitHub, it gets pushed to PopSQL. This gives you more control of your data. For example, if you need to make a bulk change to your SQL queries (ie a table name change), you can easily do a find and replace, push to GitHub, and the changes will be reflected in PopSQL. This feature is available in our Enterprise plan. Please contact us if you'd like to add it to your plan.

## More Metadata to Help You Understand What's Relevant

As you create more queries in PopSQL, it becomes hard to know which queries are relevant/important, and which queries are deprecated/safe to ignore. To solve that, we've added additional metadata to queries that tell you when the query was written, who it was written by, when it was last run, and more.

<img src="/static/images/blog/20200823_social_context.gif" />

## Improved Search

We improved the search experience so that it's faster to find what you're looking for, and we added additional metadata to show you what's relevant and what's not.

When you first open the search modal with `Cmd+K`, you'll see a list of queries you recently opened. As you start typing, we'll do an instant search for **query names** that match your search term. This is useful when you know the exact name of the query you want.

<img src="/static/images/search/fuzzyfinder.gif" />

For Premium Team users, if you want PopSQL to perform a deeper search to search the **body** of your queries, press `Enter`. This is useful when you want to search if a specific table is being used, or search by a comment you know you wrote. In this example, I'm searching for a [CTE](/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql) I know I've used.

<img src="/static/images/search/advanced_search.png" />

## Other Changes in This Release

* You now have the ability to rename dashboard widgets :tada:
* We simplified the `Share` button

GitHub Integration, More Metadata to Help with Relevance, and Improved Search

[Query variables](/docs/query-variables) let data analysts DRY up their SQL without typing the same values over and over again. More importantly, they allow business users to easily re-run queries with different parameters, like when sales wants to pull a report on a specific account, or marketing wants to pull a report on a specific campaign.

We've improved query variables so that you can:
* Specify a type
  * String
  * Number
  * Date
  * Boolean
  * Dropdown
* Set a default value
* Mark the variable required

![](/static/images/query_variables/settings.gif)

Types are important so your teammates know what kind of value to fill in. For example, you can make a dropdown list:

<img src="/static/images/query_variables/dropdown.gif" />

If someone tries to run your query without filling out a required variable:

<img src="/static/images/blog/20200730_query_variable_required.png" class="w-1/2" />

For full details and lots more examples, please see our docs on [query variables](/docs/query-variables). Enjoy! :tada:

Query Variable Improvements

In this update, we've made it easier to analyze your results, switch between queries, and invite teammates.

## Improved query results table

We improved the [query results table](/docs/getting-started/query-results) to feel more like a spreadsheet. You can now:

* Filter columns
* Sort columns
* Hide columns
* Expand a row to read it in a vertical format
* Highlight cells to copy/paste into a spreadsheet

![](/static/images/query_results/table.mp4)

## Automatically switch connections when you switch queries

You might have some queries that are meant to be run against one connection (eg Postgres), and other queries that are meant to be run against another connection (eg Snowflake). PopSQL will now remember which connection you used with each query and automatically switch connections for you :tada:

## Invitation links

We've added a new way for people to join your PopSQL organization: [invitation links](/docs/popsql-for-teams/inviting-teammates#invitation-links).

![](/static/images/inviting_teammates/copy_invitation_link.mp4)

July 2020 Update

Lots of new features and improvements to announce in PopSQL this month! 🎉

## Public dashboards

Now you can make your dashboards public so people don't have to sign in to view your dashboard.

![](/static/images/blog/20200619_public_dashboards.gif)

## Scheduled dashboards

Now you can schedule dashboards to refresh at a set interval. Perfect for having fresh metrics every morning when your day starts.

![](/static/images/dashboards/schedule-refresh.png)

## Slack notifications for scheduled queries and dashboards

When your scheduled query or scheduled dashboard completes, PopSQL can now send a Slack message with a beautiful preview. Visit our [Slack docs](/docs/slack) to connect Slack with PopSQL.

![](/static/images/blog/20200619_scheduled_dashboard_slack.png)

## Share a link to a specific run

Previously, [presentation links](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results) would show the _latest_ run of your query.
Now, if you click the "link" icon on a run, the URL will be specific to that run. So when you share the link with someone, they'll see the exact same results you see.

![](/static/images/blog/20200619_share_link_to_run.gif)

## Expand rows for better readability

When you hover a row, click the arrow icon to expand the row so you can easily read the row.

![](/static/images/blog/20200619_expand_row.gif)

June 2020 Update

Lots of exciting things to announce today!

## Charts

Since the very beginning of PopSQL, customers have wanted to customize their charts in PopSQL. They liked that we automatically created a chart for them based on their SQL query results, but they didn't like that they couldn't configure that chart, like changing from bar chart to line chart, or choosing which column should be your X-axis.

Today, we're excited to announce that **you can now fully customize your charts in PopSQL!** After running your query, hit the `Chart` button, and you'll be able to create a beautiful chart to your liking. You can choose the chart type (bar, line, pie, etc), choose your X-axis, and choose one or many columns from your SQL query to plot. This saves precious time by eliminating the need to export to Excel just to make a chart.

Learn more about our new charts on our [docs](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data/).

![charts](/static/blog/videos/charts.mp4)

## Draft Queries

When you create a new query in PopSQL, it starts off with the name "Untitled Query" until you give it a name, just like a Google Doc. The problem is, sometimes you're writing an ephemeral query that you have no intention of reusing or sharing, and therefore don't want to give it a name. So you end up with dozens of untitled queries, cluttering your private folder or team folder.

To solve this, **new queries now start off as drafts** and live in a new drafts folder. If you don't care about the query, there's no need to name it. If you do care about the query, give it a name, move it into your folder "My queries," or move it into "Team queries" if you want it to be discoverable by your team. We hope this helps you create a more organized workspace.

## Database Switcher and Draggable Tabs

Two other small but much needed improvements:

If you're connected to a server that has multiple databases, **you can now quickly switch between the databases**:
![database switcher](/static/blog/gifs/20200504_database_switcher.gif)

**You can now drag and rearrange tabs** in PopSQL, just like you can in Chrome:
![draggable tabs](/static/blog/gifs/20200504_draggable_tabs.gif)

New charts, draft queries, and more

New features in 0.9.10 make PopSQL a more robust editor and allow teams to share links to queries with more control.

## Enhanced editor

We've greatly enhanced the query authoring experience. From minimap navigation to clearer error messages, these updates to the editor make query writing smoother and faster. You can customize the experience in your [app preferences](/docs/getting-started/customizing-your-preferences/).

<script src="https://fast.wistia.com/embed/medias/uoibau3ps5.jsonp" async></script></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:55.94% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_uoibau3ps5 videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/uoibau3ps5/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onLoad="this.parentNode.style.opacity=1;" /></div></div></div></div>

## Run all statements

You can now run multiple statements at once. Perfect for faster analysis or chaining together actions (like creating, populating, and analyzing a [temporary table](/docs/getting-more-out-of-popsql/temporary-tables/) in one click).

![run all statements](/static/blog/videos/run-all-statements.mp4)

## Public share links

We have a new `Share` button that lets you get a PopSQL desktop link, web app link, or a presentation link. We also support public links now, so can share your query with people without them having to sign in. Check out our [sharing docs](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results/) for all the details.

![public share links](/static/blog/videos/public-share-links.mp4)

Happy querying! 🍭

Enhanced editor, run all statements, and public share links

We've added many features in 0.9.5 that make PopSQL a better tool for teams and power-users alike.

## Team-Focused Features

### Slack Integrations

Does your team share a lot of links to PopSQL queries? Get the gist of a query without ever leaving Slack with our [new integration](/docs/getting-more-out-of-popsql/slack-integration/).
![alt text](/static/images/slack/unfurl.gif "A PopSQL link shared in Slack now displays key metadata about the query.")

### Query Descriptions

Introducing query descriptions in PopSQL. Bring all the needed context up top where your teammates can see it.
![alt text](/static/images/blog/20200331_query_descriptions.gif "User adding a description to a query in PopSQL")

### Invite Teammates

The more teammates on PopSQL, the more time you save. Now invite all your favorite colleagues in just a click.
![alt text](/static/images/blog/20200331_invite_teammates.gif "User opens the invite teammates feature in PopSQL and quickly sends multiple teammates an invitation.")

## Editor Improvements

### Improved Temporary Table Experience

Use a lot of temp tables? We’ve added [new preferences](/docs/getting-more-out-of-popsql/temporary-tables/) to give you an even better experience in PostgreSQL and Redshift.
![alt text](/static/images/blog/20200331_temp_table_improvements.png "Screenshot of preferences menu that allows user to specify how temporary tables are handled in PopSQL.")

### Attach a CSV to a Scheduled Query

When you [schedule a query](/docs/getting-more-out-of-popsql/scheduled-queries/) in PopSQL, you can now get a CSV of the query results in the notification email. Save a few steps or power a workflow.
![alt text](/static/images/blog/20200331_scheduled_query_with_attached_csv.gif "User schedules a query in PopSQL and specifies results include a CSV attached to the notification email.")

### Connect to Snowflake with Okta / SSO

For teams who use MFA and/or Okta to sign into Snowflake, we have you covered. [Learn more](/docs/getting-started/connecting-to-snowflake/) about connecting with Okta.
![alt text](/static/images/blog/20200331_snowflake_okta.png "Snowflake and Okta logos, announcing that users can connect to Snowflake with Okta in PopSQL")

### Easy Access Preferences

PopSQL now puts your preferences and other key actions right where you need them.
![alt text](/static/images/blog/20200331_easy_access_preferences.gif "A screenshot showing a new access point to preferences menu in PopSQL")


Slack integration, query descriptions, and more

With dashboards, you can see your key metrics at a glance. Add multiple queries and visualizations to a dashboard to stay informed on key metrics.

Choose whether to view the results as data or an [auto-generated chart](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data/). Then rearrange and resize the results however you’d like. And with [query variables](/docs/query-variables/), you can refresh the entire dashboard with a click (view data by day or by week).

Perfect for weekly reports, product launches, marketing campaigns, sales growth, database health... any metric that matters to your team!

![alt text](/static/images/dashboards/dashboards.png)

This feature is available now for Premium Team users. Please see our [docs on dashboards](/docs/getting-more-out-of-popsql/dashboards/) for full information - enjoy! 📊

Dashboards

PopSQL now saves you time, even when you don't have PopSQL open.

With scheduled queries, PopSQL can alert you on important events (think new orders over a certain size, or no new data in the past day). You can also schedule your queries to run at a specific point in time, or on a recurring basis like every hour, day, or week. No more updating queries fifteen minutes before the next meeting; let PopSQL do it for you.

![alt text](/static/images/blog/20200127_scheduled_queries.png "Image of PopSQL modal for scheduling a query")

This feature is available now for Premium Team users. Please see our docs on [how to schedule queries](/docs/getting-more-out-of-popsql/scheduled-queries/) for full information - enjoy!


Scheduled Queries

PopSQL now supports connecting to BigQuery via OAuth. You can still connect via a Service Account if you'd like, but an OAuth connection is faster and simpler.

Connecting to OAuth is supported on the PopSQL desktop and [web app](https://app.popsql.com).

Read the [full guide on how to connect to BigQuery](/docs/getting-started/connecting-to-bigquery/).

BigQuery OAuth

PopSQL makes it extremely easy for your team to switch between databases. Those databases, though, have differences in syntax that are tricky to remember.

Date and time functions have tripped us up in particular. That's why we created this handy cheat sheet for SQL date and time functions across some of our most popular databases: PostgreSQL, MySQL, Redshift, Snowflake, and BigQuery.

<a href="https://popsql-misc.s3.amazonaws.com/popsql-datetime-cheatsheet.pdf" target="_blank">Download the PDF</a>

<embed src="https://popsql-misc.s3.amazonaws.com/popsql-datetime-cheatsheet.pdf" type="application/pdf" width="100%" height="700px">

SQL Date and Time Functions Cheat Sheet

Free samples are easily my favorite thing about grocery stores.

Free sample datasets, though? Those can be awfully dry.

You're guaranteed to need sample data at some point. Perhaps you need to interview a potential analyst but can't use your company's data. Maybe you're tired of your company's data. It's not the same as when you first met it. And you're dying to rekindle that spark that made you love SQL in the first place.

We've scoured the web for some non-boring datasets so you don't have to:

- <a href="https://github.com/timescale/examples/tree/master/crypto_tutorial/Cryptocurrency%20dataset%20Sept%2016%202019" target="_blank">Historical Bitcoin Prices</a>
- <a href="https://tech.instacart.com/3-million-instacart-orders-open-sourced-d40d29ead6f2" target="_blank">3 Million Instacart Orders (Open sourced)</a>
- <a href="https://movement.uber.com/cities?lang=en-US" target="_blank">2 Billion Uber rides (anonymized)</a>
- <a href="https://pleiades.stoa.org/" target="_blank">Database of the Ancient World</a> _(for history buffs)_
- <a href="https://www.kaggle.com/tmdb/tmdb-movie-metadata" target="_blank">5000 Movies Dataset</a>
- <a href="https://docs.atlas.mongodb.com/sample-data/sample-airbnb/" target="_blank">Airbnb listings Data</a> _(for MongoDB)_
- <a href="https://webrobots.io/kickstarter-datasets/" target="_blank">Data on all Kickstarter projects since 2016</a>
- <a href="https://www.kaggle.com/NUFORC/ufo-sightings" target="_blank">UFO Sightings</a> _(can you believe there have been 80,000 sightings and we still haven't taken a good picture?)_

Most of these datasets will be downloaded as a CSV. If you don't know how to import a CSV into a database, we have guides for that too: [PostgreSQL](/learn-sql/postgresql/how-to-import-a-csv-in-postgresql/), [MySQL](/learn-sql/mysql/how-to-import-a-csv-in-mysql), [Redshift](/learn-sql/redshift/how-to-import-a-csv-in-redshift/).

Need more data? Sign up for Jeremy Singer-Vine's newsletter, <a href="http://tinyletter.com/data-is-plural" target="_blank">Data is Plural</a>. He sends 5 interesting datasets every Wednesday.

Stay querying, my friends.


Cool Sample Data

We've been working hard to build the best SQL editor for teams. With release 0.7.8, the two most highly requested databases are now live on PopSQL: Amazon Athena and MongoDB. Learn more on how to connect to [Amazon Athena](/docs/getting-started/connecting-to-athena/) and [MongoDB](/docs/getting-started/connecting-to-mongodb/).

Announcing Support for MongoDB and Athena

Better autocomplete, auto limiting results, and some performance improvements are all new in version 0.6.6 of PopSQL.

## Better autocomplete

​Now suggestions will be more contextual. We'll suggest columns for tables that are in your query. Works with aliases too!

![alt text](/static/images/blog/20190402_better_autocomplete.gif "GIF of autocompleting an inner join")

## Auto limit

​Sometimes you might forget to include a `limit` clause in your query and end up executing an expensive query. Now PopSQL will automatically include a `limit 100` clause for you. Of course you can disable this whenever you want by unchecking the box, or providing your own `limit` clause.

​![alt text](/static/docs/query/auto_limit.gif)

## Performance improvements and bug fixes

Integer sorting is actually works now, and opening or closing `Explore` doesn't take ages anymore. Enjoy!


Better Autocomplete and Auto Limit

Now PopSQL queries are automatically version controlled. You can view unlimited version history of your query and restore to any point in time. This is useful if you or a coworker has made a mistake on your query and you'd like to go back in time.

![alt text](/static/images/blog/20190203_version_history.png "Screenshot of version history")

Version history is available now for Premium Team members. Please see our [docs on version history](/docs/popsql-for-teams/version-history/) for full information 🎉

Version Control for SQL Queries

SQL queries commonly have a `where` condition for a specific ID or date range. Wouldn't it be nice to extract that into a variable? For example, show me users that belong to `$organization_id`, or users created after `$start_date`. If you're sharing SQL queries with non technical folks, what if they could edit the variable without changing the any code?

Now in PopSQL, you can have <strong>variables</strong> in your SQL queries. To create a variable, simply wrap you variable in double curly braces, like `{{id}}`:

![alt text](/static/images/blog/20190110_query_variables.png "Screenshot of a query variable")

This feature is available now for Premium members. Please see our [docs on query variables](/docs/query-variables/) for full information - enjoy!


Query Variables

A common workflow for is to [create a temporary table](/learn-sql/postgresql/how-to-create-a-table-in-postgresql/) to break down your analysis into smaller pieces.

Before, PopSQL would only maintain PostgreSQL and Redshift sessions for 30 seconds. So if you ran one query to create a temporary table and didn't run another query for 30 seconds, the session would be terminated and your temporary table would be dropped. Bummer!

Now, PopSQL will keep your PostgreSQL and Redshift sessions open for 8 hours. So if you run a query to create a temporary table, you can come back up to 8 hours later and it will still be there for you to continue your analysis.

Enjoy!

Temporary Tables

Has anyone written a query on retention? Does anyone use this table or column? How do you use that function again? These are all questions you can answer using PopSQL's improved search capability, now available for Premium Team subscribers.

You'll find a new search box in the top right of the app:

![alt text](/static/images/blog/20180716_search_box.png "Screenshot of the search box in PopSQL")

Clicking the search box will open the search popup. You can also press Cmd+T for faster access. When the search popup opens, we'll show you <strong>recently opened queries</strong> so you can jump back somewhere if you need to. When you start typing, we'll instantly <strong>search queries by name</strong>. You can use your mouse or arrows keys to quickly navigate to a result.

![alt text](/static/images/blog/20180716_recent_history.png "Screenshot of recently opened queries in PopSQL")

For Premium Team subscribers, we'll also perform a deeper search to find <strong>queries that have matching content</strong>. This is where you can see if anyone is using that table or column, or how to use that confusing function, or search by a comment you know you wrote last week.

![alt text](/static/images/blog/20180716_advanced_search.gif "Screenshot of the searching query contents in PopSQL")

We hope these new search capabilities help you get your data faster!

Search

If you're writing code for hours, you want something easy on your eyes. That's why we created dark mode for PopSQL, by popular demand.

![alt text](/static/images/blog/20180520_dark_mode.png "Screenshot of PopSQL dark mode")

Dark mode is available today for [Premium](https://popsql.com/pricing) users. To turn it on, go to `Preferences > Editor > Theme`, or in your menubar, `View > Dark Mode`.

Enjoy!

Dark Mode

Mixpanel is great, but it's very helpful to have your raw event data in your database so you can use SQL to extract insights. In this post, I'll show you how you can accomplish that. We'll be using Ruby on Rails and PostgreSQL, but the general approach of pulling data from Mixpanel, inserting it into the database, and scheduling a background job on an interval should work with any language and database.



## Overview

Here's what we'll cover:

- A service class that is responsible for consuming the Mixpanel API, parsing the response body into JSON, building a database row for each event, and inserting those events efficiently
- A background job that calls the service class
- A scheduler that runs the background job on an interval (eg every minute)

There are some considerations that make this not so simple:

- How to avoid downloading duplicate events from Mixpanel?
- How to convert Mixpanel event timestamps to UTC?
- What to do if the request to Mixpanel fails?
- What to do if your database is slow and the background job takes longer than the interval?
- How can we do this in a memory efficient way?

## Dependencies

Here are some of the technologies we're using:

- <a href="https://github.com/mperham/sidekiq" target="_blank">Sidekiq</a> for background jobs
- <a href="https://github.com/mhenrixon/sidekiq-unique-jobs" target="_blank">sidekiq-unique-jobs</a> to create a lock while the background job is running
- <a href="https://github.com/nickelser/zhong" target="_blank">Zhong</a> for scheduling jobs
- <a href="https://github.com/ohler55/oj" target="_blank">Oj</a> for fast JSON parsing
- <a href="https://github.com/zdennis/activerecord-import" target="_blank">activerecord-import</a> for bulk database inserts
- <a href="https://www.mixpanel.com" target="_blank">Mixpanel</a>...duh :wink:

## The background job

```ruby
class EventWorker
  include Sidekiq::Worker
  sidekiq_options retry: false, unique: :until_executed, log_duplicate_payload: true
  PT = Time.find_zone!('America/Los_Angeles')

  def perform
    from_date = PT.now.hour < 5 ? PT.yesterday : PT.today
    EventService.ingest_mixpanel_events(from_date: from_date, to_date: PT.today)
  end
end
```

We specify `retry: false` because if the job fails, it's okay, let it try again on the next interval.

The `unique` and `log_duplicate_payload` options are provided by `sidekiq-unique-jobs`. This will create a lock and release it only when the job is finished executing. That way, only one of these jobs can run at a time. If someone tries to enqueue this job and one is still running, it will be logged, and it the duplicate won't be processed.

The math with `from_date` is so that when 12:01am strikes, we don't just ignore yesterday's events, because our systems could still push an event that happened minutes ago, or Mixpanel could do the same if they have a delay in their processing. We're effectively creating a 5 hour buffer here. You can adjust this to whatever you like (eg 1 hour, 8 hours, etc).

## The service class

```ruby
# frozen_string_literal: true

class EventService
  def self.ingest_mixpanel_events(from_date:, to_date:)
    ApplicationRecord.transaction do
      # Blow away all the events in this range
      Time.use_zone(PT) do
        query = <<-SQL.squish
          delete from events
          where time between '%s' and '%s'
        SQL
        sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, [query, from_date.beginning_of_day.to_s(:db), to_date.end_of_day.to_s(:db)])
        ActiveRecord::Base.connection.exec_query(sanitized_query, "EventService#ingest_mixpanel_events")
        log "Deleted events between #{from_date.beginning_of_day} and #{to_date.end_of_day}"
      end

      # Ingest the events in this range
      (from_date..to_date).each do |day|
        events = []
        jsonl_string = fetch_mixpanel_raw_data(from_date: day, to_date: day)
        log "Fetched JSONL events from mixpanel for #{day}"
        Tempfile.open(['mixpanel', '.json']) do |file|
          file.write(jsonl_string.force_encoding('UTF-8'))
          log "Wrote JSONL to #{file.path}"
          file.rewind
          file.each_line do |line|
            events << convert_line_to_event(line)
          end
        end

        # Perform the inserts
        ActiveRecord::Base.logger.silence do
          Event.import(events, batch_size: 500)
        end
        log "Imported events for #{day}"
      end
    end
  end

  def self.convert_line_to_event(line)
    raw_event = Oj.load(line)
    raw_time = Time.zone.at(raw_event.dig('properties', 'time')) # This is a Pacific unix timestamp, but Rails doesn't know that
    pacific_time = Time.find_zone!('America/Los_Angeles').parse(raw_time.iso8601.gsub(/Z$/, '')) # Tell Rails it's a Pacific timestamp
    raw_event['properties']['time'] = pacific_time.utc # Convert the timestamp to UTC
    build_event_from_raw_event(raw_event)
  end

  def self.fetch_mixpanel_raw_data(from_date:, to_date:)
    conn = Faraday.new do |conn|
      conn.basic_auth(Rails.application.secrets.mixpanel_api_secret, '')
      conn.request :url_encoded
      conn.adapter Faraday.default_adapter
      conn.options.timeout = 60 # 60 seconds
    end
    resp = conn.get('https://data.mixpanel.com/api/2.0/export/', {from_date: from_date, to_date: to_date})
    resp.body
  end

  def self.build_event_from_raw_event(raw_event)
    # Choose whatever Mixpanel event properties you want in your table
    Event.new({
      name: raw_event['event'],
      time: raw_event.dig('properties', 'time'),
      distinct_id: raw_event.dig('properties', 'distinct_id')
    })
  end

  private

  def self.log(msg)
    Rails.logger.tagged(self.name) do
      Rails.logger.debug(msg)
    end
  end
end
```

There's a lot going on here, so let's try and break it down.

`ingest_mixpanel_events` the main method of the class. It's responsible for deleting events in our database in the `from_date..to_date` range, asking Mixpanel for the events in this range, and performing the database inserts. Why do we need to delete events in this range first? The reason is because <a href="https://mixpanel.com/help/reference/exporting-raw-data#export-api-reference" target="_blank">Mixpanel's raw data export API</a> doesn't give you unique identifiers for each event, so you can't do something like "give me all of today's events, but don't insert ones that I already have." You _could_ do this with timestamps, like "give me all of today's events, but ignore ones up to $last_import_time," but then there's a chance you'll miss some events if you or Mixpanel ever insert events _before_ $last_import_time.

We use a `Tempfile` to store the response body from Mixpanel on disk rather than in an Array, which keeps the memory usage of this method low. Then we iterate over each line and call `convert_line_to_event`.

Everything is wrapped in a transaction because without one, if someone ran a query for today's events and the background job just deleted today's events, then the query would return nothing. But if they ran that query again in a few seconds when the background job has a chance to complete the inserts, then it would have results. Using a transaction prevents this confusing experience.

`convert_line_to_event` is responsible for taking `line` (String), parsing it as JSON, converting the timestamp to UTC, and calling `build_event_from_raw_event`. The timestamp work is necessary because <a href="https://mixpanel.com/help/reference/exporting-raw-data#api-details" target="_blank">Mixpanel stores events in your project's timezone</a>, and it's best practise to store timestamps in your database as UTC.

`fetch_mixpanel_raw_data` is responsible for hitting <a href="https://mixpanel.com/help/reference/exporting-raw-data#export-api-reference" target="_blank">Mixpanel's raw data export API</a> and returning the response body.

`build_event_from_raw_event` is responsible for taking `raw_event` (JSON) and creating an `Event` instance (assuming you have an `Event` model and `events` table).

## The scheduler

```ruby
every 1.minute, 'Run EventWorker' do
  EventWorker.perform_async
end
```

At PopSQL, we use <a href="https://github.com/nickelser/zhong" target="_blank">Zhong</a> to schedule jobs. Simply add a few lines to your `clock.rb` file to run the background job every minute:

## Ways to make this better

How can we reduce the memory footprint of the background job? When I used <a href="https://github.com/SamSaffron/memory_profiler" target="_blank">memory_profiler</a> to measure the memory usage, a lot of it was coming from initializing instances of `Event` for each row, and doing the time parsing/math.

<a href="https://twitter.com/andrewkane" target="_blank">@andrewkane</a> pointed out I could pass <a href="https://github.com/zdennis/activerecord-import/wiki/Examples#import-using-columns-and-arrays" target="_blank">activerecord-import arrays of columns and values</a>, which is its fastest import mechanism, rather than creating `Event` instances.

We could also hit the Mixpanel API _before_ opening the database transaction.

## Conclusion

Now that your database has your Mixpanel events up to the minute, you can use PopSQL to extract all kinds of insights. Who are the newest users? Which users activated or need help activating? Who's taking high value actions on the site right now that sales should follow up with? Enjoy, and feel free to chime in in the comments below.

<em>Thanks to <a href="https://twitter.com/octernion" target="_blank">Nick Elser</a> and <a href="https://twitter.com/andrewkane" target="_blank">Andrew Kane</a> for guidance and suggestions.</em>

Blog