Count
count.co
  • Getting Started
    • What is Count?
    • Count FAQ
    • Intro to your workspace
    • Example canvases
    • Getting started guides
      • Set up your workspace and projects
        • 1. Review workspace settings
        • 2. Create and organise your projects
      • Canvas orientation
      • Your first ad hoc analysis
        • 1. Examples and templates
        • 2. Build your first queries
        • 3. Create visuals
        • 4. Caching, local cells and scheduling
        • 5. Collaborating with a stakeholder
      • Your first report
        • 1. Examples and templates
        • 2. Filters and control cells
        • 3. Sharing your report
        • 4. Alerts
  • Connect your data
    • Database connection overview
      • Athena
      • Azure Synapse
      • BigQuery
      • Databricks
      • Microsoft SQL Server
      • MySQL
      • PostgreSQL
      • Redshift
      • Snowflake
    • Refresh database schema
    • Upload CSV files
    • dbt integration
      • ☁️dbt Cloud integration
      • 👩‍💻dbt Core integration
  • Import & Export
    • Import from other tools
      • Import Miro files
      • Import SQL files
      • Import Google Sheets
      • Import Jupyter notebooks
    • Export code and results
      • Export compiled SQL and Jinja-SQL
      • Export CSV files
      • Export images and PDF files
  • THE CANVAS
    • Navigating the canvas
      • Canvas tool bar
      • Data sidebar
      • Customizing the canvas
    • Canvas objects
      • Cells
      • Text and markdown
      • Shapes and tools
      • Sticky notes
      • Frames
      • Images
      • Embeds
      • Stamps
      • Grouping objects
      • Object order and alignment
      • Locking objects
      • Scaling objects
      • Shared styles
    • Overviews
    • Templates
    • Count AI
    • Alerts and subscriptions
      • Slack integration
    • Keyboard shortcuts
  • Querying data
    • Cells overview
      • Dynamic query compilation engine
    • SQL cells
      • Referencing other cells
      • Jinja templating
      • SQL formatting
    • Python cells
    • Visual and low-code cells
      • Calculations in visuals and low-code cells
      • Joins in visuals and low-code cells
    • Control cells
      • Single and multiple selects
      • Date controls
      • Text, number, and boolean controls
      • Custom control cells
    • Local DuckDB cells
      • DuckDB on the server
    • Query caching and scheduling
    • Manage queries and results
    • Troubleshooting
  • Visualizing data
    • Visualization overview
    • Templated visuals
    • Custom visuals
      • Marks
      • Facet
      • Subplots
      • Style
      • Filters
    • Formatting a visual
      • Axes
        • Secondary Axis
      • Colors and labels
      • Legends
      • Tooltips
    • Column summaries
    • Dynamic text
    • Every Visual Under the Sun
  • Presenting and Reporting
    • Reports and Slides
  • Count Metrics
    • Intro to Count Metrics
    • Build and edit a catalog
    • Views
      • Creating views
      • Customizing views
    • Datasets
      • Creating datasets
    • Save changes to the catalog
      • Catalog validation
      • Version control
    • Exposing catalogs to the workspace
    • Caching in Count Metrics
    • Using the catalog
      • Explore from cell
  • Sharing and Permissions
    • Real-time collaboration
    • Comments
    • Sharing permissions
    • Shared links
    • Embedding canvases
  • History and Versions
    • Version control
    • Duplicating and merging
    • Data snapshots
  • Settings and administration
    • Workspace settings
      • Workspace members
      • Groups
      • Tags
      • Billing
      • Single sign-on (SSO)
        • Okta
        • Entra ID
        • JumpCloud
        • Google
        • Generic OIDC
      • Brand
    • Connection settings
    • Project settings
    • User settings
    • Roles and permissions
  • Quick guides
    • Interactive control guides
      • Date ranges
      • Date groupings
      • Search
      • Select All
  • Resources
    • Join the Slack community
  • Blog
  • Security overview
  • Terms of use
  • Pricing FAQ
Powered by GitBook
On this page
  • SQL query results appear different between Count and another tool
  • Differences in client connection details
  • Differences due to execution time of the query
  • Differences due to result representation
  • Differences due to non-deterministic queries
  1. Querying data

Troubleshooting

A guide to resolving issues with queries

SQL query results appear different between Count and another tool

In performing a consistency check between Count and another tool, you may try running the same SQL query in both tools. Infrequently you may find the results of both queries appear different. The inconsistency is almost always the result of a subtlety of the SQL language or a difference in connection details. Below is a list of things to check before contacting Count support.

Differences in client connection details

If the user credentials attached to the Count connection and the other tool are different, please check that:

  • Both point to the same database/scope; modern data stacks often reproduce production schemas in a staging environment, so you may be accidentally querying different tables.

  • Both user credentials have the same level permissions for the tables being queried; this is particularly important if your database supports row level permissions.

As Count supports querying of multiple database connections within the same canvas, please check that the cell refers to the same database instance being queried in the other tool.

Differences due to execution time of the query

The time at which the query is executed can lead to different results between Count and the other tool in two ways:

  • At high load times your query may fail (in either tool), leading to an error for one query but not the other. Please interpret any errors returned and try performing both queries at a similar time.

  • For transactional databases, tables may be appended to at any time. As a result, both query results may not exactly match. Consider creating a snapshot table within the database and performing consistency checks on that instead.

Differences due to result representation

Count currently doesn't support the representation of object and nested datatypes within the Count UI. Columns containing this type of data will appear as a JSON-stringified representation of the data.

Differences due to non-deterministic queries

A deterministic query returns the same results each time it's called. A non-deterministic query may return different results each time it's called. Some queries are obviously non-deterministic e.g. SELECT CURRENT_TIME, but others may be non-deterministic due to a subtlety of the implementation of the SQL language by your database. Please check the query executed, including any SQL used to generate views referenced in the query. Below is a list of common causes of non-deterministic queries.

Non-deterministic functions

  • Time/date related functions may return different values in successive calls, and if used as a filter in your query, a different number of rows for your query. These functions include (but not limited to):

    • CURRENT_TIME, CURRENT_DATE, CURRENT_DATETIME, CURRENT_TIMESTAMP, or equivalent.

  • Some functions by definition return different values in successive calls, or are dependent on configurations in your database environment. These functions include (but not limited to):

    • GENERATE_UUID, SESSION_USER, RAND, or equivalent.

  • Some window functions are non-deterministic if they are navigational in nature when used without an ORDER BY clause within the OVER() clause. Some databases error when these functions are used without an ORDER BY clause, but may not check that the chosen ordering is appropriate. These functions include (but not limited to):

    • FIRST_VALUE, LAG, LAST_VALUE, LEAD, NTH_VALUE, PERCENTILE_CONT, PERCENTILE_DIST, CUME_DIST, RANK, NTILE, or equivalent.

Non-determinism due to floating point accuracy

Not all number types in databases can be stored with exact precision. This can lead to non-determinism when:

  • The number exceeds the maximum supported value of that data type.

  • Calculations using that magnify any floating point round-off errors, e.g. by subtracting two very similar floating point numbers.

Please consult your database documentation to find the bounds and precision of the data types used.

Non-determinism due to ordering

The SQL standard does not order query results for SQL statements without an ORDER BY clause. This detail commonly manifests itself in two ways:

  1. Executing the same SELECT * or SELECT column_name, ... query multiple times against a table may return results in a different order. This is to be expected.

To work within this restriction and obtain the quantity of data you require, consider performing any ordering operations, aggregations and filters on the original database query before referencing in local cells.

Please do contact Count support if this limit is still restricting your analysis.

PreviousManage queries and resultsNextVisualization overview

Last updated 1 year ago

All database queries in Count are by default limited to the first batch of 10,000 rows. Successive SELECT * or SELECT column_name, ... queries may return different batches of data. This is particularly important if these cells are referenced within local ( or ) cells or visuals.

When a Count cell is only showing a subset of the query result data, a label is shown in the bottom-right of the cell (or upper-right of a visual). Read more about how to handle these cases .

If you are having trouble interpreting the difference between two sets of query results, to us to schedule a support session

DuckDB
Python
reach out
here